Agent Jobs

Automatically Failover All Database Mirrors With A Single Job (10 of 10)

JOB:  DATABASE MIRRORS – FAILOVER ALL MIRRORED DATABASES

Automatically Failover All Database Mirrors With A Single Job_10

STEP 10:  FINISH MIRROR FAILOVER PROCESS

Step logic:

 

use msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it's not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure 'show advanced options',1
		reconfigure;
		exec master..sp_configure 'database mail xps',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@description		= 'SQLDatabaseMail';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= 'sqldatabasemail@MyDomain.com'
		,	@email_address		= 'sqldatabasemail@MyDomain.com'
		,	@mailserver_name	= 'MySMTPServer.MyDomain.com'  
		--,	@port			= ####		--optional
		--,	@enable_ssl		= 1		--optional
		--,	@username		='MySQLDatabaseMailProfile' --optional
		--,	@password		='MyPassword'	--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@account_name		= 'sqldatabasemail@MyDomain.com'
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= 'SQLDatabaseMailProfile'
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare @get_basic_server_name		varchar(255) = (select cast(serverproperty('servername') as varchar(255)))
		declare @get_basic_server_name_and_instance_name	varchar(255) set @get_basic_server_name_and_instance_name = (select  replace(cast(serverproperty('servername') as varchar(255)), '\', '   SQL Instance: '))
		declare @basic_test_subject_message		varchar(255) set @basic_test_subject_message = 'Test SMTP email from SQL Server: ' + @get_basic_server_name_and_instance_name
		declare @basic_test_body_message		varchar(max) = 'This is a test SMTP email from SQL Server:  ' + @get_basic_server_name_and_instance_name + char(10) + char(10) + 'If you see this.  It''s working perfectly :)'

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		EXEC msdb.dbo.sp_send_dbmail
			@profile_name	= 'SQLDatabaseMailProfile'
		,	@recipients	= 'SQLJobAlerts@MyDomain.com'
		,	@subject	= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- Confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare
	@server_name_basic		varchar(255) = (select cast(serverproperty('servername') as varchar(255)))
,	@server_name_instance_name	varchar(255) = (select  replace(cast(serverproperty('servername') as varchar(255)), '\', '   SQL Instance: '))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary	varchar(255) = ( select @@servername )
,	@secondary	varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex('.', mirroring_partner_name) - 1), 'TCP://', '') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance	varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness	varchar(255) = ( select top 1 case mirroring_witness_name when '' then 'None configured' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set	@message_subject		= 'Mirror Failover Process (2 of 2) has completed.  Failover was successful to Server: ' + @server_name_instance_name

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- create table for mirrored databases
if object_id('tempdb..#mirrored_databases') is not null
	drop table #mirrored_databases

create table #mirrored_databases
	(
		[current_server]	varchar(255)
	,	[database]	varchar(255)
	,	[current_state]	varchar(255)
	,	[partner_name]	varchar(255)
	)

----------------------------------------------------------------------------------------
-- populate table for mirrored databases
insert into #mirrored_databases
select
	'current_server'= @primary
,	'database'	= upper(DB_NAME(database_id))
,	'current_state'	= mirroring_state_desc
,	'partner_name'	= mirroring_partner_name
from
	sys.database_mirroring
where
	mirroring_guid is not null
order by
	db_name(database_id) asc

----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top			NVARCHAR(MAX)
declare @xml_mid			NVARCHAR(MAX)
declare @body_top			NVARCHAR(MAX)
declare @body_mid			NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td's
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select
			[current_server]as 'td'
		,	''
		,	[database]	as 'td'
		,	''
		,	[current_state]	as 'td'
		,	''
		,	[partner_name]	as 'td'
		,	''

		from  #mirrored_databases
		order by [database] asc 
		for xml path('tr')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td's
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as 'td'
		,	''
		,	[Column2]	as 'td'
		,	''
		,	[Column3]	as 'td'
		,	''
		,	[...]	as 'td'

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path('tr')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		'<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<h1>' + @message_subject + '</h1>


<h1>


		The Mirror Failover Process has successfully completed.  Primary (Principal) server is:  <font color="blue">'	+ @primary		+ '.  </font> The Secondary (Mirror) now is Server:  <font color="blue">' + @secondary		+
						case
							when @secondary <> @instance then @secondary + '\' + @instance
							else ''
						end + '				</font>
		

		</h1>

		 

<h1></h1>


<table border = 1>

<tr>

<th> Server	</th>


<th> Database	</th>


<th> Current State	</th>


<th> Process	</th>

		</tr>

'

set @body_top = @body_top + @xml_top +
/* '</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

'		
+ @xml_mid */ 
'</table>


<h1>This process is driven by the Job ( DATABASE MIRRORS - Failover All Mirrored Databases )</h1>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:' + @server_name_basic + '</h1>

'
+ '</body></html>'

----------------------------------------------------------------------------------------
-- send email.

exec msdb.dbo.sp_send_dbmail
	@profile_name		= 'SQLDatabaseMailProfile'
,	@recipients		= 'SQLJobAlerts@MyDomain.com'
,	@subject		= @message_subject
,	@body			= @body_top
,	@body_format		= 'HTML';


drop table #mirrored_databases

Here’s the .sql file for the Job and all it’s steps.

create_database_mirror_failover_job.sql.pdf

As a reminder; You’ll need to replace just a few things in this logic before running it.

After you open the .sql file in Management Studio do a find and replace for the following items:

Find: MyDomain.com
Replace with: YourDomain.com

Find: MySMTPServer.MyDomain.com
Replace with: YourSMTPServer.YourDomain.com

Find: @recipients = ”SQLJobAlerts@MyDomain.com”
Replace with: @recipients = ”YourEmailAddress@YourDomain.com”
Note: It’s recommended that you use a distribution group for this

Everything else is dynamic and will pull server names from system tables. Theirs not much you need to do here.

You might want to change the security context that the Jobs are running under. You can replace the ‘sa’ cause you might have that disabled, and are running your Agent Jobs under another account. If thats the case… Do this:

Find: @owner_login_name=N’sa’
Replace with: @owner_login_name=N’SomeOtherLogin’

Thats it.

Hope it works for you. As usual with any process; you might need to tweak it for your needs. This is NOT intended as some officially supported operation. There is always another way of doing things. Some might be better. Some maybe not so much.

https://mikesdatawork.wordpress.com/2015/10/29/automatically-failover-all-database-mirrors-with-a-single-job-1-of-10/

If you want to try your hand at the ridiculous syntax formatting of in this blog on the total Job script here you go.

/***********************************************************************************/
/***********************************************************************************/
/******                  CREATE_DATABASE_MIRROR_FAILOVER_JOB                  ******/
/******                                                                       ******/
/******                           ( mikes data work )                         ******/
/******                                                                       ******/
/******                  https://mikesdatawork.wordpress.com                  ******/
/******                                                                       ******/
/***********************************************************************************/
/***********************************************************************************/

/****** Object:  Job [DATABASE MIRRORS - Failover All Mirrored Databases]     ******/

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE 	@ReturnCode INT
SELECT		@ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]     ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC	@ReturnCode = msdb.dbo.sp_add_category 
		@class=N'JOB', 
		@type=N'LOCAL', 
		@name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE 	@jobId				BINARY(16)
EXEC		@ReturnCode			=  msdb.dbo.sp_add_job 
@job_name			=N'DATABASE MIRRORS - Failover All Mirrored Databases', 
		@enabled			=1, 
		@notify_level_eventlog	=0, 
		@notify_level_email		=0, 
		@notify_level_netsend	=0, 
		@notify_level_page		=0, 
		@delete_level			=0, 
		@description			=N'This Job will only failover the Databases that are currently running as PRINCIPAL on this Server.  Those that are NOT PRINCIPAL will be ignored.', 
		@category_name		=N'[Uncategorized (Local)]', 
		@owner_login_name		=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Confirm Primary Server]     ******/
EXEC		@ReturnCode 			= msdb.dbo.sp_add_jobstep 
@job_id			=@jobId, 
@step_name			=N'Confirm Primary Server', 
		@step_id			=1, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####  				--optional
		--,	@enable_ssl		= 1 					--optional
		--,	@username		=''MySQLDatabaseMailProfile'' 	--optional
		--,	@password		=''MyPassword'' 			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare	@get_basic_server_name			varchar(255)
		declare	@get_basic_server_name_and_instance_name	varchar(255)
		declare	@basic_test_subject_message			varchar(255)
		declare	@basic_test_body_message			varchar(max)
		set		@get_basic_server_name			= (select cast(serverproperty(''servername'') as varchar(255)))
		set		@get_basic_server_name_and_instance_name	= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		set		@basic_test_subject_message			= ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		set		@basic_test_body_message			= ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name	= ''SQLDatabaseMailProfile''
		,	@recipients	= ''SQLJobAlerts@MyDomain.com''
		,	@subject	= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on

----------------------------------------------------------------------------------------
-- confirm principal and mirror servers
declare
	@confirm_mirror_server	varchar(255) = ( select @@servername )
,	@confirm_principal_server	varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance			varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness			varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- create confirmation email message

declare	
	@confirm_server_message_subject	varchar(max) = ''You are attempting to run the Mirror Failover Process from the Secondary Server: '' + @confirm_mirror_server + ''.  The Failover process is typically run from the Primary Server"'' + @confirm_principal_server
,	@confirm_server_message_body	varchar(max) = ''You are attempting to run the Mirror Failover Process from the Secondary Server: '' + @confirm_mirror_server + ''.  There are presently no Principal Databases on this Server.  This Job will now cancel on this local server, and execute the Failover process instead from the Primary Server '' + @confirm_principal_server + ''. A notification will be sent out automatically from the Primary server when the process begins.''

if not exists (select top 1 database_id  from sys.database_mirroring where mirroring_role_desc = ''PRINCIPAL'')
	begin
		exec msdb.dbo.sp_send_dbmail
			@profile_name	= ''SQLDatabaseMailProfile''
		,	@recipients	= ''SQLJobAlerts@MyDomain.com''
		,	@subject	= @confirm_server_message_subject
		,	@body		= @confirm_server_message_body

		waitfor delay ''00:00:5'';
		
		if not exists(select value from master.sys.configurations where name = ''show advanced options'')
			begin
				exec master..sp_configure ''show advanced options'', 1; reconfigure with override
			end

		if not exists(select value from master.sys.configurations where name = ''xp_cmdshell'')
			begin
				exec master..sp_configure ''xp_cmdshell'', 1; reconfigure with override
			end

		declare
			@retcode		int 
		,	@job_name		varchar(255)
		,	@step_name		varchar(255)
		,	@server_name		varchar(255)
		,	@query			varchar(8000) 
		,	@cmd			varchar(8000)
		set	@job_name		= ''DATABASE MIRRORS - Failover All Mirrored Databases''
		set 	@step_name		= ''Start Mirror Failover Process''
		set 	@server_name		= @confirm_principal_server
		set 	@query			= ''exec msdb.dbo.sp_start_job @job_name = ''''''	+ @job_name + '''''', @step_name = '''''' + @step_name + '''''''' 
		set 	@cmd			= ''osql -E -S '' + @server_name + '' -Q "''		+ @query + ''"'' 

		print '' @job_name		= ''	+isnull(@job_name,		''NULL @job_name'') 
		print '' @server_name	= ''	+isnull(@server_name,	''NULL @server_name'') 
		print '' @query		= ''	+isnull(@query,			''NULL @query'') 
		print '' @cmd			= ''	+isnull(@cmd,			''NULL @cmd'')

		--exec	@retcode = xp_cmdshell @cmd

		if @retcode <> 0 or @retcode is null 
			begin 
				print ''xp_cmdshell @retcode = ''+isnull(convert(varchar(20),@retcode),''NULL @retcode'')
			end

		raiserror(''50005 Mirror Failover Warning.  Mirror Database Failover was initiated from the Mirror Server.  Process will instead be executed on the Primary Server'', 16, -1, @@servername )
	end', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Start Mirror Failover Process]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Start Mirror Failover Process', 
		@step_id			=2, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####					--optional
		--,	@enable_ssl		= 1					--optional
		--,	@username		=''MySQLDatabaseMailProfile'' 	--optional
		--,	@password		=''MyPassword''			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare @get_basic_server_name				varchar(255) = (select cast(serverproperty(''servername'') as varchar(255)))
		declare @get_basic_server_name_and_instance_name	varchar(255) set @get_basic_server_name_and_instance_name = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		declare @basic_test_subject_message			varchar(255) set @basic_test_subject_message = ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		declare @basic_test_body_message				varchar(max) = ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name	= ''SQLDatabaseMailProfile''
		,	@recipients	= ''SQLJobAlerts@MyDomain.com''
		,	@subject	= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare
	@server_name_basic		varchar(255) = (select cast(serverproperty(''servername'') as varchar(255)))
,	@server_name_instance_name	varchar(255) = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary		varchar(255) = ( select @@servername )
,	@secondary		varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance		varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness		varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set		@message_subject		= ''Mirror Failover Process (1 of 2) has started on Server: '' + @server_name_instance_name

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- create table for mirrored databases
if object_id(''tempdb..#mirrored_databases'') is not null
	drop table #mirrored_databases

create table #mirrored_databases
	(
		[current_server]	varchar(255)
	,	[database]		varchar(255)
	,	[current_state]	varchar(255)
	,	[process]		varchar(255)
	)

----------------------------------------------------------------------------------------
-- populate table for mirrored databases
insert into #mirrored_databases
select
	''current_server''	= @primary
,	''database''		= upper(DB_NAME(database_id))
,	''current_state''	= mirroring_state_desc
,	''process''		= ''Failing over to Server: '' + @secondary
from
	sys.database_mirroring
where
	mirroring_guid is not null
order by
	db_name(database_id) asc

----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top		NVARCHAR(MAX)
declare @xml_mid		NVARCHAR(MAX)
declare @body_top		NVARCHAR(MAX)
declare @body_mid		NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td''s
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select
			[current_server]	as ''td''
		,	''''
		,	[database]		as ''td''
		,	''''
		,	[current_state]	as ''td''
		,	''''
		,	[process]		as ''td''
		,	''''

		from  #mirrored_databases
		order by [database] asc 
		for xml path(''tr'')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td''s
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as ''td''
		,	''''
		,	[Column2]	as ''td''
		,	''''
		,	[Column3]	as ''td''
		,	''''
		,	[...]		as ''td''

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path(''tr'')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		''<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<h1>'' + @message_subject + ''</h1>


<h1>


		The database Mirror Failover Process has started on Server:  <font color="blue">''	+ @primary		+ ''.  </font> The following databases will failover to the secondary Server:  <font color="blue">'' + @secondary		+
						case
							when @secondary <> @instance then @secondary + ''\'' + @instance
							else ''''
						end + ''				</font>
		

		</h1>

		 

<h1></h1>


<table border = 1>

<tr>

<th> Server		</th>


<th> Database		</th>


<th> Current State	</th>


<th> Process		</th>

		</tr>

''

set @body_top = @body_top + @xml_top +
/* ''</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

''		
+ @xml_mid */ 
''</table>


<h1>This process is driven by the Job ( DATABASE MIRRORS - Failover All Mirrored Databases )</h1>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:'' + @server_name_basic + ''</h1>

''
+ ''</body></html>''

----------------------------------------------------------------------------------------
-- send email.

exec msdb.dbo.sp_send_dbmail
	@profile_name		= ''SQLDatabaseMailProfile''
,	@recipients		= ''SQLJobAlerts@MyDomain.com''
,	@subject		= @message_subject
,	@body			= @body_top
,	@body_format		= ''HTML'';


drop table #mirrored_databases

', 
		@database_name			=N'master', 
		@flags					=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set Full Safety on all Mirrored Databases ( Required before failover )]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Set Full Safety on all Mirrored Databases ( Required before failover )', 
		@step_id			=3, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 master;
set nocount on

declare
	@set_full_safety_on_mirror_databases	varchar(max) = ''''
select
	@set_full_safety_on_mirror_databases	= @set_full_safety_on_mirror_databases + 
''alter database ['' + cast(DB_NAME(database_id) as varchar(255)) + ''] set safety full;''		+ char(10)
from
	sys.database_mirroring
where
	mirroring_guid is not null
	and mirroring_role_desc = ''PRINCIPAL''

--exec (@set_full_safety_on_mirror_databases)', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Confirm Full Safetys]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Confirm Full Safetys', 
		@step_id			=4, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####					--optional
		--,	@enable_ssl		= 1					--optional
		--,	@username		=''MySQLDatabaseMailProfile''	--optional
		--,	@password		=''MyPassword''			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare	@get_basic_server_name				varchar(255)
		declare	@get_basic_server_name_and_instance_name		varchar(255)
		declare	@basic_test_subject_message				varchar(255)
		declare	@basic_test_body_message				varchar(max)
		set		@get_basic_server_name				= (select cast(serverproperty(''servername'') as varchar(255)))
		set		@get_basic_server_name_and_instance_name		= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		set		@basic_test_subject_message				= ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		set		@basic_test_body_message				= ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@recipients		= ''SQLJobAlerts@MyDomain.com''
		,	@subject		= @basic_test_subject_message
		,	@body			= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare	@server_name_basic		varchar(255)
declare	@server_name_instance_name	varchar(255)
set		@server_name_basic		= (select cast(serverproperty(''servername'') 		as varchar(255)))
set		@server_name_instance_name	= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary	varchar(255) = ( select @@servername )
,	@secondary	varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance	varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness	varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set		@message_subject		= ''Failover error found on Server:  '' + @server_name_instance_name + ''  - Not all mirrored databases were set to full safety prior to failover.''

----------------------------------------------------------------------------------------
-- create table to hold mirror operating modes

if object_id(''tempdb..#mirror_operating_modes'') is not null
	drop table #mirror_operating_modes

create table #mirror_operating_modes
	(
	[server]		varchar(255)
,	[database]		varchar(255)
,	[synchronous_mode]	varchar(255)
)

----------------------------------------------------------------------------------------
-- populate table #mirror_operating_modes

insert into #mirror_operating_modes
select
	[server]		= @@servername
,	[database]		= upper(db_name(sdm.database_id))
,	[synchronous_mode]	= case sdm.mirroring_safety_level_desc
							when ''full''	then ''SYNCHRONOUS	- HIGH SAFETY''
							when ''off''	then ''ASYNCHRONOUS	- HIGH PERFORMANCE''
						else ''Not Mirrored''
					end
from
	master.sys.database_mirroring sdm
where
	db_name(sdm.database_id) not in (''tempdb'')
	and sdm.mirroring_safety_level_desc = ''off''
order by
	@@servername, [database] asc


----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top		NVARCHAR(MAX)
declare @xml_mid		NVARCHAR(MAX)
declare @body_top		NVARCHAR(MAX)
declare @body_mid		NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td''s
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select 
			[server]		as ''td''
		,	''''
		,	[database]		as ''td''
		,	''''
		,	[synchronous_mode]	as ''td''
		,	''''

		from  #mirror_operating_modes
		order by [server], [database] asc 
		for xml path(''tr'')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td''s
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as ''td''
		,	''''
		,	[Column2]	as ''td''
		,	''''
		,	[Column3]	as ''td''
		,	''''
		,	[...]		as ''td''

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path(''tr'')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		''<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<H3>'' + @message_subject + ''</H3>



<h1>
		A Failover error occurred before the databases could be failed over from the Primary Server:  <font color="blue">''	+ @primary		+ ''	</font> 
		to the Secondary Server: <font color="blue">'' + @secondary		+
						case
							when @secondary <> @instance then @secondary + ''\'' + @instance
							else ''''
						end + ''				</font>
				
		Not all the Mirrored Datatabases could be set to Full Safety.  Full safetys are required before failover.  Please check the databases and ensure all are set to full safety before the failover process can proceed to the next step.
		</h1>

		 

<h1>Current Safety Modes						</h1>


<h1>Full Safety Off = (ASYNCHRONOUS - HIGH PERFORMANCE)	</h1>


<h1>Full Safety On  = (SYNCHRONOUS	- HIGH SAFETY)		</h1>


<table border = 1>

<tr>

<th> Server			</th>


<th> Database			</th>


<th> Synchronous Mode	</th>

		</tr>

''

set @body_top = @body_top + @xml_top +
/* ''</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

''		
+ @xml_mid */ 
''</table>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:'' + @server_name_basic + ''</h1>

''
+ ''</body></html>''

----------------------------------------------------------------------------------------
-- send email.

if exists(select top 1 * from #mirror_operating_modes)
	begin
		exec msdb.dbo.sp_send_dbmail
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@recipients		= ''SQLJobAlerts@MyDomain.com''
		,	@subject		= @message_subject
		,	@body			= @body_top
		,	@body_format		= ''HTML'';
		
		drop table #mirror_operating_modes
		raiserror(''50005 Mirror Failover Error.  Not all Mirrored Databases were set to Full Safety'', 16, -1, @@servername )
	end', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Failover All Mirrored Databases]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId,	
		@step_name			=N'Failover All Mirrored Databases', 
		@step_id			=5, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 master;
set nocount on

declare
	@failover_mirror_databases	varchar(max) = ''''
select
	@failover_mirror_databases	= @failover_mirror_databases + 
	''alter database ['' + cast(DB_NAME(database_id) as varchar(255)) + ''] set partner failover;'' + char(10) + char(10)
from
	sys.database_mirroring
where
	mirroring_guid is not null
	and mirroring_role_desc = ''PRINCIPAL''

--exec (@failover_mirror_databases)', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Confirm Mirror Database Failover]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Confirm Mirror Database Failover', 
		@step_id			=6, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####					--optional
		--,	@enable_ssl		= 1					--optional
		--,	@username		=''MySQLDatabaseMailProfile''	--optional
		--,	@password		=''MyPassword''			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare	@get_basic_server_name			varchar(255)
		declare	@get_basic_server_name_and_instance_name	varchar(255)
		declare	@basic_test_subject_message			varchar(255)
		declare	@basic_test_body_message			varchar(max)
		set		@get_basic_server_name			= (select cast(serverproperty(''servername'') as varchar(255)))
		set		@get_basic_server_name_and_instance_name	= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		set		@basic_test_subject_message			= ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		set		@basic_test_body_message			= ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@recipients		= ''SQLJobAlerts@MyDomain.com''
		,	@subject		= @basic_test_subject_message
		,	@body			= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare	@server_name_basic		varchar(255)
declare	@server_name_instance_name	varchar(255)
set	@server_name_basic			= (select cast(serverproperty(''servername'') as varchar(255)))
set	@server_name_instance_name		= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary	varchar(255) = ( select @@servername )
,	@secondary	varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance	varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness	varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set		@message_subject		= ''Failover error found on Server:  '' + @server_name_instance_name + ''  - Not all databases were properly failed over.''

----------------------------------------------------------------------------------------
-- create table to hold confirm failover

if object_id(''tempdb..#confirm_mirror_failover'') is not null
	drop table #confirm_mirror_failover

create table #confirm_mirror_failover
	(
		[server_name]			varchar(255)
	,	[database]			varchar(255)
	,	[mirror_state]		varchar(255)
	,	[mirror_role]			varchar(255)
	,	[mirror_partner_name]	varchar(255)
	)

----------------------------------------------------------------------------------------
-- populate table to hold confirm failover
insert into #confirm_mirror_failover
select
	upper(@@servername)
,	upper(DB_NAME(database_id))
,	mirroring_state_desc
,	mirroring_role_desc
,	mirroring_partner_name
from
	sys.database_mirroring
where
	mirroring_guid is not null
	and mirroring_role_desc in (''PRINCIPAL'', ''DISCONNECTED'', ''SUSPENDED'')

----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top		NVARCHAR(MAX)
declare @xml_mid		NVARCHAR(MAX)
declare @body_top		NVARCHAR(MAX)
declare @body_mid		NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td''s
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select 
			[server_name]			as ''td''
		,	''''
		,	[database]			as ''td''
		,	''''
		,	[mirror_state]		as ''td''
		,	''''
		,	[mirror_role]			as ''td''
		,	''''
		,	[mirror_partner_name]	as ''td''
		,	''''

		from  #confirm_mirror_failover
		order by [database], [mirror_role] asc
		for xml path(''tr'')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td''s
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as ''td''
		,	''''
		,	[Column2]	as ''td''
		,	''''
		,	[Column3]	as ''td''
		,	''''
		,	[...]		as ''td''

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path(''tr'')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		''<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<H3>'' + @message_subject + ''</H3>



<h1>
		A Failover error occurred while failing over from the Primary Server:  <font color="blue">''	+ @primary		+ ''	</font> 
		to the Secondary Server: <font color="blue">'' + @secondary		+
						case
							when @secondary <> @instance then @secondary + ''\'' + @instance
							else ''''
						end + ''				</font>
				
		Not all the Mirrored Datatabases failed over.  Please check the databases on the server, and resolve before proceeding to the next step.
		</h1>

		 

<h1>The following databases did not failover properly.</h1>


<table border = 1>

<tr>

<th> Server Name		</th>


<th> Database			</th>


<th> Mirror State		</th>


<th> Mirror Role		</th>


<th> Mirror Partner Name	</th>

		</tr>

''

set @body_top = @body_top + @xml_top +
/* ''</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

''		
+ @xml_mid */ 
''</table>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:'' + @server_name_basic + ''</h1>

''
+ ''</body></html>''

----------------------------------------------------------------------------------------
-- send email.

if exists(select top 1 * from #confirm_mirror_failover)
	begin
		exec msdb.dbo.sp_send_dbmail
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@recipients		= ''SQLJobAlerts@MyDomain.com''
		,	@subject		= @message_subject
		,	@body			= @body_top
		,	@body_format		= ''HTML'';
		
		drop table #confirm_mirror_failover
		raiserror(''50005 Mirror Failover Error.  Not all Mirrored Databases were properly failed over.'', 16, -1, @@servername )
	end
', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set High Performance Mode On Partner Server]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Set High Performance Mode On Partner Server', 
		@step_id			=7, 
		@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 master;
set nocount on

if not exists(select value from master.sys.configurations where name = ''show advanced options'')
	begin
		exec master..sp_configure ''show advanced options'', 1; reconfigure with override
	end

if not exists(select value from master.sys.configurations where name = ''xp_cmdshell'')
	begin
		exec master..sp_configure ''xp_cmdshell'', 1; reconfigure with override
	end

----------------------------------------------------------------------------------------
-- confirm all servers failed over (only mirrors should exist on this local server at this stage) thus the partner server is the Principal where the HIGH PERFORMANCE mode should be set.
-- this was confirmed by the former step (Confirm Mirror Failover).  If this step was reached; it passed otherwise it would fail before this step.  The ''if exists'' is added so this logic block so it
-- could exist outside of this job step flow if necessary.

if not exists (select top 1 database_id  from sys.database_mirroring where mirroring_role_desc = ''PRINCIPAL'')
	begin
		declare
			@new_principal	varchar(255) 
		,	@retcode		int 
		,	@job_name		varchar(255)
		,	@step_name		varchar(255)
		,	@server_name	varchar(255) 
		,	@query			varchar(8000) 
		,	@cmd			varchar(8000)
		set	@new_principal	= ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
		set	@job_name		= ''DATABASE MIRRORS - Failover All Mirrored Databases''
		set 	@step_name		= ''Set High Performance''
		set 	@server_name		= @new_principal
		set 	@query			= ''exec msdb.dbo.sp_start_job @job_name = ''''''	+ @job_name + '''''', @step_name = '''''' + @step_name + '''''''' 
		set 	@cmd			= ''osql -E -S '' + @server_name + '' -Q "''		+ @query + ''"'' 

		print '' @job_name		= ''	+isnull(@job_name,		''NULL @job_name'') 
		print '' @server_name	= ''	+isnull(@server_name,	''NULL @server_name'') 
		print '' @query		= ''	+isnull(@query,		''NULL @query'') 
		print '' @cmd			= ''	+isnull(@cmd,			''NULL @cmd'')

		--exec	@retcode = xp_cmdshell @cmd

		if @retcode <> 0 or @retcode is null 
			begin 
				print ''xp_cmdshell @retcode = ''+isnull(convert(varchar(20),@retcode),''NULL @retcode'')
			end
	end', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Set High Performance]     ******/
EXEC		@ReturnCode			= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Set High Performance', 
		@step_id			=8, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 master;
set nocount on

declare
	@set_full_safety_off_on_mirror_databases	varchar(max) = ''''
select
	@set_full_safety_off_on_mirror_databases	= @set_full_safety_off_on_mirror_databases 	+ 
	''alter database ['' + cast(DB_NAME(database_id) as varchar(255)) + ''] set safety off;''		+ char(10)
from
	sys.database_mirroring
where
	mirroring_guid is not null
	and mirroring_role_desc = ''PRINCIPAL''

--exec (@set_full_safety_off_on_mirror_databases)', 
		@database_name			=N'master', 
		@flags					=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Confirm High Performance]     ******/
EXEC	@ReturnCode				= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Confirm High Performance', 
		@step_id			=9, 
		@cmdexec_success_code	=0, 
		@on_success_action		=3, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####					--optional
		--,	@enable_ssl		= 1					--optional
		--,	@username		=''MySQLDatabaseMailProfile''	--optional
		--,	@password		=''MyPassword''			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare	@get_basic_server_name			varchar(255)
		declare	@get_basic_server_name_and_instance_name	varchar(255)
		declare	@basic_test_subject_message			varchar(255)
		declare	@basic_test_body_message			varchar(max)
		set		@get_basic_server_name			= (select cast(serverproperty(''servername'') as varchar(255)))
		set		@get_basic_server_name_and_instance_name	= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		set		@basic_test_subject_message			= ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		set		@basic_test_body_message			= ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name	= ''SQLDatabaseMailProfile''
		,	@recipients	= ''SQLJobAlerts@MyDomain.com''
		,	@subject	= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare	@server_name_basic		varchar(255)
declare	@server_name_instance_name	varchar(255)
set		@server_name_basic		= (select cast(serverproperty(''servername'') as varchar(255)))
set		@server_name_instance_name	= (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary	varchar(255) = ( select @@servername )
,	@secondary	varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance	varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness	varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set		@message_subject		= ''Failover error found on Server:  '' + @server_name_instance_name + ''  - Not all mirrored databases were set to full safety prior to failover.''

----------------------------------------------------------------------------------------
-- create table to hold mirror operating modes

if object_id(''tempdb..#mirror_operating_modes'') is not null
	drop table #mirror_operating_modes

create table #mirror_operating_modes
	(
	[server]		varchar(255)
,	[database]		varchar(255)
,	[synchronous_mode]	varchar(255)
)

----------------------------------------------------------------------------------------
-- populate table #mirror_operating_modes

insert into #mirror_operating_modes
select
	[server]		= @@servername
,	[database]		= upper(db_name(sdm.database_id))
,	[synchronous_mode]	= case sdm.mirroring_safety_level_desc
							when ''full''	then ''SYNCHRONOUS	- HIGH SAFETY''
							when ''off''	then ''ASYNCHRONOUS	- HIGH PERFORMANCE''
							else ''Not Mirrored''
						  end
from
	master.sys.database_mirroring sdm
where
	db_name(sdm.database_id) not in (''tempdb'')
	and sdm.mirroring_safety_level_desc = ''full''
order by
	@@servername, [database] asc


----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top		NVARCHAR(MAX)
declare @xml_mid		NVARCHAR(MAX)
declare @body_top		NVARCHAR(MAX)
declare @body_mid		NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td''s
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select 
			[server]		as ''td''
		,	''''
		,	[database]		as ''td''
		,	''''
		,	[synchronous_mode]	as ''td''
		,	''''

		from  #mirror_operating_modes
		order by [server], [database] asc 
		for xml path(''tr'')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td''s
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as ''td''
		,	''''
		,	[Column2]	as ''td''
		,	''''
		,	[Column3]	as ''td''
		,	''''
		,	[...]		as ''td''

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path(''tr'')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		''<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<H3>'' + @message_subject + ''</H3>



<h1>
		A Failover error occurred before the databases could be failed over from the Primary Server:  <font color="blue">''	+ @primary		+ ''	</font> 
		to the Secondary Server: <font color="blue">'' + @secondary		+
						case
							when @secondary <> @instance then @secondary + ''\'' + @instance
							else ''''
						end + ''				</font>
				
		Not all the Mirrored Datatabases could be set to Full Safety.  Full safetys are required before failover.  Please check the databases and ensure all are set to full safety before the failover process can proceed to the next step.
		</h1>

		 

<h1>Current Safety Modes</h1>


<h1>Full Safety Off = (ASYNCHRONOUS - HIGH PERFORMANCE)	</h1>


<h1>Full Safety On  = (SYNCHRONOUS	- HIGH SAFETY)		</h1>


<table border = 1>

<tr>

<th> Server			</th>


<th> Database			</th>


<th> Synchronous Mode	</th>

		</tr>

''

set @body_top = @body_top + @xml_top +
/* ''</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

''		
+ @xml_mid */ 
''</table>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:'' + @server_name_basic + ''</h1>

''
+ ''</body></html>''

----------------------------------------------------------------------------------------
-- send email.

if exists(select top 1 * from #mirror_operating_modes)
	begin
		exec msdb.dbo.sp_send_dbmail
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@recipients		= ''SQLJobAlerts@MyDomain.com''
		,	@subject		= @message_subject
		,	@body			= @body_top
		,	@body_format		= ''HTML'';
		
		drop table #mirror_operating_modes
		raiserror(''50005 Mirror Failover Error.  Not all Mirrored Databases were set to High Performance'', 16, -1, @@servername )
	end', 
		@database_name		=N'master', 
		@flags				=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Finish Mirror Failover Process]     ******/
EXEC	@ReturnCode				= msdb.dbo.sp_add_jobstep 
		@job_id			=@jobId, 
		@step_name			=N'Finish Mirror Failover Process', 
		@step_id			=10, 
		@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 msdb;
set nocount on
set ansi_nulls on
set quoted_identifier on

----------------------------------------------------------------------
-- Configure SQL Database Mail if it''s not already configured.
if (select top 1 name from msdb..sysmail_profile) is null
	begin
		----------------------------------------------------------------------
		-- Enable SQL Database Mail
		exec master..sp_configure ''show advanced options'',1
		reconfigure;
		exec master..sp_configure ''database mail xps'',1
		reconfigure;

		----------------------------------------------------------------------
		-- Add a profile
		execute msdb.dbo.sysmail_add_profile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@description		= ''SQLDatabaseMail'';

		----------------------------------------------------------------------
		-- Add the account names you want to appear in the email message.
		execute msdb.dbo.sysmail_add_account_sp
			@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@email_address	= ''sqldatabasemail@MyDomain.com''
		,	@mailserver_name	= ''MySMTPServer.MyDomain.com''  
		--,	@port			= ####					--optional
		--,	@enable_ssl		= 1					--optional
		--,	@username		=''MySQLDatabaseMailProfile''	--optional
		--,	@password		=''MyPassword''			--optional

		-- Adding the account to the profile
		execute msdb.dbo.sysmail_add_profileaccount_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@account_name		= ''sqldatabasemail@MyDomain.com''
		,	@sequence_number	= 1;

		-- Give access to new database mail profile (DatabaseMailUserRole)
		execute msdb.dbo.sysmail_add_principalprofile_sp
			@profile_name		= ''SQLDatabaseMailProfile''
		,	@principal_id		= 0
		,	@is_default		= 1;

		----------------------------------------------------------------------
		-- Get Server info for test message

		declare @get_basic_server_name				varchar(255) = (select cast(serverproperty(''servername'') as varchar(255)))
		declare @get_basic_server_name_and_instance_name	varchar(255) set @get_basic_server_name_and_instance_name = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))
		declare @basic_test_subject_message			varchar(255) set @basic_test_subject_message = ''Test SMTP email from SQL Server: '' + @get_basic_server_name_and_instance_name
		declare @basic_test_body_message				varchar(max) = ''This is a test SMTP email from SQL Server:  '' + @get_basic_server_name_and_instance_name + char(10) + char(10) + ''If you see this.  It''''s working perfectly :)''

		----------------------------------------------------------------------
		-- Send quick email to confirm email is properly working.

		exec msdb.dbo.sp_send_dbmail
			@profile_name	= ''SQLDatabaseMailProfile''
		,	@recipients	= ''SQLJobAlerts@MyDomain.com''
		,	@subject	= @basic_test_subject_message
		,	@body		= @basic_test_body_message;

		-- confirm message send
		-- select * from msdb..sysmail_allitems
	end

use master;
set nocount on
----------------------------------------------------------------------------------------
-- get basic server info.

declare
	@server_name_basic		varchar(255) = (select cast(serverproperty(''servername'') as varchar(255)))
,	@server_name_instance_name	varchar(255) = (select  replace(cast(serverproperty(''servername'') as varchar(255)), ''\'', ''   SQL Instance: ''))

----------------------------------------------------------------------------------------
-- get basic server mirror role info.

declare
	@primary		varchar(255) = ( select @@servername )
,	@secondary		varchar(255) = ( select top 1 replace(left(mirroring_partner_name, charindex(''.'', mirroring_partner_name) - 1), ''TCP://'', '''') from master.sys.database_mirroring where mirroring_guid is not null )
,	@instance		varchar(255) = ( select top 1 mirroring_partner_instance from master.sys.database_mirroring where mirroring_guid is not null )
,	@witness		varchar(255) = ( select top 1 case mirroring_witness_name when '''' then ''None configured'' end from master.sys.database_mirroring where mirroring_guid is not null )

----------------------------------------------------------------------------------------
-- set message subject.
declare	@message_subject		varchar(255)
set		@message_subject		= ''Mirror Failover Process (2 of 2) has completed.  Failover was successful to Server: '' + @server_name_instance_name

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- create table for mirrored databases
if object_id(''tempdb..#mirrored_databases'') is not null
	drop table #mirrored_databases

create table #mirrored_databases
	(
		[current_server]	varchar(255)
	,	[database]		varchar(255)
	,	[current_state]	varchar(255)
	,	[partner_name]	varchar(255)
	)

----------------------------------------------------------------------------------------
-- populate table for mirrored databases
insert into #mirrored_databases
select
	''current_server''	= @primary
,	''database''		= upper(DB_NAME(database_id))
,	''current_state''	= mirroring_state_desc
,	''partner_name''	= mirroring_partner_name
from
	sys.database_mirroring
where
	mirroring_guid is not null
order by
	db_name(database_id) asc

----------------------------------------------------------------------------------------
-- create conditions for html tables in top and mid sections of email.

declare @xml_top		NVARCHAR(MAX)
declare @xml_mid		NVARCHAR(MAX)
declare @body_top		NVARCHAR(MAX)
declare @body_mid		NVARCHAR(MAX)

----------------------------------------------------------------------------------------
-- set xml top table td''s
-- create html table object for: #check_mirror_latency

set @xml_top = 
	cast(
		(select
			[current_server]	as ''td''
		,	''''
		,	[database]		as ''td''
		,	''''
		,	[current_state]	as ''td''
		,	''''
		,	[partner_name]	as ''td''
		,	''''

		from  #mirrored_databases
		order by [database] asc 
		for xml path(''tr'')
		,	elements)
		as NVARCHAR(MAX)
		)

----------------------------------------------------------------------------------------
-- set xml mid table td''s
-- create html table object for: #extra_table_formatting_if_needed
/*
set @xml_mid = 
	cast(
		(select 
			[Column1]	as ''td''
		,	''''
		,	[Column2]	as ''td''
		,	''''
		,	[Column3]	as ''td''
		,	''''
		,	[...]		as ''td''

		from  #get_last_known_backups 
		order by [database], [time_of_backup] desc 
		for xml path(''tr'')
	,	elements)
	as NVARCHAR(MAX)
		)
