Microsoft SQL Server is an extensive and complicated database host, there are many ways of keeping services online during patches or instance failure.

Typically systems that can scale to maintain uptime are regarded as Highly Available. Highly Available systems are designed to be online as much as possible, often with no single points of failure.

SQL Server has many availability technologies built in to achieve robust and consistent SQL Server continuity of service,

• Replication
• Log Shipping
• Mirroring
• AlwaysON Availability Groups
• SQL Server Big Data Clusters

SQL Server Replication

SQL Server Replication is best suited for site to site connections to remote locations where connectivity is not ideal. Replication has roles for SQL Server, publishers, distributors and subscribers.

SQL Server Replication is best suited for modes of use where data is published to endpoints for dissemination, such as point of sale terminals or batch processing.

SQL Server Replication has not been modified significantly in SQL Server 2016 or SQL Server 2017 and has been available since SQL Server 2008.

SQL Server Log Shipping

SQL Server Log shipping is best suited for architectures where delays of 15 or 30 minute intervals are acceptable for changes in the data to be reflected, such as report generation or cataloguing through analysis tools like Microsoft PowerBi or SQL Server Integration Services for data transformation.

Because Log Shipping involves exporting a set of transactions to network attached storage and importing it into subscriber databases there is inherent delays in changes to the data and databases can fall out of synchronisation.

SQL Server Log Shipping involves configuring the ‘primary’ database to run a log shipping configuration and provide a network location to allow the client to read the transactions from and run restore jobs. When using SSMS this process is more or less straightforward as it will handle creating Jobs on the servers and seeding the database on the downstream secondary databases, as well as providing a standard report on transaction log shipping status to diagnose issues with the configuration if necessary.

SQL Server Log Shipping although easy to set up I have found when out of synchronisation restoring the database to catch up again with the transaction log is not always straightforward compared to other methodologies, especially with large databases.

Log shipping does not failover gracefully out of the box, clients will not be able to natively switch over if the ‘primary’ database has failed.

Mirroring

Using Mirroring in SQL Server is inadvisable as it is soon to be removed in future versions.

Database mirroring relies on having two different SQL Servers mirror each other and the principal (primary) database performs the transactions and the mirror server (secondary) then follows as soon as possible, depending on the configuration this can be asynchronous or synchronous.

Database Mirroring is effectively a more robust version of log shipping with automatic failover and does not require a witness to failover gracefully.

Always On Availability Groups

Always on availability groups can be configured to use Windows fail-over clustering or Linux Pacemaker.

Always on is the preferred option for most configurations due to its relatively easy to use nature and flexibility. Always On Availability Groups rely on a listener that applications connect to and WSFC (Windows Server Failover Cluster) (or Pacemaker) manages the cluster environment to ensure the databases remain up when the primary is unavailable and handle the mediation to elect a new primary. WSFC also allows you to select a methodology for defining a new primary database through quorum.