Replication Strategies with SQL Server

Objective

The replication is used to copy data (and most often entire databases) from one database to another, either in the same MS SQL instance on the same server or on another one located on another server or infrastructure.

 

Warning! The duplication module copies data from flat PSQL tables to flat MS SQL tables, on the same server or on another server. Data duplication and data replication are two very different concepts.

For more information, please consult the following document: Data Duplication Services.

 

Summary

 

SQL Server AlwaysOn

SQL Server offers the AlwaysOn High Availability Technology which allows for data recovery in case of a disaster, and insures internal and external data redundance. This facilitates the fast failover of applications in the case of downtimes (planned or not).

The secondary replica can be more than just an inactive passive server. It can be used to treat demands in read only mode for report and dashboard applications, as well as perform backup tasks, freeing up space on the original replica.

AlwaysOn offers the following functionalities:

  1. The AlwaysOn Availability Groups functionality allows for the basic mirroring of databases and helps to ensure access to application databases. The data is transferred to secondary instances in near real time. It provides automatic and manual failover options for database groups, the support of eight secondary databases, and the automatic restoration and repair of pages.
  2. The AlwaysOn Active Secondaries function allows the discharge of tasks, such as backups/restorations, or report generations, from the original database to secondary databases. This function offers the optimisation of resources and significantly reduces unoccupied servers.

SQL Server Configuration for AlwaysOn

 

SQL Replication

Replication is used to duplicate one database's information to another. When changes are made on the source database, the system displays those changes on a distributor, where users can go and view them.

Theoretically, replication requires the use of three SQL servers: The source server, the distribution server, and the user's server. However, the distribution and user servers can be combined to improve performance.

There exists many replication models but, in an operational context, the transactional mode is preferred. This mode has a very short latency time which allows for transactions to be replayed on the second instance very quickly. Therefore, without being in real time, this replication mode renders a quick updated second instance based on the first.

Depending of the infrastructure, a replication can be more complex to put in place and use. Also, in the case of a disaster, an intervation is required to failsafe the instances.

SQL Server Configuration for Replication

 

Mirroring

Mirroring consists of capturing transactions produced on an instance through time, in order to reproduce them on the "mirror" instance of a distant server. The source server sends the transaction data to be reproduced on the wanted server through an encrypted http layer.

Databases are synchronised using the backup/restore with no recovery strategy. The transaction log backup/restore step is obligatory.

A database mirroring session can be executed synchronously or asynchronously. When in asynchronous operational mode, transactions are validated without waiting for the mirror server to save the log on the disk, which increases the performance to the maximum. In synchronous operational mode, the transaction is saved on the two partner servers, but this results in a higher transaction latency.

SQL Server Configuration for Mirroring

 

Snapshot

A database snapshot is a read-only static view of an SQL Server database (the source database). Upon creation, the snapshot and source database are transactionally coherent. The snaphot always resides on the same server instance as its source database. While the database snapshot offers a read-only view of the database, as it was upon its creation, the snapshot file size increases every time changes are made to its source database.

A snapshot differs from a regular backup since the snapshot creation process is instanteneous and only takes up disk space when modifications are made to the source database. On the other hand, a backup stores a complete data copy upon its creation. In sum, a snapshot offers an instanteneous read-only copy of the database, whereas the backup needs to be restored on a server to be read.

SQL Server Configuration for Snapshot

 

Backups and Restoration

The SQL Server backup and restoration component offers an essential backup for the protection of critical data stored on your SQL Server databases. To reduce the risk of catastrophic data loss, databases should be backed up regularly so as to preserve all modifications made to them. A well planned backup and restoration strategy makes it possible to protect databases against data loss due to various failings. Test your strategies by restoring a backup set and then recuperating your database, in order to be thoroughly prepared in the case of an incident.

In order to use restoration for reporting, you must set up an automatic restoration towards a new database.

SQL Configuration for Backups and Restoration

 

Last modification: September 19, 2024