Cluster terminology – What “Active/Active” actually means

As a follow-up to my last entry (attempting to clear up some Windows Clustering terminology), I’ve found an article that makes another distinction that I forgot to include – the difference between an active/passive and an active/active cluster:

The misconception of active/active clustering (a la AirborneGeek.com)

The understanding among those new to cluster seems to be that a/a vs. a/p is a licensing question, and then if you’re licensed for it, you just turn it on. In reality, it really just describes whether you have clustered services living on only one node or split between both nodes (during normal operation – during a cluster failover, any cluster might be active/active for a short period of time. Or, I suppose, your cluster is active/active if your quorum drive lives on the opposite node from your clustered service). There’s no load-balancing involved in clustering at all – at any time, only one node owns a particular resource, and only that node is responding to client requests for that resource.

In SQL Server 2012 AlwaysOn, the new high-availability feature, the SQL Server service is running on both cluster nodes, but client access (through the “Availability Group”) is controlled by the cluster service. That means that all clients making a connection go first to the active server, and then the SQL Service there might send them to get their data from one of the other nodes (it’s worth reiterating here that, in AlwaysOn, SQL Server isn’t clustered, but the SQL services operate independently on each node).

Clean up vendor names and other data with unwanted numbers/characters

In creating an accounting report, the vendor names we get back from our credit card processor needed some major clean-up: “52334SOUTHWESTAIR1234”, “ABD2343-BLUE DINER 843”, and so on. I initially found a great function for this from Pinal Dave:

http://blog.sqlauthority.com/2007/05/13/sql-server-udf-function-to-parse-alphanumeric-characters-from-string/

But I wanted to make a few enhancements to it:

  1. He leaves numbers in the string and I’d like to remove them
  2. I’d like to combine multiple spaces in a row into a single space, but leave spaces intact

The first is pretty easy to do – just remove the [0-9] and add a space to the PATINDEX. The second one uses a trick from another post I did a few years ago.

Here’s the modified version:

CREATE FUNCTION dbo.UDF_ParseAlphaChars2
(
   @string VARCHAR(8000)
)
RETURNS VARCHAR(8000) WITH SCHEMABINDING
AS
BEGIN
   DECLARE @IncorrectCharLoc SMALLINT
   SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)

   WHILE @IncorrectCharLoc > 0
   BEGIN
      SET @string = STUFF(@string, @IncorrectCharLoc, 1, '')
      SET @IncorrectCharLoc = PATINDEX('%[^ A-Za-z]%', @string)
   END

   -- Trim groups of spaces into single space
   SET @string = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@string,' ','<>'),'><',''),'<>',' ')))

   RETURN @string
END
GO

--Test
SELECT dbo.UDF_ParseAlphaChars2('ABC”_I+{D[]}4|:e;””5,<.F>/?6')
SELECT dbo.UDF_ParseAlphaChars2('52334SOUTHWESTAIR1234')
SELECT dbo.UDF_ParseAlphaChars2('ABD2343-BLUE DINER 843')
GO

SQL Server Contention Monitor (super-alpha) posted to Codeplex

In response to some conversation in an Experts-Exchange question, I decided to post an application I wrote about four years ago and never did anything with (or add much polish, as you’ll see). It’s a small VB.NET application that polls MSSQL servers and displays information about currently blocked SPIDs, as well as the block chain and age of blocked processes.

It’s something I’ve used when I troubleshoot blocking on a SQL Server, and generally leave running in my system tray – it will pop up a balloon when there’s a blocked process that lasts longer than some pre-determined threshold, and then optionally pop another balloon when it’s resolved. It works well as an early warning system for a critical server, and I’ve often used it when I’m performing maintenance on a production server that I’m concerned might block legitimate use – running the application lets me know within a few seconds when I’ve gotten in somebody’s way so I can act accordingly.

It’s still very much a work in progress and is pretty rough around the edges (and well inside the edges), but I’ve posted it to a Codeplex project for anybody else who might find it useful. Though I’m working on a few other things right now and haven’t done any development with this app in almost four years, I’ll keep an eye on the issues list at Codeplex (or feedback here) and try to address anything that comes up.

Without any further ado, here it is!

https://mssqlblockmonitor.codeplex.com/

Presenting a pair of lightning sessions at SQL Saturday in Phoenix

I’ll be presenting a pair of lightning sessions at SQL Saturday in Phoenix, AZ, on April 28th – if you’re in the area and up for a day of free training on everything database-related, as well as some networking time with your fellow DBAs and Developers, please join us!

