July 20, 2015 at 7:23 am
Hi guys,
Just want to double check something. I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.
I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues?
I've already taken a look at the following:
https://technet.microsoft.com/en-us/library/ms143179(SQL.110).aspx
https://msdn.microsoft.com/en-us/library/cc707785(v=sql.110).aspx
and I think we're pretty solid on this but would like something official like the upgrade advisor report
I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
Thanks
July 20, 2015 at 7:35 am
Being in compat mode 90 does not stop you from using 2012 features. The compat mode is only there to tell the query processor how to handle syntax where the behaviour has changed between 2005 and 2012 and, in the case of SQL 2014, to tell which version of the cardinality estimator to use.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2015 at 7:41 am
Hi Gila - In terms of testing what the implications would be for changing the compatibility mode from 90 to 110, what would be the best way of verifying that this would work correctly.
Its an application database, so from previous experience I've never had to worry about whether the application worked or not, but I'm on the other end now where I have to make sure that the application still works correctly.
Is it just a case of trial and error with the app or is there some other 'nice' ways this can be tested?
July 20, 2015 at 7:52 am
Test your app end to end. I've recently used distributed replay to do similar, though I was looking for performance regressions due to the optimiser changes in SQL 2014.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2015 at 7:56 am
No Gila, you're supposed to say just flick it over to 110 and everything will be fine 🙂
Thought as much anyway, it should be ok as I have some good friends in our QA team who will only be too happy to go through that!!
Before I get into the app testing I might do a quick scope of the breaking changes as outlined in
https://technet.microsoft.com/en-us/library/ms143179(SQL.110).aspx
Other than app testing end to end, can you see any other possible pot holes in upgrading the compatibility level from 90 to 110?
July 20, 2015 at 8:03 am
To be honest, that's one of the fairly safe upgrades. 2000 -> anything is one that's very likely to have something break. Anything -> 2014 needs testing because of the cardinality estimator changes. 2005-2012 should, in general, in most cases, be OK.
If you feel comfortable not testing comprehensibly and just going on the docs and what you know of the app, you may well be fine. And if something does break, you can always drop the compat mode back down to 90 until you can fix it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 20, 2015 at 8:05 am
Cheers Gail
July 21, 2015 at 8:38 am
Could you please let us know if you ever do come across any issues?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply