Post reply

TDE for one of the database

  • December 20, 2019 at 1:40 pm

    #3709572

    I just enabled TDE on one of the test DB (just created the db and inserted some data into it). However, I have some questions about how this works when working with AG

    What happens if the DB is already part of AG and I enable TDE on the DB on primary? How does TDE work in that situation? I am guessing I will have to drop the DBs from AG (Primary and secondary) and restore the DB on the secondary using the certificate and then add DBs back into AG?

    Can 1 certificate be used to encrypt multiple databases or do I have to create a certificate for each DBs on the server?

    Any help is highly appreciated!

    • This topic was modified 4 years, 11 months ago by  LearningDBA.
    • This topic was modified 4 years, 11 months ago by  LearningDBA.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • December 20, 2019 at 7:03 pm

    #3709678

    "What happens if the DB is already part of AG and I enable TDE on the DB on primary? How does TDE work in that situation?"

    SQL Server encrypts the database in a series of transactions, working through each data file encrypting each data page in it. Each of these changes is recorded to the transaction log. If the database is part of an AG, then those transactions are shipped to the AG secondary databases in-line with all other transactions occurring in the database. The secondary database(s) will be encrypted as a result.

    Because the transaction will also be encrypted with the Certificate, this will only work if the encryption Certificate is loaded on all AG Secondary Replicas. The process to TDE a database in an AG:

    1. Create/load the Certificate in any of the AG replica's instances
    2. Back up the Certificate (include its Private Key) so you may copy it to the other AG instances: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-certificate-transact-sql?view=sql-server-ver15
    3. On the remaining AG instance(s), copy the Certificate backup file and load it using CREATE CERTIFICATE <use_the_same_name_on_all_servers> [...] FROM FILE [...] WITH PRIVATE KEY [...]
    4. Once the Certificate is loaded on all of the instances participating in the AG, you can then TDE the Primary Replica just like a non-AG database.

    "I am guessing I will have to drop the DBs from AG (Primary and secondary) and restore the DB on the secondary using the certificate and then add DBs back into AG?"

    - No. You only need to have the Certificate loaded everywhere.

    "Can 1 certificate be used to encrypt multiple databases or do I have to create a certificate for each DBs on the server?"

    - A single Certificate may be used for any number of databases.

    Note: These answers are basically the same if you introduce EKM and use Asymmetric Keys hosted in EKM instead of Certificates. Just make sure the thing that encrypts/decrypts the database is loaded everywhere you need to decrypt and encrypt the database, and you're good.

    Eddie Wuerch
    MCM: SQL

  • December 20, 2019 at 7:08 pm

    #3709679

    Also, be very protective of that Certificate. You will need it to restore backups of databases encrypted with it. The database backup will not have the Certificate; you must separately back up the Certificate and restore it to the new instance before restoring a backup.

    • This reply was modified 4 years, 11 months ago by  Eddie Wuerch. Reason: fixed a typo

    Eddie Wuerch
    MCM: SQL

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

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