SQLServerWiki

“The Only Thing That Is Constant Is Change”

Merge replication from subscriber to publisher only.

Posted by database-wiki on July 10, 2012

One of my customer had a unique scenario, He wants to replicate data from subscribe to publisher only. The clients will be using sqlexpress (filestream database) that will hold the pictures taken during site visit for example the progress in drilling the well and the wareout of the drills used. Once the pictures were taken he wanted to replicate this to the publisher on demand. He does not want the publisher data to reach the clients(subscribers).

We can do this in couple of ways and below are the following:

A. Parameterized Row Filters. Because of the following reason we did not implement this:

1. Parameterized row filter will cause schema changes to the publisher table for example, implement a column “owner” this will action as a filter condition to sync data to that particular owner also they dont have time to implement this as it will take couple of weeks.

2. Schema change might cause issues with the application as well.

3. Computational time taken when filter is involved.

B. Using NO SYNC and EXCHANGETYPE parameter 1 in merge replication with pull subscription.

  1. First I created a database called PHOTOS.
  2. I created table PHOTOS with following structure and inserted two rows. Please see the screen shot.

USE [PHOTOS]

GO

 

/****** Object:  Table [dbo].[PHOTOS]    Script Date: 07/10/2012 08:03:22 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[PHOTOS](

            [emp] [int] NOT NULL,

            [empname] [varchar](10) NOT NULL,

            [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

ALTER TABLE [dbo].[PHOTOS] ADD  CONSTRAINT [DF_PHOTOS_rowguid]  DEFAULT (newsequentialid()) FOR [rowguid]

GO

NOTE: you need to add the ROWGUID column manually. If you allow replication setup to create ROWGUID column, you will have the following error. This is by design behavior so you need to add the ROWGUID column manually.

Command attempted:

{call sp_MSsetconflicttable (N’photos’, N’MSmerge_conflict_PHOTOS_photos’, N’ALL-IS-WELL-MS\SQL2008R2ENT’, N’PHOTOS’, N’PHOTOS’)}

Error messages:

  • Incorrect syntax near ’empid’. (Source: MSSQLServer, Error number: 102)
    Get help: http://help/102
  • The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
    Get help: http://help/MSSQL_REPL-2147201001

WORKARROUND:

===========
Workaround:
===========
1. Delete all subscriptions and publications created on that database.
2. Modify the design of each of the tables that need to be published => Add a
column to the table with the following properties:
a. Name => rowguid

b. Data Type => uniqueidentifier
c. Default Value => newid()
d. RowGuid Property => Yes
3. Take a backup of the database and restore it on the subscriber. Make sure that
the rowguid column is present on the subscriber.
4. Create the publication. While creating the publication, set the Action for all
acticle properties to “Keep existing object unchanged”.
5. Now, proceed with the subscription creation. Instead of creating the
subscription, script it out. Now, change the script to have @sync_type = N’None’
instead of @sync_type = N’Automatic’.
6. Run the script to create the subscription.
7. Make sure that the Snapshot agent has completed and is successful.
8. Now run the Merge Agent. Merge Agent should now succeed.

3. Now the database is created I am going to create the publication and run the snapshot agent so the meta data get populated.

4. Take a backup of PUBLISHER database in my case PHOTOS and restore it in SUBSCRIBER also truncate all the row. Make sure you provide the correct location for the database files.

5. Run the mergeagent job once so that it can get initialized after which you need to add this parameter.

So there you go. We have achieved what we wanted to.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: