patternsqlMinor
Best practise for copying a database from production
Viewed 0 times
productiondatabasecopyingforpractisefrombest
Problem
Currently after releasing a new major or minor version of our application, we take a copy of the production database and perform the below process:
The intention of this is to ensure all our environments are as close to one another as possible. This originated as a manual process but due to increasing complexity we've started to automate it.
However what is the best way to automate a post deployment process such as this? Our currently automation is a bespoke c# program - however, would an SSIS package be more appropriate? Or is there a tool out there that is made for the job?
- Copy production database to test server
- Obfuscate the data and setup the test users
- Switch the old test database with the obfuscated new database
- Repeat for the development database.
The intention of this is to ensure all our environments are as close to one another as possible. This originated as a manual process but due to increasing complexity we've started to automate it.
However what is the best way to automate a post deployment process such as this? Our currently automation is a bespoke c# program - however, would an SSIS package be more appropriate? Or is there a tool out there that is made for the job?
Solution
SSIS could certainly take care of this for you but as with many things there are several ways to complete the task.
1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that depends on the configuration of your infrastructure.
2) Easily taken care of in SSIS by executing a no. of scripts against a target database.
3) Again, easily completed by running a SQL script against the target server.
4) You could use package configurations to specify a different environment/connection strings very easily.
I hope that's some help.
1) You have several options within SSIS to complete the copy process, Copy Database is one of them. You could backup the database and restore it using a SQL Script. You may need to use the file system task to move the backup file but that depends on the configuration of your infrastructure.
2) Easily taken care of in SSIS by executing a no. of scripts against a target database.
3) Again, easily completed by running a SQL script against the target server.
4) You could use package configurations to specify a different environment/connection strings very easily.
I hope that's some help.
Context
StackExchange Database Administrators Q#28175, answer score: 3
Revisions (0)
No revisions yet.