Here’s the summary for my two lightning sessions (they’ll be 15 minutes each):

Automating SQL Server source control – This session will look at a quick and easy process that takes regular snapshots of object definitions in the database and stores any changes.

Representing SQL Server data spaces visually – This session will take a quick look at a Codeplex project that does this, SQL Space Map, and how it accomplishes it by leveraging a library from Microsoft Research

If you’d like to see what else is planned for the day, http://sqlsaturday.com/131/schedule.aspx. You can also follow the event on twitter at #sqlsat131

Export from SQL Server to XLS and email results

Sometimes you want to take some query results and export them directly to an XLS file – here’s how you can set that up in SQL Server. The biggest caveat is that you need to run it from an x86 instance of SQL Server – the x64 instance won’t have access to the Jet driver needed to write the Excel file (Microsoft.Jet.OLEDB.4.0), where the x86 version will. In fact, we maintain an older x86 instance of SQL Server for random processes like this that need it – x64 is better in almost every case, but we can’t see to completely ditch x86… 🙂

I use a stored proc that I call from a SQL Agent Job, which works great. The actual process is a bit awkward – for starters, you’ll need access to xp_cmdshell. SQL Server can’t create a new Excel file from scratch, so you have to keep a blank Excel file around, make a copy of it, and then insert into the copy to get your final result.

That said, here’s the code to generate the XLS file from your query results:

SELECT Column1, Column2, Column3, Column4
  INTO ##YourTempTable
  FROM SomeOtherTable

SET @Folder = 'C:\Temp\'
SET @DocumentBlank = 'Your Document - Blank'
SET @DocumentLong = 'Your Document - ' + CONVERT(VARCHAR(10), GETDATE(), 120)

DECLARE @CMD NVARCHAR(4000)
SET @CMD = 'COPY "' + @folder + @DocumentBlank + '.xls" "' + @Folder + @DocumentLong + '.xls"'
exec master..xp_cmdshell @CMD

-- Export the Excel sheet
SET @CMD = 'insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
	''Excel 8.0;Database=' + @Folder + @DocumentLong + '.xls;'',
	''SELECT * FROM [Sheet1$]'')
	select Column1, Column2, Column3, Column4 from ##YourTempTable'

exec sp_executesql @CMD

Once that’s exported, you can just set up the email process using sp_send_dbmail and attach the file you just generated:

DECLARE @Body VARCHAR(2000)

SET @Attachments = @Folder + @DocumentLong  + '.xls'
SET @Body = 'Your file has been generated for ' + CONVERT(VARCHAR(10), GETDATE(), 120)

exec msdb..sp_send_dbmail @profile_name = 'YourMailProfile',
	@Recipients = 'Recipients@YourDomain.biz',
	@subject = 'Your file is ready',
	@Body = @Body,
	@file_attachments = @DocumentLong

Return a list of all dates between a start and end date

In some situations, you’ll need to work with a list of numbers or dates that are between some start or end, and you don’t have a complete list handy for joining to. Using the script below, you can create one to use in your query – if you wanted to use this in-line in another SQL Statement (and assuming you’re using SQL 2005+), you could either do a subquery or a WITH CTE clause and then join directly to it.

To do it, I’m using a table that has a large number of rows in it, even in an empty database (sys.columns), and then doing a cross-join to ensure that I’ll have enough rows to satisfy my entire range. This table has about 890 rows in an empty databases (or you can use the “model” database if you don’t have any user databases handy), meaning that the cross join yields about 800,000 rows – enough for almost 2200 years of days, or 100 years of hourly increments (change the “dd” in the “DATEADD” statements below to “hh” or even “mi” to do any increment of time you want).

The code:

DECLARE @StartDate DATETIME,
	    @EndDate   DATETIME
	
	SET @StartDate = '2012-12-01'
	SET @EndDate   = '2015-12-31'
	
;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT DATEADD(dd, number-1, @StartDate)
  FROM numberlist
 WHERE DATEADD(dd, number-1, @StartDate) <= @EndDate

I’m using dates above, but if you wanted to use INT instead, it’s pretty straightforward:

DECLARE @Start INT,
		@End   INT
	
	SET @Start = 1500
	SET @End   = 64000
	
;WITH numberlist(number)
   AS (SELECT RANK() over(order by c1.object_id,
								   c1.column_id,
								   c2.object_id,
								   c2.column_id)
	     from sys.columns c1
   	    cross
	     join sys.columns c2)
