Just Published! Threat Report: Exposing Malware in Linux-Based Multi-Cloud Environments | Download Now

Downgrade Cb Protection Database from Enterprise to Standard edition

Downgrade Cb Protection Database from Enterprise to Standard edition

Version

Cb Protection, all versions (7.x, 8.x)

Topic

This document outlines the steps necessary to remove Enterprise level features (data compression) from the 'das' database, in order to attach it to a SQL Standard Edition server.

Steps

To remove data compression from an existing 'das' database, it will be necessary to have SQL Enterprise Edition currently installed. 

1) First, confirm that data compression is the only Enterprise feature enabled by running the following query on the database

USE das

GO

SELECT * FROM sys.dm_db_persisted_sku_features;

The expected output is

‚Äč

feature_namefeature_id
compression

100

2) Next, stop the Cb Protection Server and Cb Protection Reporter services.

3) Execute this SQL to generate a list of objects that have data compression enabled

SELECT

SCHEMA_NAME(sys.objects.schema_id) AS SchemaName

,OBJECT_NAME(sys.objects.object_id) AS ObjectName

,[rows]

,[data_compression_desc]

FROM sys.partitions

INNER JOIN sys.objects ON sys.partitions.object_id = sys.objects.object_id

WHERE data_compression > 0

        AND SCHEMA_NAME(sys.objects.schema_id) != 'SYS'

ORDER BY SchemaName, ObjectName

4) For each object returned in #3, run the following statement to remove the compression

ALTER INDEX ALL ON objectNameFromStep3 REBUILD WITH (DATA_COMPRESSION = None);

5) After this is completed, run the statement from #1 again to confirm that no data compression exists.   If it does, run through #3 and #4 again to take care of any objects that were missed.

6) Detach the database from the current SQL Enterprise Edition server, and move / attach is to your SQL Standard Edition server.

7) Restart Cb Protection Server and Cb Protection Reporter services.

FAQ

What if I have already moved the database to SQL Standard Edition before removing data compression?

SQL Standard Edition will not allow you to attach a database that contains data compression.   If this is the case, you will need to attach the database to an Enterprise edition server in order to make the changes.   Often this can be done by creating a temporary VM, installing a trial version of SQL Enterprise Edition, and moving the database there to run through the above steps.

I'm getting the following error when attaching the database to SQL Standard Edition:

Database 'das' cannot be started in this edition of SQL Server because part or all of object 'object' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

This error is due to the database still containing data compression, and this is not supported by SQL Standard Edition.   You will need to run through the steps above, using SQL Enterprise Edition, to remove any data compression from the das database before it can be attached to SQL Server Standard Edition.

External Reference

The SQL Post: Microsoft SQL Server: Error: 909 while migrating a Database from SQL Server Enterprise...

Labels (1)
Was this article helpful? Yes No
No ratings
Article Information
Author:
Creation Date:
‎05-22-2017
Views:
482
Contributors