SQL Server High Availability: Everything You Need to Know

SQL Server High Availability: Everything You Need to Know

You’ve probably heard terms like Service Level Agreement (SLA), Recovery Time Objective (RTO), and Recovery Point Objective (RPO).

Another common term is Always On Availability Groups — meaning that, after any server crash or failure, SQL Server databases remain accessible with the least possible amount of downtime.

Always On Availability Groups are closely tied to these requirements, and Microsoft SQL Server offers several possible solutions for meeting them. Which one fits depends on budget, SQL Server edition, automation requirements, scope, IT support level, and more.

Here are the possible solutions for making SQL Server more robust and reliable:

• Replication
• Log Shipping
• Mirroring
• Windows Clustering
• Always On Availability Groups

Replication

This technology can be broken down into the following types:

• Transactional: This relies on a synchronization process using SQL Server transaction logs. This type of replication starts with a snapshot of the primary database applied to the target machine, after which every transaction that happens on the publisher is forwarded to that machine. This option provides the lowest latency compared to the other replication types.
• Snapshot: This type of replication is best used when the data changes only slightly. A snapshot is taken of the entire database and copied to the target database. In some cases, it’s also used as a starting data set to kick off subsequent replication processes.
• Merge: This type of replication is best suited for cases where the target and the current environment aren’t always connected (such as a laptop and a server). All changes are tracked, and once the current environment and the target come back online, the changes are synchronized.
• Peer to Peer: This replication type has the advantage of involving all nodes in transaction execution. In some cases, it can help to scale out data across several instances to achieve a better balance between them.

Summarizing by type, this technology can help scale out data, all replicated databases remain accessible, and replication can work in both directions.

That said, replication can fall out of sync, requires manual failover (not to mention Always On Availability Groups), and snapshot replication can be time-consuming in some cases.

Log Shipping

This Microsoft solution relies on transaction logs. Needless to say, the database needs to be in the Full recovery model to use transaction logs.

The main technique is to use SQL Agent jobs, which:

• Create a transaction log backup on the primary database.
• Transfer the log backup to the secondary databases.
• Restore that log backup on the secondary database.

Before this process can run, the primary database’s backup needs to be restored on the secondary node without a recovery notification. SQL Agent jobs can be scheduled at whatever interval fits the requirements — the most commonly used schedule is probably every 15 minutes.

Looking at the advantages of this technology, it’s worth noting that it can support several secondary servers. In addition, the secondary instances can be queried as long as a log-backup restore isn’t running at that moment.
The downside of Always On Availability Groups here is that they require manual failover, and the secondary database needs to be brought up to date by restoring every missing log backup required.

Mirroring

This method works as a solution alongside Always On Availability Groups. Using this method requires at least two servers (a Primary, called the principal, and a Secondary, called the mirror). A third server is recommended (the witness), but optional. The witness server monitors the connection between the principal and the mirror, and can switch roles or perform automatic failover.

When a server acts as the secondary, it holds a database restore with no recovery option. During mirroring, new transactions are copied to this database. If they’re copied to the secondary first and then committed on the principal afterward, this runs in high-safety mode (also called synchronous). New transactions made to the principal database without waiting for the mirror to catch up run in high-performance mode (also called asynchronous).

While this solution can offer automatic failover, it also has downsides. The mirror database on the secondary server can’t be queried until it becomes the principal. In addition, a mirroring solution is limited to just two servers.

Windows Clustering

There’s yet another method related to Always On Availability Groups — Windows clustering. Using this method requires shared storage and two (or more) servers. In fact, Windows clustering works at the server level, not the database level. If a physical machine fails, another physical machine can take over the workload the previous server was carrying. This is common in environments that require 99% uptime or higher.

Windows clustering is useful in the event of hardware failure, and it’s also handy during maintenance windows. The physical server can fail over its workload to another server, then have its software/hardware upgraded, and later be brought back without further interruption to the databases.

That said, this type of Always On Availability Groups solution has a downside too. It’s complex to set up, and it requires extra redundant hardware that’s rarely used (the related node is only active when needed). In addition, the data itself isn’t protected, since the same storage is used — the redundancy is only on the server side.

Always On Availability Groups

First introduced in SQL Server 2012, this Microsoft SQL Server feature is called Always On Availability Groups. It’s based on the previously mentioned hybrid of Windows clustering and mirroring, but it offers more advantages.

This setup runs from a primary server, called the primary replica, which hosts the database copy for read and write operations. Another part is called the secondary server, which offers a read-only copy of the database. There can be up to 8 secondary servers, for example, which can also be used for reporting purposes.

Compared to Windows clustering, Always On Availability Groups doesn’t need shared storage, which makes the system more reliable since each server uses its own storage drives.

Always On Availability Groups don’t work at the SQL instance level — they work at the individual database level. This means some databases can be included in an Always On setup while others aren’t.

Even though Always On looks like the best solution, it comes at a cost — starting with SQL Server 2012, this feature is only available with the Enterprise edition. In later versions, there have been offers to make it available in the Standard edition as well.

Conclusion

To summarize, all of the various options for making SQL Server more reliable and providing a highly available database solution depend on specific requirements. Some solutions may suit one situation, while others may fit a different SQL Server setup. In short, each technology has its advantages and its downsides, and the most important ones are cost, setup complexity, and the ability to fail over automatically in the event of a server crash.