September 3, 2008 at 10:31 am
we have two locations who's SQL databases need to be kept fully in sync.
For server A->B, I've setup a transactional replication, all tables included, agent scheduled to run on an hourly basis. Works perfectly fine.
Now, to replicate the changes from DB back from server B->A, whenever I setup the publisher and subscriber and take a look at the replication monitor after the snapshot was succesfully created, I continually receive scheme replication errors, such as "cannot drop table XXXX as it is being used for replication". Removing the table/view from the articles.
I get a message that the initial snapshot is not available server A.
Can anyone please help me in solving this problem
Thanks
Prateek
September 3, 2008 at 10:42 am
Based on what you are saying it sounds like you should be using merge replication not transactional.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
September 3, 2008 at 12:09 pm
You cannot set up transactional replication in both directions like that. You either need to configure your transactional replication with the updatable subscriber options, or you need to use merge replication.
2 way replication gets a bit complicated. I would recommend you read the books online sections regarding this, get a book, and if this is an important production envrionment you may want to consult someone with some experience with MSSQL replication.
September 3, 2008 at 3:54 pm
In addition you could consider peer-to-peer replication 🙂
Previous advice is very wise. Consult with a professional if this is a Production level environment. Replication has many quirks!
* Noel
September 5, 2008 at 7:55 am
Thanks guys
Peer to Peer transacitonal replication worked fine in my case.
Although the dev team has to make some changes in the Db.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply