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
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:
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.
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.
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.
When you are using the Immediate Updating Subscribers option, the Subscriber
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.
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
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.
In this exercise, you will push the articles (to the Subscriber) that were published (on the Publisher) in the previous lesson.
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 |
It will take a few minutes for the Merge Agent to complete the initialization of the new subscription.
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.
USE StudyNwind SELECT * FROM Customers |
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
USE nwrepl SELECT * FROM Customers |
Review the actions that were taken during the session.
In this exercise, you will update the Customers table on the Publisher and the Subscriber with conflicting information and then review the results.
USE StudyNwind UPDATE Customers SET ContactName = 'Maria Anders-Smyth' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers |
USE nwrepl UPDATE Customers SET ContactName = 'Maria Anders-Smythe' WHERE CustomerID = 'ALFKI' SELECT * FROM Customers |
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
In this exercise, you will resolve the conflict, using the Microsoft Replication Conflict Viewer.
The Microsoft Replication Conflict Viewer appears, indicating a conflict in the Customers table of the StudyNwind database.
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.
After creating publications on the Publisher, you can create two kinds of subscriptions to these publications. Push subscriptions are created and managed on the Publisher. Pull subscriptions are created and managed on the Subscribers. The distribution agent for push subscriptions runs on the Distributor, and the distribution agent for push subscriptions runs on the Subscriber.
SQL Server 7 introduces Immediate Updating Subscribers, which allow updates to be made to subscribed data on a Subscriber. The updates made on a Subscriber are applied immediately on the Publisher, using a distributed transaction managed by MS DTC.