| Author |
Topic |
|
glt101
Starting Member
28 Posts |
 Posted - 2011-08-29 : 17:36:08
|
| Hi,We have a production server (SQL Server 2008 R2)whose data we want to replicate to a single test system (likewiseSQL Server 2008 R2).The test server will have test data placed on it that is not presenton the production server.After replication, we want the test database to contain both the testand the all the production server data.Also after replication, we want the live server database NOT tocontain any production data.This seems to be a good candidate for Merge replication, but I wasworried that the test data might be merged into the productiondatabase as well.Cheers,Geoff |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glt101
Starting Member
28 Posts |
Posted - 2011-08-29 : 17:48:44
|
| Hi tKizer,This will be an ongoing replication....and that was an awful typo on my part. It should have read:Also after replication, we want the live server database NOT tocontain any TEST data.Meaning no data should leak back to the production server from the test server.Cheers,[one embarrassed!]Geoff |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glt101
Starting Member
28 Posts |
Posted - 2011-08-29 : 18:02:31
|
| Hi tKizer,But wouldn't Transactional replication wipe the Test datathat already existed on the test server?Cheers,Geoff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glt101
Starting Member
28 Posts |
Posted - 2011-08-29 : 18:39:25
|
| OK Tara,Thanks for the help - we'll give it a shot(after relevant backups of course!).Cheers,Geoff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-08-29 : 23:02:20
|
| I wouldn't likely use transactional replication from production to dev.The developers are highly likely to break it often, which could have performance impact on the publisher and/or distributor. It also means you'll be re-initializing it often.I'd choose snapshot replication, nightly, instead. But...snapshot will wipe the test data. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-29 : 23:24:00
|
| Yeah that's an important point that I should have addressed rather than just answering the question. If the developers do break replication by doing something on the test system, replication would start backing up the transaction log in production. This could cause a system outage in production. What I normally do in a situation like this is just backup/restore and then have scripts to re-apply the test data. But that really depends on how much test data you have.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
glt101
Starting Member
28 Posts |
Posted - 2011-10-20 : 19:59:12
|
| Hi All,I didn't get notice of the last 3 replies for some reason,but thanks for the info. and sorry for this late comment from me.It appears that transactional replication works at first.But then I get the error"The row was not found at the Subscriber when applying the replicated command"Reading around suggests that the production and test databases aresupposed to be in sync at the start. But that is not what we have.They will always be different. We just want *new* data added at theproduction site to be copied to the development server.Backing up and restoring the test data is not feasible becuasethe developers are global and are working 24/7. If we overwrite theirnew test accounts everyday they will not be happy!Is there no way to replicate new data to the development side withoutwiping what's already there?Cheers,Geoff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
glt101
Starting Member
28 Posts |
Posted - 2011-10-20 : 20:36:00
|
| Hi tkizer,I see. So there's no way to start the replication wit he databasesout of sync then?Cheers,Geoff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
...and best type of replication for us?