SQL Server – Making Backup Compression work with Transparent Data Encryption by using Page Compression

Encrypted data does not compress well if at all so using the BACKUP with COMPRESSION feature will be ineffective on an encrypted database using Transparent Data Encryption (TDE), this post deals with a method of combining Page Compression with TDE and getting the best of both worlds. Transparent Data Encryption (TDE) feature encrypts data at rest i.e.… Read More »

Read more

Transaction Log Concepts – Part 2: Full Database Backups and Mirroring/Availability Groups

In Transaction Log Concepts – Part 1: ACID, Write Ahead Logging, Structure of the Log (VLF’s) and the Checkpoint/Lazywriter/Eagerwriter process I talked about Write Ahead Logging and how the Checkpoint process interacts with the Transaction Log; To recap – the transaction log is an append only log of all changes to the Database, an incremental Log Sequence… Read More »

Read more

Transaction Log Concepts – Part 1: ACID, Write Ahead Logging, Structure of the Log (VLF’s) and the Checkpoint/Lazywriter/Eagerwriter process

I’ve put together a number of posts that will hopefully both inform and put to bed some misconceptions around the use of a Transaction Log within a database context and how SQL Server makes use of it across a number of features such as normal day-to-day transactions, Backups, Checkpoints/Lazywriter/Eagerwriter, Mirroring, Internal structure of the transaction… Read More »

Read more