I have been at a client all week implementing a SQL Server 2005 Standard Edition SP3 cluster using Windows Server 2008 R2 for the underlying operating system. Since this is W2K8 R2, it's 64-bit only, so we implemented SQL Server 2005 x64. Everything went pretty smoothly, as did the cutover from their current production box. However, we did notice one weird issue: we couldn't configure a Maintenance Plan (I'm not a huge fan - I'm more of a create it on your own kind of guy). As it turns out, I found the following blog post from a Microsoft support engineer. We had named the nodes ending in lowecase letters. The problem was that by the time we figured this out, we were already live in production, and there is no way we could take another outage, and run through the whole process of evicting and renaming nodes.

The blog only mentions Maintenance Plans and Database Mail. What it doesn't mention is that it also does not create the System/Performance Monitor counters for use, and you have replication issues if you are using a Publisher and Distributor on the same instance (although replication works, you get errors and it is just odd). If you configure replication to go against a remote Distributor, everything is just fine. We did a bunch of testing against another cluster we configured (same hardware configuration) for their soon-to-be reporting server and had none of the problems we had on the original cluster we configured as production.

The unfortunate thing is that the customer is now in production and they can't afford another outage to go and evict things and reconfigure the servers in use. Everything else is working and stable. They may consider migrating to the other cluster in another outage, but not anytime soon.

Moral of this sad tale of woe: never use lowercase letters for Windows Server 2008 (RTM or R2) node names with a SQL Server 2005 clustered implementation. It affects more than just Database Mail and Maintenance Plans.

If this isn't a reason to use SQL Server 2008, I don't know what is! :)

I've been talking about it for awhile, but it's finally here - my update of the old SQL Server 2000 consolidation whitepaper. It just went live less than an hour ago.

The basic info including the link to the download of the Word document can be found here:
http://msdn.microsoft.com/en-us/library/ee692366.aspx

or you can just download the Word doc from http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/SQLServer2008Consolidation.docx.

I hope you find it useful!

Now that Windows Server 2008 R2 is RTM and the book is done, I can get to some of the things that were impossible to do prior to getting Pro SQL Server 2008 Failover Clustering out the door.

One of the things I was not able to do was document how to create the Microsoft Distributed Transaction Coordinator using PowerShell. It is not hard, but it also isn't the most straightforward task, either. Use the script below to create DTC; all you need to do is change the parameters (self-explanatory) to the right values for your environment.

$clunm = "Windows failover cluster name"
$dtcdisknm = "Name of the disk resource to use with DTC"
$dtcdnsnm = "Name for DTC in DNS"
$dtcgrpnm = "Name of the DTC resource group"
$dtcipaddr = "IP address for DTC"
$dtcsubnet = "Subnet mask for DTC"
$dtcipresnm = "Name of the DTC IP address resource"
$dtcnetnm = "Name of the DTC network name resource"
$dtcresnm = "Name of the DTC resource name"

Add-ClusterGroup $dtcgrpnm -Cluster $clunm
Add-ClusterResource $dtcipresnm -ResourceType "IP Address" -Cluster $clunm -Group $dtcgrpnm
$ipres = Get-ClusterResource $dtcipresnm
$ipaddr = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $ipres,Address,$dtcipaddr
$subnet = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $ipres,Address,$dtcsubnet
$setparams = $ipaddr,$subnet
$setparams | Set-ClusterParameter
Add-ClusterResource $dtcnetnm -ResourceType "Network name" -Cluster $clunm -Group $dtcgrpnm
$nnres = Get-ClusterResource $dtcnetnm
$netnm = New-Object Microsoft.FailoverClusters.PowerShell.ClusterParameter $nnres,Address,$dtcdnsnm
$netnm | Set-ClusterParameter
Add-ClusterResourceDependency $dtcnetnm $dtcipresnm -Cluster $clunm
Add-ClusterResource $dtcresnm -ResourceType "Distributed Transaction Coordinator" -Cluster $clunm -Group $dtcgrpnm
Add-ClusterResourceDependency $dtcresnm $dtcnetnm -Cluster $clunm
Move-ClusterResource $dtcdisknm -Cluster $clunm -Group $dtcgrpnm
Add-ClusterResourceDependency $dtcresnm $dtcdisknm -Cluster $clunm

In my next blog post, I will show you how to map MSDTC to a specific instance of SQL Server since even the current SQL Server 2008 failover clustering whitepaper gets it slightly wrong. Screenshots will be included.