SELECT @Start + number - 1
  FROM numberlist
 WHERE @Start + number - 1 <= @End

Failing maintenance plan on SQL Server 2005 when databases are offline

My backup/maintenance plan recently started failing with the following (not so helpful) error message:

Code: 0xC0024104
Source: Update Statistics
Description: The Execute method on the task returned error code 0x80004002
(Unable to cast object of type 'System.DBNull' to type 'System.String'.).
The Execute method must succeed, and indicate the result using an
"out" parameter.

It turns out the error was as a result of one of my databases being offline – the plan was set to work on all databases (the default), but there’s a checkbox that tells SQL Server to skip databases that aren’t online, and it’s not checked by default when you create your maintenance plan. Checking it solved the problem and the maintenance plan ran normally again.

To find the box, open your maintenance plan, and then in each task that runs in your databases, open the properties and click the dropdown, then check the box as in this picture:

Changing SQL Server data file locations in clustered instance using service SIDs

Recently I added a second LUN to a clustered instance of SQL Server to isolate the logs files (and another for the TempDB) and I mounted them to an empty NTFS folder as I’ve done before, but when I started SQL Server, I recieved the following error:

CREATE FILE encountered operating system error 5 (Access is denied.)
while attempting to open or create the physical file
'X:\MSSQL10_50.INSTANCENAME\TempDB\tempdb.mdf

The error means that SQL Server doesn’t have NTFS rights to the location of the TempDB, but when I tried to add those rights, the permissions weren’t granted to the domain proxy account as I’d expected, but were instead granted to the service SID account, MSSQL$InstanceName. I attempted to grant the permissions to this account at the new location, but couldn’t get it to resolve to an actual account. What finally worked was:

  1. In the permissions change dialog box, change the “Location” from the domain to the local machine (even though it’s a cluster and your using a domain account to run the service)
  2. In the text box, type “NT Service\MSSQL$INSTANCENAME” and click “Check Names”

Even though that appears to be a local account, it will resolve properly on all the cluster nodes involved. This step, as opposed to using the domain proxy account the service is running as, was necessary because (during the initial SQL Server setup process) I’d selected to use the proxy account SID to host permissions rather than a domain group. The better choice permissions-wise, but the source of some confusion!

Clustering walkthrough for SQL Server 2008 on Windows 2008

I recently stumbled across a great walk-through for clustering SQL Server on newer versions of Windows. It’s really thorough – everything from setting up iSCSI (in this case, to simulate a shared disk when it’s physically attached to one node – not ideal, but lets you test the walk-through), adding the required server roles, preparing the servers, and then a walkthrough of every screen in the SQL installation process. Thanks to the writer of this awesome blog!

http://dbperf.wordpress.com/2010/07/10/walkthrough-cluster-setup-sql-win-2008/

Slow SQL Server queries over a linked server from Windows Server 2008

We recently ran into an issue where linked server queries where sometimes taking significantly longer than they should have – queries that executed in under a second on the source server took two minutes when executed from a remote SQL Server over a linked server connection. We assumed it was something to do with our Windows Failover Cluster network connections, but it was happening on non-clustered servers as well.

After some digging, we discovered that it happened when the source server was Windows Server 2008 (or 2008 R2), regardless of what OS the remote linked server was running, and the issue wasn’t tied to any particular version of SQL Server. As it turns out, it related to everybody’s least favorite network setting, TCP Auto-Tuning – disabling it on the affected source servers resolved the problem immediately, and the queries returned instantly again.

To remedy the situation, follow these steps:

  1. On the source SQL Server (the one executing the query, not the linked server), open an elevated command prompt (Right click on CMD.EXE and click “Run as Administrator”)
  2. To disable TCP Auto-tuning, run the following command:
    netsh int tcp set global autotuninglevel=disabled
  3. To disable Windows Scaling Heuristics (another feature that can speed up clients, but is no good for servers), run:
    netsh int tcp set heuristics disabled
  4. This will automatically take effect after Windows has the chance to refresh some network communications – you can either wait about an hour, or, if you’d prefer it take effect immediately, restart the SQL Instance. A reboot isn’t necessary for this setting to take effect.

I’m not sure why these settings are on by default for servers – they seem to speed up internet browsing and other client-type activities (though there’s no shortage of complaints online about them grinding Windows Vista/7 clients to a halt as well), but can cause big problems on servers.