High availability with SQL Server 2012 and 2014

Always Ready

Log Forwarding

Log forwarding involves the primary database server writing a backup of its transaction log to a shared volume. Other database servers can then access this volume from a remote location to collect the logs and, from this, restore the database locally. Users can send queries to these restored databases. However, recovery and the queries must not occur simultaneously; before restoring, all running queries must be terminated forcibly. Log forwarding provides a robust method for database recovery in case of emergency. Log forwarding is supported by the Enterprise, Business Intelligence (BI), Standard, and Web SQL Server editions. (See the "Standard and Enterprise" box for more information on supported features.)

Standard and Enterprise

The 2014 version of SQL Server introduces powerful new features, including:

In-memory OLTP (code-named Hakaton) for significant performance improvements with frequent table access
Improvements in handling big data using the Resource Governor

Numerous critical features of SQL Server 2014 are unfortunately only available to users of the Enterprise Edition. They include:

Database snapshots
Online re-indexing and parallel indexing operations
Database encryption (e.g., for e-commerce)
Auditing
Regulatory compliance
The vast majority of business intelligence capabilities
Support for AlwaysOn High Availability Groups with up to eight (instead of four) readable secondary servers.

The Standard Edition of SQL Server 2014 therefore provides nothing in the line of non-deprecated high-availability features; instead, it offers the fairly useless, because deprecated, database mirroring. Even more regrettable is the fact that the Standard Edition of SQL Server 2014 reaches the end of the line at just 64GB of RAM, which makes practical use of native in-memory OLTP questionable in this version of the server.

AlwaysOn Failover Cluster

AlwaysOn Failover Cluster instances provide failover from a node that is currently not available to another node using Windows Server Failover Clustering (WSFC). Local high availability is provided in this case by redundant server instances; these failover cluster instances (FCIs) share SAN storage.

An FCI is a single instance of SQL Server distributed over several WSFC nodes (possibly across multiple subnets). On the network, this installation looks like a single computer. Microsoft's SQL Server Management Studio connects to the cluster as to an ordinary host.

The BI and Standard editions of SQL Server 2012 and 2014 each support exactly two nodes. The Enterprise version pushes this to the operating system's limits. If the active node fails, failover occurs fully automatically: The second node takes over the tasks of the first node. For the end users, nothing has changed; they can continue with their work. However, incomplete transactions are mandatorily discarded. Completed transactions are kept.

AlwaysOn Failover Cluster instances protect the environment against the failure of a server in the cluster but not against the failure of the SAN storage. The data is therefore not automatically saved redundantly; on the contrary, all nodes simply access the same shared SAN storage. A hardware defect here can thus easily lead to the loss of all data. To mitigate the risk of data loss, SAN replication can be used. In the case of a primary SAN storage failure, you also have the option of manually sharing the SAN replica in the cluster. Unfortunately, this solution entails considerable additional costs and requires the involvement of a SAN administrator.

AlwaysOn HA in SQL Server

AlwaysOn High Availability Groups replace the functionality of database mirroring and log forwarding through improvements such as log synchronization. AlwaysOn High Availability Groups rely on WSFC to ensure redundancy at the database level and allow the use of the secondary server for read requests. Unlike the case of database replication, indices and schemas must be identical on all instances.

An availability group is a failover environment for user databases (known as availability databases). Failover between synchronous replicas in an AlwaysOn High Availability Group is fully automatic. Asynchronous replicas are only suitable for manual failover, but they can be located in different data centers and run on completely different hardware.

Microsoft introduced AlwaysOn High Availability Groups in SQL Server 2012 and has expanded this technology in version 2014. SQL Server 2014 now supports a total of eight instead of four previously supported secondary replicas. In multisite environments, these initially remain readable, even if a connection to the primary server cannot be established. Admins who set up high-availability groups in various, geographically distributed data centers will benefit most from this approach. Asynchronous replicas can relieve the primary instances by handling some of the read requests. SQL Server 2014 introduces the ability to manage the load on the high-availability group with a load balancer. (See the "Test Drive" box for information on trying out SQL Server 2014.)

Test Drive

If you want to take SQL Server 2014 for a test drive, you can install the trial version [1] on an ordinary AWS EC2 instance of Windows Server. You can even launch a Spot Instance to do so; this setup reduces the cost compared with an on-demand instance, often by a factor of 10.

When moving to the cloud, users will tend to take their existing SQL Server licenses [2] with them and continue to use these licenses if they have an active subscription to Software Assurance [3]. You can also try out SQL Server 2014 directly on Azure. To do so, when you access the trial version, select "Preview SQL Server 2014 CTP1 on Azure."

SQL Server 2014 integrates seamlessly with Windows Azure. Users of SQL Server in the Microsoft cloud can use SQL Server 2014 as a synchronous secondary replica for the Azure services. The reverse scenario is also possible: If you use SQL Server internally, you can asynchronously replicate its databases to Windows Azure for manual failover in case of emergency.

Unfortunately, AlwaysOn High Availability Groups require the Enterprise Edition in both SQL Server 2012 and 2014. Database mirroring, which you can expect to be removed from the next version of SQL Server 2014 was even available in the Standard Edition. Thus, it is unfortunate that Microsoft has not given the Standard Edition of SQL Server 2012 and 2014 at least the lowest level of AlwaysOn High Availability Groups. The absence of high-availability technologies in the Standard Edition therefore practically means a gradual price increase. Many users of SQL Server thus see themselves forced to consider alternative solutions.

« Previous 1 2 3 4 Next »