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.
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:
- 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:
- 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.
- Once we have list of all these edition specific features then we need to disable all these.
- Once we disabled all these features then we can back up the database.
- After installation of lower edition, we can restore\attach our database to lower edition and it should work.
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:
- In new version database, create DDL scripts for all objects i.e. tables, SPs, functions etc.
- Create all data scripts.
- Uninstall current version.
- Install older version which is required.
- 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 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.