SQL Server AlwaysOn in a virtual world

  • Article

Despite what I posted yesterday about clustering, mirroring is the preferred option for many DBAs in a virtual world although you can of course combine the two.  On the up side mirroring gives you one (and only one) copy of the database in another location which you can’t really use, and failover is instantaneous, the down side is that the connection needs to be mirror aware (e.g.OLE DB) and you are only protecting a database at a time. 

However the SQL Server product team have a cunning plan for the next release (Codenamed Denali) originally called HADRON, now called AlwaysOn which provides mirroring like ease of use but is built on Windows Server failover clustering (WSFC). Don’t panic all you need to do in WSFC is turn on the feature and setup a cluster..

cluster

 

As you can see from the above you don’t need shared storage so forget about FCoE, ISCSI Fibre Channel etc.  You then enable Always On in configuration manager by right clicking on the SQL Server service and selecting its properties specifically the SQL HADR tab (you’ll need to do this on each node on the cluster)..

hadron

 

The next step is to create an availability group from SQL Server Management Studio (under management), select some databases to include in it and your done.  One catch here is the databases have to be suitable , the full list of restrictions is here but essentially they need to be in full recovery mode and backed up.  The wizard does all the work for you and of course there are T-SQL commands to do this programmatically as well as PowerShell support for control and management and of course the wizard will generate a script for you as well.

Here you can see I have declared a replica server (in ctp1 you can only have one), and I can decide which connections to allow..

availibility

The process also sets up endpoints (note the option to encrypt data)..

endpoints

You can then synch the data (I created a share called backup for this)..

data synch 1

and then when you go back to management studio you can see that you have something like mirroring setup..

hadr completed

so interesting stuff but you need to be aware that

  • I have done this in ctp1 which only has a basic implementation of AlwaysOn, (so only 2 nodes just now) compared to what will be in the final release. However I wanted to make you aware that this is coming as it will drastically change the way high availability is done, and make your  virtual world a lot easier.
  • Books Online has a big section on this
  • Before you ask I don’t have details of when the next ctp is coming out, like you I have to track announcements at the big US shows like WDC, PASS and TechEd