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 does SQL Server Replication work and how to set up SQL Server Replication steps. Let’s get started!
Table of Contents
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 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.
Hevo Data, an automated no-code data pipeline, provides you with a hassle-free solution to perform the SQL Server Replication with an easy-to-use no-code interface. Hevo is fully managed and completely automates the process of not only replicating data from SQL Server but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Hevo supports data replication from multiple variants of SQL Server like Generic MS SQL Server, Amazon RDS MS SQL, Azure MS SQL, and Google Cloud SQL Server.
Hevo’s fault-tolerant Data Pipeline offers a faster way to move your data from SQL Server and 150+ other data sources(including 40+ free data sources) into Data Warehouses, Databases, or any other destination of your choice. Hevo will take full charge of the data replication process, allowing you to focus on key business activities.
Check out what makes Hevo amazing:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations
- Auto Schema Mapping: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data from SQL Server and replicates it to the destination schema.
- Data Transformations: Using Transformations in Hevo, you can perform multiple operations like data cleansing, enrichment, and data normalization before loading the data to your desired destination. You can perform these transformations using 2 methods – Writing a Python-based transformation script or Using Hevo’s drag-and-drop transformation blocks.
- 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 Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call
Use Hevo’s No-code Data Pipeline to replicate data from SQL Server to a destination of your choice in a seamless and automated way. Try our 14-day full feature access free trial.
Get Started with Hevo for Free
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:
![Download the Ultimate Guide on Database Replication]()
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.
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”.
Image Source
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.
Image Source
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.
Image Source
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.
Image Source
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”.
Image Source
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.
Image Source
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”.
Image Source
Step 2: A new window will appear giving you the general details about creating a new publication. Click on “Next”.
Image Source
Step 3: The “Publication Database” window will appear. Choose the AdventureWorks2016 database, and then click on “Next”.
Image Source
Step 4: The “Publication Type” window will appear. Choose “Transactional Publication” then click on “Next”.
Image Source
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”.
Image Source
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”.
Image Source
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”.
Image Source
Step 9: On the next page, specify the account to be used to run the Snapshot Agent and click on “OK”.
Image Source
Step 10: Choose the first option to create the publication immediately. Click on “Next”.
Image Source
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.
Image Source
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”.
Image Source
Step 2: The next window will show the general details about creating subscriptions. Click on “Next”.
Image Source
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”.
Image Source
Step 5: The “Subscribers” window will open. Select “Add Subscriber”, and then select “Add SQL Server Subscriber” from the provided drop-down.
Image Source
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.
Image Source
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”.
Image Source
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”.
Image Source
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.
Image Source
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 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.
Since Hevo Data 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. Hevo caters to 150+ data sources (including 40+ free sources) and can seamlessly perform SQL Server Replication in real-time. Hevo’s fault-tolerant architecture ensures a consistent and secure replication of your SQL Server data.
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!