The need for high-availability features with Microsoft's SQL Server is growing fast, especially in the age of the cloud. However, instead of providing the much-needed high-availability features to a wide user base, Microsoft has scrapped database mirroring and restricted its successor, the AlwaysOn high-availability groups introduced with SQL Server 2012 and upgraded in SQL Server 2014, to the Enterprise Edition.

Better Safe than Sorry

High availability (HA) maximizes the accessibility of database servers by mitigating the effect of hardware damage, compensating for the failure of virtualized IaaS services or buffering failure of the network infrastructure. Thanks to high-availability features, downtime can be minimized from the perspective of the end user, and you can largely rule out the loss of transaction data.

High availability of a database, however, is difficult to achieve because you must always ensure the integrity of transactional data. If this integrity is lost, things can quickly become expensive. In the 2012 version, MS SQL Server met these requirements with up to three high-availability features, depending on the configuration:

• Database mirroring
• AlwaysOn Failover Cluster Instances
• AlwaysOn Availability Groups (only available in the Enterprise Edition)

Additionally, features like database replication and log forwarding guarantee data backup in an emergency.

Database Mirroring Discontinued

Synchronous database mirroring in MS SQL Server is a high-availability solution, whereas asynchronous database mirroring (only available in the Enterprise Edition) is a solution for restoring data in an emergency. In any case, the database mirroring environment consists of two instances with local storage: a principal server with the principal database, and a mirror server with the mirror database. In high-security mode, automatic failover can take place. This configuration requires a third server, called the witness. The witness ensures that, in case of a failure of the principal server, the mirror server can take over its tasks seamlessly.

In synchronous database mirroring, the principal server passes the commit of each transaction directly to the mirror server and only reports the process as complete after receiving confirmation. The secondary server can take over the tasks of the primary server at any time. Information from outside of the database (e.g., jobs, joins, and transactions across multiple databases) is either lost or cannot be correctly taken over. Moreover, this solution is slow.

In contrast, asynchronous database mirroring occurs with a considerable time delay and is thus not suitable for automatic failover. It is, however, an option for data backup and disaster recovery.

Microsoft has now declared the database mirroring feature to be deprecated. Users of SQL Server are advised not to use this feature and not to develop new applications for it. As a substitute, Microsoft recommends AlwaysOn High Availability Groups. The only flaw: Each server instance in a high-availability group requires the Enterprise Edition of SQL Server. Just the license fees for minimum equipment for the smallest version of a high-availability group total more than US$  100,000 for a period of three years.

Database Replication

Database replication can improve availability of the database and also enable disaster recovery (DR) of the data. Replication in SQL Server is based on the publish/subscribe model. In this model, a primary server (the "publisher") passes its data, or a subset of the data, to at least one secondary server (the "subscriber"). In SQL Server 2012, the publication database can belong to an AlwaysOn availability group.

The biggest advantage of database replication is its flexibility. The SQL Server can also publish a subset of a database if necessary. Additionally, different server instances can be provided with separate indexes. This approach allows you to instruct the replica instance to create detailed reports, for example and relieve the load on production servers. The main disadvantage of database replication is the considerable complexity, which also entails a serious potential for error.