Replication is a technology of copying, distributing, and synchronization data between the main or master server and the slave or replica server. It is a key technology for high-load infrastructures, which enables them to:
● Acquire fault tolerance. Restoring from a backup can take up to several hours, and the backup may not be up to date/faulty. However, database replication will allow you to be sure of workflow continuity.
● Gain performance. It is like horizontal scaling. The load on databases is distributed between servers. Reporting and backup can be done from the backup version of the database, which will relieve the main one.
Types of Microsoft SQL Server Replications
There are 3 main types of Microsoft SQL Server replications:
➡️ Transactional replication. Changes on the master server are translated to the slave server with minimal latency, depending on network speed and hardware capacity. When transactions appear in the objects participating in replication on the publisher, the log reader agent copies these transactions to the distributor instance. Then the distributor agent copies the data to the subscribers. In addition to the standard one, it has varieties such as peer-to-peer, with updatable subscriptions, etc.
➡️ Merge replication. Merge replication uses a merge agent to replicate data changes on the publisher and subscriber.
➡️ Snapshot Replication. On the subscriber, a snapshot of data from the publication is applied without involving transaction logs and triggers.
Transactional replication is considered to be the most popular because it is easy to configure and allows you to quickly replicate the necessary SQL database fragments, thus solving the issue of access rights and data redundancy within OLTP. Therefore, in this article, we mainly consider its configuration.
Preparing for replication
SQLServer data replication works according to the subscription principle, which involves dividing servers into three groups: publishers (master server), distributors, and subscribers (slave). Slave servers subscribe to parts of the base on the master and apply to themselves all the changes that are passed by the distributor. Replicated objects are called articles.
Before setting up replication, DB Serv LTD specialists, based on their experience, recommend taking into account several SQLServer peculiarities and performing several preparatory steps that are rarely mentioned in Internet publications.
1. The first thing to do is to set up replication propagation.
a. It will be more convenient for an administrator to work with the push model of replication when the master server administers slave servers in addition to processing transactions. We recommend giving it a preference.
b. It is also worth configuring Windows authentication for all replication agents. Unlike the second (mixed) type of authentication, it gives you the ability to create Windows groups at the domain level and the login name in SQLServer for the entire group. This makes administration more intuitive.
c. Ensure that the owner of all agent jobs is the sysadmin, not the user who configures replication.
2. Now let’s preconfigure the publisher.
a. When creating a publication, add objects to it gradually, not all at once.
b. If snapshots are planned, check that the directory meets several requirements. It must be available on all servers in the cluster, have enough free space for all tables in replication, and be placed on a 4 KB cluster size disc to allow compression to be enabled since no one wants a 64 KB cluster size disc. The amount of free space on the disc where the snapshot is placed must be 2x the size of the uncompressed tables being replicated.
c. Since the snapshot application deletes objects in the replicated database, therefore, it is necessary to save in advance everything that may be needed after recovery – indexes, triggers, access rights to objects.
3. Finally, you should also think about subscribers.
a. A snapshot deletes objects in the replicated database, so keep everything you might need: indexes, triggers, and so on.
b. While taking the snapshot, monitor the locks in the database on the server with the publication, the free space on the disc where the snapshot is being taken, the load on the disc (reduce by compression if necessary), the locks in the subscriber database, and finally the space in the database on the subscriber.
c. The process of applying the snapshot should be monitored via Replication Monitor, sys.sysprocesses, and error tracing.
Configuring replication in Microsoft SQL Server
SQLServer data replication works on the subscription principle, which implies three groups of servers: publishers (master server), distributors, and subscribers (slave) Backup servers subscribe to parts of the base on the master and apply all the changes that are transferred by the distributor. Replicated objects are called articles.
Setting up the distributor
● In the context menu go to Replication => Configure Distribution. In the window that opens, select the top one from the two items “[distribution server name] will act as its own Distributor; SQL Server will create a distribution database and log”
● Select the folder where the database will be stored, taking into account the planned volume of transmitted data.
● If needed, mark the database instances for which this server can act as a distributor (you can return to this later if necessary).
● Set a password for communication with the selected SQL databases and click «Finish».
Setting up the publisher
In Configure Distribution click on the second item — “use the following server as the Distributer” and select the distributor server. Enter the password you set when configuring the distributor and complete the configuration wizard.
✅ Go to the context menu Replication => Local Publication => New Publication and select from the list the database you want to replicate.
✅ You will see a list of four replication types – snapshot, merge, peer-to-peer, and standard transactional – from which you should select Transactional publication.
✅ Now select the articles to be replicated and apply the filters. In our case, we are talking about tables, but transactional replication can also work with custom procedures, functions, and views.
✅ In Security Settings, click “Run under SQL Server Agent service account” and select the accounts from which the agents will be executed.
Setting up the subscriber
On the slave server create a new subscription, going Replication => Local Subscriptions and selecting the distribution server, the database, and the desired publication.
✅ To execute agents on the distributor, click on «Run all agents at the Distributor».
✅ Select the database to which the data will be transferred from the Subscription Database.
✅ In the security settings specify the desired accounts for agent fulfillment.
✅ Specify again the account from which the replication agents will be run. To make replication continuous, activate Agent Schedule => Run continuously.
✅ To activate the subscription, tick the Initialise checkbox and complete the configuration wizard. That’s it, MS SQL replication configuration is complete.
Microsoft SQL Server replication support
Tools for transactional replication are available in all versions of SQL Server and are fully included in the most massive Standard license. For example, Replication Monitor is used to configure publications. Going to the Add Publisher => Specify a Distributor and Add its Publishers path, you will see which publishers work with a particular distributor and can view logs to see if the required table has appeared in the database and if transactions are being transferred.
Common mistakes
MS SQL transactional replication is asynchronous, so in case of failure of one of the servers involved in replication, the transaction may fail, some data may be lost, and synchronization between publisher and subscriber may be disrupted. We advise you to pay attention to this when configuring replication, to improve data availability in SQL Server and work with their redundancy.
While there are many tutorials online on how to work with different types of replication, with the high cost of error, we advise businesses to seek professional help. At DB Serv LTD, we will provide Microsoft SQL Server administration and support to reliably protect your data and business processes.