This will be the final semi-regular post of the year so before I get into the subject I’d like to say a brief thanks for your readership over the year!
This week I have been focused more on the platform side of things: organising updates on our VMWare ESXi host with our data centre and preparing for an upcoming attempt at Cyber Essentials certification. In other words not that much on SQL Server this week but still had a dabble when called for.
It has come to my attention that you can’t licence SQL Server 2016 or 2017 through distributors any more. You’ll have to pardon my ignorance on licensing as through my main job we don’t do the SQL Server licensing. Either there’s already an SQL Server there or a new one is deployed.
I recently did a rapid-fire upgrade for a client which called for a move of an existing application from Windows Server 2012 R2 and SQL Server 2012 infrastructure. Because of the age of the application running there which wasn’t to be upgraded the design called for Windows Server 2016 and SQL Server 2016 to be used as these were the highest supported. Unfortunately the client’s IT were not able to source SQL Server 2016 from their distributors any more.
This is a nice segue into SQL Server compatibility levels, what they are and what they are used for and how that could save us.
Every database connected to a SQL Server instance has a compatibility level set. By default for a new database it will be the compatibility level for that particular version of SQL Server that’s been installed (unless the instance has been upgraded but that’s a different matter). The database compatibility level is used by the Database Engine to control what specific SQL Server behaviours and changes are used for that database. If you want to know more then Microsoft have that in their documentation if you fancy a detour.
The official word from Microsoft is that you certify an application against the database compatibility level and not the SQL Server instance version. In other words it’s valid to have a SQL Server 2022 (160) instance and connect databases with a SQL Server 2014 (120) database to it with a corresponding compatibility level to maintain backwards compatibility. Your application vendor might begrudgingly disagree with that policy though so check in with them first.
Examining and Changing the Database Compatibility Level
In SQL Server Management Studio the easiest way to determine the database compatibility level is by right clicking your target database > properties > Options tab. The current compatibility level is displayed in the window.
SELECT [name],[compatibility_level]
FROM sys.databasesYou’ll notice that the compatibility level is not 2022, 2019, 2017 etc but a number corresponding to the product version of the SQL Server instead.
If you determine that a compatibility level change is required you can set a new level using the following command:
ALTER DATABASE [MyDatabase]
SET COMPATIBILITY_LEVEL = 160 | 150 | 140 | 130 | 120 | etcYou should note that you may experience query regressions when moving up compatibility levels. The usual reminder to test carefully before deploying to production counts here!
