Lesson 1: Publishing Data Using Merge Replication
In this lesson, you will create a merge publication using SQL Server Management Studio to publish a subset of the Employee, SalesOrderHeader, and SalesOrderDetail tables in the AdventureWorks2008R2 sample database. These tables are filtered with parameterized row filters so that each subscription contains a unique partition of the data. You will also add the SQL Server login used by the Merge Agent to the publication access list (PAL). This tutorial requires that you have completed the previous tutorial, Preparing the Server for Replication.
To create a publication and define articles
Connect to the Publisher in SQL Server Management Studio, and then expand the server node.
Expand the Replication folder, right-click Local Publications, and click New Publication.
The Publication Configuration Wizard launches.
On the Publication Database page, select AdventureWorks2008R2, and then click Next.
On the Publication Type page, select Merge publication, and then click Next.
On the Subscriber Types page, ensure that only SQL Server 2008 is selected, and then click Next.
On the Articles page, expand the Tables node, select SalesOrderHeader and SalesOrderDetail, then expand Employee, select BusinessEntityID or LoginID, and then click Next.
On the Filter Table Rows page, click Add and then click Add Filter.
In the Add Filter dialog box, select Employee (HumanResources) in Select table to filter, click the LoginID column, click the right arrow to add the column to the WHERE clause of the filter query, and modify the WHERE clause as follows:
WHERE [LoginID] = HOST_NAME()Click A row from this table will go to only one subscription, and click OK.
On the Filter Table Rows page, click Employee, click Add, and then click Add Join to Extend the Selected Filter.
In the Add Join dialog box, select Sales.SalesOrderHeader under Joined table, click Write the join statement manually, and complete the join statement as follows:
ON Employee.BusinessEntityID = SalesOrderHeader.SalesPersonIDIn Specify join options, select Unique key, and then click OK.
On the Filter Table Rows page, click SalesOrderHeader, click Add, and then click Add Join to Extend the Selected Filter.
In the Add Join dialog box, select Sales.SalesOrderDetail under Joined table, click OK, and then click Next.
Select Create a snapshot immediately, clear Schedule the snapshot agent to run at the following times, and click Next.
On the Agent Security page, click Security Settings, enter <Machine_Name>\repl_snapshot in the Process account box, supply the password for this account, and then click OK. Click Finish.
On the Complete the Wizard page, enter AdvWorksSalesOrdersMerge in the Publication name box and click Finish.
After the publication is created, click Close.
To view the status of snapshot generation
Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
In the Local Publications folder, right-click AdvWorksSalesOrdersMerge, and then click View Snapshot Agent Status.
The current status of the Snapshot Agent job for the publication is displayed. Ensure that the snapshot job has succeeded before you continue to the next lesson.
To add the Merge Agent login to the PAL
Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
In the Local Publications folder, right-click AdvWorksSalesOrdersMerge, and then click Properties.
The Publication Properties dialog box is displayed.
Select the Publication Access List page, and click Add.
In the Add Publication Access dialog box, select <Machine_Name>\repl_merge and click OK. Click OK.
Next Steps
You have successfully created the merge publication. Next, you will subscribe to this publication. See Lesson 2: Creating a Subscription to the Merge Publication.