-1

We are planning to change Compatibility level of databases from 90 to 100.

Would this cause any impact on application side?

If yes then please guide me, what steps or measures we need to look upon and take care of.

Details:

This would be a production server having

  1. Cluster setup

  2. Around 20 databases which are running 24/7.

  • Have you / are you going to test this in non-production environment first? Why has the compatibility level been set to 90, was there an issue and has it been fixed? Have you read BOL for the things related to compatibility level changes and checked that those are not an issue? – James Z May 22 '15 at 14:56
4

Would this cause any impact on application side?

Even if you run BPA or Upgrade Advisor, they are not going to catch everything that would potentially break your application (if it is using deprecated stuff).

The best way is to take a full backup of your database in question, restore it on a test machine with newer compatiblity mode and do a regression test of your application. Make sure you do a full business cycle test - keeping in mind that there are always some reports that are run at end of month or very rarely, but are important enough not to be broken.

You can read - What is the actual behavior of compatibility level 80? and Clinging to old compatibility levels - from Aaron Bertrand.

Edit:

From Best Practices about changing compatibility Level for Backward Compatibility

Changing the compatibility level while users are connected to the database can produce incorrect result sets for active queries.

click to enlarge

enter image description here

0

Start by checking your T-SQL syntax and reserved keywords. The differences between compatibility levels are documented in BOL: https://msdn.microsoft.com/en-us/library/bb510680(v=sql.105).aspx

  • 3
    Can you add more to your answer ? Link only answers are discouraged here. – Kin Shah May 22 '15 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.