TDE with Automatic Database Seeding

Question

TDE with Automatic Database Seeding

Evgeny Garaev

SSCertifiable

Points: 7428
More actions
March 8, 2018 at 12:05 am

#344332

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

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

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

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

SELECTdek.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()

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

"Ya can't make an omelette without breaking just a few eggs" 😉

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

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: 14277 More actions · Answer 3

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: 7428 More actions · Answer 4

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: 14277 More actions · Answer 5

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