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?

more ▼

asked Aug 06, 2012 at 12:11 PM in Default

avatar image

10 2 2 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Aug 06, 2012 at 01:51 PM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

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?

Aug 06, 2012 at 02:54 PM UT6

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.

Aug 06, 2012 at 03:07 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Aug 07, 2012 at 06:08 PM

avatar image

15.6k 22 55 38

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 06, 2012 at 12:11 PM

Seen: 1427 times

Last Updated: Aug 07, 2012 at 06:08 PM

Copyright 2018 Redgate Software. Privacy Policy