I’ve been fortunate enough to give a presentation on Data Compression in SQL Server several times over past and coming months. I put this post together to showcase the slide deck, answer some questions, and list some resources around the feature.
Data Compression is an Enterprise Edition only feature, which was introduced in SQL Server 2008, starting with 2008 R2 Backup Compression was made available in all editions of SQL Server.
The SQLCAT blog about compression (and specifically to answer the question of there are benefits to using backup compression with a row and/or page compressed database–the answer is yes) is here.
Hugo Kornelius answers some questions around LOBs and why SQL Server doesn’t compress them at SQL Blog here.
My blog post on Columnstore Archival Compression in SQL 2014.
The full SQLCat white paper on Data Compression is here.
To answer the question about backup compression affecting the transaction log chain–basically, you can’t mix compression modes in a given media set. So if you are not using backup compression and you turn it on, you need to start backing up to a new media set (a new backup file). For example, the below code is correct:
backup database [AdventureWorksDW2012] to disk='c:\temp\1.bak'
go
backup log [AdventureWorksDW2012] to disk='c:\temp\2.bak'
with compression
backup log [AdventureWorksDW2012] to disk=’c:\temp\3.bak’
BACKUP LOG [AdventureWorksDW2012] TO DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012_LogBackup_2013-05-09_14-22-10.bak’ WITH NOFORMAT, NOINIT, NAME = N’AdventureWorksDW2012_LogBackup_2013-05-09_14-22-10′, NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5
RESTORE DATABASE [AdventureWorksDW2012] FROM DISK = N’c:\temp\1.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’c:\temp\2.bak’ WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’c:\temp\3.bak’ WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5
RESTORE LOG [AdventureWorksDW2012] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDW2012_LogBackup_2013-05-09_14-21-30.bak’ WITH FILE = 1, NOUNLOAD, STATS = 5
GO
Also–the following rules apply around using compression in conjunction with partitioned tables:
When you use data compression with partitioned tables and indexes, be aware of the following considerations:
- When partitions are split by using the ALTER PARTITION statement, both partitions inherit the data compression attribute of the original partition.
- When two partitions are merged, the resultant partition inherits the data compression attribute of the destination partition.
- To switch a partition, the data compression property of the partition must match the compression property of the table.
- When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state.