Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

compatibility level Expand / Collapse
Author
Message
Posted Monday, April 28, 2008 5:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:19 AM
Points: 21, Visits: 238
When I upgrade databases from SQL Server 2000 to SQL Server 2005 do I need to change the compatibility level option to SQL Server 2005 after the upgrade?
Is it best practis to change the compatibility level and is it any risk to change the compatibility level?
Post #491295
Posted Monday, April 28, 2008 6:00 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
Do not change compatibility level unless and untill you thoroughly test it in your test environment.

Keeping the 2000 compatibility won't hurt anything. But if you will change it to 2005 your Stored Procedures MIGHT give some different results as it will take all settings of 2005.


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #491308
Posted Monday, April 28, 2008 8:50 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Wednesday, October 22, 2014 12:34 PM
Points: 31,181, Visits: 15,626
The database is structured the same in both cases, but in different modes, it behaves fairly closely to that version in terms of code, keywords, etc. I believe that a 80 mode database could still run newer joins available in 90, but they keywords and hints wouldn't work.

Change it only after you've tested things.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #491488
Posted Monday, April 28, 2008 9:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, Visits: 401
I have wandering around the net for some answers for a while now but I still have the same questions what about performance and things like that? does the compatibility level have a impact on this? and what do you mean by saying that some procedures won't have the same results? :S
Thanks
Post #491534
Posted Monday, April 28, 2008 9:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390
To see examples of what might break between earlier versions of SQL against 2005, try this link: http://geekswithblogs.net/influent1/archive/2007/01/04/102774.aspx

There is one simple example of getting different results depending on compatibility levels right at the bottom of the page.

Paul
Post #491552
Posted Tuesday, April 29, 2008 7:01 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
I'm currently working on a 2000 to 2005 conversion myself. The biggest thing I have found is that the stored procedures that the vendor wrote do not fully adhere to ANSI standards. For example, I am having to re-write portions of these procs to remove *= and =* in favor of OUTER JOIN. The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.
Post #492051
Posted Tuesday, April 29, 2008 7:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:02 AM
Points: 339, Visits: 390
Which is why it is very important to perform an analysis using the migration wizard as a first step!

Paul
Post #492066
Posted Tuesday, April 29, 2008 7:35 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 3,966, Visits: 3,645
jim.powers (4/29/2008)
The unfortunate thing is that simply importing the database to 2005 and changing the compatibility mode to 90 won't tell you of these types of problems. You won't know there is a problem until you try to run the proc the first time.


Yes as Paul points out the SQL Server 2005 Upgrade Advisor works very well for this purpose. Also try the BPA (Best Practice Analyzer) as it gives some valuable information as well.

http://msdn2.microsoft.com/en-us/library/ms144256.aspx
Post #492087
Posted Tuesday, April 29, 2008 7:37 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:11 AM
Points: 1,330, Visits: 815
The Upgrade Advisor is exactly what told me of the problems that would need to be corrected in advance. Our vendor appears to be useless...
Post #492092
Posted Tuesday, April 29, 2008 7:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 3,966, Visits: 3,645
Some vendors are like that. But be sure and test thoroughly as there will most likely be things that you encounter that will cause some problems. We have several developers who do not pay attention to column length. SQL 2000 would truncate the data if you tried to insert values larger than the column. SQL 2005 will throw an error. The upgrade advisor will not uncover those types of problems.
Post #492116
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse