1

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.

Improve this question
2
  • 1
    Is reporting server also running enterprise edition ? Have you looked into AlwaysON AGs ?
    – Kin Shah
    Nov 2, 2016 at 15:58
  • Yes, the reporting server is EE. I am looking at AlwaysON for next year, however I still intended to keep the transactional replication due to the fact that reporting has different needs such as many more indexes and they don't use all of the tables.
    – Andrew Lackenby
    Nov 2, 2016 at 16:13

3 Answers 3

Reset to default
0

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.

Improve this answer
0

There's no real pitfalls from doing this. You just need to be aware that there will be a slight increase in CPU on the subscriber, so if you're already CPU bound it might not be the best path forward.

You could consider using either pre or post snapshot script in order to help ensure that index compression is maintained in the event you need to run another snapshot (and to add any additional indexes that you require).

When performing the compression be aware that you will probably experience transaction log growth, much like you would if you rebuilt any other index. Sorting in tempdb is generally a good practice for something like that.

Improve this answer
0

Have you considered creating nonclustered columnstore indexes on your subscription.

Columnstore indexes will give you the benefit of massive compression rates.

I have just tested this with SQL Server 2016 Developer edition and it allowed me to create nonclustered columnstore indexes on my subscription.

Improve this answer
3
  • In SQL Server 2014 (which the OP is using), non clustered columnstore indexes are non updateable - so replication will fail. SQL Server 2016 changes that .. so you did not see any errors.
    – Kin Shah
    Nov 2, 2016 at 18:27
  • Apologies to @Andrew Lackenby, I missed the SQL Server 2014 tag. Hopefully still useful information for someone. This question will be around long after 2014 is a distant memory.
    – Andy Jones
    Nov 2, 2016 at 18:41
  • I would suggest you edit your answer and put my comment as note or disclaimer.
    – Kin Shah
    Nov 2, 2016 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.