Thursday, August 21, 2008

SQL Server : error detaching msdb

I received the following trying to detach the msdb database in SQL Server 2005 :

Cannot detach an opened database when the server is in minimally configured mode

I was trying to move msdb according to kb224071. I finally found why.

When I opened my query in SQL Server Management Studio (SSMS) I was connecting to msdb database by default. Event if I do a use master; after that, it was too late, I always got an error doing a sp_detach_bd 'msdb';

So here's the trick :

  • Click new query on the tool bar of SSMS
  • Click on the Options >> buton in the Connect to Server dialog
  • Select the Connection Properties tab
  • Type master in the Connect to database field
  • Click Connect

Et voilĂ  ! You will now be able to detach the msdb database.

Note : Of course this will only work if you follow kb224071 and start SQL Server with the -c -m -T3608 startup options.

Can't logon to SQL Server 2005 Cluster in single-user mode

Last night I spent a few hours trying to follow the kb224071's instructions to move the MSDB system database but was always getting the following error trying to connect with the -c -m -T3608 startup options :

Login failed for user '******'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: *.*.*.*]

But nobody else was using the database !

Seems that I was wrong. When I looked again at my error log I saw this line :

Login succeeded for user 'NT AUTHORITY\SYSTEM'. Connection: trusted. [CLIENT: *.*.*.*]

Then I tried the same thing in virtual without a cluster and everything worked fine, so I got a little suspicious about the cluster service.

So here's what I've done and it worked :

  1. Put all SQL Server resources offline in the cluster (except the storage of course).
  2. execute :
    NET START "SQL SERVER (instance)" /c /m /T3608
  3. You will now be able to connect to your database in single-user mode.
  4. When your finished, execute the following command and restart all SQL Server resources in the cluster :
    NET STOP "SQL SERVER (instance)"

I'm not an SQL engineer but it looks to me like the Cluster Service opened a connection to the SQL Server.

Hope this could help someone else.

FastCGI Extension - RTM

I misted that news, but looks like FastCGI has bean released to manufacturing.

Read more and download :

But Wait – There’s More! IIS 7.0 Extensions.

So far, we have made available 12 different Extensions as CTP, Go Live, or RTW releases. Each one is completely free and integrates fully with IIS 7.0. We even released versions of the FTP and WebDAV extensions for IIS 6.0, although you’ll see us do that less often in the future because we feel strongly that IIS 7.0 should be the Web platform of choice so that’s where we’ll concentrate our efforts. Here’s the full list:

Deployment

Management

Security

Request Handling

Content Publishing

Media Serving

Source : Windows Server Division Weblog

IBM TV

IBM TV

Cool ! lot of Tv shows about IBM technologies.

Check it out.

Wednesday, August 13, 2008

SQL Server 2005 Database Mail in cluster

Updated in december 2008 (see at the end)
I've been playing with Database Mail in my SQL 2005 Cluster for a few days now. I always get the following errors :

Database Mail Log:
Log Database Mail (Database Mail Log)
Last Modified By sa
Message : Activation failure.

EventViewer Application Log: Event Type: Information
Event ID:   9724
User:       N/A
Description: The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Last night I found a solution and I though it would be a good idea to share it.
As you know SQL Server use an external program to process mail : DatabaseMail90.exe. As I understand it, when SQL launch DatabaseMail90.exe it connect to the msdb database and look in the system tables for it's configuration and for messages to process.

First Problem : DatabaseMail90.exe connection

Looks like DatabaseMail90.exe is trying to connect to SQL using Localhost or ComputerName as it's SQL Server name. In a cluster you can't do that, you should use the Virtual SQL Server Name as define in your Cluster resources. Can we change that ? Not really.
But there is a workaround. Microsoft added a new feature in SQL SP2 to allow you to send mail from a remote server. We are going to use that feature to make DataBaseMail90.exe connect to the Virtual SQL Server (Cluster).
First we have to 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 :
<configuration>
    <appSettings>
        <add key="DatabaseServerName" value ="VirtualServerName\InstanceName" />
        <add key="DatabaseName" value ="msdb" />
    </appSettings>