*/
----------------------------------------------------------------------------------------
-- format email
set @body_top =
		''<html>
		<head>

<style>
					h1{
						font-family: sans-serif;
						font-size: 87%;
					}
					h3{
						font-family: sans-serif;
						color: black;
					}

					table, td, tr, th {
						font-family: sans-serif;
						font-size: 87%;
						border: 1px solid black;
						border-collapse: collapse;
					}
					th {
						text-align: left;
						background-color: gray;
						color: white;
						padding: 5px;
						font-size: 87%;
					}

					td {
						padding: 5px;
					}
			</style>

		</head>
		<body>

<h1>'' + @message_subject + ''</h1>


<h1>


		The Mirror Failover Process has successfully completed.  Primary (Principal) server is:  <font color="blue">''	+ @primary		+ ''.  </font> The Secondary (Mirror) now is Server:  <font color="blue">'' + @secondary		+
						case
							when @secondary <> @instance then @secondary + ''\'' + @instance
							else ''''
						end + ''				</font>
		

		</h1>

		 

<h1></h1>


<table border = 1>

<tr>

<th> Server		</th>


<th> Database		</th>


<th> Current State	</th>


<th> Process		</th>

		</tr>

''

set @body_top = @body_top + @xml_top +
/* ''</table>


<h1>Mid Table Title Here</h1>


<table border = 1>

<tr>

<th> Column1 Here	</th>


<th> Column2 Here	</th>


<th> Column3 Here	</th>


<th> ...		</th>

		</tr>

''		
+ @xml_mid */ 
''</table>


<h1>This process is driven by the Job ( DATABASE MIRRORS - Failover All Mirrored Databases )</h1>


<h1>Go to the server using Start-Run, or (Win + R) and type in: mstsc -v:'' + @server_name_basic + ''</h1>

''
+ ''</body></html>''

----------------------------------------------------------------------------------------
-- send email.

exec msdb.dbo.sp_send_dbmail
	@profile_name	= ''SQLDatabaseMailProfile''
,	@recipients	= ''SQLJobAlerts@MyDomain.com''
,	@subject	= @message_subject
,	@body		= @body_top
,	@body_format	= ''HTML'';


drop table #mirrored_databases
', 
		@database_name		=N'master', 
		@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_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

 

Advertisements

1 reply »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s