SQL Server 2012 AlwaysOn Availability Groups – Part 1

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

In the last weeks and months I have prepared tons of SQL Server 2012 content for my own SQL Server 2012 Deep Dive Days workshop that I’m currently running across Europe. Because this content shows in a very good way how you can use all the various new features of SQL Server 2012, I have decided to share over the year some of that content through my weblog. Today I want to start with SQL Server 2012 AlwaysOn Availability Groups, because that’s one of the hottest features in SQL Server 2012!

I want to take you from a zero knowledge level and show you step by step how you can configure, deploy, and manage a SQL Server 2012 AlwaysOn High Availability solution. In today’s first part I want to talk about the basic architecture of AlwaysOn and how you can prepare SQL Server for running an AlwaysOn solution.

Overview

AlwaysOn Availability Groups is the big new feature in the High Availability stack of SQL Server 2012. Simply said it is the successor of Database Mirroring that was introduced with SQL Server 2005 SP1. Database Mirroring is a great technology for achieving HA (High Availability) and DR (Disaster Recovery), but has some weaknesses and limitations:

  • You can only mirror 1 database through a Database Mirroring session. When you have an application that consists of several database (just think of SharePoint Server), then you need to have several distinct Mirroring sessions.
  • The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
  • There is only 1 Mirroring partner possible. You can’t mirror to different partners. The Mirroring partner can be run with Synchronous Commit for achieving High Availability or with Asynchronous Commit for achieving Disaster Recovery. If you are using Synchronous Commit you can get an Automatic Failover if you deploy a Witness instance. This witness instance is used for acquiring a quorum and avoiding a so-called Split-Brain scenario.
  • The Mirroring database is always replaying Transaction Log records, which means that you are not able to access the database for read-only access (the undo part of Recovery has not yet run). The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis, but again – you have to do this at your own.

Beginning with SQL Server 2012 Microsoft provides us now AlwaysOn Availability Groups which are the successor of Database Mirroring.

Side Note: Database Mirroring is still available in its original feature set, but it is marked as a Deprecated Feature, which means, that it will be removed in a future release of SQL Server.

AlwaysOn Availability Groups offers you the following advantages over traditional Database Mirroring:

  • Multi-Database Failovers
  • Multiple Secondaries
  • Active Secondaries
  • Integrated HA Management

The following figure gives you a basic overview about AlwaysOn Availability Groups.

As you can see from the previous figure, an AlwaysOn Availability Group is created between several standalone SQL Server instances. At least you need 2 different SQL Server instances. All instances that participating in an AlwaysOn Availability Group don’t need any Shared Storage – each SQL Server instance has its own local storage. It’s the same concept as with Database Mirroring. The only requirement is the fact that each participating SQL Server instance must be in the same Windows Domain, because you must create a Windows Server Failover Cluster (WSFC) that contains all participating SQL Server Instances. SQL Server uses underneath several WSFC features like the Quorum Model. Database Mirroring uses its own Quorum Model by using a dedicated Witness instance.

Side Note: In my opinion this one of the biggest disadvantages of AlwaysOn Availability Groups. I have a few customers which wanted to migrate from Database Mirroring to Availability Groups, but they can’t go that direction at this time, because their SQL Server instances are not in the same domain. Some even have deployed SQL Server instances in separate workgroups… But that’s the way how AlwaysOn Availability Groups are working, and that’s the price that you have to pay for that new feature.

An Availability Group itself can contain several databases, which can failover in a whole set – that’s a huge difference and improvement compared to Database Mirroring. Each participating SQL Server instance hosts a so-called Replica. There is always one Primary Replica and up to four Secondary Replicas. Up to 2 of these Secondary Replicas can use Synchronous Commit, and the other 2 Secondary Replicas can use Asynchronous Commit. When you run 2 Replicas with Synchronous Commit you can also configure an Automatic Failover between them. They are forming a so-called “Automatic Failover Pair”.

