TDE with Automatic Database Seeding

  • Evgeny Garaev

    SSCertifiable

    Points: 6298

    Comments posted to this topic are about the item TDE with Automatic Database Seeding

  • Perry Whittle

    SSC Guru

    Points: 233794

    Why not just use a query to join the relevant catalogs and produce the cert and encryption key info in use

    SELECT dek.create_date
    , dek.regenerate_date
    , dek.modify_date
    , dek.set_date
    , dek.opened_date
    , dek.key_algorithm
    , dek.key_length
    , dek.encryptor_type
    , dek.encryptor_thumbprint
    , c.[name]
    , c.[subject]
    , c.[start_date]
    , c.[expiry_date]
    FROM sys.dm_database_encryption_keys dek
    INNER JOIN master.sys.certificates c
    ON dek.encryptor_thumbprint = c.thumbprint
    WHERE database_id = db_id()

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • alen teplitsky

    SSC-Dedicated

    Points: 30014

    Don't think i've supported a server with TDE before, but if you set up a cluster with databases encrypted with TDE why would you not restore the certificates on the secondaries as part of the cluster installation?

  • AlexSQLForums

    SSChampion

    Points: 14249

    We can remove an old expired certificate the same way it was done for a TDE database outside of an AG from all participating replicas:

    USE [master];GODROP CERTIFICATE ExpiredTDECert;GO

    Please check that you Availability Group is healthy and the TDE in synchronized state after you have renewed the TDE certificate. Keep the new certificate in a secure and reliable storage so you would be able to restore your TDE database in case of disaster or migration.

    Note: the process of renewing certificate is very similar to the initial activation of the TDE for a database. Also, before doing any manipulations on the AG, make sure that the AG is healthy, and databases are synchronised.

    Note: make sure that you keep a backup of an old certificate in case you will need a restore database to the point in time before we switched the encryption certificate.

    Don't forget to
    ALTER DATABASE [MySecretDatabase] SET HADR RESUME;
    GO

    otherwise your secondaries will be in paused state.

    Alex S
  • Evgeny Garaev

    SSCertifiable

    Points: 6298

    AlexSQLForums - Thursday, March 8, 2018 11:22 AM

    Don't forget to
    ALTER DATABASE [MySecretDatabase] SET HADR RESUME;
    GO

    otherwise your secondaries will be in paused state.

    You don't need pause or resume HADR if you follow my instruction. The method was tested on many SQL Server 2016 databases.

  • AlexSQLForums

    SSChampion

    Points: 14249

    Evgeny Garaev - Thursday, March 8, 2018 12:43 PM

    AlexSQLForums - Thursday, March 8, 2018 11:22 AM

    Don't forget to
    ALTER DATABASE [MySecretDatabase] SET HADR RESUME;
    GO

    otherwise your secondaries will be in paused state.

    You don't need pause or resume HADR if you follow my instruction. The method was tested on many SQL Server 2016 databases.

    Evgeny
    I should've mentioned that this only happens in secondary AG's which are in async mode with manual failover.
    I had to renew expired certs over the weekend and for AG's which are in async mode
    databases in AG pause after issuing "ALTER DATABASE ENCRYPTION KEY"

    Alex S

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

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