ayesamson

Recently during a deployment I ran through a replication setup rehearsal in order to practice my processes. It is always good practice to run through an upgrade rehearsal and document your steps, experiences and results. To give you a brief background in all of our deployments we use merge replication since we send and receive data between our server and mobile subscribers.

As I ran through the article selection process I selected the appropriate tables, indicated their sync direction (download only or bi-directional), followed by the stored procedures, views and ended with the functions as usual. We didn’t have an indexed views otherwise they would have went before the functions. Little did I realize that a view would so happen to reference two other views, meaning that in order for the initialization to complete successfully the two referenced views would need to be created first. The rule “you cannot put the carriage before the horse” definitely applies here.

So to clarify here is a simple scenario: you have a table article and a view which directly references the specific table… simple enough. Well you cannot have the view process before the table because it has a dependency which requires the table to be in place before the view can be processed. Otherwise the initial snapshot at the subscriber will fail. Hopefully that makes sense.

Well in the case with the two views, they needed to be processed before the initial referencing view can be applied. The quick and dirty work-a-round was to remove the article and reapply it, then generate a new snapshot. Honestly this is really not an acceptable practice even if you have a small subscriber base of three and especially not acceptable when dealing with subscriber numbers that exceed 200+. The problem with this is that if we introduce new subscribers they will fail at the initial snapshot delivery at this very article because of its dependencies. However the process works fine when you do a re-initialization with an existing subscriber.

So a method to rectify this issue is to identify the article processing order by running the sp_HelpMergeArticle as followed:

USE [AdventureWorks]
exec sp_HelpMergeArticle

It returned a result set of all the articles including the name (article name), upload_option (sync direction), processing_order and such. The information proved to be very useful as it identified the values for the processing_order were all set to zero. However in the event that you want information returned about a specific article then the following statement will do just that.

USE [AdventureWorks]
exec sp_HelpMergeArticle @publication = 'AdventureWorks_publication'
     ,@article = 'AddressType'

Well the default processing_order values were all set to zero based on the first query. Since all were set to zero the article would always revert back to its original position. Luckily there is an option to change the article by using the sp_ChangeMergeArticle store procedure:

USE [AdventureWorks]
exec sp_ChangeMergeArticle @publication = 'AdventureWorks_publication'
     ,@article = 'AddressType'
     ,@property = 'processing_order'
     ,@value = 10
     ,@force_invalidate_snapshot = 1
     ,@force_reinit_subscription = 0

Just to make sure it worked I re-ran the first query and sure enough the article in question dropped down to the very last position in the results set now that it’s processing_order value was set to 10. The real test revealed the true results when I staged a new subscriber by dropping the local database and clearing out the accounts. The delivery of the initial snapshot processed successfully without any further errors. Now I can rest better knowing that their support staff can add subscribers without running into this issue.