The final step in the replication process is having servers subscribe to the publication. You can do this using push or pull subscriptions.
With push subscriptions, the publisher is responsible for replicating all changes to subscribers without subscribers asking for the changes. You will usually use push subscriptions when you need to send changes to subscribers immediately or when you want to schedule updates periodically. Because the publisher initiates the replication, push subscriptions also offer more security than pull subscriptions. Making the publisher responsible for replicating changes, however, increases overhead on the publisher and may not be the ideal subscription model for a server with a heavy workload.
With pull subscriptions, subscribers request periodic updates of all changes from the publisher. You will usually use pull subscriptions when you have a large number of subscribers or when you need to reduce overhead on the publisher. You also may want to use pull subscriptions for independent mobile users. A single publication can support a mixture of push and pull subscriptions.
You can also use a special type of pull subscription called an anonymous subscription. With an anonymous subscription, the publisher and distributor do not maintain subscription information. Instead, the subscriber is responsible for maintaining and synchronizing the subscription, which increases the load on the subscriber, but reduces the load on the publisher and distributor. Accordingly, anonymous subscriptions are most useful when you have a large number of subscribers or when you allow subscriptions using the Internet.
Note
You create anonymous subscriptions to publications in the same way you create pull subscriptions. You enable anonymous subscriptions using the Subscription Options page of the Publication Properties dialog box. Set the Allow Anonymous Subscriptions option to True to allow anonymous subscriptions. Set Allow Anonymous Subscriptions to False to prevent anonymous subscriptions.
The Distribution Agent and Merge Agent are responsible for synchronizing subscriptions and resetting their retention period. If these agents are not running, subscriptions become incompatible with their publications and are marked as deactivated. A deactivated subscription is a subscription that has exceeded the publication retention period. Deactivated subscriptions no longer receive updates during synchronization, and you must mark these subscriptions for reinitialization to enable them again. If you do not re-enable deactivated subscriptions before they expire, the Expired Subscription Clean Up job will delete them.
The main difference between push and pull subscriptions involves how they are initiated. The subscriber initiates pull subscriptions. The distributor initiates push subscriptions. You configure pull subscriptions by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Right-click the Local Subscriptions folder and select New Subscriptions. This starts the New Subscription Wizard.
Click Next. You use the Publication page, shown in Figure 12-11, to specify where you want to look for a publication. Select Find SQL Server Publisher, Find Oracle Publisher, or a registered server as appropriate.
After you select a server, you can browse available publications on that server. Select the publication to which you want to subscribe, and then click Next.
Choose where to run the Distribution Agent or agents used with this publication. If you want the agents to run on the distributor and therefore create a push subscription, select Run All Agents At The Distributor. If you want the agents to run on each subscriber and therefore create a pull subscription, select Run Each Agent At Its Subscriber.
Click Next. On the Subscribers page shown in Figure 12-12, choose one or more subscribers for the publication.
If you are using updatable transactional replication and a server you want to act as a subscriber is not listed, click Add SQL Server Subscriber, and then configure a connection to a SQL Server using the Connect To Server dialog box.
If you are using merge, snapshot, or nonupdatable transactional replication and a server you want to act as a subscriber is not listed, click Add Subscriber, and then:
Choose Add SQL Server Subscriber to configure a connection to a SQL Server using the Connect To Server dialog box. Registered servers are listed in the Server Name drop-down list, and you can browse for others. The default authentication is Windows Authentication, which uses your current login and password. Click Connect. In the New Subscription Wizard page, specify the destination database in which to create the subscription or select New Database to create a new database for the subscription.
Choose Add Non-SQL Subscriber to configure a connection to an Oracle or IBM DB2 server using the Add Non-SQL Server Subscriber dialog box. Enter the data source name that can be used to locate the database on the network. SQL Server generates a connection string for the database using the data source name, combined with the login, password, and any connection options you specify in the Distribution Agent Security page in this wizard. The data source name and connection string are not validated until the Distribution Agent attempts to initialize the subscription. Click OK. The subscription database is set as the default destination, which is the database you specified in the data source name.
Click Next. On the Distribution Agent Security page shown in Figure 12-13, set the process account and connection options for each subscriber (for pull subscriptions) or for the distributor (for push subscriptions) by clicking the related Properties button (...), and then:
Specify the Windows account under which the Distribution Agent runs for the selected server (which is either a subscriber or a distributor). This account is referred to as the process account. Be sure to type domain account names in the form domainaccount, such as cpandlsqlserver. Then type and confirm the account password. The account must be a member of the Publication Access List. (To check or modify the access list, access the Local Publications folder in Object Explorer view, right-click the publication, and then select Properties. This displays the Publication Properties dialog box. Select the Publication Access List page.)
Specify whether the agent should make connections to the distributor by impersonating the account specified in the Process Account text box or by using a SQL Server account. If you choose to use a SQL Server account, enter a SQL Server login and password. Typically, you will want to impersonate the Windows account rather than use a SQL Server account. The account must be a member of the Publication Access List.
Specify whether the agent should make connections to the Subscriber by impersonating the account specified in the Process Account text box or by using a SQL Server account. If you choose to use a SQL Server account, enter a SQL Server login and password. Typically, you will want to impersonate the Windows account rather than use a SQL Server account. The account must be a database owner of the subscription database.
Click OK to close the properties dialog box. Click Next. Set the synchronization schedule for Distribution or Merge Agents using one of the following options:
Run Continuously. Select this option to continuously check for updates on the publisher.
Run On Demand Only. Select this option if you want to update the subscription database manually.
Define Schedule. Select this option to set a periodic schedule, such as once an hour.
If you selected a publication that uses updatable transactions, the next wizard page will be titled Updatable Subscriptions. Specify if you want the subscription databases to be initialized.
Clear the Replicate option check box for a subscriber if you do not want to create an updatable subscription at this time.
Set the Commit At Publisher option to Simultaneously Commit Changes to enforce immediate updating. Changes are committed on both the subscriber and the publisher at the same time, which requires a dedicated connection.
Set the Commit At Publisher option to Queue Changes And Commit When Possible to allow queuing of changes. Queued changes are committed on the subscriber immediately and on the publisher during the next online synchronization.
If you choose to create an updatable subscription, the next dialog box allows you to configure the technique that will be used by the subscriber to access the publisher (see Figure 12-14). You can use an existing linked server or remote server to establish the connection if you have already configured these options as described in Chapter 11. Or you can use a SQL Server login and password, provided the login is listed on the Publication Access List.
Use the Initialize Subscriptions page to determine whether or not the subscription databases should be initialized.
Clear the Initialize option check box if you have already initialized the subscription or will initialize a transactional subscription from backup.
Set the Initialize When option to Immediately to initialize the subscription database with a snapshot of the publication data and schema as soon as possible after the Snapshot Agent generates the snapshot.
Set the Initialize When option to At First Synchronization to initialize the subscription database with a snapshot of the publication data and schema the first time the subscription is synchronized.
Click Next. Choose the wizard action upon completion. By default, the wizard creates the subscription(s). You can also generate a script file with the steps to create the subscription(s). If you want to only generate a script, clear Create The Subscription(s). Click Next.
Click Next. Check the setup and click Finish when you are ready to create the subscription(s). The Creating Subscription(s) dialog box shows you the status of the creation process. Click the message link provided for any errors to read the error text.
To view the configuration properties of a subscription, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the server instance. Click Refresh or press F5 if you do not see a list of publications.
Double-click a publication to show its current subscriptions.
To view the properties of a subscription, right-click the subscription name and choose Properties to display the Subscription Properties dialog box.
To update, maintain, or delete a subscription, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Click the Local Subscriptions folder to see a list of subscriptions for the server instance.
Right-click a subscription, and then choose one of the following options:
View Synchronization Status. Displays a status of the synchronization process for the selected subscription.
Set Update Method. For updatable subscriptions only, this option allows you to switch between immediate updating and queued updating.
Delete. Deletes the subscription. Confirm the action by clicking Yes when prompted.
You validate and reinitialize subscriptions through the associated publication. For more details, refer to the following two subsections, titled "Validating Subscriptions" and "Reinitializing Subscriptions."
You can validate subscriptions to verify that the subscribers have the same number of rows of replication data as the publisher. When you mark a subscription for validation, the validation occurs the next time the Distribution Agent runs, and the results are available in the Replication Monitor.
To validate one or more subscriptions, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for this server instance.
If you want to validate subscriptions for a transactional publication, right-click the transactional publication you want to use, and then choose Validate Subscriptions. This displays the Validate Subscriptions dialog box. You can validate all SQL Server subscriptions or the specified subscriptions on SQL Servers. Non-SQL Server subscriptions cannot be validated. Optionally, click Validation Options to configure how the Distribution Agent computes row counts, whether or not the agent compares checksums, and whether or not the Distribution Agent is stopped after the validation is completed.
If you want to validate all subscriptions for the selected merge publication, select Validate All SQL Server Subscriptions. This displays the Validate All Subscriptions dialog box. By default, the distributor validates only the row counts on the subscriber. If all subscribers are running SQL Server, you can also verify the data in rows by comparing checksum values.
Click OK. The validation occurs the next time the Distribution Agent runs, and the results are available in the Replication Monitor. More information about how to work with the Replication Monitor is provided in Chapter 13.
You can reinitialize snapshots in a publication’s subscription database by using the current snapshot or a new snapshot. To reinitialize all subscriptions, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for this server instance.
Right-click the publication you want to work with, and then choose Reinitialize All Subscriptions. This displays the Reinitialize The Subscription(s) dialog box.
To use the current snapshot to reinitialize the subscriptions, select Use The Current Snapshot.
To generate a new snapshot to reinitialize the subscriptions, select Use A New Snapshot. If you want to generate the snapshot immediately, select Generate The New Snapshot Now.
Click Mark For Reinitialization.
To reinitialize a specific subscription, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the database server instance that will act as a subscriber or distributor, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for this server instance.
Double-click the publication you want to use to show its current subscriptions.
Right-click the subscription you want to reinitialize, and then choose Reinitialize. This displays the Reinitialize The Subscription(s) dialog box.
Select Use The Current Snapshot or Use A New Snapshot as appropriate. If you want to generate a new snapshot immediately, select Generate The New Snapshot Now.
Click Mark For Reinitialization.