February 7, 2018 at 5:29 am
Hi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks
February 7, 2018 at 5:56 am
PearlJammer1 - Wednesday, February 7, 2018 5:29 AMHi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks
I have never come across this particular configuration sequence at any client, but my understanding is that there is no restoring of a TDE protected database without the TDE encryption stuff being in place/restored on the secondary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 7, 2018 at 6:46 am
PearlJammer1 - Wednesday, February 7, 2018 5:29 AMHi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks
Most likely what you need to do is import / restore into SQL Server the certificate you're using for TDE on the primary server. Just copying those files to the secondary server does not allow SQL to use them.
As for removing the files from the primary server, yes, you can do that, as the files on disk are backups of the certificates held in SQL Server itself.
If you run this:select *from sys.certificates;
do you see your encryption certificate on BOTH servers?
February 7, 2018 at 7:14 am
Hi
Yes I only see my encrypted certificate on the primary.
I guess I have to restore these on the secondary then (as you say merely copying them to a directory does not do anything).
February 7, 2018 at 7:24 am
PearlJammer1 - Wednesday, February 7, 2018 7:14 AMHi
Yes I only see my encrypted certificate on the primary.
I guess I have to restore these on the secondary then (as you say merely copying them to a directory does not do anything).
That would be correct. Although generally, I'd lean towards creating a new master key on the secondary server, then restore the TDE certificate using that new master key. It'll still be able to decrypt your TDE backups.
However, and bear in mind I don't use log shipping, it's entirely possible you're going to need to "re-seed" your secondary from a fresh, TDE-protected full database backup. I'd even guess it's going to be required. You *might* be able to sneak around that by restoring the certificate to the secondary, then enabling TDE on the secondary database using the certificate.
February 7, 2018 at 9:04 am
PearlJammer1 - Wednesday, February 7, 2018 5:29 AMHi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks
The secondary server needs to have a backup of the cert restored from the primary in order for TDE to be complete.
You already have a backup of the cert as you indicated, so go to the secondary instance and if no database master key already exists in the master database, then create one.
Once this is done create as new cert on the secondary specifying the backup of the primary cert.
See my article at this link for creating certs from backups
http://www.sqlservercentral.com/articles/Security/122707/
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2018 at 2:17 am
Thanks everyone for all your input.
I have done what was suggested, that is, on the secondary I created a new master key on the secondary, and created a new certificate from the backup of the primary cert.
As an extra step, and I don't know if this was required but it worked anyway, was that I removed the already restoring logshipped database that was present before TDE was turned on, and I re-established Logshipping from the primary to the secondary (this extra bit was done after I'd created the TDE configurations). What I did notice was that the database full backup that gets restored to initialise Log shipping (25Gb .bak file) did take considerably longer probably due to the overhead of encryption process.
Thanks for all your help guys.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply