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;