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)?
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.
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.
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.