SQL Publisher / Subscriber

I have a question regarding publisher / suscriber configuration in SQL.  How does conflict resolution work? For example, what if 2 databases have been disconnected for a while and the publisher / subscriber configuration has to be recreated?
LVL 3
albatros99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
If the subscription has to be recreated, then there are two options when you reinitialize is:
1 - Upload changes - This might cause conflicts. How conflicts are resolved depends on your configuration. There are several models. This article gives a nice discussion of conflict resolution in SQL Server http://www.brentozar.com/archive/2011/06/resolving-conflicts-database/
2 - No uploading of changes (if the subscription has actually expired, this is the option you will likely have to take) - probably no conflicts.
Vitor MontalvãoMSSQL Senior EngineerCommented:
What do you mean with "disconnected"?
Anyway if you'll recreate means that you'll need to run a new snapshot so the subscribed articles will need to be recreated (drop & create process), losing everything that it has, like data and triggers.
albatros99Author Commented:
Disconnected means, there has been no network connectivity.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If the fail is only because network connectivity then the replication should start replicating after recover from the network fail. If not, then you can do it manually by start the replication agent/job.
albatros99Author Commented:
Let me rephrase the question: I have DB A and DB B. The are syncing as Publisher / Subscriber. The connection is down for a couple of days. Changes are being made on both sides. Because of continued network problems, the admin removes the publisher / subcriber relationship, fixes the network problem, then recreates the publisher / subscriber relationship. DB A is in state Z and DB is in state Y.  

Will the two DB's converge without data loss?
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, this is tricky one because depends on the objects that you have in each database.
If it's a simple table replication then after recreation it should be all fine but for example, if you have triggers, those triggers may not ran for the records changed (insert, delete and update operations) during the replication break. So, in these cases you'll need to run a touch procedure that is not more than update a table without changing data values only with the purpose of activating the trigger. Example: UPDATE TableA SET columnA = columnA (this won't change the columnA value but will fire the update trigger to perform any necessary operation.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.