In this blog, I will discuss SQL Servers’ database compatibility level. By using dbWatch, we will check the proper setting and configure the right compatibility level based on the version you are using.
Each new version of SQL Server introduces new functionalities and performance benefits. These are identified by compatibility level and cardinality estimators.
Each database version is associated with a compatibility level, so if you want to utilize the newest features available for your database, you need to check and set the right compatibility level for your instance.
As DBA, you must ensure the compatibility level of each database is matched with your instance current SQLServer version.
The usual problems of enterprises are some take too long to migrate to a newer version of SQLServer.
Some DBAs end up taking two steps. For example –they first migrate the SQL Server 2000 database to SQL Server 2008 R2, and then migrate it to SQL Server 2019. This is the reason NOT to wait too long before migrating a SQL Server database to its latest version.
Sooner or later, your compatibility level will have to change, and this will impact your applications and processes. By doing it sooner, you will reduce the amount of work you will have to do later.
In this view, you can manually check for each databases compatibility level, by opening the database properties window in your SQL Server management studio.
But on a database farm level, especially, when managing hundreds up to thousands of databases, this is not the ideal way to investigate for the compatibility level of your databases.
dbWatch Control Center provides you the overview of your database’s compatibility level and displays other attributes of the SQLServer database they are using.
Let us start by clicking on the Farm Management Icon, which is at the left side of your Control Center monitor as seen in the image below.
Expand Inventory Overview Performance.
Click on the Per platform option and choose MS SQL Server.
In this view displays different attributes, you have the instance status, name, database version, edition, port #, instance name, host, host IP, OS type, the creation date of your database and total DB count.
With this information you will have a quick understanding of the different versions of SQL server that is running within your database farm.
On top, choose the compatibility tab.
On the topmost part of this view, you can see the servers and databases compatibility level.
It has 2 groups:
- Databases with lower compatibility than the server
- Databases with the same compatibility as the server
As you can see, there are 2 databases that use lower compatibility level than the servers’ version, and 60 databases with the same compatibility level as the server.
On the right side, you have a pie chart that displays the different compatibility levels configured on your databases. We have 2 databases with SQL Server 2014 (120) compatibility level, 50 databases with SQL Server 2016 (130) compatibility level and 10 databases with SQL Server 2019 (150).
At the bottom of the view, displays the instance name, database, status, create date, recovery model, database compatibility, server compatibility, server version.
With this information, you will be able to analyze which databases you need to reconfigure and update their assigned compatibility level based on the servers’ compatibility level.
In the image below, you can see a database farm with a total of 903 SQL Server databases with version SQLServer 2008 R2. This feature is very handy when managing these huge numbers of databases, as it helps you keep track of your databases compatibility level when it comes to the latest available version.
The change in compatibility level tells the instance to change its feature sets, it might add new features or remove some old features so be wary of the changes that might affect your existing application.
The compatibility report view enables DBAs to find where they have the potential for performance improvements by upgrading the compatibility level of a database which might be a version behind.
For more tutorials visit wiki.dbWatch.com
