Raleigh Finance

Jul 31 2017

Can I Restore My Enterprise Edition Database on All Editions of SQL Server? #backups, #disaster


#

Can I Restore My Enterprise Edition Database on All Editions of SQL Server?

Q: I m designing a disaster recovery strategy for SQL Server 2008 and want to make sure I ve got an alternative server to restore my critical databases on. I d prefer to use Standard Edition to save on licensing costs, but will it be a problem if I m using Enterprise Edition for the production server?

A: Yes, this might be a problem. In SQL Server 2005 and later, some Enterprise Edition only features make a database Enterprise Edition only (i.e. it can t be restored on a Standard Edition or lower SQL Server system) if the features are present in the database.

In SQL Server 2005, the only feature to which this rule applies is table/index partitioning. There s no easy way to tell whether you have partitioning in a database without querying the storage metadata to see if any tables/indexes have multiple partitions.

In SQL Server 2008, there are four features to which this rule applies:

1. table/index partitioning

2. transparent data encryption

3. change data capture

4. data compression

Figure 1 shows an example of an error message you d receive when trying to restore a SQL Server 2008 database with data compression to a SQL Server Express instance using the following code:

Figure 1: Error message returned when attempting to restore a SQL Server 2008 database with data compression to a SQL Server Express instance

Because SQL Server 2008 contains more features that can trip you up, it includes a Dynamic Management View (DMV) called sys.dm_db_persisted_sku_features that will tell you if you have any of these features in your database. For example, if you run the code

you ll receive the following output, which shows you have data compression enabled:

Other than data compression, all of the SQL Server 2008 R2 and SQL Server 2008 Enterprise Edition features that make a database Enterprise Edition only require essentially database owner (DBO) or systems admin permissions, but data compression requires only the ALTER TABLE permission on the table involved. This means a table owner can make a database Enterprise Edition only without the DBA realizing it. Therefore, you must make sure that no one can enable these features without you knowing it if you want to be able to restore your database to a non-Enterprise Edition SQL Server instance.

Share this blog


Written by admin


Leave a Reply

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