compatibility level

  • 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?

  • 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."

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

  • 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

  • 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

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

  • Which is why it is very important to perform an analysis using the migration wizard as a first step!

    Paul

  • 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

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

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

  • When we upgraded from 2000 to 2005 we tested running everything in 2000 compatability mode. We did not see any degredation in performance. I will concede that we do not have tera byte databases, but we do have some databases in the double and triple didget gigabyte range. We have moved internal databases to 2005 after completing thorough testing in development.

    We wait until vendors certify their software on 2005 before moving their databases. We still perform testing in development first. The major reason for waiting for vendors to certify is that they generally will not support the product if there are issues and they have not certified.

    Q

    Please take a number. Now serving emergency 1,203,894

  • Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.

  • jim.powers (4/29/2008)


    Interestingly enough, our vendor has certified the product for SQL Server 2005 but they require the database to remain in compatibility mode 80.

    Actually, that is what we did as well since we had to have a version that was backwards compatable to SQL 2000 but that we certified to run on SQL 2005. So that is not uncommon. Since it had to be backwards compatable we could not use any SQL 2005 features and it had to run compatibility mode 80.

    Generally we find that performance over all is better on SQL 2005 but we do find several queries that run considerably slower on SQL 2005. We have opened up incidents with Microsoft on these and they admit there are some issues with complex queries involving outer joins. We have had to add some Force Order query hints to resolve them until we can rewrite the queries.

  • What's the point of that! Unless they are not 100% sure that everything will work in 90 mode? Don't trust your vendors and fully test your system yourself I would say 😛

  • We are the vendor for our product. Our requirement was that the product still had to run on SQL 2000 and also on SQL 2005. We discovered through testing that the behavior of certain queries was not exactly the same on level 90 as level 80 which forced us to run on SQL 2005 on level 80 for one version.

    As a vendor you always have new customers and early adopters that want to run on SQL 2005 and a few customers you have to drag kicking and screaming to the new platform. They are the lowest common denominator . So you have to create an upgrade path that allows the late adopters to get there.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply