Replicating SQL Server databases is essential task in many environments and keeping them in absolute sync over time can be a challenge.
In this article, we are going to quickly address potential issues and provide recommendations on important parts of the replication process with ApexSQL Log, a SQL Server tool which audits transaction log files and creates replication scripts.
When preparing and configuring transactional replication in ApexSQL Log, it is important to take into consideration the fact that ApexSQL Log will only allow one connection at a time to the “Primary” database. This means that the tool cannot be run for multiple replication tasks even when different destination databases are used. Additionally, this means that consecutive replication job cannot start until the prior one completes
Transactional replication is not an instant job, and processing transaction logs and replicating changes to another SQL Server database will require time – how much, depends on environment and volume of changes. The time frame for transactional replication should be no less than 15 minutes in common production environments. Anything lower than that may result in unnecessary job fails with the ‘next’ job starting before the prior one finishes which will still use CPU, disk and SQL resources in addition to failing at the task. With this in mind, it is better to aim at less frequent replication, since the cost to run a job every 30 minutes, than running 2 jobs every 15 minutes, while the one fails and the other one equals the 30-minute job is much greater.
Suggested replication time:
- Small-traffic databases – 15 minutes
- Medium-traffic databases – 30 minutes
- High-traffic databases – 60 minutes
So, what can we do to speed up the replication?
In the How to increase auditing and exporting performance in ApexSQL Log we offer several recommendations on how to maximize ApexSQL Log potential and allow more frequent replication jobs to occur.
Another potential pitfall of transaction replication is a data loss or desynchronization. Replication jobs can fail due to various reasons, including SQL failure, connection issues, deadlocks and more. With this in mind, it is important to ensure that each and every failure is addressed, and that synchronization is achieved as quickly as possible. Also, defending against duplicate replication jobs which can double or triple operations from the primary to secondary databases must be avoided
This potential hazard is nested in the replication setup which can incorrectly be configured with predetermined time frames – e.g. replication on last xy minutes/hours, or replicating at exact same time, like 07.30, 8.00, 8.30 etc. This creates foundations for a disaster to strike because if any of the hard-scheduled replication jobs fails, data from that time frame will not be replicated, or in some cases, data may be replicated multiple times.
To remedy this, ApexSQL Log uses continuous auditing feature to ensure continuous replication jobs do not overlap with one another, and in case that they do, to ensure that this overlap does not result in data loss or de-synchronization of primary and subscriber databases. Furthermore, since the feature uses and tracks LSN values to ensure no data is missed or duplicated and that the end LSN value of a previous successful replication job is used as a starting point for the next one, even skipping several consecutive replication jobs will not result in the data loss – the next successful auditing job will still look for the end LSN value to ensure no data or structure changes between two successful jobs are missed.