Wednesday, December 28, 2011

MutliSubnetFailover – the DNS conundrum

I love new technology and I love new ideas, but sometimes the coolest of those are somewhat not thought through… When Microsoft announced the support for multisubnet clustering in SQL 2012 I was cheerful, as this is a feature that can come in really handy in time like these. Unfortunately in the real world this new feature soon proved to be … let’s say … improvable.

Here’s the deal: When you have a virtual network name (VNN) in a cluster with multiple IP addresses behind it in the end what that does is automatically reset DNS entries whenever you failover the group. Now as all of you know (and if you don’t know it’s still a fact unfortunately…) client computers have this nasty thing that’s called “DNS resolver cache”, which basically means that names that have already been resolved to IP addresses will be cached locally and not sent to the DNS server again for a while. (That while is called the “Time to Live”, or TTL for short.) VERY good idea in normal life, otherwise DNS Servers would have a real problem and every request would be WAY slow… Unfortunately… VERY bad idea for failover scenarios like ours with the DB, especially if you require real high availability…

OK, now, Microsoft is not THAT stupid… in fact they have quite smart people there and those people thought about this problem too. What they did to tackle this was quite simple: Make the client driver aware of the problem so it can solve it. The way they did this was… well… Interesting at best… They changed the behavior of the VNN so it would always register all IPs in DNS, not only the active one, (ok, this was not a real change, cluster service had this feature for a while now, they just use it…) and build a change into the client to get all those IPs and automatically connect and reconnect to the active one.

Nice idea in theory… The problem starts when you begin using applications that are not aware of that idea. Why? Because what happens by default when you have multiple IPs in DNS is that the client fetches a random one (OK, not really random, but it doesn’t matter for now…) and places that one in his cache. If that one is the one that is currently inactive… Well… Let’s call it a bad day… You will just not get a connection open, and the server will not fetch a new IP because the resolver cache already has one… So… Tough luck…

OK… Enough with that… So how do you overcome this problem? Well, unfortunately there is no really good way to do it… (Unless of course all your applications can be made aware of the situation by using the latest drivers…) Here is what I would do for now as a next best thing to a solution:

  1. Stop the DNS behavior of the VNN, let it run as it would have normally. Unfortunately you can’t change this in the UI, only via commandline: cluster.exe RES <ResourceName> /PRIV RegisterAllProvidersIP=0
  2. Reduce the TTL of that DNS entry to whatever is the longest you can possibly wait for a failover. I chose 15 seconds for that, but feel free to go your own way. Luckily this setting can be changed in the DNS management UI.
  3. Still keep the MultiSubnetFailover=True parameter in the apps that support it. It doesn’t really help, but maybe you reach the point at some time where you have a homogenous system again and can use that new pattern.

There is an alternative way too that might even be better than this one. But I would strictly only recommend this if your system is rather small. (In numbers of DBs…) You could leave the SQL listener as it is and connect your MutliSubnet aware applications there. Then add a second VNN to the group that is set with minimal TTL and no RegisterAllProvidersIP flag to connect your legacy apps to. This setup will give you maximum availability and flexibility, but it requires extra work and extra IP addresses…

Thursday, November 10, 2011

BitReverse again

Just to satisfy the high performance fanatics: Below you find the most efficient reverser algorithm I could write so far.Unfortunately getting this code into SQL Server is not as simple as it might look. ODS (the extended stored procedure API) seems to have more overhead than the CLR and CLR interop also generates more overhead than you can save. If anybody has good ideas on how I can get this efficiently in, please give me a ping.

BitReverse PROC
PUSH r13

XOR rax, rax
MOV r13, rcx
MOV rcx, 63
SHR r13,1
RCL rax,1
LOOP JumpLabel
POP r13


BitReverse ENDP

Friday, October 14, 2011

When sp_ doesn’t mean “stored procedure”

For some people in the community this seems to be very common knowledge. Well, if it’s so then shame on me, because I didn’t know…

I have been asked some times how those special system stored procs like sp_help work. Those sprocs that only exist in master database, but still you can call them from every DB you are in. So far I thought this was something special, internal, of SQL Server… Well, wrong…

The trick is easy: Whatever object you create in master database that is called sp_<something> you can automatically use in every DB on that server. Because in this case sp_ doesn’t mean “stored procedure” but it means “special”…

Thanks at this point to Kelan Delaney who brought this up at a presentation today…

Wednesday, October 5, 2011

The day Sequences saved the world

In my last post I brought up the idea of reverse indexes and how those could save you from latch contentions.Well, after another day of hard work it turns out that the new Sequence feature in SQL Denali really is a kind of universal life saver for high load OLTP applications… I don’t know how many sequences we did already to get rid of latch contention and last page inserts, but I can tell you, it were quite a few…

There are two things I have to add though regarding my last post:

First… If you run sequences in really high load environments you have to use the CACHE feature. In the case of a server crash this might leave you with gaps in your sequence, but if you don’t use the Cache you will get locking issues on the sequence once you hit somewhere around 10.000 Fetch next statements per second.