I do a bit of work in the consolidation space, and speak about it a lot. The update to my old SQL Server 2000 consolidation planning whitepaper is almost done and I should be doing a webcast next month. But I wanted to address one thing here in the interim: the biggest sticking point is not moving databases or maintenance plans; it's applications. There are lots of aspects to this which I am not addressing in this blog post. I am specifically going to address application compatibility with later versions of SQL Server such as 2005 or 2008. A common complaint I hear from many customers is that the applications they use do not support SQL Server <insert version here>.

There are a few issues with this really large, and to some degree, crippling problem:
1. Part of the problem is not the vendor, but the customer.
a. Many vendors do have later versions of their application; customers just haven't implemented it. I'm oversimplifying here; it's a deeper problem than that which is explored in the next few sub-letters.
b. Change is hard. Later versions introduce new, or changed, functionality that may not even be used. If something is changed, it may not be to a customer's liking. As far as unused functionality, there comes a point for a lot of people where what is there is "good enough" and there may be no reason in terms of features to seemingly want to upgrade.
c. If the application is highly customizable (such as Siebel), it's not a slam dunk just to implement it. There's downtime as well as retraining of end users.
d. This one is really a side effect of a, b, and c: even if it means not being supported, it sometimes may feel like it is just easier to go with what you know.
e. ... and then there is cost. Upgrades generally are not free.

You can work through downtime and any technical issues associated with an upgrade; the more intangible (perceived value) and tangible (do we have money to do this?) are a bigger problem.

2. In the cases where there is no later version of the software, it's unfortunate, but Microsoft cannot play the heavy and force vendors to make an application support a later version of SQL Server. Believe me, I wish they could, but I can only imagine the potential legalities of that. It's really up to the customers to make their voice heard to get a version of an application that supports what they need. If they can't, realistically, it may be time to move to another software package that supports the platforms you deploy. You could possibly consider virtualizing the server and its SQL Server instance, but you're still supporting the old version of SQL and you need to be sure the vendor supports you in a virtualized environment - but you do get rid of the physical box.

3. All of this is a Catch-22. Your application pain now forces you to deploy or still keep older versions of SQL Server in play, affecting your long term supportability not only from Microsoft but in terms of your DBAs. Do you want your DBAs to be a long way behind in their knowlegde of the platform? Heck no!

By the way, this  problem extends to service pack levels. In my experience, some software won't allow you to upgrade/patch your SQL Server instances to a later service pack unless they support it. That doesn't bode well for consolidating that application where you have a shared instance with N number of databases, all of which will be upgraded at the same time.

Trust me, I wish I had a magic wand to make these issues go away. They are very real and must be dealt with appropriately in a consolidation effort. What is right for one customer may not be for another. I do enjoy helping customers work through these issues, but I can assure you that it is never easy and hard decisions need to be made.

Hello all. I think it is appropriate that my first technical blog post be about clustering. I was doing some testing and setups for both my book and some upcoming webcasts, and for the first time in quite awhile, I tried to install a SQL Server 2005 failover clustering instance on a Windows Server 2008 failover cluster. When you start Setup, you will see this message:

First, one thing you have to know is that when you configure a Windows Server 2008 failover cluster, all of the storage goes into a pool (which is really a resource group) called Available Storage. With Windows Server 2003, each disk went into its own resource group. If you don't take that into account, when you run SQL Server 2005's Setup and get to the Cluster Group Selection dialog, here is what you see:

At this point, click Cancel to get out of SQL Server 2005's Setup. You won't be able to proceed unless you want to use Available Storage which I do not recommend.

To solve this problem, perform the following steps:
1. Make sure first that you are logged in as the cluster administration account.
2. Open a command window. It can be found under the Start menu. Right click, and select Run as Administrator.

3. In a command window, create the resource group with the command cluster <CLUSTERNAME> group <groupname> /CREATE.
4. Bring the group online with the command cluster <CLUSTERNAME> group <groupname> /ONLINE.

5. To see a list of resources in the Windows Server 2008 failover cluster, use the command cluster <CLUSTERNAME> resource.
6. Move the disk you want to use from the Available Storage group to the group you created in Step 3 with the command cluster <CLUSTERNAME> resource "<Resource Name>" /MOVE:"<Resource Group>".

7. If you want to verify visually, you should see something similar to the screenshot below from Failover Cluster Manager.

8. Re-run Setup. When you get to the Cluster Group Selection dialog, the new group with with its storage will be available.