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 |
|
danielc
Starting Member
49 Posts |
 Posted - 2010-08-29 : 18:57:56
|
| 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
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-30 : 03:31:16
|
| 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... :)- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
danielc
Starting Member
49 Posts |
Posted - 2010-08-30 : 15:06:59
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-08-31 : 03:12:37
|
| 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 server2. 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/ldf4. Give db_owner/run unorphan-script/etcI've always done this manually though so I usually make sure to delete the old database after a week or so.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
Coping data to enviroments