0

Database Compatibility Levels – Introduction

Hello friends, let’s have a little idea about database compatibility levels and impact. I’ve phrased few common questions and answered those. Let’s have a look into those and feel free to ask in case of any confusion.

What is Database Compatibility Level?

Database compatibility level is just a number which indicates what all features your database still supports. This is used for backward compatibility. The compatibility level of a database dictates how certain language elements of the database function as it relates to an earlier version of SQL Server.  In a nutshell, this offers up partial “backward compatibility” to an earlier version. 

If you are having a database which is designed in Sql server 2000. In this case, compatibility level of your database will be 80.

Now your organization want to upgrade to Sql server 2005. You upgraded Sql server version and restored your old database to this newer version.

Perhaps you didn’t notice that there are few features which are there in Sql server 2000. You are using those features and now Sql server 2005 is not having those features. You may expect that those DB object will not work which are using old features. But you are wrong. Those features still work in newer version of Sql server. But how?

This is because of compatibility level. When you restored your database to newer Sql version then compatibility level of your database is still 80. This indicates to Sql server 2005 that there are still few features of Sql server 2000 which you may be using so those should not break.

So things will not break immediately. You have some time to find out these old features and replace those with new ones.

You can check compatibility level of your database from management studio.

Right click on your database and click on properties. Go to options tab and you’ll be able to see Compatibility Level.

In addition to this, you can check compatibility level of all databases using below query.

SELECT name, compatibility_level FROM sys.databases;

 

Who sets Compatibility Level?

Database compatibility level is initially set by Sql server. Following are few scenarios to understand this.

  1. You are creating a new database in Sql server 2012, then compatibility level of this database will be 110.
  2. If you are having a database which is created in Sql 2000 and now you are restoring this backup into Sql 2005 then compatibility level of this database will be 80.
  3. If you are having a database which is created in Sql 2000 and you created scripts for all DB objects. You created a new database in Sql server 2005 and run all scripts to make it replica of your old database. Then this new database will have compatibility level 90. Note: In this case your old features will not work as your database is having compatibility level according to Sql 2005.

Below is the chart of default compatibility levels of Sql server versions.

database compatibility levels

What is the Impact of this?

Let’s see this with an example. You have a database which is designed in Sql server 2000. So the compatibility of this database would be 80. Now suppose that your database is having few queries, stored procedures which are having non-ANSI joins.

select a.name,a.empno,b.loc
from tab a, tab b
where a.deptno *= b.deptno;

From above query you can see that we are using *= in query. This is kind of Left Outer Join which is supported in Sql version 2000 and lower.

Now you upgraded your Sql server to 2005 and restored your database. When you run your stored procedure which is having non-ANSI join, it’ll work fine because of compatibility level. Your database compatibility level is still 80.

If you change your database compatibility level to 90 then you may break things. So make sure that you replace all old features with new ones before upgrading compatibility level of your database.

Can I change it?

Of course, you can change it but now you understand impact of this. If you are using old features and changing compatibility level to new then you can break few stored procedure or other Db objects. So before changing compatibility level, make sure you have replaced all old features with new one.

Here is an example of how to set the database compatibility level in T-SQL:

ALTER DATABASE AdventureWorks2014 SET COMPATIBILITY_LEVEL = 120;

You can change it using management studio also. Just select compatibility level according to your case.

database compatibility levels change

With SQL Server 2014 and newer, the database compatibility level also controls whether the new cardinality estimator or the legacy cardinality estimator is used. The new cardinality estimator gives equivalent or better query performance for many to most queries, but it sometimes has very significant performance regressions. It is very important to do as much testing as possible before you change the compatibility level to 120 or higher.

Note:

  1. Don’t assume that this setting will let you restore the database backward to an older version. You cannot restore new database backup to older version. There are certain rule for downgrading which you can check here. Sql Server Downgrades-Rules & Restrictions
  1. Don’t think that compatibility level is same as database version. Both are different properties with different concept behind. The database version is an internal versioning system that defines what version of SQL Server the database was a recent resident of. Will cover more details about database version in upcoming articles.

Upgrade Advisor

We have an option to help with upgrades i.e. Upgrade Advisor. SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2014. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.

You can get more information about upgrade advisor here Upgrade Advisor

Feels free to experiment new things and connect to me.

0

SQL Server Downgrades – Rules & Restrictions

Hello Friends, Today we will discuss about rules according to which Sql server downgrades are possible. Like upgrades, these operations can be edition specific or version specific.

What is the need to downgrade?

But what is the need to downgrade? Well there is. Let’s say you have an application which is working fine with current edition\version of Sql server and then you decided to upgrade to new edition\version. You upgraded your database edition\version and then you saw that there are many issues exposed which could be new features related to security, availability etc. of new edition\version. And your application is not ready to handle those features without additional work.

Now to get things working, you’ll have to downgrade your Sql edition\version back to previous one and then decide upon the strategy to follow for future.

Again it is not possible to downgrade any edition, version without any rules or restrictions. There are some set of rules.

Let’s see how this works.

Edition Downgrades

If we want to downgrade Sql server edition then we need to first uninstall the higher edition and then install the lower edition of Sql server. There is no standard tool which can provide this downgrade out of the box. Uninstallation is the only option.

Now we may have few databases which were there in higher editions. What about those databases?

There are following steps to get these databases working on lower editions:

  1. To get those databases in lower editions, first we need to check if these databases are using any higher version specific features or not. A database created in SQL Server 2012 Enterprise Edition that uses enterprise edition features, such as Table Partitioning or Transparent Data Encryption (TDE), cannot be restored or attached to the SQL Server 2012 Standard Edition without first disabling these edition-specific features. To check this we can use below query.
SELECT [feature_name] FROM [sys].[dm_db_persisted_sku_features]

The [sys].[dm_db_persisted_sku_features] dynamic management view may return rows containing any of the following Enterprise or Developer Edition features:

  • Compression
  • Partitioning
  • Transparent Data Encryption
  • Change Capture

If no edition-specific features have been enabled in the user database, the [sys].[dm_db_persisted_sku_features] dynamic management view does not return any rows.

  1. Once we have list of all these edition specific features then we need to disable all these.
  2. Once we disabled all these features then we can back up the database.
  3. After installation of lower edition, we can restore\attach our database to lower edition and it should work.

Version Downgrades

It is same as edition downgrade, if we want to downgrade Sql server version then we need to first uninstall the higher version and then install the lower version of Sql server. There is no standard tool which can provide this downgrade out of the box. Uninstallation is the only option.

But there is one issue here. You cannot restore\attach those backups which were created in higher versions. The only option to get your database is, you’ll have to create new database in old version Sql and then run all DDL scripts, Data scripts in this newly created database to get everything back.

Following are the steps:

  1. In new version database, create DDL scripts for all objects i.e. tables, SPs, functions etc.
  2. Create all data scripts.
  3. Uninstall current version.
  4. Install older version which is required.
  5. Create new database and run all DDL scripts, DML scripts.

By using above flow we should be able to get our database in lower version.

Below are few points to consider and few points to understand while downgrading.

Database Compatibility Level:

Let’s discuss this with an example. Let’s say you are running your application in Sql server 2008 and now you decided to upgrade you Sql server to version 2012.  Did the upgrade and then you came to know that your application was using some features of Sql server 2008 which are no longer supported in sql server 2012.

Now in this case you need to change the application code so that it should stop using these un supported features of Sql server 2008 and start its alternative features of Sql server 2012. But by the time you make these changes into you application, you want your application to working. You don’t want your application to be in this broken mode.

In this case you will set compatibility level of your new database to Sql server 2008 compatibility level. This compatibility level ensures that it will support all those old features which were there in Sql server 2008. This way your application will not break and you’ll get time to upgrade application code so that it can start using new features of new version.

To see the compatibility level of each database, right click the database in SQL Server Management Studio and select Properties, then click the Options tab. See highlighted field below:

In the image above, I’m running a SQL Server 2008 database, which has compatibility level of 100, on a machine that is running an instance of SQL Server Express 2014 (see the Connection section on the bottom left, my instance name is SQLEXPRESS2014).

Another way to look at the compatibility level of each database on your server is to query the compatibility level column of the sys.databases table:

USE YourDatabaseName;
GO
SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDatabaseName';
GO

The main reason for having compatibility levels is to allow for Backward Compatibility. Each compatibility level has its own set of features. You must program against those features when developing an application that uses the database. If you want to move your database to a newer instance of SQL Server but at the same time have it backwards compatible, you need to make sure the compatibility level stays the same. 

Below is the chart of compatibility levels of different Sql versions.

SQL Server 2012 supports only databases at compatibility levels 90, 100, and 110. Meaning it supports databases going back to SQL Server 2005 only.

Database Version:

Database compatibility levels are not the same as database version numbers. The database version number is an internal number associated with a specific structure of a database’s system tables containing metadata about various objects such as tables, columns, indexes, allocations, and details about the relational and physical structure of the database.

Below is the chart of different versions of Sql server databases.

To identify the version number of a specific SQL Server database, you can query the master.sys.sysdatabases table as follows

SELECT version
FROM master.sys.sysdatabases
WHERE name='AdventureWorks2012'

All databases attached\restored to a newer version of a SQL Server instance, upgrade automatically to the database version that the newer SQL Server instance supports. In the case of SQL Server 2012, all databases created, attached, or restored in a SQL Server 2012 instance convert to database version number 700.

0

SQL Server Upgrades – Rules & Restrictions

Hello Friends, Today we will discuss about rules according to which Sql server upgrades are possible. These operations can be edition specific or version specific.

It is not possible to upgrade any edition, version without any rules or restrictions. There are some set of rules.
Let’s see how this works.

Edition Upgrades

Most of the time organization make decisions about edition of Sql server based on the requirements which it has at that point of time. It chooses the edition which is minimum to fulfill all the requirements. But later in time when organization grows, it needs to expand its business, data and Sql server edition also to get more features.

You can get more information about features which are available in different editions here.

However this is considered a wise decision to install lower edition based on the current set of requirements but sometime it has its downsides also. When organization choose to upgrade to higher edition then it may need to upgrade its infrastructure also to get the full benefits of all the features which higher edition offers. Due to this additional cost sometime organizations opt to install higher edition at the first step to avoid any additional cost in between.

You can upgrade all editions of SQL Server 2012 except for the Compact Edition to higher tier SQL Server editions. For example, you can upgrade Express, Web, and Workgroup editions to Standard, Enterprise, or Data Center editions. You can upgrade the Standard Edition to only the Enterprise or Data Center editions.

Edition Upgrade Matrix

  • Evaluation edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise
  • Developer edition can be upgraded to:
    • Web
    • Standard
    • Enterprise
  • Standard edition can be upgraded to:
    • Enterprise
  • Web edition can be upgraded to:
    • Standard
    • Enterprise
  • Express edition can be upgraded to:
    • Web
    • Developer
    • Standard
    • Enterprise

According to above matrix you can find out that is it possible for the edition upgradation you are looking for. Based on this you can actually make decision on which edition you should go for. You should choose the edition so that in future you should be able to upgrade easily which any major problems.

Version Upgrade

When it comes to upgrade Sql server version then it can come with two flavors.

  1. Either you want to upgrade only version not the edition. E.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 standard edition.
  2. Or you want to upgrade both e.g. you want to upgrade Sql server 2008 standard edition to Sql 2012 enterprise edition.

There are again set of upgrade which are possible. Below is the list of those:

Version Upgrade Matrix

Upgrade from

Supported upgrade path

SQL Server 2008 SP4 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 SP4 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 SP4 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 SP4 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 SP4 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2008 R2 SP3 Datacenter

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2008 R2 SP3 Developer

SQL Server 2017 (14.x) Developer

SQL Server 2008 R2 SP3 Small Business

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2008 R2 SP3 Workgroup

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2008 R2 SP3 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express

SQL Server 2012 (11.x) SP2 Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2012 (11.x) SP1 Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2012 (11.x) SP2 Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2012 (11.x) SP2 Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2012 (11.x) SP2 Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2014 (12.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2014 (12.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2014 (12.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2014 (12.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Enterprise

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Developer

SQL Server 2017 (14.x) Developer 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Standard

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard

SQL Server 2016 (13.x) Web

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web

SQL Server 2016 (13.x) Express

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Express 

SQL Server 2017 (14.x) Developer

SQL Server 2016 (13.x) Business Intelligence

SQL Server 2017 (14.x) Enterprise 

SQL Server 2016 (13.x) Evaluation

SQL Server 2017 (14.x) Evaluation 

SQL Server 2017 (14.x) Enterprise 

SQL Server 2017 (14.x) Standard 

SQL Server 2017 (14.x) Web 

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) release candidate*

SQL Server 2017 (14.x) Enterprise

SQL Server 2017 (14.x) Developer

SQL Server 2017 (14.x) Enterprise

 

So one can make decisions of which edition\version it should buy so that it could be upgraded easily to new version\edition.