As a SQL Server database administrator (DBA), it is important to be aware of the edition specific features that are being used in a database. This knowledge becomes crucial when you need to move a database from one edition of SQL Server to another. In this article, we will discuss how to identify edition specific features in SQL Server.

The Challenge

Imagine a scenario where you are tasked with moving a database from SQL Server Enterprise Edition to SQL Server Standard Edition. However, you are unsure whether the database uses any features that are restricted to the Enterprise Edition. It is important to identify these edition specific features before attempting the migration, as the database may not function properly in the new edition.

The Solution

SQL Server provides a dynamic management view called sys.dm_db_persisted_sku_features that allows DBAs to identify edition specific features used by a database. This view returns information about features such as Data Compression, Partitioning, Transparent Data Encryption, and Change Data Capture.

Here is an example of how to use the sys.dm_db_persisted_sku_features view:

USE AdventureWorks
GO
SELECT * 
FROM sys.dm_db_persisted_sku_features 
GO

If the view returns any rows, it means that the database is using edition specific features. The returned rows will provide details about the specific features being used.

For instance, let’s say we have a database called AdventureWorks that uses the Compression feature of SQL Server 2008 Enterprise Edition. When we execute the above code, the result will indicate that the AdventureWorks database uses the Compression feature.

It is important to note that if the sys.dm_db_persisted_sku_features view does not return any rows, it means that the database does not use any edition specific features.

Considerations

When moving a database from one edition of SQL Server to another, it is crucial to remove any edition specific features before performing the migration. This ensures that the database can function properly in the new edition.

Features such as Data Compression, Partitioning, Transparent Data Encryption, and Change Data Capture are designed to be used in specific editions of SQL Server. If a database uses any of these features, it cannot be moved to a lower edition that does not support those features.

Therefore, before attempting a migration, it is recommended to execute the following T-SQL code to identify whether the current database uses any edition specific features:

USE AdventureWorks
GO
SELECT * 
FROM sys.dm_db_persisted_sku_features 
GO

By following this approach, you can ensure a smooth migration process and avoid any compatibility issues.

Conclusion

Identifying edition specific features in SQL Server is crucial for successful database migrations. The sys.dm_db_persisted_sku_features dynamic management view provides a convenient way to determine whether a database uses any features that are restricted to a specific edition. By removing these edition specific features before performing a migration, you can ensure that the database functions properly in the new edition of SQL Server.