Earlier last week SQL SERVER 2019 is Released. It has been an amazing experience to download and work with this new version. As I have been working for almost 20 years with SQL Server, I want SQL Server to be robust and stable, while performing better than the previous version of the SQL Server in the same condition. One thing impressed me about the latest version of SQL Server 2019 support to Compatibility Level from 2008 to 2019.
Compatibility Level from 2008 to 2019
Compatibility Level is a database configuration that decides what algorithm SQL Server will use to execute queries and also support various available features. For example, you are running the latest version of SQL Server but still guide your SQL Server to behave and perform like it is any of the earlier versions of the SQL Server which is allowed by your current version.
It is one of the most important settings if you want performance from the latest version of SQL Server. Unfortunately, most of the database which I see during my Comprehensive Database Performance Health Check, I see it be set to a very incorrect value.
SQL Server 2019
SQL Server 2008 and SQL Server 2008 R2 both are out of the Microsoft Extended Support. The only option one should follow if they are using SQL Server 2008/R2, is to upgrade it to the latest version of the SQL Server. SQL Server 2019 allows you to directly upgrade from SQL Server 2008/R2 and keep the database compatibility to the earlier version.
I am personally very happy that the Microsoft team has given one small chance to the organizations which are using the earlier version of SQL Server to upgrade to the latest and greatest version of the SQL Server and without breaking the compatibility and cardinality settings.
Here is the screenshot of the SQL Server 2019 version’s server.
You can see in the dropdown, that you can go up to SQL Server 2008 compatibility in the latest version of the SQL Server.
Here is the script which you can execute if you want to change your compatibility of the database to the latest version of SQL Server 2019. Please note that the following script will only work on the SQL Server 2019.
USE [master] GO ALTER DATABASE [SQLAuthority] SET COMPATIBILITY_LEVEL = 150 GO
Have you started to use the latest version of SQL Server 2019? Please leave a comment.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Thanks Pinal Dave.
we have 2008R2 with few databases at compatibility level of 90. Can I still upgrade them directly to 2019? Or do I have to update teh compatibility level to 100 first and then do the upgrade?
We have 2008R2 (RTM) with few databases at compatibility level of 90. Can I still upgrade them directly to 2019? Or do I have to update compatibility level to 100 and service pack to SP4 first and then do the upgrade?
Did you get the answer for your post
I am also having the same scenario . Also I my DB server is running on SQL 2008R2 RTM version
No, I didn’t get any answer. But I was able to successfully upgrade to 2019. I updated the compatibility level on my databases to 100 , then I did sql upgrade to 2019 from 2008R2 without any issues.
Does features apply to previous compatibility level?
I mean, I’m on SQL Server 2019 but my databases are still on Compatibility Level 110 (SQL Server 2012). I want to rebuild a fragmented index with the options RESUMABLE = ON and OPTIMIZE_FOR_SEQUENTIAL_KEY = ON. Will these new feature apply even if the Compatibility Level is 110?
HI Vijaya can you please me here to upgrade the SQL server 2008 r2 to 2019.
@Vijaya Nettam – Can you add here the steps that you followed for the MSSQL upgrade from 2008 to 2019 please.