Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Creating a Push Subscription

Transactions can be delivered to the subscribers through push or pull subscriptions. Push subscriptions are implemented by the distribution agent that runs on the distributor, whereas with pull subscriptions the distribution agent executes on the subscriber.

Push subscriptions are created on the publisher and deliver transactions without a request from the subscriber(s).

Let's activate the Push Subscription Wizard by choosing Tools, Replication, Push Subscription to Others while connected to the server where a publication has been defined. We will first see the following screen, which lets us create and manage publications.

Figure 23Figure 23.

Let's choose the publication that we created earlier (pubs_authors_table) and click Push New Subscription. Once again, I'll skip the introductory screen of the wizard and jump straight to the following screen, which lets us choose subscriber servers.

Figure 24Figure 24.

Here, I'll choose the only enabled subscriber (D10ZF411\SUBSCRIPTION_SRV) and click Next. At this point, we have the opportunity to create a subscription database or choose an existing database in which transactions should be replicated, as shown in the following figure.

Figure 25Figure 25.

For this example let's use the PUBS database as the subscriber. Next, we can specify the schedule for the distribution agent. We can replicate transactions continuously or periodically, according to a predefined schedule (see the following figure).

Figure 26Figure 26.

Running the distribution agent continuously minimizes the latency in delivering transactions. Keep in mind, however, that doing so also increases the load on the distribution server. So if latency is not an issue, we might want to change the default option and run the distribution agent periodically. For this example, I'll choose continuous execution of the distribution agent.

The following screen lets us initialize schema and data on the subscriber, if needed.

Figure 27Figure 27.

If we choose the default option, the snapshot agent will create a "snapshot" of the published objects. In this case, the snapshot would include the CREATE TABLE command for the authors' table, any indexes and constraints associated with this table, and its data. Note that we have the option of starting the snapshot agent immediately, or we can start the agent manually after replication setup is complete. Alternatively, we can choose not to deliver the snapshot to the subscriber. We would use that option for cases when data and schema were already delivered to the subscriber; for example, we might have taken the database backup on the publisher and restored it on the subscriber. In such a case, the subscriber would not need the snapshot. For this example, I choose to initialize the schema and data and start the snapshot agent immediately.

We then click Finish and watch the Push Subscription Wizard display the progress of the subscription setup.

After we finish creating a push subscription, we can examine the Replication Monitor folder on the distribution server; we should see the publication and subscription we just created, as shown in the next figure.

Figure 28Figure 28.

  • + Share This
  • 🔖 Save To Your Account