Data Replication / Geographic Redundancy / Corporate Infrastructure
Why a Backup Server in the Same Data Center is Insufficient
For large enterprises, the availability of corporate information systems increasingly depends on the resilience of not only individual servers but also infrastructure sites as a whole.
Clustering architecture helps mitigate the impact of individual server failures. Disk system redundancy protects against damage to individual drives. Duplicating network components reduces the risk of communication loss due to equipment malfunction. However, all these mechanisms can prove insufficient if the primary and backup servers are located in the same facility or use shared critical infrastructure.
A fire, a prolonged power outage, damage to the cooling system, or a large-scale network failure can simultaneously make all components of a local cluster inaccessible. For enterprises that cannot afford prolonged operational downtime, one possible solution is to create a geographically separate backup site.
It is necessary to ensure the transfer of current changes from the production database to its remote copy. Replication is used for this task – a mechanism for maintaining copies of information on multiple servers or in different information environments.
However, replication between data centers poses complex technical challenges: how to ensure transaction consistency, what level of latency is acceptable, and what to do in case of communication loss between sites. The correct answer depends on the DBMS architecture, the nature of the business workload, and recovery requirements.
Main Models of Enterprise Database Replication
Replication can be implemented at several technological levels.
Storage System Level Replication
In this case, specialized storage systems transfer changes between arrays at different sites. This approach can be practical for virtualized infrastructure and large enterprise environments.
Its advantage is relative independence from specific application software. However, block copying does not always guarantee a consistent database state at the transaction level.
For correct recovery, it is necessary to consider DBMS mechanisms, the order of writing data and logs, and the possibilities of creating consistent copies. Recovery is particularly complex for systems where a single business operation simultaneously modifies several independent databases.
DBMS-Level Replication
Most industrial database management systems have their own change transmission mechanisms. They take into account the transactional model and the specifics of information storage.
For example, PostgreSQL can transmit WAL log records to a standby server. Microsoft SQL Server uses AlwaysOn Availability Groups, Log Shipping, and other tools depending on the requirements.
Oracle Data Guard transmits redo information to a standby database. Such solutions can provide more reliable consistency control than simple file copying.
Logical Replication
Another approach involves transmitting changes at the level of individual tables or records. It can be used to synchronize a defined portion of information between systems.
For example, an enterprise can transfer customer directories to a regional data center without copying the entire corporate database. However, logical replication requires control of the data schema, operation sequence, and conflict handling.
It is also important to understand that the capabilities of logical and physical replication differ significantly between DBMS.
Synchronous and Asynchronous Replication
One of the key architectural choices is the transaction commit mode.
Synchronous Replication
In the synchronous model, the completion of a transaction on the primary server depends on receiving a defined acknowledgment from the replica. This allows for a high level of protection for committed transactions.
However, this scheme increases the latency of operation execution. If servers are located in different cities, each transaction may depend on the network request transit time between sites.
For systems with a large number of short operations, this can significantly impact performance. Therefore, synchronous replication is most often practical with low network latency and a stable channel.
Specific data durability guarantees depend on at which stage the DBMS confirms the log write and whether it has been applied to the standby node.
Asynchronous Replication
In the asynchronous model, the primary server can confirm a transaction before the changes are fully transferred to the standby site. This reduces the impact of network latency on the performance of the production system.
However, there is a possibility of losing some of the last confirmed transactions if the primary server becomes unavailable before the standby receives the corresponding changes. For example, if the remote replica is 20 seconds behind, a failover may result in operations from that period being absent on it.
Actual loss depends on the state of the logs and the ability to retrieve data from the previous primary node. The PostgreSQL 9.4 documentation explicitly distinguishes between synchronous and asynchronous models and their impact on performance and data loss risk.
Active-Passive Architecture
One of the most common geographical redundancy models is Active-Passive. The primary site hosts the production database, which handles read and write operations.
The standby site maintains a copy of the information but does not perform independent changes to the same transactional data.
- Primary DC
- ERP / CRM
- Primary DB
- Read / Write
- Backup DC
- Standby DB
- Backup Applications
- Standby
- Log Shipping and Replication Status Monitoring
Simplified inter-site backup model. In normal operation, the Primary DB remains the main source of changes. In case of primary site failure, the backup database can be switched to production mode.
However, the switchover involves more than just activating the DBMS. It is also necessary to ensure the availability of application servers, network addresses, authentication systems, and other components.
If the ERP is physically available but cannot connect to the new database, the business process will not be restored.
Active-Active: Advantages and Complexity
Another approach is the Active-Active architecture, in which multiple sites simultaneously serve production workloads. However, it is necessary to distinguish between two scenarios.
In the first, both sites can serve read requests, while writes are performed only on the primary server. In the second, writes are allowed on multiple nodes.
It is the second option that creates the greatest consistency challenges. For example, two regional data centers might simultaneously change the same customer record.
To correctly resolve the conflict, it is necessary to determine which change takes precedence, whether they can be merged, and which business rules apply. The usual last-write-wins rule by no means always ensures a correct result.
For financial systems, inventory management, and order processing, uncontrolled multi-site replication can create significant risks. Therefore, Active-Active should only be used when there is a clearly defined record distribution model and the technological platform supports it.
Replication in Microsoft SQL Server 2014
Microsoft SQL Server 2014 provides several mechanisms for organizing backup databases.
AlwaysOn Availability Groups
AlwaysOn Availability Groups allow maintaining a group of databases with a defined primary replica and secondary copies. Synchronous or asynchronous change transmission modes can be used for different secondary replicas.
Remote replicas can be used as disaster recovery targets. In some configurations, secondary databases can also serve read requests.
In SQL Server 2014, Microsoft increased the maximum number of secondary replicas to eight, expanding the possibilities for building geographically distributed configurations.
At the same time, automatic failover is available only under specific conditions, including a suitable synchronous configuration and a correct cluster state. For a remote asynchronous standby, a controlled manual failover procedure usually needs to be planned.
Log Shipping
Log Shipping involves regularly backing up the transaction log and transferring it to a secondary server. After being received, the log is applied to the secondary database.
This is a relatively straightforward mechanism for setting up a standby site, but it has its own limitations regarding latency, automation, and the usability of the secondary database. The log transfer interval directly affects the potential data loss volume in case of a disaster.
It is also necessary to monitor the continuity of the log chain.
Oracle Data Guard
For enterprise systems based on Oracle Database 12c, one of the main mechanisms for geographic redundancy is Oracle Data Guard.
The technology allows maintaining physical or logical standby databases and transferring changes from the production system.
For a physical standby database, redo data is used, which is applied to maintain a consistent state of the copy.
Data Guard can be used to increase availability and disaster recovery.
Specific capabilities, in particular opening a physical standby database for reading during the application of changes, are related to the Active Data Guard option and the corresponding licensing.
Switchover and Failover
In redundancy technologies, it is important to distinguish between planned and emergency switching.
Switchover is a controlled role change between the primary and standby databases, for example, for hardware maintenance.
Failover — transition to a standby database due to the primary’s emergency unavailability.
These operations may have different consistency requirements and subsequent return of the system to normal operation.
The enterprise must document both scenarios.
PostgreSQL 9.4: Streaming Replication
PostgreSQL 9.4 supports Streaming Replication based on the Write-Ahead Log.
The primary server transmits WAL records to the standby node, which uses them to replay changes.
In the default configuration, streaming replication operates asynchronously.
However, PostgreSQL also supports synchronous mode with appropriate configuration.
The PostgreSQL 9.4 documentation describes WAL streaming, standby servers, and the ability to maintain a more up-to-date copy compared to periodic transfer of completed log files.
Log Control
For reliable operation, it is necessary to consider WAL storage.
If the standby server is unavailable for an extended period, and the required logs have already been deleted from the primary node, replication recovery may require re-creating the base backup.
In PostgreSQL 9.4, replication slots are already available, which allow for the standby servers’ need for corresponding WAL records to be taken into account.
However, incorrect use of slots also creates a risk of uncontrolled log accumulation and disk space filling.
Therefore, monitoring is necessary.
MySQL 5.6: Binary Log Replication
For MySQL 5.6, the typical mechanism is replication based on the binary log.
The primary server records the necessary information about changes, after which it is transferred to the standby node.
MySQL supports various logging formats, including statement-based and row-based replication.
For improved replication management convenience in MySQL 5.6, Global Transaction Identifiers (GTID) can be used.
They help identify transactions and simplify certain replication source change operations.
However, automated failover requires additional control mechanisms and correct configuration.
Additionally, standard asynchronous replication does not guarantee the absence of loss of the latest transactions in case of a sudden failure of the primary server.
Geographic Replication in a Cloud Environment
Cloud platforms also offer capabilities for placing backups in different regions.
For example, Amazon RDS supports Cross-Region Read Replicas for MySQL 5.6.
This feature was introduced by Amazon in November 2013.
It allows maintaining a replica in another AWS region and, if necessary, promoting it to a standalone primary instance.
However, this functionality is not equivalent to a fully automated disaster recovery system.
The enterprise must define the procedure for switching applications, changing connections, and verifying data currency.
For Microsoft SQL Server, it is also possible to use Availability Groups between Microsoft Azure regions at the infrastructure virtual machine level.
Microsoft confirmed support for relevant configurations back in June 2014.
Therefore, cloud platforms can serve as a component of geographic redundancy, but they do not eliminate the need to design recovery procedures.
Split-Brain: The Danger of Two Primary Servers
One of the most complex failure scenarios is split-brain.
It occurs when two nodes or sites mistakenly believe they are the primary and allow independent changes to be made.
For example, network connectivity between data centers is lost.
The standby site assumes the primary server has failed and activates its own writeable database.
However, the primary server continues to operate and accept transactions.
As a result, the enterprise ends up with two independent change histories.
Subsequent merging of such data can be extremely difficult.
To prevent this, quorum mechanisms, role control, fencing, and old primary node isolation procedures are used.
It is especially important not to allow automatic failover based solely on a temporary loss of network connectivity without a proper cluster state determination mechanism.
Practical Scenario: Two Data Centers of a Trading Enterprise
Let’s consider a hypothetical trading company whose central ERP system operates in Kyiv.
The enterprise has regional offices, warehouse complexes, and a corporate online store.
To reduce the risk of prolonged ERP unavailability, a backup site is created in another city.
Primary Site
In Kyiv, a production database operates, to which the ERP and integration services connect.
Changes are transmitted to the remote data center asynchronously.
Backup Site
The second data center supports a copy of the database and backup application components.
The connection between sites is secured, and the replication status is monitored by the monitoring system.
Disaster Scenario
In case of complete unavailability of the primary site, responsible specialists check the backup status and decide on its activation.
After switching, application services gain access to the new primary server.
The enterprise must check the last available transaction and determine if there are any operations that require re-entry.
Return to Normal Architecture
After the primary data center is restored, its previous copy for writing cannot be simply activated.
It is necessary to establish the current data state, perform reverse synchronization or re-creation of the backup, and organize a controlled role return.
This is a separate stage of Disaster Recovery that must be foreseen during the design phase.
Replication Monitoring
For industrial operation, it is insufficient to only check server availability. The DBMS may respond to queries, but the replica lag will be several hours.
This situation creates a risk of significant data loss in case of an accident.
Therefore, it is necessary to monitor the connection status, log transfer lag, change application speed, accumulated log volume, and free disk space.
- For PostgreSQL, standard system views of replication status can be used.
- SQL Server provides AlwaysOn diagnostic views.
- Oracle uses its own Data Guard monitoring mechanisms.
Backup execution should be monitored separately.
Replication does not replace Backup, as erroneous changes can propagate to standby nodes.
Organizational and Financial Aspects
Geographic redundancy is a complex infrastructure project. The enterprise needs to consider the cost of servers, storage systems, network channels, licenses, and ongoing administration.
If the backup data center is located far away, transmitting large volumes of logs can incur significant communication costs. Additionally, the backup site requires regular testing.
It is necessary to check not only the DBMS startup but also the availability of application services, the correctness of business operations, and the possibility of returning to the main infrastructure.
Therefore, investments in geographic redundancy must be justified by the real cost of downtime.
For a secondary information system, independent backup may be sufficient.
For a critical ERP or order processing platform, the feasibility of a remote replica can be significantly higher.
Requirements for geographic redundancy
Building a distributed information architecture requires coordinated work by specialists in databases, infrastructure, and business processes. After a switchover, check data consistency, reference data, and recovery of integration flows.
Map dependencies between integrated systems, document workflows and business processes to determine their recovery order. Define compute, storage, connectivity, and availability requirements for the recovery site.
If some backup resources are hosted externally, verify access to them from the recovery environment.
However, the decision on acceptable data loss and recovery time should remain with the company’s management.
Practical Implementation Guidelines
Replication between data centers allows companies to reduce dependence on a single infrastructure site and create additional disaster recovery mechanisms. However, its effectiveness is determined not only by data transfer speed.
It is necessary to correctly choose the synchronous or asynchronous mode, ensure protection against split-brain, monitor replica lag, and plan for switchover and failback procedures.
Various technologies are available for enterprise DBMS: Microsoft SQL Server AlwaysOn, Oracle Data Guard, PostgreSQL Streaming Replication, and MySQL Replication. Each has its own capabilities, limitations, and operational requirements.
It is also important to maintain an independent backup system, as replication does not protect against all categories of data loss.
Geographic redundancy becomes effective only when an enterprise has not just a second copy of the database, but a tested mechanism for restoring full business processes at a backup site.
Author