In today’s world where businesses operate in a global environment, data replication has become a critical aspect of database management. MS SQL Server Replication is one of the popular replication methods that is used by many organizations. It allows you to create multiple copies of data in real-time or periodically, and distribute them across different locations. With MS SQL Server Replication, you can ensure that the data is available to users in different locations, and you can improve the performance of your database by reducing the load on the primary database server.
Let us look at how to configure the MS SQL Server Replication. At the end of this article, you will have a better understanding of the replication method that is available in MS SQL Server and how to configure it.
Definition of Replication
Replication is a method of copying the data between two databases. In MS SQL Server, replication is a feature that allows you to distribute the data from one database to multiple databases. This process involves copying the data from the publisher database to one or more subscriber databases.
Related Terms to SQL Server Replication
-
- Publisher –It refers to the database server that sends or duplicates the data to another server in the SQL Server Replication.
- Subscriber – The publisher server sends a replicated data to the subscriber server.
- Article –An article is a database object, such as a table or view, which is replicated from the publisher to the subscriber.
Installation of SQL Replication
-
- Open the “SQL Server Installation Center” and click on the “Installation” tab.
- Click on the “New SQL Server stand-alone installation” option.
- Select the appropriate SQL Server instance.
- Go to the “Feature Selection” page and click on “SQL Server Replication”.
- Complete the rest of the installation process.
How to Configure the MS SQL Server Replication
We will discuss the main type of replication in MS SQL Server and how to configure it. “Snapshot Replication” is the simplest form of replication. It involves taking a snapshot of the database at a specific point in time and copying it to the subscriber.
Follow these steps to configure the MS SQL Server Replication:
Configure the Distributor
First, we have to configure the Distributor. Open the “Object Explorer” and navigate to the “Replication” folder. Right-click the folder and select “Configure Distribution”.
Choose to configure the current instance as the distributor or select an existing configured distributor instance.
Select the first radio button to configure the “Server Agent Service”.
Select a new path for the SQL replication snapshot folder on the following window or type in a custom path.
On the following window, choose the publisher that uses the distribution database. Then, click “Next”.
Select “Configure distribution”.
Review the distribution configuration settings. Then, click “Finish”.
Create a database and a table that we can use to configure the snapshot replication.
GO
USE school;
GO
CREATE TABLE employees (
EMP_ID INT,
NAME VARCHAR(50),
JOB_LOCATION VARCHAR(50),
SALARY DECIMAL(10,2)
);
GO
INSERT INTO employees (EMP_ID, NAME, JOB_LOCATION, SALARY)
VALUES (1, 'Papan', 'New Delhi', 70000.00),
(2, 'Neha', Hyderabad, 60000.00),
(3, 'Adil', 'Chennai', 55000.00),
(4, 'Arnita', 'Mumbai', 65000.00);
GO
SELECT * FROM employees;
Output:
Configure the Publisher
Now, we have to configure the Publisher. To begin, expand the “Replication” folder in the object explorer, and navigate to “Local Publications” -> “New Publication”.
Afterward, a new window will appear which displays various types of publications. Select “Snapshot Publication” and click on “Next”.
Choose the table that will be included in the publication. Then, click on “Next”.
To activate it, select the checkbox for “Create a snapshot immediately”. Then, click on “Next”.
On the “Agent Security” window, you need to configure a service account that the snapshot agent will run under. Simply click the “Security Settings…” button and enter the username and password for the service account. Then, click “OK”.
Select the “Create the publication”. Then, click on “Next”.
Configure the Subscriber
Now, we have to configure the Subscriber. To proceed, right-click on your publication in the object explorer and select “New Subscriber”. Choose the publication and click on “Next”.
In the next window, choose the first option to run all agents.
On the following window, choose the database on the subscriber server (SQL1).
On the next window, enter the connection details of the distribution agent. Click on the “(…)” and enter the necessary details.
Choose “Run continuously” for the synchronization schedule.
Initialize the subscription database using a snapshot of the publication. Then, click on “Next”.
Tick the checkbox on “Create the subscription” option. Then, click on “Next”.
Review the configuration options. Then, click on “Finish”.
By following these steps, you can configure the snapshot replication in MS SQL Server and replicate your data to one or more subscribers. This type of replication is useful when you need to make a copy of the database at a specific point in time and distribute it to the subscribers.
Conclusion
Configuring the MS SQL Server Replication can be a complex process. But by following the given outlined steps, you can easily configure the replication. Remember to plan your replication topology, create the publisher and distributor, and configure the replication. Monitor the replication to ensure that it is running smoothly.
Overall, MS SQL Server Replication is a powerful tool that can help the organizations to distribute the data across different locations and improve the database performance. By carefully planning and configuring the replication, you can ensure that your data is available to users in different locations and that your database is running smoothly.
