DMV: sys.dm_db_persisted_sku_features(Check Database Before Migration Between Editions)

In some projects or scenarios, we need to move some databases between different SQL Server editions. For example, in some organizations, SQL Server Evaluation Edition, Enterprise Edition or another Edition can be installed unconsciously. When we examine these systems and give information to the relevant managers about this, they can say “we don’t need it, we can use a lower edition if we can do our job.” after hearing the licensing costs.

Because there is a lot of difference between the licensing costs between Enterprise Edition and the Standard Edition. And for some companies, it seems unreasonable to pay that difference unnecessarily, rightly…

In such cases, there is a very critical issue that we need to pay attention to when we move a database from an Instance in a higher edition edition to an Instance in a lower edition. If we use a feature that can only be used in a higher edition, we cannot move this database to a lower edition. Let’s continue with an example.

For example, we cannot move a database that uses Table Partitioning in SQL Server 2012 Enterprise Edition Instance to another SQL Server 2012 Standard Edition Instance. Because Table Partitioning is a feature only in Enterprise Editions and this feature is not available in Standard Edition.

SQL Server will report this to us during Database Recovery. This means that you can back up a database from Enterprise Edition Instance, and you can start to restore it to the destination SQL Server Instance. There will be no problems until the recovery phase. However, during the Recovery phase, SQL Server checks whether the database uses the property of any parent edition, and if there is such a thing, Restore fails and cannot be completed.

For Example; You create a database in an Enterprise Edition, you take a Full Database Backup, then you compress the data in a table (which is the Enterprise Edition feature), and then you take a Transaction Log backup that will also contain this change. When you restore this Full Database Backup to your target Standard Edition Instance in NORECOVERY mode, there will be no problem that the Recovery process is not complete yet; When you restore the Transaction Log backup that you recently took in NORECOVERY mode, you still do not get an error because the database still did not perform the Recovery operation; but when you attempt to recover the database with the RESTORE DATABASE dba_test command, you will get the following errors.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 1
Database ‘dba_test’ cannot be started in this edition of SQL Server because part or all of object ‘table_adi’ is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.
Msg 933, Level 21, State 1, Line 1
Database ‘dba_test’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

In such a scenario, that is, when you are moving a database from a higher edition to a lower Edition, it is useful to query the related databases with the DMM named sys.dm_db_persisted_sku_features before moving. This DMV will report to you the features that have been used in the database, not in other editions. In this way, if there is a feature that you don’t really actively use, you can turn off this feature and perform your migration successfully. If there is a feature that you can’t give up, then you will have to tell the managers about it.

Sample query: