Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2009-04-27 : 17:26:57
|
| The subject states the question but let me elaborate. For those of you that deal with database platforms that follow a development, testing and production - how do you deal with refreshing your data from production to the lower environments? Right now this process takes quite some time and is very manual - this translates into time and dollars. My objective is to make this a task that can happen seamlessly and with as little intervention as possible.Now let me toss in a few caveats:- We are not talking small databases. The total of all databases are almost 300GB and growing about 100GB per year. These will peak eventually but not for the next few years (due to data retention requirements).- Some of the servers are separated geographically with only a 1.5 Mbps line between them.The method I'm thinking about is on each database server, install second instance just for refresh purposes. The first issue is the best way to get the data to that instance (i.e. backup and restore / replication, etc). Suggestions are welcome here. We currently do backups and either mail a tape with the backups files, or copy it compressed over the wire. Neither of these are pretty.Now, once I get the data to the "refresh" instance we have several "custom" processes we need to run against the data including. This is the primary need for this "refresh staging" area. So all these processes can be ran against the instance in a secure and controlled manner.- Purging specific data from the database (due to HIPAA, SLAs, etc)- Running deployments to obtain most current database code on server- Several other minor tasks not worth mentioning since they consume little time.I guess no real suggestions are needed for this step, since I included this to provide the full picture of what happens. Since these steps can take some time, it is beneficial to have them performed in a staging area so they can be ran during normal business hours and not during some off hour schedule. Mainly because we want to be able to do this frequently.Once these tasks are complete, we can do either a backup and restore or a deattach and reattach to the primary instance to get the data. This is the easy part.So, any suggestions on how other DBAs address this type of task would be very helpful. I'm hoping someone out there has already addressed some of these issues and have answers I can glean out of their comments. |
|
|
|
|
|
Cost beneficial refresh architecture/strategies