Merge Replication Step by Step

In an earlier post, I mentioned Merge replication and the
scenario to use it. Below is a step by step guide for configuring merge
replication.

A. Configure Distribution for
the SQL server:
     

(Note: This step assumes that you want to
configure the publisher as its own distributor)

1.
Open SQL Server 2008
Management Studio

2.
On Central Database, select
replication, Configure Distribution

3.
Select the server to be its
own distributor

4.
Select a snapshot folder

5.
Leave the Database name as
distribution

6.
Select Database files
(select like other already present databases)

7.
Select the publisher server
to be the same server (default setting)

8.
Select Configure
distribution as well as generate script for the distribution

B. Configure a New Publication:

1.
Select New Publication

2.
Select Database you want for
publication

3.
Select merge Replication

4.
Select Subscriber Types to
be SQL 2008

5.
Select the articles that
you want to publish

For each table note to set the following:

                                                              
i.
Synchronization Direction:

Bidirectional, Download-only to subscriber (allow subscriber changes),
Download-only to subscriber (prohibit subscriber changes)

                                                            
ii.
Partition Options

                                                           
iii.
Publisher Range Size

                                                          
iv.
Subscriber Range Size

                                                            
v.
Range Threshold

6.
Set Filtered Rows (if you
need to filter rows)

                                                              
i.
Add the following condition

1.
Select
<published_columns> from [dbo].[city] where [login_username] =
SUSER_SNAME()

Note: This is based on the assumption that you want to split data to
different cities

                                                            
ii.
Select “Automatically
generate other filters”. This will generate additional filters based on the
condition you set earlier.

                                                           
iii.
Note: For this step to
work, a table need to be created (appended) that contains the loginName for
each city. This table needs to have referential constraint with all the tables
that will be filtered.

 

7.
Set the snapshot to start
immediately

                                                              
i.
Set the snapshot agent to
run daily at 12:01 AM

8.
Specify the account that
the snapshot agent will run under

Enter a Windows account. The Windows
account must:

·
At minimum be a member of
the db_owner fixed database role in the distribution database.

·
Have write permissions on
the snapshot share.

[Admin on the DB Server][SysAdmin on SQL
Server]

                                                              
i.
Keep Connect to the
Publisher to be impersonating the process account

9.
Select Create the
publication as well as generate script for the creation

10.
Give the publication a
meaningful name

C. Create a subscription to
the published database:

1.
Create an AD account for
each city

                                                              
i.
[Admin on the DB Server]

                                                            
ii.
SysAdmins on SQL Server

2.
On the server that will
receive the subscription, create a SQL Account with sysadmin permissions

3.
Create a new database at
the remote server to receive the publication (leave the database empty)

4.
From the newly created
publication, select New subscription

5.
In the Wizard, Select the
publication you want to subscribe to (It is selected by default)

6.
Choose Run all agents at
the distributor

7.
Add Subscribers to the
publisher (Select the server name and the account created in step 2)

8.
Select the subscription
database created in step 3.

9.
For the Merge Agent
Security, select the domain account created for this specific city

                                                              
i.
Keep Connect to Publisher
and Distributor as “Impersonate the process account”

                                                            
ii.
Set the Connect to
Subscriber to be “Use the Following SQL Account” and select the account created
in step 2

10.
In the synchronization
schedule, choose run continuously

11.
In the Initialize
Subscriptions, choose Initialize Immediately

12.
Keep the subscription type
as per the default

13.
Select Create the
subscription as well as generate script for the creation

You can repeat step C to create additional subscriptions (cities)
you want to publish to.