Easy Steps to Configure SQL Server Replication
Traditionally, applications operated in a standalone environment and used a centralized server to interact with users. However, many issues regarding performance, availability, and maintenance were faced by this centralized method. Hence, the concept of Replication was introduced to solve all of these issues.
Data Replication allows you to use multiple copies of the data in various locations simultaneously. It allows for the maintenance of specified sections of the database or a collection of required objects in several locations. Changes done at other locations are eventually synced with the main server.
SQL Server Replication is a high-availability functionality that allows you to replicate or synchronize data across databases at the object level. SQL Server Replication can be one-way, one-to-many, bidirectional, or many-to-one, depending on the Replication direction. It allows you to set either Master/Slave or Multi-Master Replication configurations. SQL Server offers both homogeneous and heterogeneous Replication, which means that you can replicate changes across SQL Server instances as well as SQL Server to Oracle or any other RDBMS.
Types of SQL Server Replication
SQL Server Replication provides many Replication types to replicate data in SQL Server. Before we look at what they are, it’s a good idea to brush up on the SQL Server Replication terms.
- Article: An article is an object such as tables, views, functions that can be replicated.
- Publication: A collection of articles on a publisher is referred to as a publication.
- Publisher: A publisher acts an instance that stores publications having articles that need to be given to subscribers.
- Subscriber: Each article in a publication will be delivered to a subscriber who has subscribed to them.
- Distributor: The distributor functions as the key intermediary between subscriber and publisher. It helps to collect articles from the publisher and deliver them to subscribers.
The following types of Replication are supported in SQL Server:
- Transactional Replication: In this type of Replication, changes at the Publisher are sent to the Subscriber as soon as they happen. The data updates are applied to the Subscriber in the same sequence that they were made to the publisher, and within the same transaction boundaries.
- Merge Replication: In this mechanism, you can modify the data on both the Subscriber and the Publisher. Triggers are typically used to keep track of the modified data. When connected to the network, the Subscriber synchronizes with the Publisher and exchanges any rows that have changed between the two since the last synchronization.
- Snapshot Replication: This Replication applies a Publisher snapshot to the Subscriber, which delivers data precisely as it appears at a certain point in time. It does not monitor any data modifications. The whole snapshot is created and transmitted to Subscribers when synchronization takes place.
Microsoft SQL Server also supports Peer to Peer, Updatable Subscriptions, and Bidirectional Replication. Each SQL Server Replication type has its own set of advantages and requirements. You can select any of these depending on your Replication use case.
Key Steps to Configure SQL Server Replication
Transactional Replication helps solve the challenge of transporting data between linked servers. You can quickly configure and administer a Replication topology with the help of the Replication Wizard. In this section, you’ll learn how to configure a Transactional Replication for connected servers.
Before you proceed, you must ensure you’re satisfying the following requirements:
- Install SQL Server Management Studio.
- Install SQL Server 2017 Developer edition.
- Download the AdventureWorks sample database.
- A Distributor must be configured before Replication can be set up. Hence, if you haven’t already configured your Distributor, refer to Tutorial: Prepare for SQL Server for Replication to configure it.
You can now execute the steps given below, to begin with, Transactional Replication in SQL Server.
Step 1: Configuring the SQL Server Publisher
Here, you need to set up a connection with the Publisher straight in your Management Studio and proceed ahead to expand the server node. Then, follow the given steps:
- From the “Object Explorer“, expand the “Replication” folder. Select the “New Publication” option. You’ll be able to locate this in the context menu after you right-click on “Local Publications“. You will now see a new window open up with the info on starting a new publication, as seen below. Then select “Next“.
- The window “New Publication Database” will display. Then choose the AdventureWorks2016 database and click “Next“.
- Select “Transactional Publication” in the Publication Type window, then click on “Next” to proceed forward, as shown below.
- You’ll be sent to the “Articles” section. Click “Next” after selecting all of the articles that should be included in the publication.
- Now, click on “Show only checked articles in the list” check box after selecting the required objects. You’ll have produced a list of potential candidates for publishing. Click “Next” to move forward.
- You will now get redirected to a window called “Filter Table Rows“. The filters that will be applied to your articles should be specified here. Just use the default settings and click on “Next“.
- In the following box, set the start time for the Snapshot Agent. You may either run it right away or schedule it to run later. Tick the check box to instantly generate a snapshot. Then click on “Next“.
- Now, select the account that will be used to execute the Snapshot Agent and click “OK“.
- To generate the publication right away, select the first option and click “Next“. Fill in the publication’s title and click “Finish“. The success notification should appear in the following window. You can then double-check whether the publication has been established in “Local Publications” as seen below.
Step 2: Configuring the SQL Server Subscriber
You can create the subscription by following the steps given below:
- Expand the “Replication” folder under the “Object Explorer” option. Here, you need to choose the New Subscriptions option by right-clicking on the “Local Subscriptions” button.
- The following window will show you how to create subscriptions in general. Click “Next” to proceed further.
- Click on the “Next” option, in the new window that opens up on your screen.
- Now the next window displays the “Distribution Agent Location“.
- Now, click on the Add SQL Server Subscriber option. You may find this in the dropdown menu. You can then click on the Connection option in the “Connect to Server” window and give a name to the instance.
- Next, select the dropdown next to your Subscriber instance and click on “New Database” to provide the database’s name. Then click “OK“.
- The subscription database will be generated, and the Subscriber will be added to it. Also make sure the account has the db_owner permission on the database.
- In the next window, “Distribution Agent Security“, enter the process account’s details and click “OK“.
- Accept the default values on the following pages and click “Finish“. You will be able to see if the process was successful or not on the “Creating Subscription” page.
- In SQL Server Management Studio, create a connection to the Publisher. To monitor the status of the Transactional Replication configuration you just made, right-click on the “Replication” folder in “Object Explorer” and select “Launch Replication Monitor“.
You’ll now be able to see the data streaming across the servers after you’ve inserted, updated or even deleted the data from the articles in your publication.
In a nutshell, you understood the need for SQL Server Replication and learned its various types. You also understood the steps to configure the Transactional Replication. But, with large and complex databases you might face many more challenges while replicating your data. So, an alternative like an automated pipeline tool such as Hevo Data might save your day.