SQL Server Replication Configuration – Part 3

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:


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


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


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.


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.


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


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 “…”.


Fill the account page:



Click on the Ok button.


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.


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.


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


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.


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


Step 11: Click the Close button.


Hence this was all about the Push Subscription.

And also comments on this!!



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


About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published.