SQLTeam.com | Weblogs | Forums

Database refresh from Production DB to Test DB


#21

IF i will be implementing TDE then following steps looks fine?

  1. Backup all Prodcution Databases
    2)Create a database master key and a certificate in the PRODUCTION master database.

USE master ;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
3) Create self-signed certificate in the PRODUCTION master database.

CREATE CERTIFICATE TDEProdServerCert
WITH SUBJECT = 'Certificate to protect ProdTDE key'
GO

  1. Create a backup of the server certificate in the PRODUCTION master database.
    BACKUP CERTIFICATE TDEProdServerCert
    TO FILE = 'TDEProdServerCert'
    WITH PRIVATE KEY
    (
    FILE = 'SQLPrivateKeyFile',
    ENCRYPTION BY PASSWORD = 'P@ssw0rd';
    );
    GO
    -- This will stores the backup of the certificate and the private key file in the default data location
    But I should also keep the key into another server/Drive, Right?

  2. Create a database encryption key into Production DB, that is protected by the server certificate in the master database.

USE CustomerPRodDB
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDEProdServerCert;
GO
ALTER DATABASE CustomerPRodDB
SET ENCRYPTION ON;
GO

6)Do for All PRODUCTION DBs

  1. Move or copy the backup of the server certificate and the private key file from the source server to the destination server.
    Is it Location matter like same location as production DB location for TEST?

-- Create a database master key on the destination instance of SQL Server - TEST SERVER.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';
GO
-- Recreate the server certificate by using the original server certificate backup file.
-- The password must be the same as the password that was used when the backup was created.

  1. CREATE CERTIFICATE TDEProdServerCert
    FROM FILE = 'TDEProdServerCert'
    WITH PRIVATE KEY
    (
    FILE = 'SQLPrivateKeyFile',
    DECRYPTION BY PASSWORD = 'P@ssw0rd'
    );
    GO

#22

But I would not try this in production first at all. NEVER try anything in prod that you have NOT fully vetted in preprod.
What upstream processes and applications do the databases support? You NEED to test all of these in a preprod environment with encryption enabled. Then when fully tested (including backup and restore of encrypted databases) you can confidently proceed to prod.
What is your backup plan if encryption hoses your prod system databases?


#23

Didn't understand "What is your backup plan if encryption hoses your prod system databases?"
If i understand correctly, then I have to turn off the encryption,

  1. Restore Backup
  2. Drop Master Key
  3. Drop certificate
  4. turn off the encryption
  5. Restart

#24

Have you ever performed those specific steps in dev/stage?


#25

Nope


#26

so I would recommend you test it in preprod 1st then when tested and vetted fully deploy to prod.


#27

Sure, We will do first in TEST but need to just verify the steps.


#28

your steps are fine.
try those steps in preProd,
test all applications

when all is good
implement same steps in production


#29

Sure, Thanks Yosiasz for your help!
I have one question as we have our data center at other location where we replicate our data so if we implement TDE then what will happen with our data replication as Microsoft does not support For Data Encryption, right?


#30

Any other surprises under your sleeve? Lay all of your sins, confess


#31

Thanks for your good help!


#32

I am little confused for TDE implementation as we have our data center at other location where we replicate our data so if we implement TDE then what will happen with our data replication as Microsoft does not support Data Encryption for Replication, right?

We have SAN Replication so need to know TDE will be ok if we have to restore from Data center?


#33

I am not sure about that. But as I have been recommending in this thread have you tried this scenario in preprod environment.
If it works or does not work in preprod then you know


#34

I agreed with you to try first in DEV but before we implement, we have to know Pros and Cons.