August 9, 2013 at 9:07 am
Need to automatic backup/restore ENCRYPTED database from Server A to Server B. What method is best and support ENCRYPTED Database. Log shipping, Mirroring or Replication?? Please advice
August 9, 2013 at 9:24 am
You need to read up on TDE.
The server A db needs to have the Certificate backed up.
Server B needs needs to be able to restore using the certificate. Copy the file locally with Private Key.
May need to create a master key or cert on server B if one doesn't exist. Think its Cert.
Although not best practise to keep both on the same server.
T-SQL to do the restore may examples can be found on the net.Using the Cert and Private Key.
August 9, 2013 at 9:30 am
Plz read my ques. I have asked what's the best method to do automatic restore on DR server from Source server with encrypted database. Does Log shipping, replication and mirroring support encrypted database to copy to target server?
August 9, 2013 at 9:31 am
Backup as normal, after the cert has been backed up.
Restore using Cert and Private Key T-SQL
Something like this to BACKUP the CERT.
TEST first.
CREATE CERTIFICATE NorthwindCert
FROM FILE = 'C:\NorthwindCert_File.cer'
WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk',
DECRYPTION BY PASSWORD = 'mY_P@$$w0rd');
GO
August 9, 2013 at 9:33 am
Your question is mis-leading BACKUP/RESTORE would indicate that a) you want to back it up and b) you would like to restore.
August 9, 2013 at 9:41 am
I have prod server and DR server. I want to copy encrypted database (or backup) from Prod to DR automatically, now I am doing manual backup restore.
what is the best way to copy/replicate/ move encrypted database from Production to DR server??
Do Log shipping or mirroring or replication support encrypted database for DR/HA?
August 11, 2013 at 11:09 am
First, there's no such thing as "best". There are positives and negatives for all methods.
Second, how is the database encrypted. Is it TDE? If it is, then as mentioned first, you need the certificate from prod on the secondary server. Once that is done, backup / restore is backup/restore. It works the same.
Replication doesn't use backup restore. It works as it does elsewhere.
Log shipping/mirroring work differently. You should read about the way they work, and the positives and negatives. What works well for my environment doesn't work for your environment.
What are you trying to accomplish with this? What's your RTO/RPO? If you have that, we can help with guidance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply