Sql-server – Backup errors after TDE certificate expired and new cert was created with AlwaysOn

ola-hallengrensql-server-2016transparent-data-encryption

I have 2 databases in AlwaysOn with TDE enabled.
My TDE cert expired and i created a new one but Ola's Backup script is still failing:

EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'DB1, DB2', 
@Directory = 'D:\SQL-Backup', 
@CopyOnly='Y', 
@CleanupTime = 120, 
@Compress='Y', 
@BackupType = 'FULL', 
@Verify = 'N', 
@CheckSum = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'DB_DEK', 
@LogToTable = 'Y'

With this error (which is an old Cert):

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0xFAF8C09F62ED7D523AE634133331690FC2FD8367'.

Using native sql backup works:

BACKUP Database [DB1] 
TO  DISK = N'D:\SQL-Backup\DB1.bak' 
WITH FORMAT, INIT,  NAME = N'DB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,
ENCRYPTION(ALGORITHM = AES_256, SERVER CERTIFICATE = [DB_DEK]),  STATS = 10
GO

For this to work I had to take the DBs from AlwaysOn.

Has anyone else experienced this type of scenario? And, if so, how did you resolve it?

Best Answer

So after upgrading to SP1 CU7 the backups started working again with TDE.

Related Solutions

Sql-server – AlwaysOn with TDE

The secondary databases are just "clones" of the primary, so you cannot enable it on just one node.

The way to do it is actually to enable encryption on the primary, then it will replicate by itself if all is well (if you copied the certificate on the secondary nodes).

However, you should really look into MSDN and practice in a virtual lab before doing anything on production. It could be easy to get into an otherwise avoidable situation.

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

No you don't need to install the Ola's solution again since the initially installed commandexecute.sql and databasebackup.sql would have been saved on master DB as SP.

You just need to create a new job , as one created early but this time for 5 left over DB;s and schedule it weekly per you're timings.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',-- Name of those 5 DB's would come here
@Directory = 'C:\Backup',-- You're backup path
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y'

enter image description here You can use above script while creating the T-SQL job and schedule it accordingly.

More details on how to do above refer to schedule job

Also, just to make sure both, initial backup and this newly created backup job not to run on same time you can put a condition or per the analysis when you're daily backup job completes, you can schedule this new weekly/nightly backup job for remaining 5 DB's

Best Answer

Related Solutions

Sql-server – AlwaysOn with TDE

SQL Server – Implement Ola Hallengren Backup Plan for Different Databases

Related Question