May 12, 2008 at 10:59 am
I have a prod and dev servers. On the prod server, the db is 100 GB with approx 4,000 tables. My goal is to do daily refresh of my dev db and minimize the load on the production server.
I am considering snapshot replications. Since our prod enviroment is down everynight between 10 and 11 pm, I would schedule the snapshot of prod at 10 pm. I beleive I can reduce the impact on the prod server by configuring a pull subscription on my dev server.
I am running 64 bit Windows 2003 servers with SQL 2005. The prod server has 32 GB ram.
Any other suggestions, recommendations?
Thank you.
Andre
May 12, 2008 at 11:08 am
Sure:
1) use SAN mirrors; (split the mirror, copy to dev, rejoin mirror)
2) use DB mirrors (similar)
3) use Prod's daily backups to restore to Dev
3) Log shipping
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2008 at 11:27 am
Snapshot is essentially a backup. If you are already doing this, and copying to another server (and you should), grab it from there. This would allow multiple restores the same day if needed as well. Course snapshots will do this.
Replication will do this, but then if you make changes on dev, you can get into issues.
The SAN snap mirror works well if you can do this. Be sure you use separate physical disks.
May 12, 2008 at 12:43 pm
I tend to use backup / restore rather than snapshot replication.
Since the backups are already happening (assuming you are using SQL backups) there is little or no additional load on the production server.
You also get the added benefit of an automated test of your backups every day.
May 12, 2008 at 1:41 pm
We use SQL Safe for the backups. I am not sure if we can automate the restore of that backup on a different server. It sure looks like a good idea since we already do backups.
Today this is how I refresh dev data. I do a native backup and then restore it on dev. The problem with that is I have to change owners, update all the views etc.
That's why I like the snapshot idea. You can select to replicate on tables. I was also considering log shipping but never used it. Do you guys know which method would be better? log shipping or replications?
Thanks.
May 14, 2008 at 7:35 am
Don't forget the classical problem whenever moving content from production to test - as part of your development work, you will change your database structures, and that can wreak havoc with your copying content over from Production to Test.
Second issue to consider - data privacy issues. The latest standards call for restricted access to 'sensitive' information - if your developers are not authorized to access content like SSN, medical or financial data, you will need to build a tool that will mask the real values as you transition data from production to test. Thank heavens for scripts!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply