SQL Data Compression and Configuration Manager = BAD

Posted: November 5, 2013 in Configuration Manager, Solved, SQL, System Center
Tags: , , ,

First off just to clarify, I am not a SQL guru, so on this topic I “know of” the feature but don’t really know that much about it.

We recently experienced a problem building a new Primary Site where the database replication links to the CAS failed and nothing we tried could fix the links. We resorted to completely uninstalling the site, deleting the Primary database and rebuilding the entire server OS from scratch. No matter what, the links replication just would not work.

RCMCTRL.LOG – Lot’s of these types of errors
Error: Replication group “Hardware_Inventory_4” has failed to initialize for subscribing site H01, setting link state to Error.

Error: Exception message: [ALTER TABLE SWITCH statement failed. Source and target partitions have different values for the DATA_COMPRESSION option.]

As it turns out, the SQL DBA’s had enabled a wonderful new feature in SQL2012 called Data Compression. In SQL land it probably makes sense and does wonderful things, but in ConfigMgr world it’s BAD.

We didn’t notice it when they first enabled it as it applied across all the datbases, CAS and Primaries together. So the compression setting was enabled for the same tables that replication applied to.

When we tried to build a new Primary though, the compression enabled on the CAS prevented the new Primary (with no compression) from establishing the replication links. No details in the Link Analyser told us the problem, and no amount of *forcing* the links to replicate would work.

A call to Microsoft Support, and much to-ing and fro-ing and eventually compression was fingered as the cause.

SOLUTION

To identify if compression has been enabled, you can use the following query against the Site database. Scroll through the results and look for any entries in the “data_compression_desc” column that have a value other than “NONE”.

SELECT OBJECT_NAME(object_id), index_id, partition_number, rows, data_compression_desc
FROM sys.partitions

If you see any entries with “PAGE” then data compression is enabled.

compression_bad

Turning off compression was easy (something the SQL guys did, I don’t know the specifics), but then all the Replication Groups needed to be re-initialised. This could probably be adapted to just do all the relevant entries, so if you work it out, feel free to share.

You can use this query to set the re-initialise flag

update RCM_DrsInitializationTracking 
set InitializationStatus = 7 
where ReplicationGroup ='High_Priority_Site' 
and SiteRequesting = 'CAS' 
and SiteFulfilling = 'PRI'
  • Change the “ReplicationGroup” entry to the name of the failed Replication Group listed under the Monitoring\Database Replication view
  • SiteRequesting is the Parent Site
  • SiteFulfilling is the Child Site

Alternatively you can create a file in the RCM.BOX inbox folder on the site server to trigger the replication (this is probably easier)

Refer to this post for more details, but essentially you do the following:

The file <replicationgroupname>.pub  works fine for Global Data. But for site-data where the CAS is the subscriber you have to add the sitecode to the file name, so it is like <replicationgroupname>-<sitecode>.pub, this is so the CAS knows which primary to re-init from.

Global data eg: configuration data.pub      => Drop these on Primary server  RCM.BOX

Site Data eg: hardware_inventory_8-PRI.pub  => Drop these on CAS server  RCM.BOX

And then kindly tell the SQL DBA’s not to enable compression again.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s