Why and When to change database compatibility level?

Personally I find changing database compatibility is a vital and often forgotten task after migration in many places. To understand the importance to change the compatibility, below points may be noted:

1. Changing compatibility will need developers to rewrite the obsolete part of the TSQL/procedures. This is good because the application will be written using up-to-date technologies and there will be no risk of obsolete code related failure with another immediate upgrade.
2. By using latest level, database will be full compatible with server to take all the advantages from new SQL engine. (For example, if you are running databases in compatibility 80 in 2005/2008, you will not be able to generate all reports from the database using DMV)
3. You will be in-compliance with current recommendations from Microsoft.

Ideally database compatibility should be changed immediately with the migration. Otherwise if you decide to allow a database to run on an older compatibility mode, there is a good chance that this task will be infinitely delayed by other priorities. Following below plan will be a good process to ensure that the database compatibility is changed at right time:

1. Make sure to generate compatibility report before migration and educate developers and other related folks on what to change with migration. If some database is already is at it’s end of life and there is no business justification to change codes of this database to support latest compatibility level, make sure to get approval from all stake-holders at this stage.

2. When you will do the development migration, make sure to change the compatibility level of the databases. This may make developers and applications folks unhappy for sometime as their standard prioritization will be impacted, but this is your (analyst’s) best time to get the work done from developers.

3. Once everything works on new compatibility in development environment, make sure follow the same practice in migrations of test environments and final mock migration of production also.

4. On the day of production migration, make sure to change the compatibility level immediately after the new environment comes online. (Unless it is approved to remain in a older compatibility level as approved in Step 1)