In Previous three parts I have explained about
Today in last part of this series I am going to explain how you can create the Subcriber to the Publisher which will complete the replication setup. Before we start the configuration please make sure that the hostname of both the servers are configured properly and include proper enteries in hosts file (Please refer Part-I for more information).
Creating a Subscription
1) Go to Management studio (On Publisher Server) and right click on the Publication you have created >> New subcriptions…
2) New Subscription Wizard will open. Press Next
3) Publication: Select the Publication Server from the Drop-down list and then select the Database and publication. Press Next after selection.
4) Distribution Agent Location: There are two type of Subscription i.e. Push Subscription and Pull Subscription using which we can setup replication. In Push Subscription publisher server pushes any changes made on database to the subscriber where as in case of Pull Subscription subscriber pull the data from publisher on a specified time. Push method is useful when the changes in database need to be replicated as soon as they occur.
So, select Push Subscription and press Next.
5) Subscribers: Here you have to select the subscriber i.e. Slave-DB, from the drop down “Add Subscriber” select “Add New SQL Subscriber…“
You will now get a MSSQL Login prompt which is similar to MSSQL Management Studio. Login to the Subscriber server using the “Hostname” of the server and “SA” Login Details.
Select the Subscriber name and in “Subscription Database” column client on drop-down which will show all the databases on subscriber server. If you have created the database on subscriber just select it or click “New database“. Press Next.
6) Distribution Agent Security: This Page allows you to configure Agent security for each Subscriber that you have added, to ensure that the Distribution Agents have the correct permissions.
I have configured this using “SQL Server Agent” User as it already in Publisher Access List (PAL). The Windows user that you select must meet the following conditions:
- The user is included in the PAL.
- The user should be a member of the db_owner fixed database role in the subscription database.
- The user has read permissions on the snapshot share.
Enter MSSQL SA Login Details and press OK. On the Distribution Agent Security window, click Next.
7) Synchronization Schedule: By default Distributor Agent runs synchronization continuously, keep it as it is and press Next.
8) Initialize Subscriptions: Select Immediately in the Initialize When and press Next.
9) Wizard Actions: Select both the options and press Next.
10) Script File Properties: Mention the path where Subscription file will be created and press Next.
11) Complete The Wizard: Summary of configuration will be shown. Press “Finish” to complete the subscription wizard.
12) Allow sometime to get it configured and you will able to see following screen when everything is properly configured.
Close the window and go to MSSQL Management Studio and open Replication Monitor for the Replication you have configured. You will able to see the Subscriber name and status now.
Now you can test the replication by adding or inserting in any table on publisher and it will get replicated in subscriber database as well.