Tuesday, July 26, 2011

Last Man Standing and HADRON

Curiously I had the discussion about this twice already so I think it’s time to write it down…

Warning: This is a constellation I consider purely hypothetical. I know it works (because I tested it…), I also know that it’s supported, but I would think twice before actually doing it…


You have two datacenters, one for primary operations, the other one for disaster recovery only. In both DCs you have a SAN that you consider 100% reliable. On each side you have a SQL cluster and the DBs are mirrored (Async) between those clusters. Like this:

The quorum model for both clusters is set to use a quorum disk. Why? Well, here comes the point of the action: Because you want a “Last man standing” configuration in your primary DC, meaning that even if two nodes go down, the DBs should still remain up.

Easy so far… Now bad bad Microsoft brings out SQL Server DENALI, which includes features that are WAY cool, and you really really really need those in your environment. (Features like Readable Secondary or Backup from Secondary…) Now what? Those features need HADRON… So how do you convert this scenario to HADRON?

Wishlist solution

What you would like to have is everything the way it was, just replace the cluster at the primary DC with a HADRON cluster (sorry, “AlwaysOn Availability Group” this is called now.) That’s OK, quite easy in fact, if it wasn’t for the Async Mirror and the limitation of HADRON that all members of the Availability Group need to be in the same cluster… OK, so here is what you come up at first:

Now you have the DBs in the primary datacenter on local drives, and still use a SQL cluster in the disaster recovery site. Cool feature by the way… Connecting an AG to a cluster instance. In our scenario this bears the advantage that you only need to transfer the bits once to your DR site.

At this point you have all you wanted from a SQL point of view. HADRON is on, so you have Readable Secondaries, Backup on Secondary, etc, all the cool features that DENALI brings.

You just have one problem: How do you setup a quorum device for that cluster? You could of course do majority nodeset (as it is recommended by Microsoft for multisite clusters.) But this would spoil the idea of a last man standing configuration in the primary site… And you can’t use a quorum disk anymore as you don’t have a SAN that all nodes can see. So what to do?

The way to do it

When I first came up with that idea the others around the table called me nuts… And somehow I can’t blame them..

The not so simple solution looks like this: There is a SAN in the primary DC, which is considered 100% reliable. What I did is to setup a clustered Micorosft iSCSI Target on the three nodes in the primary DC. (Way cool too by the way, the iSCSI Target is available for free now from Microsoft…) Now I present an iSCSI Lun through that target using the SAN disks as physical storage for it. Next I attach the LUN to all cluster nodes, including the nodes in the DR site. Voila… Now I have a cluster shared disk again on all nodes which I can use as a quorum device for the whole cluster…

The effect: I got a last man standing configuration in the primary DC again. The only downside is that if the primary DC goes down my cluster in the DR site also goes down. As bringing the DB online at the DR site means manual intervention anyway this is quite a small drawback… You just have to know that there is now one more thing to do in that case: ForceQuorum on that nodes…

Now decide for yourself if that’s mad or not. I agree with the guys that it is.

As a sidenote: Special thanks to Mike Steineke, David Smith and Thomas Grohser. They had their fingers in this solutions as well.

Monday, July 25, 2011


It took me a while to find the time for that, so sorry for the delay… This post is about how to have one installation run of SQL Server install not only the product itself, but also a Service Pack and a Cumulative Update package. Given the fact that every installation takes a reboot this method can save lots of time, not to mention lots of work. As always there are very good knowledge base articles about that and this post is not supposed to make them obsolete. I will reduce the post to just the plain basics. And give you a walkthrough on how to do it in a few minutes:

  1. You need is a plain installation media. (I usually copy the files from a DVD to a harddrive folder to start that.)
  2. You need to unpack the service pack. For that I create myself a “PCU” folder in the same directory as the installation media. Then I run the unpack like this: “SQLServer2008R2SP1-KB258583-x64-ENU.exe /x:<BaseDirectory>\PCU /Quiet”
  3. Same goes for the cumulative update. I normally use the CU folder. The commandline is the same, just of course with the other exe…
  4. Of course you can do this again with –x86 as well as –ia64 versions, having them extract into the same directory if you need the slipstream to run on all platforms.
  5. Now you need to update two files in the the base image: Setup.exe and Microsoft.SQL.Chainer.PackageData.dll. You should always use the latest version there, so normally the one from the CU. Be advised that the Chainer DLL is in the x64 directory (as well as in the x86 and ia64, so if you slipstream multiple processor architectures you have to replace all of the chainer DLLs.)
  6. Last thing: Update DefaultSetup.ini in the x64 directory (and x86, ia64 as before) to include the updates. The line looks like that: PCUSource=”C:\Install\SQL2008R2\PCU”. (That’s the line for the service pack. For the cumulative update it’s called CUSource.) Notice that I used full qualified path names. You can of course use relative paths as well, but I had some difficulties with those in the past, so I changed it.

That’s it… So how do you get Setup now to use the slipstream? Well, just click on Setup.exe… Everything else will just work…

And now for the lazy ones around: Here is the script I use for slipstreaming: (Notice that %1 is the target directory where the plain SQL Server copy is.)

ECHO Unpacking Service Pack...
ECHO   x86...
SQLServer2008R2SP1-KB2528583-x86-ENU.exe /x:%1\PCU /quiet
ECHO   x64...
SQLServer2008R2SP1-KB2528583-x64-ENU.exe /x:%1\PCU /quiet
ECHO   IA64...
SQLServer2008R2SP1-KB2528583-IA64-ENU.exe /x:%1\PCU /quiet

ECHO SlipStreaming Service Pack...

robocopy %1\PCU %1 Setup.exe >NUL 2>NUL
robocopy %1\PCU %1 Setup.rll >NUL 2>NUL

ECHO   x86...
robocopy %1\pcu\x86 %1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL
ECHO   x64...
robocopy %1\pcu\x64 %1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL
ECHO   IA64...
robocopy %1\ia64 %1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL

REM Currently no CU for R2 SP1

ECHO Unpacking Cumulative Update...
ECHO   x86...
SQLServer2008R2-KB2534352-x86 /x:%1\CU /quiet
ECHO   x64...
SQLServer2008R2-KB2534352-x64 /x:%1\CU /quiet
ECHO   IA64...
SQLServer2008R2-KB2534352-IA64 /x:%1\CU /quiet

ECHO SlipStreaming Cumulative Update...

robocopy %1\CU %1 Setup.exe >NUL 2>NUL
robocopy %1\CU %1 Setup.rll >NUL 2>NUL

ECHO   x86...
robocopy %1\CU\x86 %1\x86 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL
ECHO   x64...
robocopy %1\CU\x64 %1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL
ECHO   IA64...
robocopy %1\CU\ia64 %1\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll >NUL 2>NUL


ECHO Updating DefaultSetup.ini...
robocopy . %1\x86 defaultsetup.ini >NUL 2>NUL
robocopy . %1\x64 defaultsetup.ini >NUL 2>NUL
robocopy . %1\ia64 defaultsetup.ini >NUL 2>NUL

And that’s it. Just to be complete, here is the complete DefaultSetup.ini I am using:

;SQLSERVER2008 Configuration File
PID="<add your product key here>"

Please note that CUSource is commented out as there is no CU for SQL 2008R2 SP1 right now.