Restore Master Database

Recently I was tasked with migrating a SQL Server Instance to a new infrastructure.   Along with the migration, I also had to create a DEV, QA, UAT  environments as they were not part of the original infrastructure.  Instead of trying to move all the pieces separately, I migrated the databases, along with restoring the Master database.

To being we must make sure the new SQL Server Instance matches the existing instance.  Here is how we make sure the Instances match.

TDE - Transparent Data Encryption

Transparent Data Encryption (TDE) is a method of encrypting the database, data files and log files on the physical disk.  TDE is also known as encrypting data at rest.  TDE does real time encryption and decryption between the disk and memory.  We will be using the same Key Hierarchy and process as discussed in SQL SERVER Column Encryption.  We have one additional step, the encryption of the database itself, shown below.

SQL SERVER Column Encryption

Encryption has become a necessity when storing data.  There are many ways to encrypt data, here we are going to encrypt a column within the database using a key within the database.

Encryption Hierarchy - This is the encryption hierarchy within SQL SERVER

AES - starting with SQL Server 2016, we can only use AES_128, AES_192, and AES_256 encryption.

Looking at the standard encryption process, each layer of the hierarchy is encrypted by the layer above.  In this example we will use a symmetric key stored within our database.  We will encrypt our key with a certificate also within our database.  Our Certificate will be encrypted by the database master key DMK.  Continuing with standard SQL Server architecture, we also have a Service Master key SMK which is created when SQL SERVER is installed.  Our top level of encryption is Windows Data Protection API (DPAPI).  We could add additional layers.