My Denali First Glance – AlwaysOn Availability Groups

I’m finally finding some time to get more familiar with the new feature in Microsoft SQL Server Code-Named “Denali” called “AlwaysOn”. No, that is not a typo – capitalize “Always”, capitalize “On” and concatenate – “AlwaysOn”!

There are two flavors of AlwaysOn – failover clustering and availability groups.  AlwaysOn Failover Clustering is exactly what it sounds like – enabling a SQL Server Denali Instance to failover to another node in a Windows Server Failover Cluster. This is also known as a Failover Cluster Instance (FCI). On the other hand, AlwaysOn Availability Groups can best be described as database mirroring on steroids – and that is what I’ll cover in this post. I’ll cover the differences in Denali’s FCI implementation from prior versions in a later post.

First, think of all the things you don’t like about database mirroring:

•         Only 1 mirror
•         The mirror is always “in recovery” and unusable outside of a snapshot
•         Failover is per user database (i.e. one at a time)
•         Have to choose between synchronous (high availability) and asynchronous (disaster recovery)
•         Requires a 3^rd instance as a witness if you want “high safety mode” and automatic failover

Here’s what AlwaysOn Availability Groups will provide:

•         Up to 4 replicas (the term “replica” essentially replaces “mirror”) in CTP3
•         Read-only access is provided to replicas
•         Multiple databases can be grouped together for failover defined as “Availability Groups”
•         Synchronous and Asynchronous replicas at the same time – covering both high availability and disaster recovery (hence HADR)
•         Windows Server Failover Clustering detects when failover is required – no third (or fourth or fifth) server required

Yes, you read that right; the Windows Failover Clustering feature is required to be installed on your Denali servers in order to use AlwaysOn Availability Groups. It does not mean, however, that you have to actually implement a Windows failover cluster of two or more nodes. But, it does mean that Windows Server 2008 Enterprise Edition is required….and that makes me believe that this feature will likely only be available in the Denali Enterprise Edition or higher once the final SKU feature sets are announced by Microsoft. However, SQL Server Enterprise Edition is required today to use asynchronous mode for database mirroring, so this shouldn’t come as a surprise.

Similar to database mirroring failover options, the availability failover modes are automatic or planned manual failover for synchronous replicas to prevent data loss or forced manual failover for asynchronous replicas which may incur data loss.  If the secondary replica in synchronous mode is not synchronized with the primary replica, then a forced manual failover is also the only option.

For seamless client connectivity, when you establish an Availability Group you may also create a Virtual Network Name (VNN) which will be assigned a Virtual IP (VIP). Whenever the Availability Group fails over, the VNN will redirect connections to the new primary replica. Alternatively, if there are only two availability replicas in an Availability Group, then “old-style” database mirroring connection strings may be used until you can establish a VNN and update your application to use the VNN. If you want to add a third replica, then you must convert to VNN for your client connectivity.

In addition to having read-only access to secondary replicas, you can take backups of your secondary replicas.  There are several options you will want to specify when setting up your Availability Groups (or alter afterwards) in order to properly control the backup of your primary and secondary replicas. Since this is still the CTP as I’m writing this, I’ll just point you to this BOL link for the most current info as it may change by the time you get around to implementing this.

The main purpose of this article is to make you aware of what is coming so that you can start planning for your future HADR requirements as you make the move to Denali when it is finally released. To dig deeper, check out the current BOL entry for AlwaysOn Availability Groups.

My Denali First Glance – PowerShell Enhancements

First, PowerShell 2.0 is now required as a pre-requisite for installing SQL Server Denali (SQL Server 11). Hopefully, you’ll only be installing Denali in a Windows 2008 R2 environment which has PowerShell 2.0 installed by default. But, if not, you’ve now been forewarned – just in case you don’t read the “readme” prior to installing!

SQLPS is now a module (a new feature in PowerShell 2.0). The SQLPS utility has been deprecated, but if you use it, it apparently will start PowerShell 2.0 and import the SQLPS module.

Additionally, there is now a module of cmdlets for SSAS (Analysis Services). After installing Denali, if you execute the command Get-Module -ListAvailable you’ll see that in addition to SQLPS, SQLASCMDLETS also is displayed. This module contains 11 cmdlets as shown below:

But, my focus is Database Engine, so that’s all I’ll say about SSAS. If you are a BI guy or gal – check it out!

When you first import the SQLPS module, you will get the following warning unless you import the module using the -DisableNameChecking parameter.

You’ll notice that as soon as you import the SQLPS module, your location changes to the SQLSERVER provider. To see what’s new in this hierarchy, just type: dir

XEvent, IntegrationServices and SQLAS are all new. Thus, more features and components of the SQL Server stack are now being exposed for PowerShell management.

Then, there are all the new cmdlets available in SQLPS.

Considering there were only 5 cmdlets (highlighted in yellow above) for SQL Server 2008, I now count 29! Most appear directed at new features in Denali such as High Availability\Disaster Recovery (HADR) and Availability Group configurations. I’m sure I’ll be looking at those in detail when I get around to studying AlwaysOn.  However, two very traditional database commands have been provided now with their own cmdlets – Backup-SqlDatabase and Restore-SqlDatabase. These two cmdlets are modeled after the Microsoft.SqlServer.Management.Smo.Backup class, so if you are used to the properties in that class, then the parameters for these cmdlets will be very familiar.  

That means you can now execute commands like this as a SQLAgent PowerShell job step:

Dir SQLSERVER:\SQL\MyServer\MyInstance\Databases | Backup-SqlDatabase

This simple command will backup all user databases on an instance using all the defaults, of course. But, you can start imagining the possibilities here – especially if you start integrating any additional parameters needed in your environment.  

As indicated by the title, this is just a “first glance” at what is coming in Denali; I’m sure I’ll be investigating and writing more about using PowerShell with Denali in the future. What new PowerShell enhancements are you looking forward to implementing?