Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What would happen if you enable data compression (either row or page-level) on a replicated table (merge replication) where the publisher is SQL 2008 Enterprise (Supports data compression) and the subscriber was SQL 2008 Standard (does not support data compression). Would the compression attribute get politely dropped and the subscriber continues happily not-compressed or would it cause replication to hit an error and stop?

1 Answer
1

It will fail if you try to save replicate including compression, but if you set it to only "replicate the partition scheme but not compress the data on the Subscriber."

The Distribution Agent does not check for down-level Subscribers when it applies scripts. If the replication of compression is selected, creating the table on down-level Subscribers will fail. In the case of a mixed topology, do not enable the replication of compression.

Following from that, what happens when you restore a backup from sql 2008 enterprise with some table compression enabled, onto a sql 2008 standard server instance.. I've not actually tried and it's an interesting concept.