SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Setting Up a Subscription

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;

<Publisher>-<Publication Database>-<Publication>-<Subscriber><number>

Great Learning!!!

The post Setting Up a Subscription appeared first on .

SQL Geek

Dharmendra is a SQL Server/Microsoft Data Platform professional with over eight years of experience. He enjoys helping others in the SQL Server community and does this by contributing on blogs, speaking at several SQL events. His passion and focus is to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.dharmendrakeshari.com, opens in a new window]

Loading comments...