And second… While my T-SQL bit reverser works perfectly fine it is sort of CPU intensive… Our 80-core server burnt about 1% CPU per 1000 rows inserted, with more than half of that going into the bit reverser. I did a lot of tests following this finding, and it almost hurts me to say that, but in this one case SQL CLR really is the best solution you can have… Using the CLR function instead of the T-SQL function we are almost down to nothing for the bit reverse.

Oh, and here is the code of the CLR function: (If anyone wants the compiled DLL or the complete solution please ping me and I’ll mail it to you.)

public static Int64 Reverse(Int64 inVal)
    ulong Result = 0;
    ulong Input = (ulong)inVal;
    int i;
    for (i = 0; i < 63; i++) // 64 bits...
        Result <<= 1; // Shift result by one digit
        if ((Input & 1) == 1) // If lowest bit of input is 1 add one to result
        Input >>= 1; // Now shift the input so that the next bit is lowest
    return (Int64) Result;

Monday, October 3, 2011

Bit reversion

This might seem to be something you never need in SQL Server, but maybe you do and just never knew, so hear me out:
The problem started with a typical scenario for logging tables and even more with some OLTP tables that are heavily inserted: You have an identity column as your primary key (because GUIDs are taking too much time, or you just want bigints or whatever…) and due to the latch contention on the last index page you just can’t get more inserts than 10.000 a second. (OK, that might be enough for almost everyone… But maybe you are not almost everyone?) So what to do? The idea is called “Reverse index”… (Some other DB systems have those out of the box, SQL doesn’t… Doesn’t matter, we can build one…) the basic idea behind it is that you do a binary flip of the increasing number. How does this help? Well, look at the values you get: (tinyint as a sample…)
Identity value Binary Reverse New value
1 00000001 10000000 128
2 00000010 01000000 64
3 00000011 11000000 172
4 00000100 00100000 32
5 00000101 10100000 160
6 00000110 01100000 96
You see how the values keep jumping? Now there is no latch contention anymore on the PK index…
OK, now how do you accomplish this?
First you need to get rid of the identity. Leave the PK without default value, or build your default with what comes next.
Second we need to get a new unique number. Let us all bow before SQL Server Denali, because it comes bearing gifts for us… The magic word is called SEQUENCE. and it is simple:
Now you get the next value calling:
SET @Variable= NEXT VALUE FOR <SomeSequenceName>
Now all you need to do is the inversion… And here is how that is done: (The code works for Bigint…)
    @Input bigint
RETURNS bigint
    DECLARE @WorkValue bigint=@Input
    DECLARE @Result bigint=0;
    DECLARE @Counter int=0;
    WHILE @Counter<63
        SET @Result=@Result*2
        IF (@WorkValue&1)=1
            SET @Result=@Result+1
            SET @WorkValue=@WorkValue-1
        SET @WorkValue=@WorkValue/2
        SET @Counter=@Counter+1
    RETURN @Result
And now you can glue this together… If you want a default value it might be easiest to get rid of the input Parameter for the BitReverse and query the sequence in the function itself, but this is up to you.

Wednesday, August 17, 2011


It was not too long ago that I wrote my post regarding slipstreaming. (That is installing patches together with SQL server in one run…) Now it seems that my information is outdated already, as Microsoft changed the way this works in SQL Server “Denali”. And I have to say: THANK YOU Microsoft, this has become a hack of a lot easier… Just add two lines to DefaultSetup.ini and place the patches all in one directory. Almost to easy to even write about it, but just to be complete:


This is the entry for the defaultsetup.ini. And as you can see relative paths now work fine too.But be advised: The ini file lies in the x64 directory, the UpdateSource path still is relative to Setup.exe!

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.

Tuesday, May 24, 2011

SQL Server–Automated Setup

I know that there are many articles in Microsofts knowledge base about how to do automatic installation of SQL Server, but as I get pounded with this question time and again I figured it might be a worthy topic to start a blog. So here it is… Automatic SQL Setup, reduced to the ultimately necessary steps: (Actually it’s only one step.)


/SQLSvcAccount=<SQL Server User>

/SQLSvcPassword=<SQL User password>

/AGTSvcAccount=<SQL Agent User>

/AGTSvcPassword=<SQL Agent password>





Not that complicated now, is it?

OK, maybe I need to clarify some things here:

  1. The Parameter INSTANCENAME obviously holds the name of a named instance. If you specify MSSQLServer setup will install the default instance.
  2. What the hell is AutoConfig.ini and how do you create it? Well, you can of course follow the pages of books online and create one yourself, but I would not recommend it, as there is a far easier method: On a clean machine run through the setup wizard with the configuration you like to have. After Setup has finished you will find a ConfigurationFile.ini in the C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\<Timestamp> directory. All you have to do from there is change either “Quiet” or “QuietSimple” to true and you are done.
  3. Just to clarify: “Quiet” means that setup will run silent, without any UI. “QuietSimple” means that no questions are asked, but the progress of Setup is still displayed in the setup wizard style.

Now… This is how you do Setup automatically… So, if you can do this now, wouldn’t it be nice if setup would allow you to also install the latest Service Pack and Cumulative Update pack in the same run? Well, you can do that too, but that’s another story. (One that I will for sure tell in a short while.)