Databases are used practically in all fields, from IT to healthcare and the arts. According to Statista's ranking, Microsoft's product – SQL Server – entered the top three most frequently used databases in September 2023. However, many companies that prefer this Database Management System (DBMS) may have various questions. These include: How do you configure MS SQL Server replication?
In this article, we will discuss the types, components, and ways to set up SQL Server replication. We will also examine a real example and advanced solutions for reliable data copying.
Introduction to SQL Server Replication: Terminology
Before we get into the process of replicating data, let's understand the basic concepts:
● Microsoft SQL Server is specialized software that helps manage databases and is installed on Windows operating systems.
● MS SQL server replication is a technology that allows you to create precise copies of your database. They are necessary for quick recovery, synchronization, and the creation of distributed databases (those located on different computers or mobile devices).
Key Components in SQL Server Replication
When it comes to replication, the following components are commonly used:
➡️ Articles are basic replicable units (tables, stored procedures, functions, and views). You can expand them vertically and horizontally using filters.
➡️ Filter is a set of conditions for articles that help select specific entities for replication. This ensures that cloning does not consume a lot of traffic, reduces the volume, and eliminates redundancy of data stored in the replica.
➡️ Publication is a logical collection of articles intended for replication.
Among the components of MS SQL replication, there are key roles:
➡️ Distributor is an instance of the SQL Server database that needs to be configured to collect transactions from publications and distribute them to Subscribers.
➡️ Publisher is the main copy of the database with the configured publication, providing data availability for other SQL servers during cloning. It can contain multiple publications at once.
➡️ Subscriber is a database that receives copied data from publications.
Additionally, let's consider a few more important components of database replication SQL server:
➡️ Agent acts as a background service for the DBMS, designed to schedule the automatic execution of operations such as replication and MS SQL Server backup.
➡️ Subscription is a request for a clone of the publication that the Subscriber can provide. Traditionally, subscriptions are divided into:
● push – modified data is forcibly transmitted from the distributor to the Publisher without the latter's request;
● pull – modified data made on the Publisher is transmitted to the Subscriber upon request.
➡️ Subscription database is the target database in the MS SQL replication model.
➡️ Metadata is data applied to describe objects in the database.
Now that we've studied the terminology and basic components, let's explore the types of replication.
Types of SQL Server Replication
SQL server replication, as a function of Microsoft SQL Server, creates a clone of your database, maintains data consistency and integrity, and synchronizes changes between multiple databases. There are four types of replication:
1. Snapshot Replication
From an experienced user's perspective, this is the simplest type used to create a snapshot of data. By running the snapshot agent, a copy of the table with information is made and transmitted by default to the snapshot folder. The distributor then passes the snapshot to Subscribers.
Snapshot replication is well-suited for a database where data changes infrequently. You can also manually select the frequency of snapshot creation (daily, monthly, or quarterly).
2. Transactional Replication
This SQL database replication ensures transaction delivery. It employs a log reader agent that searches your logs for transactions marked for publication and sends them to the distributor. Two subscription types (push and pull) discussed earlier are used here.
Transactional replication is suitable when information in the source database changes frequently. It's also an excellent option to generate reports on all transactions conducted and switching in case of a failure with the main database server.
3. Merge Replication
Merge replication is similar to transactional replication, but it allows copying data from the Publisher to the Subscriber and vice versa, making it bidirectional replication. It is predominantly used in client-server environments. Merge replication is necessary to synchronize data between different database servers that cannot be constantly connected to the network.
It is the most complex and rarely used type of replication SQL server. However, it is suitable for peer-to-peer data sources (e.g., stores or warehouses) working with shared storage.
4. Peer-to-Peer Replication
This type is used when simultaneous MS SQL server replication is needed for multiple Subscribers, making it suitable for distributed servers. Notably, you can make changes on any database server, and the result will be propagated to all others.
Peer-to-peer replication is also necessary for scaling applications that use a database.
Configuring SQL Server Replication
Let's consider how to prepare and the steps to configure MS SQL Server replication using the example of Adventure Works 2016.
Prerequisites for Replication Setup
Before starting, install the following products:
➡️ Microsoft SQL Server 2008 or its later version.
➡️ Microsoft SQL Server Management Studio (SSMS) – software for managing databases with a graphical user interface (GUI).
➡️ .NET Framework – a set of libraries that support the creation and execution of web services and Windows applications.
Important: For proper MS SQL Server replication, note that if the first computer has the 2016 version as the Publisher, you should configure the Distributor MS SQL Server on the second computer with versions 2016, 2017, 2019, and the Subscriber with versions 2012, 2014, 2016, 2017, and 2019.
Step-by-Step Configuration Guide
We will take one version of MS SQL Server as the Distributor and Publisher and another as the Subscriber. Next, let's examine a step-by-step guide on configuring SQL server database replication:
1. Configuring SQL Server Distributor
Distribution can be easily used for multiple Publishers and Subscribers. When selecting the server to act as the Distributor, ensure that the disk where it is planned to be located has sufficient memory for cloning. Also, check the processor power to support MS SQL server replication.
2. Configuring SQL Server Publisher
The next step is to configure the Publisher. You should do it on the server where the main database, the replica of which you want to create, is located.
3. Configuring SQL Server Subscriber
Subscriber configuration is necessary to monitor data transfer between servers when inserting, updating, or deleting them from articles included in the publication. MS SQL Server replication divides into:
● Push replication, where the Subscriber needs to be configured to run agents on the main database server.
● Pull replication, where the Subscriber must be configured to run agents on the second computer where the database clone is created.
Real-life Example: Setting up SQL Server Replication
Follow these steps sequentially to properly configure the Distributor for SQL database replication:
1. Open SSMS to connect to your SQL Server instance.
2. Right-click on the Replication folder.
3. Choose Configure Distribution from the menu that appears.
4. Click Next in the Configure Distribution Wizard window.
5. Select the current instance as the Distributor or another one that is already configured as the Distributor and click Next. In our example, the Distributor will be local, so choose ServerName will act as its own Distributor; SQL Server will create a distribution database and log.
6. The snapshot folder will appear. You will use it during the initial synchronization or reinitialization of MS SQL Server replication. Beginners are advised to choose the default folder path. Click Next.
7. Specify the database name on the Distribution Database page. You can also choose the location of the data and log file folders here. Click Next again.
8. Choose the local instance on the Publishers page and click Next.
9. Select a scenario that you can repeat later on the Completing the Wizard Actions page and click Next.
10. Review the accuracy of the previous settings on the Complete the Wizard page and click Finish to create your Distributor.
11. Track the creation process on the final Configuring page.
This way, you have configured your own Distribution database. Next, to perform SQL replication, you need to configure the Publisher.
1. Access Object Explorer and locate the Publisher folder. Right-click on the Local Publications folder. Choose New Publication from the menu that appears.
2. Review the general information in the New Publication Wizard window and click Next.
3. Select Adventure Works 2016 in the Publication Database window.
4. In the Publication Type window, choose Transactional Publication and click Next.
5. The Articles window will open. Select all the necessary articles you want to add to the publication. Click Next. 
6. Check the box for Show only checked articles in the list to verify the selected objects and click Next to continue configuring replication in SQL Server.
7. The Filter Table Rows window will open, where you must choose filters to apply to the selected articles. It's better to choose the default value. Click Next.
8. Select the Snapshot Agent window to determine when the snapshot agent should run. Immediate launch is recommended. Click Next again.
9. Specify the account you want to use to run the snapshot agent. Click OK.
10. Choose Create the publication in the Wizard Actions window to create the publication immediately.
11. Name your publication in the next window and click Finish.
12. You should see a message about the successfully created publication.
13. Double-check the creation of your publication in the Local Publications folder.
Done! Next, proceed to the final step of configuring MS SQL Server replication – creating a subscription.
1. Right-click on the Local Subscriptions folder and select New Subscriptions.
2. In the opened window, click Next, and in the next window, click Next again.
3. On the Distribution Agent Location page, select Run all agents at the Distributor and click Next.
4. Check the box for Add Subscriber in the Subscribers window, and at the bottom, select Add SQL Server Subscriber.
5. In the Connect to Server dialog, enter the instance name of the Subscriber and click Connect.
6. Choose New Database next to the Subscriber instance in the drop-down list and give it a name. In this example, it is AdventureWorks2016.
7. The subscription database is created and registered on the Subscriber. Check if the account has db_owner permission for the new database.
8. On the Distribution Agent Security page, click (...) and add the process account data. Click OK.
9. Accept all default values on the remaining pages and click Finish.
10. The success result will be displayed on the Creating Subscription(s)… page. Click Close.
11. Establish a connection to the Publisher in SQL Server Management Studio. In the Object Explorer, right-click on the Replication folder and select Launch Replication Monitor. This way, you can check the status of the newly created MS SQL Server replication configuration.
Recommendations and Best Practices
We've gathered some crucial factors for you to consider in order to carry out a successful replication in SQL Server. Please note that:
✅ There are certain limitations on fields, and identifier triggers in the database.
✅ The publications you create may only include tables with primary keys.
✅ Scheduling the creation of snapshots may not be advisable for large databases, as it requires significant computational power.
✅ Replication in SQL Server may halt if data in the replica database, located in the Publisher, is altered, edited, or deleted.
The DB Serv expert team recommends adhering to best practices:
➡️ Pre-Research: To conduct successful MS SQL Server replication, first determine its type. Consider your goals, business requirements, and technical capabilities.
➡️ Thorough Planning: Before initiating replication, define the data to be copied and the server that will play the roles of the Publisher and Subscribers.
➡️ Using Different Servers: To maintain performance and successfully carry out replication in SQL Server, choose different servers for the Publisher and Subscribers.
Exploring Advanced Replication Solutions
Let's explore optimal and advanced implementation strategies for your database using MS SQL server replication:
1. Transactional Replication: Employ this for high-frequency changes in data. It is particularly relevant for large enterprises with multiple branches and frequently changing data.
2. Merge Replication: Necessary for load balancing and data preservation. This type of MS SQL Server replication provides greater flexibility to your business by combining the capabilities of different types of cloning.
3. Sparse Replication: Designed for distributing data over long distances with low bandwidth. The use of sparse columns ensures space savings of at least 20-40%, according to Microsoft claims.
4. Full Table Replication: This strategy helps copy all updated rows from the source to the destination. This MS SQL Server replication strategy is suitable if you need a mirrored reflection of the source or create a replica in another location, independently of user locations.
When choosing the optimal strategy, consider the needs of your system and the characteristics of the data that you need to copy.
Conclusion
Data replication is a crucial feature in maintaining a database, as copying information from one source to another is critical to the successful operation of a business. MS SQL Server replication allows you to take care of corporate information even in the event of unforeseen failures or issues on the server.
Need advice or planning for replicating your database? Contact the experts at DB Serv to get quick answers to all your questions.