Need Help Deciding if Replication Would Work in this Situation
-
End Goal: Replicate data from Server A to Server B, both servers in production, replicated tables containing identity fields and FKs inserted by both servers, break the connection to Server A after the data is synced, and then all data is now being collected on Server B.
Picture: You have server A, table A and server B, table A
Currently our setup is this:
v Data from Server A, table A, gets put in a staging table
v Every 10 minutes a SQL job does inserts into Server B, table A from that staging table based on the timestamp that the data was collected on
v Each row inserted from the staging table gets the next ID number in Server B, table A
Can we do the following with replication or has anyone done the following scenario already and have an opinion or recommendations on it?
v There are inserts happening to Server B, table A
v Data from Server A, table A is getting simultaneously inserted into Server B, table A through replication (Merge or Transaction??)
v There are identity fields on the tables and referential integrity that needs to be kept
-
There is no doubt that Replication maybe a better solution. WHich Replication model to choose is another question!
Merge would work howvere, Merge Replication is complex and most likely an overkill for your requirement.
From the description you have posted, I think Transactional Replication with Immediate Updateing Subscribers will be enough.
Questions?
1. Do you have to replicate any Image &/or text DataTypes? If you do then Merge Replication is the only Replication model that will do that.
2. If you answered NO to question 1, then Transactional Replication with Immediate Updating Subscribers will do the job for you.
3. If you choose YES to question 2, then you will have to make all identity columns "NOT FOR REPLICATION" and you will have to seed all identity columns on the subscribing database with a different range to the publishing database.
-
Do you want the data to also go from server B to server A if the data is directly inserted into server B's table?
-
Trigger and Ian -
Thanks for the responses.
I'm not replicating any Image or text data types so I will try what Trigger suggested in option number 2. Can I post back here if I have problems?
Ian - No, I don't want the data from Server B to go back to Server A. Basically, I'm trying to migrate the database from server A to server B while still accepting data with no 'down time'.
Jennifer
-
Hmmm - well then I am not sure if transactional replication with immediate updating subscribers would work as server B's updates would be sent to server A if B was the subscriber or the publisher. Someone - please correct me if I am wrong?
With merge replication, normally both sides synchronise with each other, but I have read about how you can effectively turn off the merge agent at one end so the updates flow in one direction only. Probably MS have a knowledge base article about it, and I'm sure that it has been discussed somewhere in these forums and possibly even a dedicated article.
-
Wouldn't transactional replication WITHOUT immediate updating subscribers work ? Then you'll just be replicating from A to B only.
-
Don't the subscribers have queued updates or immediate updates - can you have non-updates? I'm ignorant of lots of practical experience in the area unfortunately - particularly with trans repl.
-
You have to check the Advanced Options box in the create Publication wizard in order to get the option to enable subscriber updates, otherwise the default is no updating. I was just trying it, and I couldn't get it to update the publisher table when I enabled anyway .... Must've missed something. I do fairly "vanilla" replication here: Snapshot & transactional from A to B with a few filters here & there, so I'm no expert.
Jennifer, I have a test database on A & B that I use to experiment, so you might want to do the same. Set it up as well as you can & mimic production on a small scale & see what happens. You only need a table or 2 and a few rows of data to work with.
-
Hi Jennifer,
Having read the posts, it looks like Merge Replication is the right solution for you. Each server will have it's own ranges of IDs so there shouldn't be any problems entering data each end.
The only issue I have come across is when the replication has been shut down (which it can do by design and then catch up once it has been restarted) and then restarted, you may get the FK values attempting to insert before the corresponding PK value has been replicated (this is because they are in a different batch). One way to avoid this is to increase the batch size and keep merge downtime to a minimum.
Other than that, with adequate monitoring, this is a good way to have data entered in two locations that are then synchronised.
Regards..Graeme
-
Thanks to everyone for your help and answers. I'm in the process of setting up a couple of different 'sets' of test databases to mimic some of the scenarios mentioned above. It would be great if I could get the ID range concept to work ... and set up some decent monitoring as Graeme suggested.
Jen
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply