SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Coping data to enviroments
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

danielc
Starting Member

48 Posts

Posted - 08/29/2010 :  18:57:56  Show Profile  Reply with Quote
I have three environments that I need to replicate data to from production on a quarterly basis. Our development team has requested we copy data on a nightly basis instead of quarterly. What would be the best approach to coping data over from production and allowing the developers to insert test data into tables being replicated. My understanding is that data cannot be inserted into tables being replicated because it could potentially cause duplicate primary key insert problems. There are about 8 databases that would need to be replicated over with an average size of greater than 100 GB. What solutions have you implemented to resolve these problems?

Thank you,

D

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/29/2010 :  20:24:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
Can you do a backup/restore type thing for them instead?

Replicating from production is not a good idea as that puts a load on production, which is not advised for development use.

We use backup/restore for our developers. We do it bi-weekly.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/30/2010 :  03:31:16  Show Profile  Reply with Quote
An on-demand backup/restore is, as Tara says, the best way to go. Having it done on a schedule though has caused quite a bit of screaming in the hallways of the office a few times. Developers have a tendency to forget that they put stuff in the database and when it gets overwritten...well...screaming. I've come to the conclusion that doing this manually is the best way and also, instead of overwriting the dev-database, just renaming it and keep it put for a few days just in case. You might say that the developers should have more discipline and have control over this but at least the ones I've worked with just don't have the required control... :)

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/30/2010 :  12:51:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
We do the backup/restore onto a second dev system. So the one that they are actively developing against with database schema and data changes don't get overwritten. Having a copy of production makes a great system to debug on.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

danielc
Starting Member

48 Posts

Posted - 08/30/2010 :  15:06:59  Show Profile  Reply with Quote
Thank you responding. I have found that backup/restore creates a lot of screaming by the developers for about a week. They tend to forget that all their objects get deleted when a restore takes place. In the past I used to provide a template where they would insert there functions, stored procedures, and other scripts for objects that needed to be re-created after the restore. But that became a headache because of dependency issues. Another question would be, what about log shipping to environments? Would this cause more or less stress on production servers than replication? Additionally, how much manual intervention takes place with the backup/restore? Currently, I manually copy all backups from production and restore in environments. I have thought about automating the process and have looked at some scripts written by bloggers but before I went ahead and did that I wanted to get an idea of what yall do...

Thanks,

D
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/30/2010 :  15:17:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
I documented the process I use to automatically refresh dev/test: http://weblogs.sqlteam.com/tarad/archive/2009/02/25/How-to-refresh-a-SQL-Server-database-automatically.aspx

Log shipping is not a viable solution for this as it'll require that all users be dropped for each restore, severing the developers' connections every time you restore. Log shipping is typically done every 15 minutes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/31/2010 :  03:12:37  Show Profile  Reply with Quote
Another problem with log shipping is that the database will be in recovery. You can set it to standby mode which in essence makes it read-only but for development purposes a log shipped database is basically useless.

The best way to go (at least to my experience) is to follow the advice in Taras blog, except that I would rename the old database instead of overwriting it, and then restore the backup with the original database name. When I do the restore I also rename the new data/log-files and add the current date to the filenames. That way all the data/log-files can be in the same folder, I don't have to create a new folder for every restore. Heres "my order of play" using the database "MyDatabase":

1. Copy a full backup to the dev server
2. On the dev server, rename "MyDatabase" to "MyDatabase_old"
3. Restore the full backup as "MyDatabase" and rename data/log-files to MyDatabase_20100831.mdf/ldf
4. Give db_owner/run unorphan-script/etc

I've always done this manually though so I usually make sure to delete the old database after a week or so.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000