In this series of blog posts, you will learn in detail what goes in to SQL Server migration process. You can access the first part of "Detailed specification of the SQL Server migration planning" here.
In my previous blogpost I wrote about the Compatibility Check of the system to be migrated. In this post the topic is High Availability planning.
SQL Server High Availability planning
The major key points that affect the planning of High Availability are:
- Does the hardware already exist and is it fixed or is there flexibility in the hardware configuration?
- How many physical nodes are there?
- What is the capacity of the nodes?
- What is the storage system?
- Are there multiple datacenters involved and is there a data replication mechanism available between the data centers?
Based on the answers above, different solutions and combinations of the SQL Server HA techniques can be implemented.
SQL Server High Availability Solutions
In this blog post, Replication, Log Shipping and Mirroring will be skipped, because they are either not real HA mechanisms, or are deprecated features (mirroring).
SQL Server HA Failover Cluster
The traditional HA solution has, among others, the following features and requirements:
- Protection at instance level
- No load balancing
- Up to 64 nodes (Enterprise Edition)
- Needs a Windows Server Failover Cluster
- Needs a fault-tolerant shared storage system
- SQL Server is installed in cluster mode and all SQL Server instance databases are installed on the shared storage (tempdb can be on local drives)
- Binaries are installed on all Windows Cluster Nodes
- Automatic failover to any node and flexible failover policy
- Can use Clustered Shared Volumes (SQL Server 2014 and after)
- Minimum manual administration after installation
- Expensive hardware (storage)
SQL Server HA Availability Groups
The newer HA solution for SQL Server has at least the following features and requirements:
- Protection at database (group of them) level
- Load balancing for Read/Backup operations
- Up to 9 nodes (Enterprise Edition)
- Needs Windows Server Failover Cluster, but no shared storage
- SQL Server is a normal installation (not a cluster installation)
- Data is replicated to every Availability node either synchronously or asynchronously
- System Databases and objects (jobs, logins etc.) are not replicated
- Needs more manual administration after installation
- Can be implemented on cheap hardware
Virtualization
Virtualization is beyond the scope of this blog post. But of course, either VMware or Hyper-V Virtualization can be used in different ways with SQL Server HA Solutions.
Conclusions
The selected HA solution depends on many factors, but I would say that good old Failover Cluster is still the first choice for mission critical systems – especially if the hardware, including a fault tolerant Storage system, is already present. It protects the whole instance, and a lot less manual administration is needed compared to HA Groups.
HA Groups are a good choice for less expensive hardware and systems that can utilize load balancing. But this comes with the cost of more manual administration.
The next part is about phasing the SQL Server migration project.
Pekka Korhonen
Lead Architect, MCM
DB Pro Oy
