| sitelink1 | https://www.mssqltips.com/sqlservertip/2...rver-2008/ |
|---|---|
| sitelink2 |
By: Ashish Kumar Mehta | Read Comments (4) | Related Tips: More > Upgrades and Migrations
Problem
One of the Junior SQL Server database administrators (DBA) in my company approached me yesterday with a dilemma. He wanted to know how he can identify whether a database uses any of the features that are restricted to a specific edition of SQL Server 2008. In this tip we will go through the steps a DBA needs to follow to identify a database which uses edition specific features.
Solution
When you implement any of the features such as Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition on a database, these features will internally change the way the database engine stores information within the database files. If a database uses any of the above features which are specifically designed to be used in Enterprise Edition of SQL Server 2008, it cannot be moved to other editions of SQL Server 2008.
DBAs can identify all edition specific features that are enabled within a user database by using thesys.dm_db_persisted_sku_features dynamic management view.
As an example, let's try restoring the AdventureWorks database to a SQL Server 2008 Standard Edition instance. Unfortunately, there is a catch; one of the tables Person.Address of is using Page Level compression in SQL Server 2008 Enterprise Edition. When you try to restore the database to a SQL Server 2008 Standard Edition instance which does not support the Data Compression feature, the restoration of the database will fail with the following error message:
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 4
Database 'AdventureWorks' cannot be started in this edition of SQL Server because part or all of object 'Address' 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 4
Database 'AdventureWorks' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
If a DBA is asked to move a database from Enterprise Edition to any other edition of SQL Server 2008 it is a good idea to execute the T-SQL code below to identify whether the current database uses any of the features that are restricted to a specific edition of SQL Server. The sys.dm_db_persisted_sku_features dynamic management view will let you know which edition specific feature is being used by the database. One needs to have VIEW DATABASE STATE permission on the database to view the results.
USE AdventureWorks GO SELECT * FROM sys.dm_db_persisted_sku_features GO
As you can see in the code above snippet, the dynamic management view lets you know that the AdventureWorks database uses Compression feature of SQL Server 2008 Enterprise Edition. The sys.dm_db_persisted_sku_features dynamic management view will not return any rows if no features that are restricted by edition are used by the database. In case, you are using Data Compression , Partitioning , Transparent Data Encryption or Change Data Capture of SQL Server 2008 Enterprise Edition, you need to remove the features before you perform the full backup and restore the database.
