September 8, 2011 at 11:36 am
I'm implementing transactional replication on two databases on our network to send data to a test/report server. I've never configured a fresh SQL Server (2008), so this is a learning process.
Question is, can I still delete/update/create data on the subscriber without having any affect on the publisher?
If the answer is no, what suggestions do you recommend so I can still have the replication in place, but still be able to "play" with the data as needed? SSIS is my initial thought for this, but maybe there's a better solution?
September 8, 2011 at 1:09 pm
joshd 1807 (9/8/2011)
Question is, can I still delete/update/create data on the subscriber without having any affect on the publisher?
NO, especially for DELETE. Reason: if you delete a row for subscriber, and publisher modifies that row, the replication will not be able to find that row to apply the modification command, which causes the replication re-tries until fails.
I don't have any workaround for this if you still want to keep transaction replication running at the same. Maybe others can help.
Since you mentioned SSIS method, could you implement Snapshot replication?
Or create database snapshot against the subscriber?
September 8, 2011 at 1:26 pm
Snapshot replication is most definitely an option. I already need to have one database with this method since it used indexes instead of primary keys (not my choice :-)) so having 3 databases using snapshot replication wouldn't be a big deal. I just want current data to run reports/test against instead of using month old data as the previous DBA did. Looks like SSIS might be a good option to merge data into another new database since I'll need to manipulate some of the data.
That makes perfect sense with the DELETE scenerio and I would have never thought of that. So running snapshot replication is essentially like restoring the database with a .bak backup, except on an schedule?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply