Lesson 2: Creating a Subscription to the Merge Publication

In this lesson, you will create the subscription using SQL Server Management Studio. You will then set permissions on the subscription database and manually generate the filtered data snapshot for the new subscription. This lesson requires that you have completed the previous lesson, Lesson 1: Publishing Data Using Merge Replication.

To create the subscription

1. Connect to the Subscriber in SQL Server Management Studio, expand the server node, expand the Replication folder, right-click the Local Subscriptions folder, and then click New Subscriptions.

   The New Subscription Wizard launches.

2. On the Publication page, click Find SQL Server Publisher in the Publisher list.

3. In the Connect to Server dialog box, enter the name of the Publisher instance in the Server name box, and click Connect.

4. Click AdvWorksSalesOrdersMerge, and click Next.

5. On the Merge Agent Location page, click Run each agent at its Subscriber, and then click Next.

6. On the Subscribers page, select the instance name of the Subscriber server, and under Subscription Database, select <New Database> from the list.

7. In the New Database dialog box, enter SalesOrdersReplica in the Database name box, click OK, and then click Next.

8. On the Merge Agent Security page, click the ellipsis (…) button, enter <Machine_Name>\repl_merge in the Process account box, supply the password for this account, click OK, click Next, and then click Next again.

9. On the Initialize Subscriptions page, select At first synchronization from the Initialize When list, click Next, and then click Next again.

10. On the HOST_NAME Values page, enter a value of adventure-works\pamela0 in the HOST_NAME Value box, and then click Finish.

11. Click Finish again, and after the subscription is created, click Close.

Setting database permissions at the Subscriber

1. Connect to the Subscriber in SQL Server Management Studio, expand Databases, SalesOrdersReplica, and Security, right-click Users, and then select New User.

2. On the General page, enter <Machine_Name>\repl_merge in the User name box, click the ellipsis (…) button, click Browse, select <Machine_Name>\repl_merge, click OK, click Check Names, and then click OK.

3. In Database role membership, select db_owner, and then click OK to create the user.

To create the filtered data snapshot for the subscription

1. Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.

2. In the Local Publications folder, right-click the AdvWorksSalesOrdersMerge publication, and then click Properties.

   The Publication Properties dialog box is displayed.

3. Select the Data Partitions page, and click Add.

4. In the Add Data Partition dialog box, type adventure-works\pamela0 in the HOST_NAME Value box, and then click OK.

5. Select the newly added partition, click Generate the selected snapshots now, and then click OK.

Next Steps

You have successfully created a subscription to the merge publication and generated the filtered snapshot for the new subscription's data partition so that it will be available when the subscription is initialized. Next, you will grant rights to the Merge Agent on the subscription database and run the Merge Agent to start synchronization and initialize the subscription. See Lesson 3: Synchronizing the Subscription to the Merge Publication.

See Also

Concepts

Subscribe to Publications

Create a Pull Subscription

Snapshots for Merge Publications with Parameterized Filters

Other Resources

NIB Create a Pull Subscription (Replication Transact-SQL Programming)