asked
Database compatibility and application testing
If I upgrade a SQL Server version from 2000 to 2005/2008 version(Standard Edition) but leave database kept at 80(SQL 2000) compatibility mode, would it be possible to say that it wont break existing application connecting to this instance. This upgrade is essentially for memory issues in 2000 Standard Edition max'ed at 2GB usage. The applications connecting are legacy applications struggling on Memory limit. Would a minimal testing be ok for above upgrade of SQL Version?
No. Application connectivity will be changed. This means that full testing would be required. I would not recommend, or suggest, that existing applications would be unaffected. In fact, I've seen more issues going from 2000 to 2005/2008 than any other upgrade path (2005 to 2008, 2005 - 2008R2, 2005 - 2012, or any other combination). What you can say is that the internal database structures and code, the ones that passed the Upgrade Advisor checks (you ran that first, right?), will behave the same way. But external application connectivity and behavior, no, you can't say that. You will need to test this.
Nope. The compatibility mode ensures that the database itself behaves as if it were an on an older version of SQL Server. That will ensure some application behavior will be consistent, but not all.
Grant is completely correct, of course. You need to test this. I will say that I have done something similar in the past and (after setting the compatibility mode) the only thing that needed to be changed on the application side was the connection string. But this is hardly gaurunteed and it needs to be tested thoroughly before moving any database that an important application is relying on.
9 People are following this question.
