Hello Folks,

As you would have seen in my previous article posts, I have dealt with how to create a Publisher and Distributor, as well as how to create Snapshot Publication.

If you want to refer it, then just click on the link;

In this article-post, I will mainly focus on how to create a Push Subscription to a Snapshot Publication.

Creating a Push Subscription to a Snapshot or Transactional Publication:

Push Subscription: The Publisher can initiate the subscription and push data to the subscriber server. The Distributor copies the data to the Subscriber database.

When your communication infrastructure has stable and permanent connection among replication servers, a Push Subscription offers the advantage of providing a central management point to all replication agents that manage the replication process.

So follow the steps:

Step 1: As you would have seen my previous article-post, so now expand the Replication folder. Expand the Local Publications, and select your Publication. Then right-click on it:

1_SQL_Server_Configuring_the_Replication_Part3

Step 2: Click on the Next button, from the New Subscription Wizard;

2_SQL_Server_Configuring_the_Replication_Part3

Step 3: Choose the Publication for which you want to create one or more subscriptions.

3_SQL_Server_Configuring_the_Replication_Part3

Step 4: Now choose where to run the Distributor Agent. Like if we are dealing with Push Subscription, its better to accept the default, which is run all the agents in the Distributor. It makes it easier to administer the synchronization of subscriptions certainly.

4_SQL_Server_Configuring_the_Replication_Part3

Step 5: Subscribers – Choose one or more Subscribers and specify each subscription database. All the currently enabled Subscribers will appear in this dialog. You can also add the Subscribers with the help of “Add Subscriber” button.

5_SQL_Server_Configuring_the_Replication_Part3

Choose the Subscription Database, and then click the Next button.

6_SQL_Server_Configuring_the_Replication_Part3

Step 6: Distribution Agent Security is somewhat similar to Snapshot Agent Security. Specify the process account and connection options for each Distribution Agent. Click the “…”.

7_SQL_Server_Configuring_the_Replication_Part3

Fill the account page:

8_SQL_Server_Configuring_the_Replication_Part3

Click on the Ok button.

9_SQL_Server_Configuring_the_Replication_Part3

Step 7: Synchronization Schedule. There are basically two types of Agent Schedule:

  1. Run Continuously – This is a default option. It means the Agent will always be running in the case of Transactional Replication, but has no effect on Snapshot Publications.
  2. Run on demand only – It means then we have to run the agent either through Replication Monitor, by running the job for the distribution agent, or , by expanding the Local Publication folder, locating the Subscriber, right- clicking on it, and selecting View Synchronization Status and then clicking the Start button.

Click the Next button.

10_SQL_Server_Configuring_the_Replication_Part3

Step 8: Initialize Subscriptions.

–          If we check the Initialize option, it means the snapshot will be applied at the subscriber.

–          If we do not check it, then we need to pull all the required objects in the place. This includes all the tables, the data, stored procedures, functions, and views, as well as                 replication stored procedures.

11_SQL_Server_Configuring_the_Replication_Part3

If we select the Initialize option, then we come up with the two options in when to initialize it;

12_SQL_Server_Configuring_the_Replication_Part3

Immediately- It means the snapshot will be generated and applied on the Subscriber as soon as we complete the dialog.

At first synchronization- It means the snapshot will be generated when the distribution agent runs.

Step 9: Wizard Actions. Choose what happens when you click finish. Check the options as you may like.

13_SQL_Server_Configuring_the_Replication_Part3

Step 10: Complete the Wizard. Click the Finish button.

14_SQL_Server_Configuring_the_Replication_Part3

Step 11: Click the Close button.

15_SQL_Server_Configuring_the_Replication_Part3

Hence this was all about the Push Subscription.

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook