Exploring SQL Server Replication: A Comprehensive Tutorial

Traditionally, applications running on a standalone environment relied on a centralized server to respond to multiple users. However, the centralized approach had availability, performance, and maintenance issues. To solve these problems, the concept of replication was introduced, and SQL Server Replication comes in handy because SQL was widely used by companies.

In replication, multiple copies of data are kept at different locations. The modifications made to data at different locations are later synchronized to the main server. This helps database users achieve high availability. In this article, you will learn how to set up SQL Server Replication steps. Let’s get started!

Setting up SQL Server Replication with a Real-life Example

In this example, you will see how to configure SQL Server Replication to copy views, stored procedures, and tables from the default AdventureWorks2016 database to another database.

Prerequisites

Ensure that your computer has the following installed:

• Microsoft SQL Server 2008 or above. 
• Microsoft SQL Server Management Studio (SSMS).

SQL Server Management Studio (SSMS) will be used for this example. The SSMS provides you with a graphical user interface, making it easy for you to use SQL Server. You can download Microsoft SSMS here.

A single instance of SQL Server will be used to play the roles of the Publisher, Distributor, and another SQL Server instance to play the role of the Subscriber. 

SQL Server replication configuration involves the following tasks:

• Step 1: Configuring the SQL Server Distributor
• Step 2: Configuring the SQL Server Publisher 
• Step 3: Configuring the SQL Server Subscriber

Step 1: Configuring the SQL Server Distributor

To configure the SQL Replication Distributor, follow the steps given below:

Step 1: Open SSMS and establish a connection to your SQL Server instance. 

Step 2: Right-click on the “Replication” folder on the Object Explorer and select “Configure Distribution”. 

Step 3: The “Distribution Configuration Wizard” will pop up. The wizard shows the general details about configuring the Distributor. Just click on the “Next” button. 

Step 4: On the “Distributor” page, you can set the current instance as the Distributor or choose another instance that has already been configured as a Distributor. 

Since the Distributor is local, go with the default setting, that is, “‘ServerName’ will act as its Distributor; SQL Server will create a distribution database and log”. 

Click on the “Next” button. 

Step 5: In the next window, type the path to the SQL replication snapshot folder, or go with the default path. In my case, I will use the default path.

This folder is used for initial data synchronization of Transactional Replication, so ensure that it has enough size to hold all the replicated data. 

Click on the “Next” button.

Step 6: You can now configure the replication distribution database. Give the distribution database a name and specify the location of the data and log files. 

Click on the “Next” button. 

Step 7: You will be taken to the “Publishers” page. Specify the Publishers that will access the Distributor, and then click on the “Next” button. 

Step 8: In “Wizard Action”, you can choose to run immediately or create a script to be executed later. Click on “Next”.

Step 9: In the next window, review the settings and configuration options. If they are okay, click on the “Finish” button to enable the Distributor.

Step 10: You will then be taken to the “Success” window, meaning that the configuration was successful. 

Now that the Distributor is ready, let’s go ahead and configure the Publisher.

Step 2: Configuring the SQL Server Publisher

Follow the steps given below:

Step 1: Expand the “Replication” folder from the “Object Explorer”. Right-click on “Local Publications” and choose “New Publication”.

Step 2: A new window will appear giving you the general details about creating a new publication. Click on “Next”.

Step 3: The “Publication Database” window will appear. Choose the AdventureWorks2016 database, and then click on “Next”.

Step 4: The “Publication Type” window will appear. Choose “Transactional Publication” then click on “Next”.

Step 5: You will be taken to the “Articles” page. Select all the articles you need to include in the publication and click “Next”. 

Step 6: After selecting the objects, click the “Show only checked articles in the list” option to check it. You will have listed the candidates for publication. Click on “Next”.

Step 7: You will be taken to the “Filter Table Rows” page. Here, you should specify the filters to be applied to your articles. 

Just go with the default values. Click on “Next”. 

Step 8: In the next window, you should specify when the Snapshot Agent will start. You can run it immediately or schedule it to be run later. 

Choose the first option, which is to create a snapshot immediately. 

Click on “Next”.

Step 9: On the next page, specify the account to be used to run the Snapshot Agent and click on “OK”.

Step 10: Choose the first option to create the publication immediately. Click on “Next”. 

Step 11: Type in the name for the publication and click on “Finish”. I have given it the name, AdvWorks_Pub. 

Step 12: In the next window, you should see the success message. 

You can then check under “Local Publications” to confirm whether the publication has been created. 

Step 3: Configuring the SQL Server Subscriber

This is the final step of setting up SQL Server Replication.  To create the subscription, follow the steps given below:

Step 1: Expand the “Replication” folder from “Object Explorer”, right-click on “Local Subscriptions”, and then choose “New Subscriptions”.

Step 2: The next window will show the general details about creating subscriptions. Click on “Next”.

Step 3: In the next window, select the publication and then click on “Next”. 

Step 4: The “Distribution Agent Location” page will be opened. Select “Run all agents at the Distributor”, and then click on “Next”. 

Step 5: The “Subscribers” window will open. Select “Add Subscriber”, and then select “Add SQL Server Subscriber” from the provided drop-down.

The “Connect to Server” dialogue box will be opened. Enter the name of the Subscriber instance and choose “Connect”. 

Step 6: After adding the SQL Subscriber instance, select the dropdown next to your Subscriber instance. Select “New Database”, type the name of the database, and then click on “OK”. Since it’s a replica database, I have given it the name AdventureWorks2016. 

