Issue with SQL Server 2008 replication

Posted on

Question :

I’m doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error.

The Merge Agent failed because the schema of the article 
at the Publisher does not match the schema of the article at the Subscriber.
This can occur when there are pending DDL changes waiting 
to be applied at the   Subscriber. 
Restart the Merge Agent to apply the DDL changes and
synchronize the subscription.      
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)

Answer :

Filters

The simple answer is to use VIEWS for all your replication filters. That way, you can easily modify your filters without having to drop and re-subscribe all subscribers.

That is golden rule no. 1

DDL

If you modify any tables that are used in merge replication, you have to allow the DDL to apply down to the subscribers.
If you are worried about some tables replicating down at different times, maybe because they are in a different publication, and perhaps the FK will fail during replication inserts, then simply edit the FK’s and add ‘NOT FOR REPLICATION’.

That is my golden rule no. 2

If I can impress anything on you, please use views for your filters. They are lifesavers. We have over 100 subscribers to our databases, and they are the best thing since sliced bread. We can easily modify a VIEW and voila, all done, with no pain at all.

Leave a Reply

Your email address will not be published. Required fields are marked *