A few weeks ago, we were preparing to move our SQL Server 2005 databases from a 4-node Windows 2003 cluster to three 2-node Windows 2008 clusters. On the new clusters, we noticed that Database Mail and the monitoring application for Database Mirroring weren’t working on four of the SQL Server 2005 instances that we had installed. For the monitoring issue, we found that we were missing all of the sys.dbmmonitor* system stored procedures in the msdb database. We had installed six SQL Server 2005 instances across the six servers using the same media on each, so we were very perplexed as to why the issue was happening on four of the instances and not on all six. After doing our own research and coming up dry, we decided to open a case with Microsoft Product Support Services (PSS).
The Microsoft engineer assigned to our PSS case determined that the issue was due to five of the server names being in lower case. This caused the Resource database to not get patched when we installed service pack 3 and then October’s critical SQL hotfix (MS09-062). The Resource database was still at the RTM build, 1399, but it should have been at build 4262 like everything else.
When we first heard what the root cause was, our reaction was to laugh as this sounded absurd. I confirmed the bug with another Microsoft engineer just to be safe. Unfortunately there is no bug fix for it, but there is a workaround/solution which is documented here. The workaround involves evicting the nodes with lower case server names, upper casing the server names, and then joining the fixed node back into the cluster. After this is complete, you then reapply the service pack or hotfix. We only needed to reapply the hotfix.
The reason why we only encountered the issues on four of the six SQL Server instances that we installed is because on our third cluster one of the nodes had its server name in upper case. SQL Server 2005, service pack 3, and MS09-062 were installed from the server whose name was in upper case. Had we performed the installations on the other node with a lower case server name, we would have encountered the issue there too. We decided to perform the workaround on the working cluster since we could run into this issue again if we forgot to perform the SQL Server 2005 installations from the server with an upper case name. You do not need to perform the workaround on any servers that are properly named in all upper case.