SQL Server High Availability – All You Need To Know

SQL Server high availability solutions

You probably heard Service Level Agreement (SLA), Recovery Time Objective (RTO), and Recovery Point Objective (RPO) terminology.

Another common phrase is “High Availability,” which means that the SQL Server databases will remain accessible, with minimal downtime, following any server crash or failure.

It is closely related to high availability requirements and Microsoft SQL Server has several possible solutions for that.

That depends on the budget, SQL Server version, requirements for automation, scope, IT support level, and so on.

Possible solutions to make SQL Server more robust and reliable are:

• Replication
• Log shipping
• Mirroring
• Windows Clustering
• Always On availability groups

Replication

This technology can be divided into the following types:

• Transactional.
  It depends on a synchronization process with the SQL Server transaction logs.
  This type of replication starts with a snapshot of the primary database that is applied to the subscriber. After that, all transactions occurring on the publisher are replicated to the subscriber.

This option provides the lowest latency, compared to other types of replication.

• Snapshot.
  When data is being changed a little, it is best to use this type of replication.
  A snapshot of the entire database is taken and copied to the subscriber database. It is also used as an initial data set in some circumstances to start subsequent replication processes.
• Merge.
  If the subscriber and publisher are not connected all the time (like laptop and server), this type of mirroring suits the best. All the changes are being tracked, and when the publisher and subscriber come online again – changes are synchronized.
• Peer to Peer.
  This type of replication has the advantage of involving all nodes for transaction execution. In some cases, it may help to scale out data on several instances for a better balance between them.

To sum up, this technology depending on its type, can help to scale out data, all replicated databases can be accessed, and replication can run on both sides.

However, replication may get out of synchronization, talking about high availability it requires manual failover, and snapshot replication can be time-consuming in some cases.

Log shipping

This Microsoft solution is dependent on transaction logs.

Needless to say, to use transaction logs database must be in the full recovery model.

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

• Generate transaction log backup on primary DB
• Transfer log backup to secondary database/databases
• Restore that log backup on the secondary database(s)

Before this works, the primary database backup has to be restored on the secondary node with NORECOVERY statement.

SQL Server Agent jobs can be scheduled at an interval, depending on requirements. Probably the most commonly used schedule is 15 minutes.

The main advantages of this technology are:

• The support of several secondary servers
• Capability to query secondary instances, provided there are no ongoing queries during log backup restoration

Downsides:

• The downside of high availability is that it requires manual failover and the secondary database must be brought up to date by restoring all necessary missing log backups.

Mirroring

This method works as a high availability solution.

To use this method, a minimum of two servers are needed (primary – called principal, and secondary – called mirror).

The third server (witness) is recommended, however, it is optional.

The witness server monitors the connection between the principal and mirror and can change the role or perform automatic failover. When a server acts as secondary – it has a database restored with the NORECOVERY option.

Mirroring operational modes

During the mirroring process, new transactions are being copied to that database.

• If they are copied to secondary first and then committed on principal – mirroring runs in high safety mode (also called synchronous).
• If new transactions are committed to a principal database without waiting for secondary – mirroring runs in high-performance mode (also called asynchronous).

Although this solution can have automatic failover, it has downsides:

• The mirror database on the second server cannot be queried until it becomes primary.
• One mirroring solution is limited to two servers only.

Windows Clustering

There is another method for high availability – Windows Clustering.

To use this method, shared storage and two (or more) servers are needed.

Windows Clustering works at the server level, not the database level.

If one physical machine fails, another physical one may take over the workload where the previous server left off. This is common in environments, where 99% or higher uptime is required.

Windows Clustering is useful in case of hardware failure, it is also useful during maintenance windows. The physical server may be failover to another one, then it is the software/hardware that may be upgraded, and then it can be brought back, without any higher downtime on the databases.

However, this high availability solution also has its drawbacks:

• Setting it up is complex and necessitates additional redundant hardware, which might only see occasional use(only then the particular node is active).
• Additionally, the data itself is not protected since the same storage is used; redundancy is only applied at the server level.

Always On availability groups

Microsoft SQL Server feature, first introduced in SQL Server 2012 version, is called Always On Availability Groups.

It is based on the previously mentioned Windows Clustering and mirroring hybrid solution, however, it has more advantages.

This setup is made from one primary server, called primary replica, and that host’s database copy for read/write operations. Another part is called the secondary server which serves a read-only database copy.

There might be up to 8 secondary servers, which can also be used, for example, for reporting purposes.

Compared to Windows Clustering, availability groups do not need shared storage – which makes the system more reliable, because each server uses its own storage drives.

Availability groups run not on the SQL Server instance level – they do it on individual databases. So some databases might be included in Always On, and others – might not.

Even if it looks like Always On is the best solution, it comes with a price – starting from SQL Server 2012, this feature comes with Enterprise edition only. Later editions have offers to use it on the Standard edition as well.

Conclusion

To sum up, there are various possibilities for making SQL Server more reliable and providing highly available database solutions, all depending on specific requirements. Some solutions might fit one situation, while others may suit different ones.

In short, every technology comes with its benefits and drawbacks, with the most significant factors being cost, complexity of setup, and the ability to automatically failover in the event of a server crash.