Installing SQL Server 2008 failover cluster in Windows 2008 R2 cluster

Prerequisites

  1. SQL Server 2008 Standard/Enterprise/Developer edition install Media with Service pack 2.
  2. One IP address as SQL Cluster virtual IP and host-name as SQL Cluster virtual host-name. Our clients will have to use this host-name or IP to connect with this fail-over instance.
  3. One shared disk to place all our System DB files + error-logs etc
  4. Minimum two identical clustered Windows 2008 Servers.
  5. … Will be adding more. Couldn’t remember all of them at one shot. Please add any missed out points below as comments. Thanks in advance.

Method

  1. Log-in to any one node and move all the cluster resources to that node.
  2. Navigate to the media directory and execute the below command. This is to make sure we don’t move all the SQL server binary files into C drive.
    setup.exe /ACTION=INSTALLFAILOVERCLUSTER /INSTALLSHAREDDIR=”J:\Program Files\Microsoft SQL Server” /INSTALLSHAREDWOWDIR=”J:\Program Files (x86)\Microsoft SQL Server”.
  3. Follow the instructions on the setup wizard.

To be continue…

SQL Configuration Change Alerting

The below script helps in detecting, recording and alerting when ever a change is made to sp_config or SQL server configuration settings. This will help in pin-pointing any unnecessary changes happening to Our Beloved SQL Server…

create database HCLDBA go ALTER DATABASE [HCLDBA] SET RECOVERY SIMPLE WITH NO_WAIT

GO

use HCLDBA goSET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SQLConfig_Changes]( [TextData] [varchar](500) NULL, [HostName] [varchar](155) NULL, [ApplicationName] [varchar](255) NULL, [DatabaseName] [varchar](155) NULL, [LoginName] [varchar](155) NULL, [SPID] [int] NULL, [StartTime] [datetime] NULL, [EventSequence] [int] NULL ) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SET NOCOUNT ON

GO

CREATE PROCEDURE dbo.usp_Capture_SQL_Config_Changes @SendEmailTo VARCHAR(255) AS CREATE TABLE #temp_cfg ( TEXTData VARCHAR(500), HostName VARCHAR(155), ApplicationName VARCHAR(255), DatabaseName VARCHAR(155), LoginName VARCHAR(155), SPID INT, StartTime DATETIME, EventSequence INT )

DECLARE @trc_path VARCHAR(500), @message VARCHAR(MAX), @message1 VARCHAR(MAX), @textdata VARCHAR(1000)

SELECT @trc_path=CONVERT(VARCHAR(500),value) FROM fn_trace_getinfo(DEFAULT) WHERE property=2

INSERT INTO #temp_cfg SELECT TEXTData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence FROM fn_trace_gettable(@trc_path,1) fn WHERE TEXTData LIKE ‘%Configur%’ AND SPID<>@@spid AND fn.EventSequence NOT IN (SELECT EventSequence FROM SQLConfig_Changes) AND TEXTData NOT LIKE ‘%Insert into #temp_cfg%’ ORDER BY StartTime DESC

INSERT INTO dbo.SQLConfig_Changes SELECT * FROM #temp_cfg

/*select TextData,HostName,ApplicationName,DatabaseName,LoginName,SPID,StartTime,EventSequence from fn_trace_gettable(@trc_path,1) fn where TextData like ‘%Configur%’ and SPID<>@@spid and fn.EventSequence not in (select EventSequence from SQLConfig_Changes) order by StartTime desc*/

–select * from SQLConfig_Changes

IF @@ROWCOUNT > 0 –select @@ROWCOUNT

BEGIN DECLARE c CURSOR FOR

SELECT LTRIM(REPLACE(SUBSTRING(TEXTData,31,250), ‘. Run the RECONFIGURE statement to install.’, ”)) FROM #temp_cfg

OPEN c

FETCH NEXT FROM c INTO @textdata

WHILE (@@FETCH_STATUS <> -1) BEGIN –FETCH c INTO @textdata

SELECT @message = @textdata + ‘on server ‘ + @@servername + CHAR(13)

EXEC msdb.dbo.sp_send_dbmail –@profile_name=’ProfileName – otherwise will use default profile’, @profile_name=’DBA’, @recipients=@SendEmailTo, @subject=’SQL Server Configuration Change Alert’, @body=@message

FETCH NEXT FROM c INTO @textdata

END CLOSE c DEALLOCATE c

END

DROP TABLE #temp_cfg

go

USE [msdb]

GO

/****** Object: Job [Detect Config Change] Script Date: 08/17/2011 12:22:12 ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [Data Collector] Script Date: 08/17/2011 12:22:12 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Data Collector’ AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Data Collector’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Detect Config Change’, @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N’No description available.’, @category_name=N’Data Collector’, @owner_login_name=N’DOMAIN\USER’, @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /****** Object: Step [Run the Stored Proc] Script Date: 08/17/2011 12:22:13 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run the Stored Proc’, @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N’TSQL’, @command=N’USE [HCLDBA] GO

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_Capture_SQL_Config_Changes] @SendEmailTo = N”emailid@domain.com”

SELECT

”Return Value” = @return_value

GO’, @database_name=N’HCLDBA’, @flags=0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Detect Config Change’, @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20110817, @active_end_date=99991231, @active_start_time=90000, @active_end_time=235959, @schedule_uid=N’deb2afc2-d84e-47fd-af68-661f550ad5a5′ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N’(local)’ IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:

GO