January 14, 2019 at 7:54 am
In addition to moving the databases (backup and restore, unless you want to talk third party tooling) and configuring security (which you've received a lot of good advice around), one more thing comes up. You really need to ensure that you appropriately clean the production data for the test environments. Depending on your data and the system, there's all sorts of regulatory requirements that might prevent developers from seeing some data (personal data as defined by GDPR, health information as defined by HIPAA are just a couple of examples). So, you'll want to add that to your process as well.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 15, 2019 at 8:42 pm
Thanks for pointing out GDPR and HIPAA, need to check that too
January 15, 2019 at 9:39 pm
GilaMonster - Sunday, January 13, 2019 10:43 PMYou need the certificate to be installed in master on the test instance to be able to restore.
Thanks Gail.
So I will be following procedure, please correct me anything i am missing or something wrong.
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
--
4) 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?
5) 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
7) 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.
8) CREATE CERTIFICATE TDEProdServerCert
FROM FILE = 'TDEProdServerCert'
WITH PRIVATE KEY
(
FILE = 'SQLPrivateKeyFile',
DECRYPTION BY PASSWORD = 'P@ssw0rd'
);
GO
January 16, 2019 at 6:45 am
poratips - Tuesday, January 15, 2019 8:42 PMThanks for pointing out GDPR and HIPAA, need to check that too
And the CPPA and others, all modeled off the GDPR. It's an entertaining age we live in and it directly affects how we support database development and testing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2019 at 8:23 pm
GilaMonster - Sunday, January 13, 2019 12:56 PMYou do step 2 once. Not every time.
And you generate the login script from prod, not test. You want the logins on test to have the same sids as they have on prod.Then each time you want to refresh, you take a backup from prod and you restore it to test. If you want the users to have different DB permissions you then need to run a script to change the permissions after a restore.
SSIS and jobs are server components, not in a DB. Do you also want those on test?
Sorry If I misunderstood but we have different permission level in Prod and test and we don't want any permission privileges from production to test as test has different permission level so if from step 2 (generate before refresh and run in TEST that script in TEST after Refresh)?
If i generate Login script from Production before refresh and run into TEST after refresh then TEST will have Production SID?
January 18, 2019 at 10:37 am
poratips - Thursday, January 17, 2019 8:23 PMIf i generate Login script from Production before refresh and run into TEST after refresh then TEST will have Production SID?
Yes. And production permissions (because database permissions are included in the database).
Run the script to create the logins once, before you do the refresh. Then, after you do the refresh, each time, run a script to change the permissions to what you want on test.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 19, 2019 at 12:09 pm
Thanks Gail.
But I need TEST Login and permissions which originally i have it so i should generate login script from TEST before Refresh and after Refresh, I should run this generated script from TEST run back into TEST so it has my original Login back? because I don't want production login in TEST.
Thanks once again for your help!
January 20, 2019 at 3:29 am
Logins, no. Permissions, yes, so that you can apply that script after doing the refresh.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 21, 2019 at 7:27 am
f you use TFS, you can create Release Manager jobs to totally automate backup and refresh processes, and avoid giving users more permissions than they really need on the dev/acceptance servers. We have RM jobs that backup the prod database, restore on the selected environment, then run scripts to make any changes for permissions, logins, etc.
January 26, 2019 at 4:33 pm
Ross McMicken - Monday, January 21, 2019 7:27 AMf you use TFS, you can create Release Manager jobs to totally automate backup and refresh processes, and avoid giving users more permissions than they really need on the dev/acceptance servers. We have RM jobs that backup the prod database, restore on the selected environment, then run scripts to make any changes for permissions, logins, etc.
Thanks Ross, it's always nice to have Automated process and WE have TFS but i don't know how to use it.
January 26, 2019 at 4:46 pm
Hi,
I am curious 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?
February 5, 2019 at 9:22 pm
poratips - Saturday, January 26, 2019 4:46 PMHi,
I am curious 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?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply