Need a High Availability Option - But Have Std Edition 2

[JohnBates]

hi everyone,

We use SQl Server 2005 Standard.

I need to implement a good high-availability technique.

Is database mirroring working well for anyone that has chose that route? Do you have to use it in conjunction with any other feature (such as clustering)?

Is log-shipping better? Which is easier to set up?

Thanks, John

 

[JohnBates]

cjamodeo,

Yes I did find a way. I'll post back tomorrow when I'm at the office.

BTW mirroring works reliably. We did not use automatic failover, so it was not necessary to have a witness server. Quite a bit of effort to redirect the apps and clients over to the other server-that's why automatic failover would not work in our installation.

I'll post back tomorrow.
John

 

[JohnBates]

cjamodeo,

This is for 2005. There are 2 things to create to be able to recv the notification.

This script will create an Alert that will send an email whenever the mirroring state changes, gets broken etc.

USE [msdb]
GO
/****** Object: Alert [Database Mirroring State Change] Script Date: 04/29/2008 13:11:24 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Database Mirroring State Change',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1,
@include_event_description_in=1,
@notification_message=N'Ramco database mirroring may have stopped! ** Please investigate **',
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
@wmi_query=N'select * from database_mirroring_state_change WHERE STATE = 5 or STATE = 6 or STATE = 7 or STATE = 8 or STATE = 9 ',
@job_id=N'00000000-0000-0000-0000-000000000000'

Next, create a job to monitor the mirror state:

USE [msdb]
GO
/****** Object: Job [Ramco Database Mirroring Monitor Job] Script Date: 04/29/2008 13:13:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [System Check] Script Date: 04/29/2008 13:13:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'System Check' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'System Check'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Ramco Database Mirroring Monitor Job',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@category_name=N'System Check',
@owner_login_name=N'CORPORATE\YourLogin',
@notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [] Script Date: 04/29/2008 13:13:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'',
@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'exec sys.sp_dbmmonitorupdate ',
@database_name=N'msdb',
@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'Database Mirroring Monitor Schedule',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=2,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20071213,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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:

Give these a try, then break the mirror on you test server to see if you recv the Alert email.

John