Lessons learned ... clustering SQL 2005 on a Windows 2008 (x64) cluster

Last couple of days have been verry stressful. I've just finished a clustered install of SQL2005 on a Win2008. I'll be talking in this post about the issues i had.

The first problem I had was getting to know Win2008. I know according to Microsoft that everything has improved but the challenge for us professionals is to keep up with the fact that some things don't have the same name or are completely on a different location. So I lost quite some valuable time on calling around asking a Windows system engineer with the questions "where can I find ..." and "How do I do ...". Yes, I felt like a complete idiot.

First I had to set up the MSDTC cluster resource, in Win2008 there is a wizard for that. I used it and it worked fine.

Then I had to add my clustered disks ... formatting took like eternity. No I 'm kidding but it took a while and was very impatient to go on.

Once all that was done I organised the cluster resources and started the SQL Server install. It went as planned. I ticked the box to tell it was a cluster install and continued on my quest.

The first error message I got was this one







It is actually the full-text search service that is down and it is simply solved by installing SQL 2005 SP2. After clicking ok I ran into a next error.












It is stupid but I needed to install Visual Studio 2005 SP1 (I only found x86) to solve this one. I think this happens because the SQL Server Managament Studio is written in 32-bit and I was working on x64. I know in Windows 2003 this is no issue but appearently on Windows 2008 it becomes one.

After fixing this I installed SQL 2005 SP2 and that has a problem on its own. I noticed that I was not able to make maintenance plans. I got these nice screenshots:


























This is what happend: for some reason, don't ask me why it seems that the resource database had trouble to update in SP2. SP2 appearently changed some things ...

If you run in to this, you can verify the version of your resource databases with this query:

SELECT SERVERPROPERTY('ResourceVersion');

When I ran that query It answered me 9.00.1399 which is the RTM version (RTM is how it is shipped the first day your version is sold). Okay, that was usefull info but how to solve this was a mystery to me. I crused around on the information high way and found out that all it took wat to manually run the queries that are located in the "sysdbupg" script. This script can be found in "C:\Program Files\Microsoft SQL Server\MSSQL\Install". I ran it and got the maintenance plans back.

The last issue i had was an issue with database mail. Luckely for me there are nice people who blog and at Jean-Pierre Paradis' Blog I found what I was looking for. I got an activation failure.

I just copied Jean-Pierre's solution and it worked. I repeat it here if for some reason you would not be able to get to his blog.

First create the text file DataBaseMail90.exe.config in the \MSSQL\Binn folder of your SQL Instance (ex: \Program Files\Microsoft SQL Server\MSSQL.1\MSSQLSERVER\MSSQL\Binn). with the following content :










Next thing to do is to load this in SSMS:

USE msdb;
GO
INSERT INTO [msdb].[dbo].[sysmail_configuration]
(
[paramname]
,[paramvalue]
,[description]
)
VALUES
(
N'ReadFromConfigurationFile'
,N'1'
,N'Send mail from mail server in configuration file'
);

Then I replaced this stored procedure:

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_sysmail_activate] Script Date: 12/01/2008 15:41:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE [dbo].[sp_sysmail_activate]
AS
BEGIN
DECLARE @mailDbName sysname
DECLARE @mailDbId INT
DECLARE @mailEngineLifeMin INT
DECLARE @loggingLevel nvarchar(256)
DECLARE @loggingLevelInt int
DECLARE @parameter_value nvarchar(256)
DECLARE @localmessage nvarchar(max)
DECLARE @rc INT

EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime',
@parameter_value = @parameter_value OUTPUT
IF(@rc <> 0)
RETURN (1)

--ConvertToInt will return the default if @parameter_value is null or config value can't be converted
--Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)

--Try and get the optional logging level for the DatabaseMail process
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel',
@parameter_value = @loggingLevel OUTPUT

--Convert logging level into string value for passing into XP
SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
IF @loggingLevelInt = 1
SET @loggingLevel = 'Normal'
ELSE IF @loggingLevelInt = 3
SET @loggingLevel = 'Verbose'
ELSE -- default
SET @loggingLevel = 'Extended'

SET @mailDbName = DB_NAME()
SET @mailDbId = DB_ID()

EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @mailEngineLifeMin, @loggingLevel
IF(@rc <> 0)
BEGIN
SET @localmessage = FORMATMESSAGE(14637)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
END
ELSE
BEGIN
SET @localmessage = FORMATMESSAGE(14638)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
END

RETURN @rc
END

with this one

USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_sysmail_activate] Script Date: 08/13/2008 11:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- sp_sysmail_activate : Starts the DatabaseMail process if it isn't already running
--
ALTER PROCEDURE [dbo].[sp_sysmail_activate]

AS
BEGIN
DECLARE @mailDbName sysname
DECLARE @mailDbId INT
DECLARE @mailEngineLifeMin INT
DECLARE @loggingLevel nvarchar(256)
DECLARE @loggingLevelInt int
DECLARE @parameter_value nvarchar(256)
DECLARE @localmessage nvarchar(max)
DECLARE @readFromConfigFile INT
DECLARE @rc INT

SET NOCOUNT ON
EXEC sp_executesql @statement = N'RECEIVE TOP(0) * FROM msdb.dbo.ExternalMailQueue'

EXEC @rc = msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime',
@parameter_value = @parameter_value OUTPUT
IF(@rc <> 0)
RETURN (1)

--ConvertToInt will return the default if @parameter_value is null or config value can't be converted
--Setting max exe lifetime is 1 week (604800 secs). Can't see a reason for it to ever run longer that this
SET @mailEngineLifeMin = dbo.ConvertToInt(@parameter_value, 604800, 600)

EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'ReadFromConfigurationFile',
@parameter_value = @parameter_value OUTPUT
--Try to read the optional read from configuration file:
SET @readFromConfigFile = dbo.ConvertToInt(@parameter_value, 1, 0)

--Try and get the optional logging level for the DatabaseMail process
EXEC msdb.dbo.sysmail_help_configure_value_sp @parameter_name = N'LoggingLevel',
@parameter_value = @loggingLevel OUTPUT

--Convert logging level into string value for passing into XP
SET @loggingLevelInt = dbo.ConvertToInt(@loggingLevel, 3, 2)
IF @loggingLevelInt = 1
SET @loggingLevel = 'Normal'
ELSE IF @loggingLevelInt = 3
SET @loggingLevel = 'Verbose'
ELSE -- default
SET @loggingLevel = 'Extended'

SET @mailDbName = DB_NAME()
SET @mailDbId = DB_ID()

EXEC @rc = master..xp_sysmail_activate @mailDbId, @mailDbName, @readFromConfigFile,
@mailEngineLifeMin, @loggingLevel
IF(@rc <> 0)
BEGIN
SET @localmessage = FORMATMESSAGE(14637)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=3, @description=@localmessage
END
ELSE
BEGIN
SET @localmessage = FORMATMESSAGE(14638)
exec msdb.dbo.sysmail_logmailevent_sp @event_type=0, @description=@localmessage
END

RETURN @rc
END

Start the procedure with the follwing SQL command :

EXEC msdb.dbo.sysmail_start_sp;

Finally that was the last issue I had. Thanks to everybody who helped me, like always in our business it is a matter of looking at a problem with as many as possible.