Lesson 2: Creating a Subscription to the Transactional Publication
In this lesson, you will create a subscription using SQL Server Management Studio. This lesson requires that you have completed the previous lesson, Lesson 1: Publishing Data Using Transactional Replication.
To create the subscription
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 the AdvWorksProductTrans publication, and then click New Subscriptions.
The New Subscription Wizard launches.
On the Publication page, select AdvWorksProductTrans, and then click Next.
On the Distribution Agent Location page, select Run all agents at the Distributor, and then click Next.
On the Subscribers page, if the name of the Subscriber instance is not displayed, click Add Subscriber, click Add SQL Server Subscriber, enter the Subscriber instance name in the Connect to Server dialog box, and then click Connect.
On the Subscribers page, select the instance name of the Subscriber server, and select <New Database> under Subscription Database.
On the New Database dialog box, enter ProductReplica in the Database name box, click OK, and then click Next.
In the Distribution Agent Security dialog box, click the ellipsis (…) button, enter <Machine_Name>\repl_distribution in the Process account box, enter the password for this account, click OK, and then click Next.
Click Finish to accept the default values on the remaining pages and complete the wizard.
Setting database permissions at the Subscriber
Connect to the Subscriber in SQL Server Management Studio, expand Databases, ProductReplica, and Security, right-click Users, and then select New User.
On the General page, enter <Machine_Name>\repl_distribution in the User name box. Click the ellipsis (…) button, click Browse, select <Machine_Name>\repl_distribution, click OK, click Check Names, and then click OK.
In Database role membership, select db_owner, and then click OK to create the user.
To view the synchronization status of the subscription
Connect to the Publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
In the Local Publications folder, expand the AdvWorksProductTrans publication, right-click the subscription in the ProductReplica database, and then click View Synchronization Status.
The current synchronization status of the subscription is displayed.
If the subscription is not visible under AdvWorksProductTrans, press F5 to refresh the list.
Next Steps
You have successfully created a subscription to the transactional publication. Because the Distribution Agent for this subscription runs continuously, the subscription is initialized when it is created. Next, you will use tracer tokens to verify that changes are being replicated to the Subscriber and to determine latency. See Lesson 3: Validating the Subscription and Measuring Latency.