In our production environment, we have two 4-node clusters.  One cluster runs at the primary site; the other cluster runs at our disaster recovery site.  Each cluster is running 11 SQL Server 2005 instances.

We setup Database Mail on all of the instances at both sites, so that we could e-mail internal customers the results of various ad-hoc queries.  It was soon realized that Database Mail was not working properly at the primary site on any of the 11 instances.  We got our Exchange, server, and network administrators involved, but we were unable to figure out what was wrong.  We never received any errors but yet no e-mail was ever received.  Since we were unable to resolve the issue, we decided to open a case with Microsoft.  One of the things that they had me to do was increase the logging level of Database Mail so that it was more verbose.  After the configuration change was made, I attempted to send an e-mail with Database Mail.  As usual, no error was returned but also the e-mail never made it out of SQL Server.  Microsoft wanted me to send them the Application Log in Event Viewer.  While preparing to do this, I noticed the following error in it:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Since remote connections was enabled on all of the instances, I decided to add an alias for one of the instances on all 4 nodes of the cluster.  This worked, so I reported my findings back to Microsoft.  After a bit of time, they reported back to me that the SQL Browser service was the culprit. 

They don't have an exact reason as to why the SQL Browser service is unable to connect to a local instance (meaning an instance that is on the node where the service is running), but they did provide possible reasons:

Clustering

SQL Server Browser is not a clustered resource and does not support failover from one cluster node to the other. Therefore, in the case of a cluster, SQL Server Browser should be installed and turned on for each node of the cluster. On clusters, SQL Server Browser listens on IP_ANY.

Note: When listening on IP_ANY, when you enable listening on specific IPs, the user must configure the same TCP port on each IP, because SQL Server Browser returns the first IP/port pair that it encounters.        

Using a Firewall

To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434, in addition to the TCP port used by SQL Server (e.g. 1433). For information about working with a firewall, see "How to: Configure a Firewall for SQL Server Access" in SQL Server Books Online.

If you are unable to get Database Mail to send e-mails when the SQL Server 2005 instance is on a cluster, try adding an alias for the virtual server.  You can easily add aliases using the SQL Server Configuration Manager tool.

T-SQL: Generate a list of dates
SQL Server 2005 | SQL Server | SQL | Common Table Expression | T-SQL | List of dates
Sometimes we can be working and we write or find a ridiculously simple bit of code that we know will be useful in the future, then we just go ahead and plain forget all about it. When I find such stuff I like to stick it on my blog so that I know where to find it in the future and also on the off chance that someone else might find it useful. Here is one such example.

The following bit of code uses a common table expression (CTE) to generate a contiguous list of dates in SQL Server.

    1 with mycte as
    2 (
    3     select cast('1900-01-01' as datetime) DateValue
    4     union all
    5     select DateValue + 1
    6     from    mycte    
    7     where   DateValue + 1 < '2050-12-31'
    8 )
    9 select DateValue
   10 from    mycte
   11 OPTION (MAXRECURSION 0)

Read on

Improving ASP.NET Session State database performance by reducing blocking
On a recent consulting engagement, I was working with a client that had significant performance issues with their ASP.NET session state database. They had a combination of both large session state and a large number of concurrent sessions. They were regularly experiencing command timeouts on that database.

In my investigation, curiously I found that a DELETE statement was the culprit. I tracked it to the DeleteExpiredSessions stored procedure. Looking at it, it seems tame enough:

CREATE PROCEDURE DeleteExpiredSessions
AS
  DECLARE @now DATETIME
  SET @now = GETUTCDATE()

  DELETE ASPState..ASPStateTempSessions
  WHERE Expires < @now 

  RETURN 0

GO

Read On

In the first table (even number of rows), the median rows are B and C. These can be matched based on [Ascending Column] IN ([Descending Column] + 1, [Descending Column] - 1). In the second table (odd number of rows), the median row is C, which is matched where [Ascending Column] = [Descending Column]. Note that in the second table, the match criteria for the first table does not apply -- so the generic expression to match either case is the combination of the two:  [Ascending Column] IN ([Descending Column], [Descending Column] + 1, [Descending Column] - 1).

We can apply this logic within the AdventureWorks database to find the median of the "TotalDue" amount in the Sales.SalesOrderHeader table, for each customer:

My friends in Microsoft’s Product Support Services (PSS) group (aka SQL Server Support) are speaking again this year at the PASS Community Summit.  They’re doing some main conference sessions, the PSS Boot camp, and putting on the PSS Service Center again (where attendees can work through hands-on labs designed by PSS that demonstrate how to troubleshoot SQL Server issues).  I’d encourage you to make your way to their offerings if you possibly can.  They’re always some of the best sessions and content at the conference.

They say the ultimate measure of success with an initiative is whether it survives the departure of the person or people who started it.  Since its inception in 2003, I had coordinated the involvement of PSS in the PASS Summit.  This year, I turned over the reigns to a group consisting of Todd DeDecker, a Group Manager within SQL Server Support, Bart Duncan, an Escalation Engineer within the support group and a good friend, and Haydn Richardson in SQL Server marketing.  I’d long felt that PSS mgmt and the SQL Server marketing folks (who handle the product group’s involvement in other conferences) should work together to handle PSS’ presence at the conference.  They had the charter and resources to do so, and I knew they could go to Bart for any questions they might have of a technical nature.  Once the initiative progressed from toddlerhood to adolescence, I felt it was ready to survive without my involvement.

Read on

I know that we all have heard about Upgrade Advisor from Microsoft.  Scalability Experts has just released a new FREE tool to assist in testing your application to verify behavior with SQL 2005.  It is called Upgrade Assistant.  Here is the link....  http://www.scalabilityexperts.com/default.asp?action=article&ID=317. 

SE has been using the forerunner of this tool over the past year around the world in Application Compatibility Labs where we assist ISVs determine what changes are needed in their apps in order to upgrade to SQL Server 2005.  This version of the tool is much more refined than the one I used in the labs.  I hope that you find it useful.

Over on the Microsoft.Public.SqlServer.XML newsgroup, one Chris Kilmer asked a good question about how to shred a single XML document into multiple tables using multiple stored procedures. Chris's goal with this was to have each stored procedure update one table and pass the XML remaining nodes of the first document off to the next stored procedure. This is actually fairly easy except for one thing: maintaining referential activity between inserted elements.

The XML involved is fairly simple:

Click here to get more