SQL Server 2012 AlwaysOn – What is it?
Running SAP Applications on the Microsoft Platform
SQL Server 2012 AlwaysOn – What is it?
This is the first one of several articles we will provide about the new SQL Server 2012 AlwaysOn functionality. In this part we will go over the terms, capabilities and differences to SQL Server Database Mirroring (DBM). In subsequent other parts we will talk more about the details, configuration possibilities and how one could get from e.g. Database Mirroring to AlwaysOn.
The structure of the series we were thinking about will look like:
- General introduction (this part)
- Infrastructure requirements to deploy most significant new parts of AlwaysOn with reference architectures for SAP
- Deploying the new Functionality
- Operating and Administrating AlwaysOn
- Monitoring AlwaysOn
- Troubleshooting AlwaysOn
Hence let’s start with the first part of the General introduction
What is AlwaysOn?
AlwaysOn is a collection of High-Availability and Disaster Recovery functionality with the goal to minimize Recovery Point Objective (RPO) and Recovery Time Objective (RTO) further below the already good times we could achieve with Database Mirroring. AlwaysOn is offering methods and functionalities which either use shared storage infrastructures in combination with Windows Server Failover Cluster (WSFC) or non-shared storage infrastructure to build out highly available SQL Server configurations. We basically look at important extensions to the SQL Server Failover Cluster functionality in AlwaysOn. On the non-shared storage side a new functionality stack which exceeds capabilities of Database Mirroring or Log-Shipping got added. Log-Shipping and Database Mirroring in its origins will still be part of SQL Server 2012 HA/DR functionality. However Database Mirroring functionality can be completely replaced with new functionality in AlwaysOn. Also a lot of configurations which required Log-Shipping so far can be covered with the newly added functionality. Since Log-Shipping and Database Mirroring didn’t get changed in their basics, an upgrade to SQL Server 2012 doesn’t force an immediate upgrade to different HA/DR Technologies. Changes to the traditional WSFC usage by SQL Server are also compatible and in case of an upgrade to SQL Server 2012 don’t require any changes to keep on working.
What functionality got improved with AlwaysOn?
Over the years having Windows Clustering, Log-Shipping and Database Mirroring used by customers, a lot of feedback accumulated. Especially in the SAP space, Windows Clustering emerged as the minimal default for
HA. As well as Log-Shipping became the default with the ever rising numbers of Disaster Recovery configurations. Feedback from this class of customers was that storage in their local High-Availability configuration using WSFC still was a single point of failure. Also the fact that SQL Server could not be clustered over different sub-nets was re-occurring as feedback. Another point of complaint was that the failover would be executed on the whole instance.
Log-shipping feedback was that the RPO could develop too large and hence potential of loss of committed transactions too risky.
The single point of failure problem with Windows Clustering got addressed with Database Mirroring introduction with SQL Server 2005. However customer feedback on DBM looked like:
- Request for more than one mirror
- Ability to read real-time from the mirrors
- Ability to execute backups from the mirror
- Include multiple user databases within one mirroring relationship
- Mixture of synchronous and asynchronous data replication between primary and multiple mirrors
- Extend scalability of the data replication
- Accelerate failover
- Allows multi-subnet failover and reconnects
All these points got addressed with AlwaysOn as delivered in SQL Server 2012. AlwaysOn shows up with the following capabilities:
- Besides the primary one can have 4 mirrors (later on we see that we changed terminology a bit)
- Two of the mirrors can work with synchronous data replication whereas the additional 2 need to be asynchronous
- One can read from a synchronous or asynchronous mirror real time
- One can include multiple user databases in a mirroring relationship (no system databases allowed)
- Larger design changes were done to increase scalability as well as improve failover time
- Multi-Subnet support was added for the SQL Server Failover Cluster functionality
- Unlike a complete SQL Server instance leveraging Windows Server Failover Cluster (WSFC) where the failover unit would be the whole instance or Database Mirroring where the failover unit is a database only, the failover units can be multiple databases.
- New Health Detection for SQL Server which is used for shared disk as well as non-shared disk deployments
Before we go into details of all these new functionalities, we need to go through terms and the principle concepts behind AlwaysOn
Let’s go through some of the terms:Source: blogs.msdn.com