Lesson 4: Subscribing

[Previous] [Next]

This lesson describes how to create a subscription, specify subscription characteristics, and specify how often to refresh data on the Subscriber with data from the Publisher.

After this lesson, you will be able to

• Set up subscriptions

Estimated lesson time: 50 minutes

Setting Up Push and Pull Subscriptions

The characteristics of a subscription are determined by the type of publication to which it subscribes and whether it is a push or a pull subscription. Each type of publication supports either a push or a pull subscription. Use the Push Subscription Wizard and the Pull Subscription Wizard to create subscriptions.

Depending on the publication and subscription types, to create a subscription, you may need to do the following:

• Specify the login credentials for the Subscriber agent if using a pull subscription
• Select the publication
• Select an existing subscription database or create a new one at the Subscriber
• Select one of the initial synchronization methods
• Specify a schedule for ongoing replication
• For subscriptions to merge publications, specify a subscription priority to be used when resolving conflicts during replication
• For subscriptions to publications that are enabled for anonymous subscription, specify whether the subscription must be anonymous

Push Subscriptions

You define a push subscription centrally at the Publisher. Push subscriptions are useful with centralized administration and are set up at the Publisher.

The type of subscription controls the execution of the agents that you use to manage replication. For snapshot and transactional replication, the Distribution Agent runs at the Distributor; for merge replication, the Merge Agent runs at the Distributor.

Pull Subscriptions

You define a pull subscription at each Subscriber. With pull subscriptions, Subscribers decide what to subscribe to and when to subscribe, which offloads administration and processing from the Publisher and the Distributor. Anonymous subscriptions offload even more of the work than regular pull subscriptions and are particularly suited to Internet subscriptions.

For snapshot and transactional replication, the Distribution Agent runs at the Subscriber; for merge replication, the Merge Agent runs at the Subscriber. For pull subscriptions, you can configure the initial synchronization to use FTP to transfer data.

Using the Immediate Updating Subscribers Option

For each subscription to a publication that has the option enabled, you can choose to use the Immediate Updating Subscribers Option. If this option is used, both the Publisher and the Subscriber must be running the Microsoft Distributed Transaction Coordinator (MS DTC).

Triggers are created in the table(s) on the Immediate Updating Subscriber to perform the two-phase commit update on the Publisher.

The next two sections discuss some restrictions to using this option.

Data Type Update Restrictions

When you are using the Immediate Updating Subscribers option, the Subscriber

• Cannot update timestamp, identity, text, or image data type values
• Cannot insert into a table that has a timestamp or identity column unless the table has a unique index

Row Identity Update Restrictions

When you are using the Immediate Updating Subscribers option, the Subscriber cannot update the unique index or primary key, because these are used to identify the row.

NOTE

---

If you drop a subscription and want to continue to modify a table at the subscribing site, you must drop triggers on the replicated table at the Subscriber manually.

Editing Subscription Properties

To view the properties of a push subscription on the Publisher, open the Properties dialog box for the publication. On the Subscriptions tab, select the subscription and click Properties. There are no editable properties for a push subscription.

To edit the properties of a pull subscription on the Subscriber, select Tools/Replication/Pull Subscription to 'servername', navigate to the subscription, and click Properties. Alternatively, navigate to the subscription in the console tree in the Pull Subscriptions node below the subscribing database, right-click the subscription, and select Properties. This opens the Pull Subscription Properties dialog box, which allows you to

• Set Distribution Agent properties
• Enable the Windows Synchronization Agent for the subscription
• Configure security settings for the Subscriber to access the Publisher
• Enable and configure the subscription to use FTP to download the initial snapshot

Agents Created for a Subscription

When you create a publication, one or more agents are created to support the publication, as shown in the following table. You can see information about these agents in the Replication Monitor after creating a subscription.

Type of replication	Type of subscription	Agents created
Snapshot	Push	Distribution Agent at the Distributor
	Pull	Distribution Agent at the Subscriber
Transactional	Push	Distribution Agent at the Distributor
	Pull	Distribution Agent at the Distributor
Merge	Push	Merge Agent at the Distributor
	Pull	Merge Agent at the Distributor

Publications in the same database that are not set up for immediate synchronization use the same Distribution Agent for all subscriptions of the same type, push or pull, in a single database on a Subscriber. When the same Distribution Agent is used for multiple publications, the agent is referred to as a shared Distribution Agent. In the Replication Monitor under Distribution Agents, shared Distribution Agents are listed as <Multiple Publications> in the Publication column.

Exercise: Creating a Push Subscription

In this exercise, you will push the articles (to the Subscriber) that were published (on the Publisher) in the previous lesson.

• To create a push subscription to a publication

1. In SQL Server Enterprise Manager, click your server.
2. On the Tools menu, point to Replication, and then click Push Subscriptions To Others.
3. Expand StudyNwind, click StudyNwind_Merge_Publication, and then click Push New Subscription.
4. Use the information in the following table to create the push subscription.

Option	Value
Choose Subscribers	Select your server.
Subscription database(s)	nwrepl Click Browse Databases. Click Create New, type nwrepl in Name, and click OK to create the new database.
When should the Merge Agent update the subscription?	Using the following schedule: Click Change and set the schedule to Daily, Every 1 day, Occurs every 2 minutes.
Yes, initialize the schema and data at the Subscriber	Selected.
Start the Merge Agent to initialize the subscription immediately.	Checked
Use the following priority to resolve the conflict	25.00

5. After the subscription is created, in the console tree of SQL Server Enterprise Manager, expand your server, expand Replication Monitor, expand Agents, and then click Merge Agents.

It will take a few minutes for the Merge Agent to complete the initialization of the new subscription.

6. In the details pane, after the Status column indicates that the Merge Agent has succeeded, right-click StudyNwind_Merge_Publication, and then click Agent History. In the history list, select the oldest entry (this is the entry for the subscription initialization and should have about 38 actions). If there are other entries, they represent the Merge Agent running every 2 minutes according to the schedule you created for the subscription. Click Session Details. Review the history of the Merge Agent.

Whose system resources does this Merge Agent consume? Would a pull subscription change the resources that are used?

Answer

In this exercise, you will update the Customers table (on the Publisher) and verify that the updated information replicates to the Subscriber.

• To update the source table on the Publisher

1. Switch to SQL Server Query Analyzer.
2. Type and execute the following script:

 USE StudyNwind SELECT * FROM Customers 

3. Review the result set. Notice the first row, with the value Maria Anders in the ContactName column.
4. Type and execute the following script:

 USE StudyNwind UPDATE Customers SET ContactName = 'Maria Anders-Smith' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers 

How long will it take for this update to be replicated?

Answer

5. Type and execute the following script to verify that the update has been replicated to the nwrepl database:

 USE nwrepl SELECT * FROM Customers 

6. In SQL Server Enterprise Manager, expand Replication Monitor, expand Agents, and then click Merge Agents.
7. In the details pane, right-click StudyNwind_Merge_Publication, and then click Agent History.
8. Click the session that indicates that data changes were merged, and then click Session Details.

Review the actions that were taken during the session.

Exercise: Updating Simultaneously from a Publisher and a Subscriber

In this exercise, you will update the Customers table on the Publisher and the Subscriber with conflicting information and then review the results.

• To update simultaneously on a Publisher and a Subscriber

1. Switch to SQL Server Query Analyzer. Open two query windows.
2. Type the following script in the first query window, but do not execute the script:

 USE StudyNwind UPDATE Customers SET ContactName = 'Maria Anders-Smyth' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers 

3. Type the following script in the second query window, but do not execute the script:

 USE nwrepl UPDATE Customers SET ContactName = 'Maria Anders-Smythe' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers 

4. Execute the statements in the two query windows one after the other, as closely together as possible.
5. Switch to SQL Server Enterprise Manager.
6. In the Merge Agent details pane, verify that the update has been replicated and that there was one conflict. This will take up to two minutes.
7. Switch to SQL Server Query Analyzer and execute the following query in each of the query windows:

 SELECT * FROM Customers 

Which update was applied? Was it the update made on the Publisher server (in the StudyNwind database) or on the Subscriber server (in the nwrepl database)?

Answer

Exercise: Resolving the Conflict

In this exercise, you will resolve the conflict, using the Microsoft Replication Conflict Viewer.

• To resolve the conflict

1. In the console tree, expand Databases, then right-click the StudyNwind database, point to All Tasks, and click View Replication Conflicts.

The Microsoft Replication Conflict Viewer appears, indicating a conflict in the Customers table of the StudyNwind database.

2. Click View.
3. Click Overwrite with revised data.
4. In the ContactName box, type Maria Anders, and then click Resolve.
5. Click Close.
6. Switch to SQL Server Query Analyzer and execute the following query in both of the query windows:

 SELECT * FROM Customers 

Was the revised data applied to both servers?

Answer

IMPORTANT

---

To perform conflict resolution at the Subscribers, you have to enable the decentralized conflict-reporting option for the publication before adding Subscribers to the publication. If you do this, you have to resolve each conflict at the losing Subscriber, so you may not always be able to perform conflict resolution at the same server.