Sql-server – Controlling the order in which merge articles are applied by snapshot at initialization

replicationsql-server-2008

I have a replication scheme that was created several months ago. Since that time the schema has had a variety of changes. All existing subscriptions were updated as these changes were made as the publication is set to push schema changes as well.

Recently however when trying to add a new subscription the initial snapshot fails to apply. The cause of the error is that two different views fail to apply as tables they rely on have not been created by the snapshot yet.

I know that I can specify processing order of articles via the sp_changemergearticle stored procedure. But as I understand… the processing order setting here is for specifying the order in which data changes are applied to subscribers during sync. Is there a way to specify the order in which articles are applied when a new subscription is initialized? Does it use the same setting?

I figured that generating a new snapshot would cause the engine to figure out the proper order but in the case of my current publication it did not. The new subscription fails to apply the snapshot even after I have generated a new snapshot. It tells me the exact script that fails and when applying by hand it's obvious that it's failing because a table needed by the view does not yet exist.

What is the best way to fix this? Does setting the processing order of an article affect the schema order as well as the data order or do I need to look somewhere else to set the order that articles are built on new subscriptions.

From what I can tell the processing order setting has no effect whatsoever on the order in which the snapshot applies its articles during initialization. Mostly because currently all my articles have a value of 0 for their processing order. So, is this something I can control or am I at the mercy of the snapshot generation process?

Best Answer

Yes, utilizing sp_changemergearticle to change the processing order will alleviate this issue.