Home > Articles > Data > SQL Server

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

Creating a Pull Subscription

Now let's see how we can create a pull subscription. With such a setup, the distribution agent runs on the subscriber; transactions are replicated when a subscriber requests data changes.

Let's connect to the subscribing server and choose Tools, Replication, Pull Subscription to [server name] in Enterprise Manager. Doing so opens an introductory screen, allowing us to select the database in which the subscription should be created. We choose the PUBS database and click Pull New Subscription. We see a welcome screen, which I've omitted here to save space. The next screen lets us look for existing publications.

Figure 29Figure 29.

We can look for publications from registered servers in Enterprise Manager or in Active Directory. For now, let's choose the default option and click Next. The following screen shows us the registered servers and lets us choose the existing publication.

Figure 30Figure 30.

Simply choose pubs_authors_table:pubs and click Next. Note that we could register a new server in Enterprise Manager if the needed server hasn't already been registered.

The following screen allows us to specify security credentials that the distribution agent needs to use for connecting to the publisher and distributor. We have an option to use the SQL Server Agent account (the wizard calls this impersonating a SQL Server Agent account) or we can define a SQL Server login.

Figure 31Figure 31.

For this example, I'll use the SQL Server Agent account. The next screen gives us another opportunity to define the subscription database. I suppose this screen is useful if we change our mind halfway through the wizard because we've already defined the subscription database to begin with. We can also create a new database that will be used as a destination for replicated transactions. I'll skip this screen because we've already chosen the PUBS database as the subscriber.

The next screen looks very similar to the one we saw with the Push Subscription Wizard when we were asked whether we wanted to initialize the schema and data on the subscriber.

Figure 32Figure 32.

Notice a subtle difference, however. The Push Subscription Wizard allowed us to start the snapshot agent immediately. With pull subscriptions, we don't have that option. We can choose only whether the subscriber database already has data and schema or not. Let's choose "Yes, initialize the schema and data" and click Next.

The following screen lets us specify the snapshot files location.

Figure 33Figure 33.

Note that we get this screen only if we choose to initialize the schema and data. The snapshot files can reside in the default directory or in an alternative directory, depending on how we configured our publication. Because we defined the snapshot to be created in the default directory, we don't need to specify an alternative location here and we simply click Next.

Now it's time to define a schedule for the distribution agent. Again the screen looks familiar (see the following figure).

Figure 34Figure 34.

Note that with we have a new option: we can synchronize this subscription using Enterprise Manager or Windows Synchronization Manger on demand only. This means that transactions will be delivered to the subscriber only when we execute the distribution agent job. The other two options are the same as with the Push Subscription Wizard: We can run the distribution agent continuously or on a predefined schedule. Let's use a continuous delivery of transactions and click Next. The final two screens simply ensure that the necessary services are running and give us the summary of the options we chose. Clicking Finish on the last screen creates a pull subscription.

The Replication Monitor will look identical to that we saw after setting up a push subscription. However, we have to start the snapshot agent manually. To do so, we right-click the Snapshot agent within the Replication Monitor and then click Start Agent, as shown in the following figure.

Figure 35Figure 35.

  • + Share This
  • 🔖 Save To Your Account