Step 7: The subscription database will be created and then registered to the Subscriber. Ensure that the account has db_owner permission on the new database. 

Step 8: The “Distribution Agent Security” page will be opened. Click on the ellipsis (…) button. Type in the details of the process account then click on “OK”. 

Step 9: Click on “Finish” and accept the default values on the remaining pages.

Step 10: On the “Creating Subscription(s)…” page, you will know whether the process was successful or not. Click on “Close”.

Step 11: Establish a connection to the Publisher in the SQL Server Management Studio. Right-click on the “Replication” folder from “Object Explorer”, then select “Launch Replication Monitor”. Check the status of the Transactional Replication setup that you have just created.

You should now be able to see data being propagated between the servers when you insert, update, or delete data from the articles that you included in the publication.

SQL Server Replication Types

SQL Server Replication manages the copying and synchronizing process of data between Databases continuously. The SQL Server Replication process is one-way, one-to-many, bidirectional, and many-to-one. There are SQL Server Replication types that use the Publish/Subscribe model listed below:

1) Snapshot Replication

Snapshot Replication is used to replicate the data as it is available at the moment when the Database Snapshot was created. It is used when data is changing infrequently and it is not critical to have a Database replica that is older than a Master Database. Snapshot Replication is best suited for a large volume of changes occurring within a short period. 

2) Transactional Replication

Transactional Replication is the periodic automated replication process when data is distributed from master to replica Database in real time. It is more complex than Snapshot Replication. It is possible to track the entire history of the transaction on the replica Database because all transactions are also replicated. 

3) Peer-to-peer Replication

Peer-to-peer Replication is mainly used to replicate data to multiple subscribers at the same time. This SQL Server Replication is used when Database Servers are distributed across the globe. Changes are propagated to all database servers. The main working principle is based on transactional replication.

4) Merge Replication

Merge Replication is a bidirectional replication that is usually used in server-to-client environments for syncing data across Database servers when they cannot be connected continuously. The changes are detected on both the Databases and modified Databases to synchronize and update their state.

Requirements for SQL Server Replication

The ports open for inbound traffic are:

• TCP 1433, 1434, 2383, 2382, 135, 80, 443
• UDP 1434

The following software must be installed: 

• .NET Framework 
• SQL Server – the database server software
• SQL Server Management Studio (SSMS)

Recommendations for SQL Server Replication

There are some factors to consider before configuring the environment for SQL Server: 

• Publications contain only tables with the primary key.
• Snapshot scheduling should not be used for large Databases because it consumes computing

Before we wrap up, let’s cover some basics.

What is SQL Server Replication?

SQL Server Replication is a process of copying and distributing data and Database objects from one Database to another Database and synchronizing all the data between the Databases to maintain the integrity and consistency of the data. The process can be set to synchronize data continuously in real time or scheduled to run at the desired intervals. Mainly SQL Server Replication is used to copy and reproduce data at the desired targets. Some of the SQL Server Replication techniques are listed below:

• One-Way
• One-to-One
• Many-to-One
• Bi-Directional

SQL Server Replication Components

Replication in SQL Server consists of the following components:

• Article: This is the basic unit of SQL Server and is made up of views, tables, and stored procedures. You can use the filter option to scale the article either vertically or horizontally. You can create multiple articles on one object with particular restrictions or limitations.
• Publication: A publication is a logical collection of articles from the database. It allows you to define and configure the properties of articles at a higher level so that all articles in the group may inherit the properties. 
• Publisher Database: The Publisher is a database that contains a list of objects designed as replication articles. The Publisher may have one or more publications. Each Publisher creates many internal replication stored procedures that define a data propagation mechanism. 
• Publisher: This is a database instance responsible for making data available to other locations through replication. It may have one or more publications, with each publication defining a set of logically related objects and data to be replicated. 
• Distributor: This is a database that stores replication-specific data from one or more Publishers. In most cases, the Distributor is a single database acting as both a Publisher and a Distributor. Such a Distributor is referred to as a Local Distributor. Each Publisher must be associated with a single database called a Distributor or a Distribution database. 
• Distribution Database: Each Distributor should have at least one Distribution database. The Distribution database stores the article details,  data, and replication meta-data. Note that a single Distributor may hold more than one Distribution database. However, all publications from one Publisher must use one Distribution database. 
• Subscriber: This is a database instance that consumes the SQL Server Replication data from a publication. The Subscriber can receive data from one or more publications and Publishers. 
• Subscription: This is a request for a copy of a publication to be sent to a Subscriber. The subscription defines when the publication data is to be received, and where. 
• Subscription database: This is the target database of a replication model.

Conclusion

To summarize, this is what you’ve done in this article:

• You prepared the replication snapshot folder to hold replication data. 
• You configured the SQL Server Distributor. 
• You configured the SQL Server Publisher. 
• You configured the SQL Server Subscriber.

Phew! That was a lot of work. But it’s not all done. In most practical situations, you might face many more challenges. There will be several configuration changes that have to be made to run this effectively. You will need engineering bandwidth to write custom scripts, process data, and move it into the target. So there is a lot of learning that is required to manage a clean execution. An alternative to all of this mess is to use an automated pipeline tool like Hevo.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Learn more about Hevo[/hevoButton]

Want to take Hevo for a spin? Sign up for a 14-day free trial and experience the feature-rich Hevo suite first-hand.

Share your experiences with SQL Server Replication in the comments. We would love to hear from you!