</configuration>
Replace VirtualServerName\InstanceName by the valid names from your environment.
Don't forget your are in a cluster, so you should copy that new file on every node of your cluster.
Next we have to modify the DatabaseMail config to tell SQL Server to use the new file. We'll do that by running the following code in SQL Server Management Studio :
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'
   );
GO

Second problem : the 'sp_sysmail_activate' stored procedure

'sp_sysmail_activate' is the procedure that launch the DatabaseMail90.exe process. Normally when you updated your SQL Server to SP2 that procedure got updated to support the new DataBaseMail.exe.confg file. Don't ask me why but when you are in a cluster this is not the case (thanks FreemanRU for the tip).
So you'll have to update it by yourself. I have taken a copy of the 'sp_sysmail_activate' procedure from a working SQL server (non-cluster) SP2 with CU8 (build 3257). You can simple run the code in SQL Server Management Studio :
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
There's only one final step, you should restart the mail process with the follwing SQL command :
EXEC msdb.dbo.sysmail_start_sp;
You could always restart the SQL Server if you prefer.
You should now be able to send a test email from the Database Mail object in SQL Server Management Studio from your SQL Server 2005 SP2 in a cluster.

December 2008 update :

I missed something, in the ‘Second problem :’, ‘sys_mail_activate’ is not the only stored procedure that you have to update. There is a few others :
  • dbo.sp_ExternalMailQueueListener
  • dbo.sp_RunMailQuery
  • dbo.sp_send_dbmail
  • dbo.sp_sysmail_activate
  • dbo.sysmail_delete_mailitems_sp
  • dbo.sysmail_delete_profile_sp
  • dbo.sysmail_update_account_sp
  • dbo.sp_ProcessResponse
  • dbo.sp_validate_user
I’ve created a script to update those procedures to the same level as SQL Server 2005 SP2 with Cumulatives Updates 8. You can download my script here.
May I suggest you backup the msdb database before playing with dbmail procs.

Monday, August 11, 2008

Windows security rendered useless? Uh, not exactly

Oh dear. The Chicken Little contingent is out in full force. Break out your Kevlar helmets, everyone, because the sky is falling on Windows! At last week’s Black Hat conference in Las Vegas, researchers Alexander Sotirov and Mark Dowd presented a paper that outlined some new attack vectors they had discovered targeting some security features introduced in different versions of Windows XP and Windows Vista. It’s a fascinating paper, rich in technical detail and hewing to the Black Hat tradition of providing clues that others can follow to discover, exploit, and ultimately fix vulnerabilities in widely used computer code.

Read Ed Boot's article at blogs.zdnet.com

Debunking Common Windows Performance Tweaking Myths

As a tech writer, one of my biggest pet peeves is the plethora of bad advice littered across almost every web site dedicated to system tweaking. Besides the tweaks that simply don't work, some of them will actually cause your computer to run even slower—or worse. Let's examine some of the most offensive myths out there regarding PC performance tweaking, and debunk them once and for all.

Read the complete article at lifehacker.com

Thursday, August 07, 2008

Application virtualization : advantages & disadvantages

These days application virtualization is a hot topic. Michael Pietroforte form 4sysops blog has produce a list of advantage and of disadvantage for the new technology :

Saturday, August 02, 2008

Error 8024400E When Trying to Download Updates Via WSUS?

For a few days I'm not able to check for new updates from my WSUS server, I always get an error 8024400E. Strangely if I check for updates from Microsoft Update I've got no error.

A few computers (and servers) at my location got the same problem.

Today I've tired the workaround published by Cecilia Cole (WSUS Program Manager) and it WORKED !

Many thanks Cecilia,

Source : Client/Server Synchronization issues