SQL Server Replication: 3 Easy Steps

on Tutorials • January 7th, 2022 • Write for Hevo

Introduction

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 in just three easy steps.

Table of Contents

Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources (including 30+ free data sources) like Asana and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.

GET STARTED WITH HEVO FOR FREE

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
SIGN UP HERE FOR A 14-DAY FREE TRIAL

What Is Replication?

Replication refers to a set of technologies used for copying and distributing data and database objects from one database to another and maintaining synchronization among databases to ensure there is consistency. 

SQL Server Replication doesn’t distribute the whole database. It only distributes selected parts of the database such as tables, views, etc.

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 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 the 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

This model is made up 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 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 DistributorEach 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. It is possible for the Subscriber to 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.

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

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”. 

SQL Server Replication: Configure Distribution

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

SQL Server Replication : Configure Distribution Wizard

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 own Distributor; SQL Server will create a distribution database and log”. 

Click on the “Next” button. 

SQL Server Replication : Distributor

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.

SQL Server Replication : Snapshot Folder

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”.

SQL Server Replication : Wizard Actions

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.

SQL Server Replication: Configuring the Wizard

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”.

SQL Server Replication: New Publication

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

SQL Server Replication: New Publication Wizard

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

SQL Server Replication: Publication Database

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

SQL Server Replication: Publication Type

Step 5: You will be taken to the “Articles” page. Select all the articles that you need to be included in the publication and click on “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”.

SQL Server Replication: Articles

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”. 

SQL Server Replication: Filter Table Rows

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”.

SQL Server Replication: Snapshot Agent

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

SQL Server Replication: Snapshot Agent Security

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

Creating the Publication.

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. 

SQL Server Replication: Check Local Publications

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”.

Creating New Subscriptions.

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

Configuring the Subscription wizard.

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”. 

SQL Server Replication: Distribution Agent Location

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

Adding new SQL Server Subscriber.

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. 

Choosing the new subscriber database.

Step 7: The subscription database will be created and then registered to the Subscriber. Ensure that the account has the 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”. 

Configuring Distribution Agent Security.

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

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

Creating Subscription in Subscription Wizard.

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” so as to check the status of the Transactional Replication setup that you have just created.

Launching Replication Monitor.

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 are 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 not be used for large Databases because it consumes computing

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.

visit our website to explore hevo

Since Hevo is a No-code Data Pipeline, you can execute such replications from several sources with just a few clicks. Hevo has an extremely intuitive user interface and it needs minimal supervision. You can give it a try for free here! You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

SIGN UP for a 14-day free trial and see the difference!

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

No-code Data Pipeline for MS SQL Server