database Compatibility 90 to 100 changing causes any problem

Question

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.

Answer 1

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

Answer 2

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