Setting Up a Subscription -

Setting Up a Subscription

DHARMENDRA KESHARIMay 10, 2017SQL WINDOWS0 comments

In the last two blogs, we went through Configure Distribution Database and Publication Creation. If you haven’t read them, I will request you to go through before you start exploring this blog.

If you don’t have the Subscription database, You must create your subscription database. Once you created the database, you’re ready to create your subscription. The following steps walk you through that process.

Open the SQL Server Management Studio and connect the Publisher instance

Right-click on the created Publication and then click New Subscription

It will bring you on the “New Subscription Wizard”. Click Next

It will bring you on the “Publication” Page. On the page, you can select a publication you want to associate with your subscription. Click Next

On the “Distribution Agent Location” page, you specify whether this is a push or a pull subscription. Click Next

It will bring you on the “Subscribers” page. Click on the options “Add Subscriber”, and then click “Add SQL Server Subscriber” to select your subscriber database server

Once you selected ” SQL Server Subscriber” server, you can choose the Subscriber database from the drop down box. Click Next

On the page “Distribution Agent Security”, you configure the accounts used to connect to the Distributor and Subscriber and used to execute the agents. Click the ellipsis button at the end of the row associated with the specific Subscriber.

When you click the ellipsis button, you will get the below dialog box. Key the required user name and password. Click OK

On the Synchronization Schedule page, you select when the Distribution Agent should be executed. The drop-down list supports three options. You can select the option which fit in your requirement. Ensure that Run continuously is selected, and then click Next

It will bring you on the “Initialize Subscriptions” page. On the page, you get the option to chose when the subscription should be initialized. There are two options – Immediately and At first synchronization. In most of the cases, the default choice is “immediately”. Click Next

The “Wizard Actions” page gives you the option to generate a script that you can execute at a later time. Click Next

The “Complete the Wizard” page shows the selected configuration to have a final review. Click on the Finish to create the Subscription.

The Configure page displays that the distribution Subscription is being created

Finally, we have created the Subscription successfully!

How many jobs get created during the setting a new Subscription?

Before creating a new Subscription, let’s see how many SQL Jobs were on the instance.

After creating the new Subscription, let’s see how many jobs were on the instance.

One more new jobs get introduced when you create a new Subscription on the SQL Server.

Distribution Agent job – The Distribution Agent applies the data changes in the distribution database to the subscription database. SQL Server creates a job for each subscription by default. The job runs either on the Distributor (push subscription) or on the Subscriber (pull subscription). The job is named pattern following pattern;

Great Learning!!!