How To Create a New Publication for Merge Replication

Overview

This document provides step-by-step instructions on how to create a new Publication for database synchronization using Microsoft’s SQL Server Merge Replication technologies. In addition to using these instructions during the initial setup of the MIDS mPOD (Mobile Proof-Of-Delivery) application, we have also found them helpful when adding, deleting or changing columns to the server database (assuming that no important information has not yet been synchronized with the server) since it is often easier to simply delete any existing publication(s) and start over rather than trying to modify an existing publication.

---

---

Things You Will Need to Know

Item	Value
Database Name	mPOD (for our demo)
Publication Name	mPODPublication (for our demo)
Windows Login/Password	MIDS-MPODDemo

 

---

---

Assumptions

1. We will assume that the user has a basic knowledge of SQL Server Merge Replication.
2. Windows Security will be used by the Snapshot agent (this is a recommended “best practice”).

---

---

Step-By-Step Instructions

Step 1	Start the Publication Wizard
Step 2	Select the database that you want to publish. In this example select mPOD
Step 3	At the “Publication Type” screen, highlight the Merge publication row and click the Next button.
Step 4	At the “Subscription Types” screen, check the SQL Server 2005 Compact Edition option and click the Next button.
Step 5	At The “Articles” screen”, click the “+” symbol next to the Tables line (in the Objects to publish textbox). This will expand the list of available tables that can be published in the article.
Step 6	Click the checkboxes next to the following 4 tables: tLocation, tOrder, tOrderDetail and tTransactionLog. Click the Next button.
Step 7	At the “Snapshot Agent” screen, you can define rules for the Snapshot agent on when the snapshot publication will be created. We will just accept the defaults so click the Next button.
Step 8	From the “Filter Table Rows” screen, we can add “row filters” that can be used to reduce the amount of information that will be published in each article. This will help reduce the synchronization time and is critical for large database. If you want to add filters, click How To Add Filters to a Publication; for this example, we will not define any filters so click the Next button.
Step 9	At the “Agent Security” screen, we define the authentication rules for the Snapshot agent. Click the Security Settings button.
Step 10	At the “Snapshot Agent Security” screen, we will enter our Windows Security Information. Enter the Windows Login (Account) information and then click the OK button. This will return you to the “Snapshot Agent” screen where you will see your Windows Login information (serveruser) populated in the “Snapshot Agent:” textbox. Click the Next button.
Step 11	At the “Wizard Actions” screen, you can specify whether you want to create a script file. We normally skip this option and click the Next button.
Step 12	At the “Complete the Wizard” screen, you can enter the name of the publication in the textbox Publication Name:. For this example, we will enter “MPODPublication” in the textbox and click the Finish button. The wizard should then start creating the publication.
Step 13	At the “Creating Publication” screen, you can see the results of running the wizard.
Step 14	The publication should now be created and you can click the Close button to finish running the wizard.