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 is 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 high availability (HA) solution you’re looking for?
- Needs – What are the business requirements, and what is the impact of downtime?
- Hardware – What IT infrastructure do you have to support the desired solution?
- SQL Server version – The version of the SQL Server you’re running will determine which options are available.
- Level of automation – Does the solution require seamless failover?
- Skillset of your team – What expertise does your team possess to implement and support a solution?
At the time of writing, there are 5 main options available to you:
Read on to find out more about each option.
1. SQL Server 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.
It 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 replication.
This form of replication begins with a snapshot of the primary server at the subscriber’s end. Once the snapshot is established, it transmits all transactions that occur on the publisher to the subscriber. This method offers the lowest latency. - Merge replication.
Merge replication begins with a snapshot of the primary server at the subscriber’s end. Changes made by both the publisher and the subscriber are monitored offline. Synchronization between the client and the publisher occurs once both are 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.
Peer-to-peer replication helps to scale out requests. This is achieved because transactions are executed across all nodes involved in the replication to keep the data synchronized in near real-time.
2. SQL Server 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 main database from the master server and it is not available to end users.
When mirroring is enabled, all new transactions from the principal server will be copied to the mirror.
Using a witness server is also an option with an 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.
3. SQL Server log shipping
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 shipping process can be described as follows:
- The process initiates with the primary server creating a backup of the transaction log.
- This backup is then transferred to a secondary database backup share, utilizing the SQL Server Agent and scheduled tasks at predetermined intervals.
- Subsequently, the secondary server is tasked with restoring the transaction log. This restoration occurs at specific times, also managed by the SQL Server Agent and scheduled tasks, ensuring synchronization with the primary server’s operations.
Log shipping supports multiple secondary servers, which is beneficial. However, it is less commonly used for high availability (HA) purposes.
This is because it requires the manual application of unrestored log backups. The secondary database must be fully updated with these backups before failover can take place.
4. SQL Server clustering
Clustering involves at least two databases and is more of a high availability solution at the server level than an option at the database level.
Clustering enables a physical server to take over the responsibilities of another physical server that has failed.
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.
Forms of SQL Server clustering
- 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, which might be seen as expensive.
5. SQL Server 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.
Speak with a SQL Expert
In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide operational peace of mind
