SQL Server Replication is the process of copying databases from one node to another to improve availability of data. Replications are of three types, Transactional Replication, Snapshot replication and Merge replication. SQL server replication is supported on all SQL editions except the Express Edition and Compact Edition.
In this tutorial we will learn how to setup Transactional replication.
There are three major components of SQL server replication.
1. Publisher
2. Distributor
3. Subscriber
The Publisher resides on the source server and the Subscriber resides on the destination server. The distributor can be either created in the source server or on any other sql server instance. We will create the distributor on the publisher server. Kindly make sure to start the SQL Server Agent service if it is stopped.
Step 1
We will create following Windows accounts for replication.
1. repl_snapshot – the snapshot account created in Publisher server
2. repl_logreader – the log reader account created in Publisher server
3. repl_distribution – the distributor account created in both Publisher and Subscriber server
4. repl_merge – the merge account created in created in both Publisher and Subscriber server
Step 2
We will now create the snapshot folder and assign permissions. The folder will be created in the default MS SQL data directory.
1. Create a directory named repldata.
2. Right-click this folder and select Properties.
a. On the Sharing tab in the repldata Properties dialog box, select Advanced Sharing.
b. In the Advanced Sharing dialog box, select Share this Folder, and then select Permissions.
3. In the permissions for repldata dialog, add the user <Server_Name>\repl_snapshot user. Assign the user Full control to the directory.
4. Repeat the same steps for the users repl_distribution and repl_merge. These two users will have only read permission to the directory.
5. Now access the security tab of the repldata directory’s properties. Add all the above three users to the directory. The repl_snapshot user will be assigned full control and the other twoo users will be assigned only the read permission.
Step 3
Now we will configure the distribution at the publisher.
1. Open the SQL server management studio and login to the SQL server in the source server.
2. Expand the server node.
3. Right click the Replication folder and select Configure Distribution.
4. On the Distributor page, select <‘ServerName’> will act as its own Distributor; SQL Server will create a distribution database and log. Then select Next.
5. If the SQL Server Agent is not running, on the SQL Server Agent Start page, select Yes, configure the SQL Server Agent service to start automatically. Select Next.
6. Enter the path \\Server_Name>\repldata in the Snapshot folder box, and then select Next. This is the network path of the repldata directory. Remember, we have enabled share on the directory.
7. Accept the default values on the remaining pages of the wizard.
8. Click Finish to enable distribution.
Step 4
The next step would be to set database permissions at the publisher.
1. In SQL Server Management Studio, expand Security, right-click Logins, and then select New Login
2. On the General page, select Search. Enter <Server_Name>\repl_snapshot in the Enter the object name to select box, select Check Names, and then select OK.
3. On the User Mapping page, in the Users mapped to this login list, select both the distribution and databse which needs to be replicated.
4. In the database role membership list, select the db_owner role for the login for both the databases.
5. Click Ok to create the login.
6. Repeat these steps for the remaining three users.
Step 5
Now we will create the publication at the publisher node.
1. Open the SQL server management studio and login to the SQL server in the source server.
2. Expand the server node.
3. Expand the Replication folder, right click the Local Publications folder and select New Publication.
4. On the Publication Database page, select the database you want to replicate and then select Next.
5. On the Publication Type page, select Transactional publication and then select Next
6. On the Articles page, select all the Tables, Stored Procedures and Views and select Next
7. Select the Create a snapshot immediately and keep the snapshot available to initialize subscriptions check box, and select Next
8. On the Agent Security page, clear the Use the security settings from the Snapshot Agent check box.
9. Select Security Settings for the Snapshot Agent. Enter <Server_Name>\repl_snapshot in the Process account box, supply the password for this account, and then select OK.
10. Repeat the previous step to set <Server_Name>\repl_logreader as the process account for the Log Reader Agent. Then select OK.
11. On the Complete the Wizard page, Enter a name in the Publication name box, and select Finish.
12. After the publication is created, select Close to complete the wizard.
View the status of snapshot generation
1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click <publication_name>, and then select View Snapshot Agent Status.
3. The current status of the Snapshot Agent job for the publication appears. Verify that the snapshot job has succeeded before you continue to the next section.
Add the Distribution Agent login to the PAL
1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click <publication_name>, and then select Properties. The Publication Properties dialog box appears.
a. Select the Publication Access List page, and select Add.
b. In the Add Publication Access dialog box, select <Server_Name>\repl_distribution, and select OK.
Step 6
Now we will create a subscription to the transactional publication.
Create the subscription
1. Connect to the publisher in SQL Server Management Studio, expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, right-click the <publication_name>, and then select New Subscriptions. The New Subscription Wizard starts.
3. On the Publication page, select <publication_name>, and then select Next.
4. On the Distribution Agent Location page, select Run all agents at the Distributor, and then select Next.
5. On the Subscribers page, if the name of the subscriber instance is not displayed, select Add Subscriber, and then select Add SQL Server Subscriber from the drop-down list. This step opens the Connect to Server dialog box. Enter the subscriber instance name and then select Connect.
6. After the subscriber has been added, select the check box next to the instance name of your subscriber. Then select New Database under Subscription Database.
7. The New Database dialog box appears. Enter name of the database in the Database name box, select OK, and then select Next. You can either use the same name as the database on the source node or use a different name.
8. On the Distribution Agent Security page, select the ellipsis (…) button. Enter <Server_Name>\repl_distribution in the Process account box, enter the password for this account, select OK, and then select Next.
9. Select Finish to accept the default values on the remaining pages and complete the wizard.
Set database permissions at the subscriber.
1. Connect to the subscriber in SQL Server Management Studio of the destination server. Expand Security, right-click Logins, and then select New Login.
a. On the General page, under Login Name, select Search and add the login for <Server_Name>\repl_distribution.
b. On the User Mappings page, grant the login db_owner membership for the database created while creating subscription.
2. Select OK to close the New Login dialog box.
View the synchronization status of the subscription
1. Connect to the publisher in SQL Server Management Studio of the source server. Expand the server node, and then expand the Replication folder.
2. In the Local Publications folder, expand the <publication_name>, right-click the subscription in the replication database, and then select View Synchronization Status. The current synchronization status of the subscription appears.
We have successfully setup the Transactional replication for a database between two different SQL servers. You can replicate the database to more servers by creating subscription by following the steps mentioned in Step 6. Similarly we can add more databases for replication by creating Publications and Subscriptions.