Setting Up a Subscription

, 2017-05-10

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 .





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads