question

chitrarekha.saha avatar image
chitrarekha.saha asked

Compatibility level in sqlserver 2008R2

We are on sqlserver 2008r2 and my guess is during the upgrade from sql 2005 to 20008r2, some of the databases has the Compatibility level SQL Server 2000(80) and some SQL Server 2005(90). Is this right? Do I convert all to compatibility level SQL Server 2005(90)? What would be the impact if I converted to SQL Server 2005(90)?
sql 2008r2
1 comment
10 |1200

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

This web site runs on voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next to that answer.
0 Likes 0 ·
sp_lock avatar image
sp_lock answered
Hi, I would advise the following **before** upgrading/migrating. - Run the upgrade adviser to check for any SQL specific issues - Where applicable check with the application vendor if they support 2008R2 - Test the upgrade. In my opinion the most important one. If you have already done the migration, then I would go with the testing option. Especially after been caught out by the compat setting in my early years.
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
I think @sp_lock largely has this nailed. The one thing I would add to it is that yes, you should ensure that, over time, all the databases get updated to the new compatibility level. That will allow them to take advantage of the advances in SQL Server, one of the main reasons why you would upgrade. But, as @sp_lock says, test this carefully to ensure you don't hit issues. But automatically, your databases will be in the old compatibility level. You'll need to change that yourself. I'd also do a full update on statistics with full scan. This will happen naturally over time, but might as well get it done as part of the upgrade.
10 |1200

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

CirqueDeSQLeil avatar image
CirqueDeSQLeil answered
You can run the upgrade adviser. Sometimes that will unveil issues, sometimes not. You can switch back and forth on compatibility mode all day until the cows come home. It is an easy change. If you decide to move to the current compatibility mode supported by your version of SQL Server and don't like how something is working, switch it back. Document what the effect was, when it happened, where it happened and work towards resolving it. I have had much more success with that approach than with the upgrade adviser.
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.