Merge Replication: What's "Reinitialize" and "Validate Subscription"
-
When we install a new version of our software for a client, there may be new tables, new columns, new stored procedures, changed stored procedures and whatnot, including triggers and functions.
The support guy that does this sort of thing is certain that all he needs to do is run the installer at the publisher, then go down and click "Reinitialize" on all the subscribers. The next day, of course, it's development's job to get them going again.
The only way I've found to install an update for a replicated system is:
1. Let the system settle so you're sure the publisher has all the latest data from subscribers.
2. Delete the subscribers.
3. Delete the publication.
4. Install the update (adds tables, columns, changes or adds procedures, functions, triggers.)
5. Create the publication again.
6. Create empty databases at the subscribers.
7. Create subscriptions to connect the publication to the empty databases.
8. Wait a very long time for the snapshot to build the subscribers.
I try to tell him, but I'm the lowly Director of Development while he's the high and exalted Vice President of Support.
I've tried, without success, to use sp_add_merge_article and sp_startpublication_snapshot to get all this to take place automagically. The subscriber status keeps saying it can't add the foreign keys:
The schema script ' if object_id(N'[dbo].[FK_PayableHeader_PayableDiscount]') is null if object_id(N'[dbo].[PayableDiscount]') is not null exec('ALTER TABLE [dbo].[PayableDiscount] WITH NOCHECK ADD CONSTRAINT FK_PayableHeader_PayableDiscount FOREIGN KEY( PayableHeaderPK) REFERENCES PayableHeader (PayableHeaderPK) ON DELETE CASCADE NOT FOR REPLICATION')' could not be propagated to the subscriber.
If said VP sees this, I get a call at like 3AM demanding to know why it's not working.
(Somehow, the clients always have several gigabytes of data, are running the publisher on a discarded machine with 128mb ram running Win 98, and have a 110bps dial up line connecting it to the subscribers. Ok, I admit to exaggerating, but not big exaggerations.)
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font] -
Changes to tables and new tables can be propagated from publisher to subscribers incrementally, without any need for reinitializing. We do not replicate other objects (proc, func, views etc). They are all deployed to each subscriber.
There are a few things to keep in mind regarding new tables and changes to tables.
- You must rebuild the publication snapshot when new tables are added. The replication agents will fail until the new snapshot is created.
- If you add new tables to a publication, then do not add any foreign key constraints referencing any of the new tables, to existing tables. That could be the reason for the kind of errors you posted. The correct way to do it, based on my experience, is to add the new tables, rebuild snapshot and let the merge agent publish the new tables. When the new tables have been added to all the subscribers, then you can go ahead and add the foreign key constraints.
We have also experienced the errors that you describe, and there was no change in hell that we could reinitialize all our subscribers. First of all the database is 50GB, and secondly, our subscribers have extremly poor connections to our publisher, so a reinitialization would at leat take two to three days.
We fixed it by dropping the offending foreign key constraint on the publisher, and the deleted the foreign key script in a system table related to merge replication on the Publisher. This leads me to write with capital letters: MODIFICATIONS TO SYSTEMS TABLES ARE NOT RECOMMENDED UNDER ANY CIRCUMSTANCES. IT COULD POTENTIALLY HAVOC YOUR WHOLE SYSTEM, AND WILL PROBABLY GET YOU IN TROUBLE IF YOU CALL MICROSOFT SUPPORT AND THEY FIND OUT WHAT YOU HAVE DONE. But it was our only option... unless you consider restore from backup.
-
Interesting. Thanks.
I avoid the system tables
So don't add the foreign keys for new tables until after...
Still investigating. Yes, the clients revolt when we have to take them down for days.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font] -
Yes. Don't add foreign key constraints to columns on existing tables to new tables before the new tables have been replicated.
At least that is my experience.
-
I have seen it trying to add rows to tables before it added the table 😉
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply