Dynamic Snapshots in SQL Server Merge Replication

By:   |   Updated: 2018-10-19   |   Comments (2)   |   Related: > Replication


Problem

We have a very large SQL Server database and we need to configure SQL Server merge replication on the database. Subscribers are located at remote locations and connectivity is poor. Creating a snapshot and applying the whole snapshot is taking a lot of time.

Solution

Applying the snapshot from the default location was taking too much time, so we zipped the snapshot and moved it to the remote subscribers and applied the snapshot locally by specifying the alternate snapshot folder in the subscription properties. But, we want to limit data at the snapshot level itself, how can this be done?

In our case, the publisher holds all of the data and the subscriber holds specific data for that location. So, we used filters on the articles and generated dynamic snapshots for each subscriber.

SQL Server Dynamic Snapshots

Dynamic snapshots are created based on filters. Each subscriber will have its own snapshot and data specific to it.

Before generating dynamic snapshots, the below steps need to be performed:

  1. Create the merge publication with at least one or more parameterized row filters.
  2. Generate a schema snapshot for the publication.

Now we can generate dynamic snapshots manually or automatically.

Setup SQL Server Merge Replication in Management Studio

For this tip I created a sample database on the same server and simulated as if it was a remote server. Replication was also enabled for this server.

  • Login to SQL Server and create databases PUB, SUB1 and SUB2.
  • Create table TEST in the PUB database and insert the below sample data. 
CREATE TABLE TEST (ID INT NOT NULL, NAME VARCHAR(MAX), ORGID VARCHAR (50)) 
GO 
  
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (1, N'Adam', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (2, N'Anil', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (3, N'Bob', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (4, N'Mary', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (5, N'Paul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (6, N'Scott', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (7, N'Atul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (8, N'Uday', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (9, N'Anush', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (10, N'Amir', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (11, N'Ben', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (12, N'Brown', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (13, N'Robin', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (14, N'Rehman', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (15, N'Ravi', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (17, N'Anil', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (18, N'Bob', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (19, N'Mary', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (20, N'Paul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (21, N'Scott', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (22, N'Atul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (23, N'Uday', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (24, N'Anush', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (25, N'Amir', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (26, N'Ben', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (27, N'Brown', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (28, N'Robin', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (29, N'Rehman', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (30, N'Ravi', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (16, N'Adam', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (31, N'Adam', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (32, N'Anil', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (33, N'Bob', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (34, N'Mary', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (35, N'Paul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (36, N'Scott', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (37, N'Atul', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (38, N'Uday', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (39, N'Anush', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (40, N'Amir', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (41, N'Ben', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (42, N'Brown', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (43, N'Robin', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (44, N'Rehman', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (45, N'Ravi', N'ORG1') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (46, N'Adam', N'ORG2') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (47, N'Anil', N'ORG2') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (48, N'Bob', N'ORG2') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (49, N'Mary', N'ORG2') 
GO 
INSERT [dbo].[TEST] ([ID], [NAME], [ORGID]) VALUES (50, N'Paul', N'ORG2') 
GO 

ALTER TABLE [dbo].[TEST] ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([ID] ASC ) ON [PRIMARY]
GO

Navigate to Replication > Local Publications and right click New Publication and the wizard will open as shown below.

publication properties

Click Next. Then select the PUB database and click Next.  Select Merge publication and click Next. Check SQL Server 2008 or later and click Next. 

Expand Tables and select the TEST table as shown below and click Next.

publication properties

A message will show that a unique identifier will be added to the table. Click Next on this screen.

On the Filter Table Rows screen, click the Add button and select Add Filter.

publication properties

Add the below filter in the filter statement.  Also, select the A row from this table will go to only one subscription and then click OK.

SELECT <published_columns> FROM [dbo].[TEST] WHERE ORGID=HOST_NAME()

The screen will then show the filter that was just created and then click Next.

Select Create a snapshot immediately and click Next. 

Specify the Snapshot Agent security settings and click Next.

Click on Create the publication and click Next.

Enter Publication name as DYNAMICSNAP or any name you want and select Finish.

We created the publication and generated the schema snapshot. Now we need to generate dynamic snapshots.

Creating Dynamic Snapshots in SQL Server Management Studio

In our case we are copying snapshots to a remote subscriber and applying it locally using the alternate snapshot folder. So, I am generating dynamic snapshots manually.

  • Go to Replication > Local Publications and right click on the publication we just created and select Properties.
  • Select the Data Partitions page and click Add to add the HOST_NAMEs.  I created ORG1 and ORG2 as shown below.
  • Select both rows that were just created and click on Generate selected snapshots now.
publication properties

Now the subscriber specific snapshots are generated. Go to snapshot folder, this can be found in the Snapshot page in the above image, and we can see the different snapshot folders that were created for each partition as shown below. Each partition will have its own replication files like bulk copy script (as per filter), schema script, procedure script, etc.

dynamic snapshots sql server merge replication 005

Note: If you want to create dynamic snapshots automatically when subscriber syncs initially check the below option. You can ignore this in our case.

publication properties
  • Now I created two different folders as shown below. In the case of remote servers, the folder structure will be created on the remote servers.
    • D:\Replication\ReplData\ORG1
    • D:\Replication\ReplData\ORG2
  • Now create folder structure in ORG1 and ORG2 as shown below. The server name should be replaced by your server name. You can find the similar folder in the snapshot folder.
    • D:\Replication\ReplData\ORG1\unc\SERVERNAME_PUBLISHERDB_PUBLISHERNAME
    • D:\Replication\ReplData\ORG2\unc\SERVERNAME_PUBLISHERDB_PUBLISHERNAME
  • Copy the schema snapshot folder and partitioned snapshot folder to the respective folders.
    • In case of ORG1 we need to copy 20181010142648 and ORG1 folder to D:\Replication\ReplData\ORG1\unc\SERVERNAME_PUBLISHERDB_PUBLISHERNAME
    • In case ORG2 we need to copy 20181010142648 and ORG2 folder to D:\Replication\ReplData\ORG2\unc\SERVERNAME_PUBLISHERDB_PUBLISHERNAME

Setup SQL Server Subscribers

Publisher Configuration

  • Now go to Replication > Local Publications and right click on the publication DYNAMICSNAP and select New Subscriptions and click
  • Next.
  • Select DYNAMICSNAP and click Next.
  • Select Run each agent at its Subscriber (as subscribers are remote servers and we are applying the snapshot locally) and click Next.
  • Select the subscriber machine and select SUB1 as the subscription database and click Next.
  • Select which accounts you will use to connect to the publisher and subscriber and click Next.
  • Select Run on demand only and click Next.
  • Select the Initialize checkbox and in the Initialize When dropdown select At first synchronization and click Next.
  • For the Subscription Type dropdown select Client and click Next.
  • For the HOST_NAME Value enter ORG1 (so that ORG1 partitioned snapshot will apply) and click Next.
  • Select Create the subscription(s) and click Next.
  • Then click Finish to complete.

Subscriber Configuration

  • Now on the subscriber, go to Replication > Local Subscriptions and click on the subscription and select Properties.  Select snapshot location as the alternate folder and input the snapshot folder as D:\Replication\ReplData\ORG1 and then click OK.
subscriber snapshot settings
  • Now go to SQL Server Agent and select the appropriate merge job and schedule it as per your need.

Similarly add SUB2 as another subscriber with hostname as ORG2 following the same steps above.

Summary

  • Created merge publication with filters on articles.
  • Created partitioned snapshots (limited the size of snapshot for each subscriber).
  • Copied the snapshot to the remote server and applied the snapshot using the alternate folder (zipping the schema snapshot partitioned folder will reduce the size and copying to the remote subscriber will be faster).

This process helps where there is poor connectivity between the publisher and subscriber.

You can still use the default snapshot location and automatically generate the partitioned snapshots if connectivity between the publisher and subscriber is good without having to manually copy the data.

Next Steps
  • Please test this setup on DEV or QA environment.
  • You can still zip the data and apply the snapshot locally using the alternate snapshot folder even if you don't need to apply filters. 
  • Use pull subscriptions (to reduce load on the publisher as we have more subscribers).
  • Try to configure subscribers with different options like run continuously, define schedule, etc.
  • Check out other Replication Tips.
  • Check out other ways to Apply Snapshot Locally Using CMD.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2018-10-19

Comments For This Article




Friday, January 18, 2019 - 9:29:32 AM - Ranga Back To Top (78810)

Jon,

it depends on your network between publisher and subscriber.

there are different merge agent profiles which can be used based on your requirement.

We have used a subscriber to load data around a million rows using tools and they got replicated successfully with out any issues.

Thursday, January 17, 2019 - 6:32:46 PM - Jon Back To Top (78807)

Hello,

Thank you for your insights on Merge replication. Can you share if you have observed Merge replication used succesfully (including performance) to synchronize (bidrectionally) an OLTP DB instance with an secondary DB Instance dedicated to ingesting large transaction sets via SSIS packages or high volumes of transactions via APIs ?

Thank you.














get free sql tips
agree to terms