7

I'm migrating a database from SQL Server 2012 to SQL Server 2014 on the same server.

I'm configuring LOG SHIPPING on a database that is apparently encrypted:

enter image description here

When setting the log shipping I got this message (when it started to restore the database from a backup):

enter image description here

I'm the new DBA here and there's no report of master keys, encrypted keys etc.

I'm setting log shipping from SQL2012 to SQL2014 (with norecovery). What could go wrong if I set this database to Encryption enabled - false and try to restore the database with the log shipping wizard?

There are some questions about this problem , like This one, but all of them say I need the password.

I Just found this certificate on master > security > certificates on the primary server (2012):

enter image description here

And there are some code samples over the Interet but I really can't trust them (I don't think it's too simple):

`USE MASTER
GO
ALTER DATABASE DatabaseName
SET ENCRYPTION OFF
GO
USE DatabaseName
GO
DROP DATABASE ENCRYPTION KEY
GO`

Edit1:

In this question Dylon says:

If you are receiving the thumbprint error the certificate was not created properly from the Source Server's Cert/Key backups.

I'm confused about this topic and all I want is to disable encryption, restore the database on the other server to log shipping, and some day, activate encryption.

Edit2:

On Database > tasks > manage database encryption there are some options too:

enter image description here

Disabling this, I would be able to backup the database again, and restore it on the new instance?

Improve this question

1 Answer 1

Reset to default
7

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE.

Rather set up log shipping properly in the presence of TDE. Follow the steps described in Move a TDE Protected Database to Another SQL Server. You are going to export the TDE certificate from the original server and then import it on the standby server, making sure it is encrypted with the master database master key and the database master master key is in turn encrypted with the service master key. After that, if done correctly, restore operations should succeed as the required TDE certificate is present.

To copy the certificate: The certificate is in the master database of the primary instance. You need to run:

BACKUP CERTIFICATE TDE_CERT 
 TO FILE <where to save cert> 
 WITH PRIVATE KEY (FILE = <where to save private key>, 
  ENCRYPTION BY PASSWORD = <password for the private key file>);

Then copy both cert and private key files to the secondary and restore it in master using:

 CREATE CERTIFICATE TDE_CERT 
  FROM FILE = <your copied cert file> 
  WITH PRIVATE KEY (FILE = <your copied private key file>,
   DECRYPTION BY PASSWORD = <password that protects private key file>);

TDE_CERT in master on the secondary needs to be encrypted with master database master key, which will likely need to be created.

Improve this answer
10
  • @Resmus, thank you very much for your answer. I was talking with the manager, and we really don't need this encryption. It was made a long time ago by a DBA and we really don't need it now. But I will read your blog and see what I can do. Thank you very much and I will post the results later. And yes, our database is 1.5tb. it would take some time do disable encryption, right?
    – Racer SQL
    Dec 22, 2015 at 12:50
  • 2
    Correct, the percentage shows the progress. You can run DROP DATABASE ENCRYPTION KEY after the decryption completes.
    – Remus Rusanu
    Dec 22, 2015 at 13:47
  • 1
    Also you'll need to make sure no log record overlaps any encrypted era of the database before attempting to restore on secondary. Start with a new full backup taken after the DROP DATABASE ENCRYPTION KEY.
    – Remus Rusanu
    Dec 22, 2015 at 14:12
  • 1
    Thanks for posting the follow up. Now we can point here other who ask "how long will a 1.4Tb TDE removal last?" ;)
    – Remus Rusanu
    Dec 23, 2015 at 15:31
  • 1
    Also, now you can enable backup compression msdn.microsoft.com/en-us/library/bb934049.aspx : Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended..
    – Remus Rusanu
    Dec 23, 2015 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.