SQL Server 2008: Does my database contain Enterprise-only features?

Moving databases around is pretty common, as is moving databases between servers running different Editions of SQL Server, especially during a disaster recovery situation. You may not know this, but in SQL Server 2005, if you had partitioning anywhere in a database, you could only attach/restore that database using an Enterprise or Developer instance. I say ‘you may not know this’ because partitioning isn’t really widely used (from what I can gather from talking to customers). The upshot of this is that SQL Server 2005 will refuse to attach/restore a database with partitioning in – even if you’re in a disaster recovery situation and the only server you have available has Standard Edition.

In SQL Server 2008, a lot more people are going to bump into this issue. The list of features that are Enterprise only, and will prevent attach/restore using a lower edition has expanded to include 3 new features that WILL be much more commonly used than partitioning. The four features that are in this category are:

  • Data compression (in all editions from 2016 SP1)
  • Partitioning (in all editions from 2016 SP1)
  • Transparent data encryption
  • Change data capture

All of these require elevated permissions to enable EXCEPT data compression, which only requires ALTER permission on a table. This means someone with table-owner privileges could enable compression without the DBA knowing, and suddenly the database can’t be attached/restored to, say, Standard Edition.

If you’re a DBA and have just taken over a database, there’s now an easy way to tell whether the database contains these features. A new DMV has been added – sys.dm_db_persisted_sku_features – that will report which of these four features are present in a database. Let’s check it out.

Using a 2008 Enterprise instance:

CREATE DATABASE EnterpriseOnly;
GO
USE EnterpriseOnly;
GO

CREATE TABLE compressed (c1 INT) WITH (DATA_COMPRESSION = ROW);
GO

SELECT * FROM sys.dm_db_persisted_sku_features;
GO

feature_name    feature_id
————–  ———–
Compression     100

Now let’s try backing up and restoring the database on a different edition:

BACKUP DATABASE EnterpriseOnly TO DISK = ‘C:\SQLskills\EnterpriseOnly.bck’;
GO

And on a 2008 Express instance:

RESTORE DATABASE EnterpriseOnly FROM DISK = ‘C:\SQLskills\EnterpriseOnly.bck’
WITH MOVE ‘EnterpriseOnly’ TO ‘C:\SQLskills\EnterpriseOnly.mdf’,
MOVE ‘EnterpriseOnly_log’ TO ‘C:\SQLskills\EnterpriseOnly_log.ldf’
GO

Processed 160 pages for database ‘EnterpriseOnly’, file ‘EnterpriseOnly’ on file 1.
Processed 5 pages for database ‘EnterpriseOnly’, file ‘EnterpriseOnly_log’ on file 1.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database ‘EnterpriseOnly’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 909, Level 21, State 1, Line 1
Database ‘EnterpriseOnly’ cannot be started in this edition of SQL Server because part or all of object ‘compressed’ 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 ‘EnterpriseOnly’ cannot be started because some of the database functionality is not available in the current edition of SQL Server.

Now, it’s cool that it tells you exactly why the database couldn’t be restored, but did you notice the first two lines of output? The database is fully restored BEFORE the operation fails! This makes perfect sense, as the database needs to be fully recovered before the server can tell whether any of the four features are still enabled or not. However, in a disaster recovery situation, waiting many hours for a database to restore only to be told that it can’t be restored on this instance would be even more disastrous.

To summarize, you should always know what’s happening in databases you manage. If portability of your databases to a lower Edition is important, make sure that none of these features can be enabled without you knowing about it.

5 thoughts on “SQL Server 2008: Does my database contain Enterprise-only features?

  1. No – that’s always been the case. Backup compression is orthogonal to the features mentioned above. Don’t confuse backup compression with *data* compression – two completely separate features. Thanks

  2. I have enabled CDC in one of my databases on E. Edition and I ***can*** restore it on Standard Edition.
    Of course all CDC metadata goes away but I can fully operate with it.
    Am I missing something ?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.