Another big advantage of the Secondaries is that they are readable. You don’t need a Database Snapshot anymore, as you would have done it with Database Mirroring. But you must be aware that there is an overhead when you are using Active Secondaries – but we will look into that topic in more detail at a later stage of this weblog series. You can also use Active Secondaries for doing backup jobs, which will be also a very nice way to move backup workload away from your Primary Replica.

As with Database Mirroring, Transaction Log records are transferred from one Replica to the others. To minimize network latency, SQL Server 2012 uses Build in Log Compression to transfer Transaction Log records as fast as possible.

Preparing your SQL Server for AlwaysOn

If you want to deploy an Availability Group solution you have to prepare your SQL Server instances in the first step. For this weblog posting I’m assuming that you have setup 5 different SQL Server installations across 5 physical servers/Virtual Machines. As an Operating System I’m assuming Windows Server 2008 R2 SP1 x64. My current Hyper-V based deployment looks like the following:

  • Node1
    • DNS Name: ag-node1.sqlpassion.com
    • IP Address: 192.168.1.211
    • SQL Service Account: ag_node1_sqlsvc@sqlpassion.com
  • Node 2
    • DNS Name: ag-node2.sqlpassion.com
    • IP Address: 192.168.1.212
    • SQL Service Account: ag_node2_sqlsvc@sqlpassion.com
  • Node 3
    • DNS Name: ag-node3.sqlpassion.com
    • IP Address: 192.168.1.213
    • SQL Service Account: ag_node3_sqlsvc@sqlpassion.com
  • Node 4
    • DNS Name: ag-node4.sqlpassion.com
    • IP Address: 192.168.1.214
    • SQL Service Account: ag_node4_sqlsvc@sqlpassion.com
  • Node 5
    • DNS Name: ag-node5.sqlpassion.com
    • IP Address: 192.168.1.215
    • SQL Service Account: ag_node5_sqlsvc@sqlpassion.com

Each VM node is also part of my sqlpassion.com Windows domain, and I have performed a default SQL Server 2012 installation with a default instance of SQL Server 2012. Furthermore each SQL Server default instance uses a Windows Domain account for the SQL Server Service Account. You must also make sure during the installation that you place your MDF/LDF files on the same physical location on each VM node, otherwise you will run into troubles with the wizards provided by AlwaysOn Availability Groups.

The first step that you have to perform is the creation of a new Windows Server Failover Cluster (WSFC). In the first step this sounds like a huge step, but it is very easy to do because you don’t need any shared storage. One prerequisite of WSFC is the fact that you have to install the Failover Cluster Feature on each node. You can perform this task through the Server Manager of Windows Server 2008 R2.

After you have installed the Failover Clustering on EACH node, you have to log into one of these nodes through a Windows Domain user account. Otherwise you are not able to create the Windows Server Failover Cluster itself. To create the cluster itself you can use the Failover Cluster Manager, which can be found in the Administrative Tools of your Control Panel. On the right hand side you can see all available Actions, and here you choose the option “Create a Cluster…“. In the first step the wizard asks you which nodes should be part of the new WSFC Cluster. In my case I have selected all 5 VM nodes:

On the next wizard page you have to choose to run the Cluster Configuration Validation Tests. If you want to get support from Microsoft, you have to run them. For AlwaysOn Availability Groups you don’t have to include the tests regarding shared storage, because you are not using any form of shared storage for an Availability Group. After you have run your Cluster Configuration Validation Tests, you have to give your WSFC Cluster a name and an IP address:

With all these information in the hand, the wizard will finally create your WSFC cluster. After the successful creation you can see your new WSFC cluster in the Failover Cluster Manager:

As you can see from the previous figure, the Cluster is currently not hosting any Service or an Application yet. We just have created the skeleton so that an AlwaysOn Availability Group can be created and deployed. In the final step you have now to enable AlwaysOn Availability Groups on each SQL Server instance. This can be done through the SQL Server Configuration Manager and the tab AlwaysOn High Availability. You have to enable the checkbox “Enable AlwaysOn Availability Groups“. This option is only available when the computer where the SQL Server instance is running on, is part of a WSFC cluster:

After you have enabled that option, you must restart your SQL Server instance. Please make sure to do this configuration change on EACH node that is part of your WSFC cluster.

