Wednesday, December 31, 2008

Happy New Year ! ... hang on a second

Hello,

It is only matter of a couple of hours here in Brussels. Yesterday they said on the Belgian news that worldwide the atomic clocks will be stopped for one second at midnight since the earth is slowing down.

So before you jump up and shout Happy New Year take a second for yourself. You've deserved it :).

Happy New Year folks!

Tuesday, December 9, 2008

Netstumbling WiFi APs

A couples of evenings ago at I had to drive through a nicely populated area just outside Brussels. I wanted to see for my self what my build-in WiFi could find. I used netstumbler to see what was in the air.

I've found 1228 WiFi connections of which were 349 wide open with no form of protection. I looked at netstumbler and it picked up more signals each time I approached the center of a town. It is probably correct to say that there are more chances to find concentrations of networks in towncenters but I have to tell you as well that you drive slower so the laptop had more time to pickup signals.

In the 1228 WiFi connections there were 8 peer-to-peer connections of only 1 had a form of protection. There were 2 hpsetup, this is usually an SSID for an HP printer. One other peer-to-peer was also a printer according to the SSID.

The majority of connections were either 11 Mbps or 54 Mbps. (The maximum my card does is 54 Mbps). The brands I encountered were 3Com, Apple, bbox2 (Belgacom), Belkin, D-link, Hercules, Philips, Linksys, Mobistar, Netgear, Sagem, Thomson, Sweex, Topcom, US Robotics, ZyXel, Nokia.

Wednesday, December 3, 2008

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.