x

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

UT6 gravatar image

UT6
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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.8k 19 21 74

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

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x1834
x473

asked: Aug 06, 2012 at 12:11 PM

Seen: 1043 times

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