SQL Server High Availability (HA)

If you need to introduce a high availability solution for one of your SQL Server databases, then there’s lots of confusion as to what it the right option for you. Given the number of options available it may make you unsure which implementation will meet the needs of your business. Review these tips to learn about the five high availability choices of the native SQL Server.

SQL Server High Availability Solutions

Finding the right high availability option can be difficult. Which option is right for you can depend on the following:

Budget – first and foremost can you afford the HA solution you are looking for?
Needs – what are the business requirements and what is the impact of the downtime?
Hardware – what IT infrastructure you have to support the desired solution?
SQL Server Version – Depending on which version of SQL you are running will determine what options are available.
Level of Automation – Does the solution require a seamless failover?
Skillset Of Your Team – What expertise are there within your team to implement and support a solution?

At the time of writing there are 5 main options available to you:

Log Shipping

Read on to find out more about each option.

SQL Replication

Replication requires a publisher and subscriber at a high level, where the publisher is the primary client and the subscriber is the target server. Copying and transferring information from one database to another is the main purpose of Replication. There are four forms of replication:  

  • Snapshot replication 
  • Transactional replication 
  • Merge replication 
  • Peer to Peer replication 

Snapshot replication: snapshot replication happens when a snapshot of the entire database is taken and the snapshot is copied to the recipient. This is best used for data with minimal changes and is used in certain cases as an initial set of data to facilitate subsequent processes of replication. Transactional: Transactional replication starts with a snapshot of the subscriber’s primary server. Once the snapshot is in place it will transmit to the user all transactions that happen on the publisher. The lowest latency is offered by this method. Merge: Merge replication starts with a snapshot of the subscriber’s primary server. Changes made by the publisher and user will be monitored offline. The client synchronizes with the publisher and vice versa once the publisher and subscriber are back online simultaneously. This choice could be better for employees with laptops leaving the office, and when they are back in the office, they need to sync their data. Peer to Peer: Replication of peer to peer will help to scale a request.  This is because transactions occur in order to keep the data in sync in near real time, they are executed on all the nodes involved in replication. 

SQL Database Mirroring

Database Mirroring involves a master server with the main database and a mirror server with the mirrored database. The mirror database is recovered from the principle, making the server unavailable to end users without any recovery. When mirroring is allowed, the mirror will be copied to all new transactions from the principal.  Using a witness server is also an option with automatic failover function when using high security. The witness client helps the server to act as a hot standby server. Failure to complete this option usually takes just seconds. If the main server were to go down the mirror server would become the principal automatically. 

SQL Database Mirroring

Log shipping involves one primary server, one (optional) display server, and may involve multiple secondary servers. The secondary database(s) will be recovered from the main database, making the server unavailable to end users without any recovery. The log shipment process begins with the primary server taking backup of a transaction log and transferring the transaction log to a secondary database backup share using the SQL Server Agent and work schedules at a set time period. The secondary server must restore the transaction log at a specified time period using the SQL Server Agent and work schedules. While it is good that log shipping supports multiple secondary servers, it is probably the least used for HA because, by manually applying unrestored log backups, the secondary repository needs to be fully modified before the failover can occur. 

SQL Clustering

Clustering involves at least two databases and is more of a high-accessibility solution at the server level than an option at the database level. Clustering would allow one physical server to assume other failed physical server’s responsibilities. This is critical in conditions needing uptime of nearly 100 percent. The other server will immediately pick up where the failed server has left off causing little or no downtime when the assets of a network fail. We’re going to discuss two forms of clustering:  

Active / Active and Active / Passive.

Active / Active: SQL Server runs on both servers while operating in Active / Active mode.  If one of the SQL Server fails then the other SQL Server fails, meaning that two instances run on one server that could potentially cause performance problems if not properly configured. Active / Passive: SQL Server runs on one server while operating in Active / Passive mode while the other server waits for failure. This is the most popular choice because it doesn’t impact performance; however, you’ll need a server that just sits there with nothing running on it that might be seen as expensive. 

SQL AlwaysON

AlwaysON was first seen in SQL Server 2012 and is an alternative to mirroring the server. AlwaysON uses groups called Availability Groups, which are groups containing selected repositories that fail when a failure occurs together.   

Picture of Mark Varnas

Mark Varnas

Hey I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable. I channel my SQL into our SQL Managed Services, SQL Consulting and our internal database products.

Leave a Reply

Your email address will not be published. Required fields are marked *