By now we have created the whole infrastructure that is necessary to run a SQL Server 2012 AlwaysOn Availability Group solution. In the next weblog posting you will then see, how you can deploy your first Availability Group with SQL Server 2012.

If you are interested in SQL Server 2012, I suggest looking on my “SQL Server 2012 Deep Dive Days Workshop”, which I run from May 28 – 30 in London/United Kingdom (see http://www.SQLpassion.at/academy, Early Bird price ends on March 31). This workshop was already running in Vienna/Austria and Munich/Germany with a huge success.

Stay tuned J

-Klaus

Do you like this post? Why not share it with the SQL Server community?

Deprecated Features in the SQL Server 2012 Database Engine

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

As you might know, Microsoft has released on March 7 the RTM version of SQL Server 2012. If you are an MSDN or TechNet Subscriber, you can already download it and play with it. As with every SQL Server release, SQL Server has also marked several features as deprecated, which means they are removed in a future version of SQL Server.
 

So if you are starting a new deployment or an application based on SQL Server 2012, you should check the following link which describes all the deprecated features and their replacement: http://msdn.microsoft.com/en-us/library/ms143729%28v=sql.110%29.aspx. In this blog posting I want to mention 2 deprecated features, because there are some big changes in SQL Server 2012.
 

Database Mirroring:

With the release of SQL Server 2012 and AlwaysOn Availability Groups, Database Mirroring is deprecated. Database Mirroring was introduced with SQL Server 2005 SP1 and allowed you to mirror a database synchronous and asynchronous, and optionally you had also an automatic failover if you used a witness instance (in combination with synchronous mirroring).
 

AlwaysOn Availability Groups is an enhanced version of Database Mirroring where you can mirror a group of databases, and where your secondary databases are readable. AlwaysOn also provides you synchronous mirroring for HA, and asynchronous mirroring for DR. One requirement of AlwaysOn Availability Groups is that you need to have a Windows Server Failover Cluster (without any Shared Storage!). The Windows Server Failover Clustering functionality is used to provide several clustering infrastructure features like Quorum settings, Intra Node Health Checking, etc.
 

I already talked with some of my customers, and for a few of them this is currently a big no-go, because they are currently using Database Mirroring where each partner database is not in an AD domain. So they can’t just migrate to AlwaysOn, because they are not able to create a Windows Server Failover Cluster. But that is just the way how it works now. Another drawback is that AlwaysOn Availability Groups is currently only an Enterprise Edition feature of SQL Server. Parts of Database Mirroring are also available in the Standard Edition. Microsoft suggests to use Log Shipping of your SQL Server Edition doesn’t support AlwaysOn Availability Groups.
 

SQL Server Profiler for Trace Capture and Trace Replay

Microsoft introduced with SQL Server 2008 Extended Events. With SQL Server 2012 Microsoft has done a lot of improvements in the area of Extended Events, for example you are now getting a very cool UI where you can define your Event Sessions in a wizard based way. On the long term Extended Events is the replacement for SQL Trace and SQL Server Profiler. Therefore those features are marked as deprecated. If you are using SQL Trace/SQL Profiler for Trace Capture and Trace Replay, SQL Server 2012 provides you a new feature called Distributed Replay, which is a high scalable distributed replaying technology.
 

So, if you have existing applications based on SQL Server, or you are starting a new deployment on SQL Server 2012, please review the deprecated features so that you have fewer problems in the futures.
 

If you are interested in SQL Server 2012, I suggest looking on my “SQL Server 2012 Deep Dive Days Workshop”, which I run from May 28 – 30 in London/United Kingdom (see http://www.SQLpassion.at/events.html, Early Bird price ends on March 31). This workshop was running this week in Vienna/Austria with a huge success and next week I’m presenting this workshop in Munich/Germany.
 

Thanks for reading!
 

-Klaus

Do you like this post? Why not share it with the SQL Server community?

New upcoming events across Europe

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

I’m very happy today to announce 4 new upcoming SQL Server workshops across Europe within the next months:
 

SQL Server Configuration Day

  • Date/Location
    • April 26 in Zurich/Switzerland
    • May 16 in Vienna/Austria
  • Agenda
    • Storage Configuration
      • IO Subsystem Tests
      • Disk Partition Alignment
      • RAID Configuration
      • Data-/Log-File Configuration
      • TempDb Configuration
    • CPU/Memory Configuration
      • Resource Governor
      • CPU Affinity Settings
      • Parallelism
      • NUMA Systems
      • Buffer Pool Management
      • Query Memory Configuration
    • Baselining & Monitoring
      • Performance Monitoring
      • Wait Statistics
      • PAL Tools
      • Baselining
    • Virtualization
      • Microsoft Hyper-V
      • VMware ESXi
      • Dynamic Memory
      • Memory Ballooning
      • CPU Co-Scheduling

You can find further information and registration details about the SQL Configuration Day at http://www.SQLpassion.at/events.html.

SQL Server 2012 Deep Dive Days

  • Date/Location
    • May 28 – 30 in London/United Kingdom
  • Agenda
    • SQL Server 2012 Development
      • SQL Server Data Tools
      • T-SQL Enhancements
      • Windowing Functions
      • File Table
      • Fulltext Indexes
      • Semantic Search
      • Contained Databases
      • SQL Server Integration Services
    • SQL Server 2012 Administration
      • Extended Events
      • Columnstore Indexes
      • LOB Online Index Rebuilds
      • Adding Online Non-NULL Values
      • Database Recovery Advisor
      • Distributed Replay
      • Memory Management
      • Troubleshooting Enhancements
      • Windows Server Core Support
    • SQL Server 2012 High Availability
      • Failover Clustering Enhancements
      • Local TempDb
      • Health Check Policies
      • Failover Policy Levels
      • Indirect Checkpoints
      • Availability Groups
      • Availability Group Listeners
      • Monitoring & Troubleshooting
      • Multiple Secondaries
      • Automatic Failovers
      • Read-Only Connections
      • Backups

You can find further information and registration details about the SQL Server 2012 Deep Dive Days in London at http://www.SQLpassion.at/events.html.
 

SQL Server Performance Tuning & Troubleshooting Workshop

  • Date/Location
    • June 13 – 15 in Munich/Germany
  • Agenda
    • Performance Tuning with Indexing
      • Database Internals
        • Structures
        • Fixed Data Types
        • Variable Length Data Types
        • Data Modification Internals
      • Execution Plans
        • Understanding Execution Plans
        • Plan Generation & Caching
        • Plan Reuse & Recompilations
        • Physical Join Operators
        • Plan Cache Pollution
      • Indexing
        • Table Scan/Index Scan/Index Seek
        • Clustered/Non-Clustered Index
        • Bookmark Lookups
        • Index Intersection
        • Filtered Indexes
        • Indexed Views
        • Page Fragmentation/Page Splits
        • Fill Factor
        • Searchable Arguments
      • Index Maintenance
        • Statistics
        • Overview
        • Working with Statistics
        • Statistics Maintenance
    • Troubleshooting Looking & Blocking
      • Concurrency
        • Pessimistic
        • Optimistic
        • Isolation Levels
      • Troubleshooting Locking
        • Viewing Locks
        • Lock Granularity
        • Lock Hierarchy
        • Lock Escalation
        • Lock Compatibility
        • Lock Manager Internals
        • Dynamic Management Views
      • Dead Locking
        • Detection
        • Avoidance
        • Troubleshooting
      • Latches
        • Latch Architecture
        • Latch Types
        • Latch Modes
    • Performance Monitoring & Troubleshooting
      • Performance Monitoring Methodology
      • Wait Stats
      • SQL Server Profiler
      • Perfmon
      • Dynamic Management Views/Functions
      • Memory Management
      • TempDb
      • PAL/RML Utilities
      • SQLDiag/SQLNexus

You can find further information and registration details about the SQL Server Performance Tuning & Troubleshooting at http://www.SQLpassion.at/events.html.

Thanks

-Klaus

Do you like this post? Why not share it with the SQL Server community?