I have publisher and subscriber databases with identical schema, while some of the tables participate in replication and some don't. In some cases it is possible to have tables that have referential relationships between them, where one of the tables is replicated and the other isn't.

I've ran into various problems during initialization phase, because when the snapshot is applied, although it has various scenarios for dealing with existing objects, they often don't work well. For example, if the article properties dictate that the object should be dropped and recreated, an existing reference would get in the way of the drop.

On the other hand, if the article specifies "do nothing" for existing tables, that is literally what happens, which means that the table would not have the uniqueidentifier column (guid) which is used already by the rest of the snapshot's scripts which means the initialization fails.

What worked seemingly fine (and this is what I would like to ask about, if it's "legit" or not) was to manually add the guid column to the tables participating in replication some time before initialization from the snapshot happens.

I.e. say, I start with databases A_Pub and A_Sub which have identical structure to begin with. I then set up A_Pub as the publisher and generate the snapshot from it. Say, table A_Pub.Customer needs to be in sync with A_Sub.Customer. Now A_Pub.Customer already has A_Pub.Customer.guid (because we set up the publication, which added the column and populated it with data). And the snapshot has this column and the data. If I try to initialize the subscription now, snapshot won't get applied as A_Sub.Customer lacks the guid column. What I'm talking about is to run ALTER TABLE A_Sub.Customer ADD COLUMN guid [...] - manually, right before the subscription is initialized. In my experiments, this allowed the snapshot to get successfully applied and syncronization actually functions.

Is there something that I am failing to consider here? Is it a "normal" practice? And if not, what is?

Why can't I just initialize the replication over an empty subscriber and then put in everything else and create all relations and so on? Well, because the DB is massive and wasn't designed with replication in mind to begin with. So, I would have an incredibly hard time to script "proper" creation of all objects not participating in replication. (Or dropping all participating ones from a complete db).

Any thoughts on this are appreciate, but primarily the question is: manually adding guid column, is it "legal" or am I going to run into a different kind of problems some time soon?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.