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.