At the moment, I have my production server with several large databases and I use a second SQL server for reporting purposes. I maintain the reporting copies of the databases using transactional replication.
As we've recently upgraded to EE, I want to enable data compression on several of the large tables on the reporting server to save on disk space (I'm currently not ready to enable it on the production server)
Has anyone tried this and if so are there any hidden pitfalls?
The only pitfalls I can see is if the table is re-snapshotted, the replicated table would lose its compression. As all of the additional reporting indexes would also be lost, its not something I would undertake lightly.
Best Answer
I have tried something like this before and I found substantial savings in disk space.
However, I found that queries where the data needs to be read from disk are quicker compared to uncompressed tables and queries where the data is already buffered in RAM became slower compared to uncompressed tables.
I left my investigation there, but depending upon the relative abundance of RAM or storage then I would suggest compressing only infrequently used data, either by partition or table. (But perhaps you can't easily partition tables the partitioning is not done on the publisher too.)
I did have cases where the table mysteriously lost its compression, perhaps due to some rebuild script that did not honor compression settings.