0

Step by Step SQL Server Transactional Replication

Emeka OkekeDec 11, 2019 | edited Dec 27, 2019 - by @sqldibia

This post provides a guide on how to set up, test and monitor SQL Server Transactional Replication across multiple databases.

Definitions, Acronyms and Abbreviations

  • SQL stands for Structured Query Language
  • DB stands for Database while DBs stands for Databases
  • The word Latency used in this post means the average time in seconds that records are held in the transaction log before being distributed to the subscribers
  • SSIS stands for SQL Server integration Services
  • SP stands for Stored Procedure

Prerequisites

Servers need to be provisioned and ready for replication.If not already done, you can send request to IT department to provision publisher, subscriber and distributor servers with a windows service account that have sys admin access to all three servers.

There should be Linked Servers linking publisher, subscriber and distributor servers. If they do not already exist, create Linked Servers as follows:

  • Create Linked Servers on subscriber server linking publisher and distributor servers
  • Create Linked Server on publisher server linking subscriber and distributor servers

Since this is a transactional replication setup and configuration, all tables to be replicated must have primary keys. So ensure that as well.

Code Comments

Comments that end with the keyword "GO + newline + */" such as the one below, should be removed from all DB object definition if they exist:


/*  
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE ID = object_id(N'[dbo].[usp_rch_GetAllVendorCertifications]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)  
BEGIN  
 DROP PROCEDURE usp_rch_GetAllVendorCertifications  
END  
GO  
*/

Server Configurations

Configure distributor server by following below steps. See images as well:

Step 1

Open your SQL Server Management Studio and Right-click on replication node/folder and select Configure Distribution and a Distribution Configuration window will pop up, click on next.

replication_distributor_setup_step1.png

Step 2

The next step shows distributor server selection options. Leave the first option which says the current server will act as its own Distributor. Then click next.

replication_distributor_setup_step2.png

Step 3

The next step shows snapshot folder input. You may change the default value but you should leave it as it is and then click next.

replication_distributor_setup_step3.png

Step 4

On the next step, enter the Distributor database name as you like and keep note of it as you will need it when calling replication SPs and SSIS packages. You can leave the data file folders as they are and then click next.

replication_distributor_setup_step4.png

Step 5

On the next step, you will add publishers that will use the designated distributor server. Click on add and select SQL Server Publisher, server connection window will pop up, enter the publisher credentials and connect. It will be added to the publisher list on the current window. Click on next.

replication_distributor_setup_step5.png

Step 6

On the next window, you will enter the system password of the windows service account that was created by the IT department to be used by the replication process. Then click next.

replication_distributor_setup_step6.png

The next window gives you the option to generate a distribution configuration script but you should skip that step and click next.

replication_distributor_setup_step7.png

Step 8

The last step shows a summary of all the options you?ve selected so far from step one to the current step so you can review it before clicking finish. Click on finish to run the configuration. 

replication_distributor_setup_step8.png

Step 9

If there is no error after the configuration is done, then close the window to exit the configuration wizard.

replication_distributor_setup_step9.png replication_distributor_setup_step10.png replication_distributor_setup_step11.png

Configure publisher server by following below steps:

Step 1

Right-click on replication folder and select Configure Distribution and a Distribution Configuration window will pop up, click on next.

replication_publisher_setup_step1.png

Step 2

The next step shows distributor server selection options. Choose the second option which says use the following server as distributors, then click on add to connect to the distributor server you configured above. Then click next.

replication_publisher_setup_step2.png

Step 3

On the next step, enter the same windows service account password you entered while configuring the distribution server and click next.

replication_publisher_setup_step3.png

Step 4

The next window gives you the option to generate a distribution configuration script but you should skip that step and click next.

replication_publisher_setup_step4.png

Step 5

The last step shows a summary of all the options you?ve selected so far from step one to the current step so you can review it before clicking finish. Click on finish to run the configuration.

replication_publisher_setup_step5.png

Step 6

If there is no error after the configuration is done, then close the window to exit the configuration wizard.

replication_publisher_setup_step6.png replication_publisher_setup_step7.png replication_publisher_setup_step8.png
Emeka Okeke@sqldibia+ Follow
0
Emeka Okeke+ Follow
locationPennsburg, PennsylvaniajoinedDec 11, 2019

More from @sqldibia