question

UT6 avatar image
UT6 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?
sql-server-2008sql-server-2005sql-server-2000
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
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.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
But what about putting Database in compatibility mode? The compatibility mode should protect the application from breaking right? Sorry, but how does application connectivity change?
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.