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

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

Ok, so I haven't actually left my house yet, or even finished packing yet (I must carry like two miles of wires in my backpack and suitcase!) but the experience begins.  In this case it has meant a dearth of new blogs from the folks that have signed up to be my guinea pigs for the blog aggregation site.  If you are a PASS member and have a blog (if you attend the conference, you are a member for the next year,) send me an email at louis_davidson@sqlpass.org and I will get your blog up there. 

Read More

As you might expect, yesterday's  announcement of the .NET Integrated Query Framework (LINQ) certainly has my attention. Its pretty hard to put down more than a stream of thought about this right now, but here's some of the implications as I see them.

• Its cool because, as a developer, I'll be able to express my queries in-line as part of the program
• ...

Read More

The 2005 PASS Community Summit in Grapevine, Texas is just two weeks away!  If you haven't started to prepare for it yet, NOW is the time!  I assume (as dangerous as that is) that by now you have already completed your registration online and made your hotel reservations.  If not, stop reading and go do that now.  I'll wait here for you...

...OK, now what's next?  I like to preview the schedule of sessions to be presented. ...

Read More