I wanted to write this article to make it easier for you to choose between SQL Server’s technologies used for HA (High Availability) and DR (Disaster Recovery).
Briefly, we will compare the technologies listed below.
- Always On Failover Cluster
- Database Mirroring
- Always ON Availability Group
- Replication
- Log Shipping
Always On Failover Cluster
- You can use it for HA.
- The servers to be included in the Failover Cluster must be in the same windows cluster.
- Supports automatic failover. The failover process can occur automatically if the SQL Service stops.
- There is no disk redundancy. Because, the database files use a shared disk that can be seen by both servers.
- It can be done in Instance level (You cannot failover a database to the other server. All databases in that instance will failover. So it can not be practical for a DBA)
- You can not read or write from the secondary databases.
- It can be used with Always ON Availability Group, Replication, and Log Shipping.
Database Mirroring
- You can use it as HA or DR solution. If you choose synchronous for nodes in same data center it can be HA Solution and if you choose asynchronous for nodes in different data centers it can be DR Solution.
- Its database based. If you have too many databases, you need to do this for all the databases on the instance one by one. But it is more flexible because failover can be done on a database basis.
- There is disk redundancy. Each node uses its own disks.
- There is automatic failover if you set Witness Server and set it synchronously.
- You can not read or write from the Secondary database. But you can read from the snapshot of the secondary database(you may want to read my article “What is Database Snapshot On SQL Server“)
- It will not be available in later versions of Microsoft SQL Server. Always On Availability Group can be used instead of Mirroring.
- Supports automatic page repair. A nice feature for DBAs. Because this feature prevents the database from falling into suspect mode.
Always ON Availability Group
- It can be used as HA or DR solution like Database Mirroring.
- You can create an availability group by making a group of multiple databases. It is both more flexible and easier to manage than Database Mirroring. For example, an application has 7 databases. You can include these 7 databases into a single availability group. You can manage as you like. Availability Group is an improved version of Database Mirroring.
- There is disk redundancy. Each node uses its own disk.
- There is automatic failover if you set it synchronously. Does not need Witness server.
- You can read from Secondary databases.
- Supports automatic page repair. A nice feature for DBAs. Because this feature prevents the database from falling into suspect mode.
- With SQL Server 2016, we can now create the Availability Group among different windows clusters.
Replication
- Replication has many technologies and each offers different features. Therefore it is a little difficult to briefly describe Replication. For details, you should read the articles at the end of the article. Usually not used for HA. I’ve always used it for reporting purposes.
Log Shipping
- Its a DR solution.
- Its databases based.
- You can read from secondary database.
- There is no automatic failover.
My favorites are Always On Availability Group and Always On Failover Cluster for HA.
My reasons to choose Always On Availability Group for HA:
- It is very easy to manage Always On Availability Group.
- You can include more than one database in an Availability Group.
- You can use it for both HA and DR.
- There is disk redundancy. You can read from the secondary database.
- You can failover your availability group to the other server without anyone feeling the interruption.
- Because you can group databases, you can get maximum benefit from their resources on 2 servers by running some of your availability groups from the first server and some from the second server.
My reasons to not choose Always On Availability Group for HA:
- If you do not have enough infrastructure, sometimes there is a synchronization delay for databases with heavy transaction.
- When a problem occurs, you cannot failover because it stops transferring with secondary databases.
- So I prefer to use Always On Failover Cluster for databases with heavy transaction.
The details of all these technologies can be found in the following articles. You can also use the search section of our site to reach what you are looking for.
“Database Mirroring On SQL Server“,
“SQL Server Always ON Availability Group“,
“SQL Server Always On Failover Cluster“,
“SQL Server Replication Types“,
“Transactional Replication On SQL Server“,
“Peer to Peer Transactional Replication On SQL Server“,
“Snapshot Replication On SQL Server“,
“Merge Replication On SQL Server“,
