Post reply

TDE for Always on databases

  • February 21, 2020 at 11:03 am

    #3727624

    Hi All,

    In terms configuring and usage of TDE encryption is there a difference for Standalone instance databases & databases involved in AG?

    Because the secondary will be in read-only, just want to know how to configure TDE on AG databases and during failover do we need to take care of any additional steps etc..

    - create database master key in master db (DMK).

    - creation of certificate in user db

    - create a symmetric key in user database i.e. CREATE DATABASE ENCRYPTION KEY

    use master

    go

    ALTER DATABASE testdb SET ENCRYPTION ON;

    go

    -Bob

  • February 21, 2020 at 5:10 pm

    #3727774

    TL;DR: just get the encryption Certificate loaded on every AG instance before encrypting, and it will just work.

    TDE encryption is performed as normal transactions against a database. When you perform TDE operations against the Primary Replica database in an AG, those operations are replicated to the Secondary Replica(s) just like all other transactions.

    The key to make TDE work in an AG is to have the encryption keys loaded on all Secondary Replicas before performing the TDE ops on the Primary.

    To do certificate-based TDE for a database in an AG:

    1. Create the TDE Certificate in the master database on one of the instances.
    2. Use BACKUP CERTIFICATE to back up the Certificate and its Private Key to files.
    3. Use CREATE CERTIFICATE [...] FROM FILE to restore the Certificate on all other SQL instances that will hold replicas of the encrypted database.
    4. Encrypt Primary Replica (create Database Encryption Key, enable encryption). As the Primary is being encrypted, the Secondaries will also be encrypted. You can watch the encryption process progress on each database by selecting from the view sys.dm_database_encryption_keys. You can run that on the Secondary Replicas to verify that they are also being encrypted.

    Certificate Rotation

    To rotate the TDE Certificate ('rotate' = 'switch to a new key'), repeat steps #1-3 above to place the new Certificate everywhere it will be used, then tell the Primary to use the new Certificate (USE [your_database]; ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE [new_cert_name])

    Changes on the Primary will be automatically applied to the Secondaries.

    DEK Rotation

    Rotating/regenerating the Database Encryption Key (DEK) works in an AG the same way it does for non-AG: rotate on the Primary and the changes will be replicated to the Secondaries.

     

    Eddie Wuerch
    MCM: SQL

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply