Automating the SQL Database Mirroring Setup

• Download source files - 11.9 KB

Introduction

In one of the client requirements, I had to design a solution that can bring the database into mirror state on DR site (or Secondary SQL Server) in no time, by establishing the mirroring session from Primary Server [Principal Instance] to Secondary Server [Mirrored Instance], without any manual effort when a new database is created on the principal Instance.

Once the database is created on the Primary Server, the entire process of creating the database mirroring should take place without any manual intervention by the DBA. Every time a new database is created on the SQL instance, either at Principal [Or Mirror, in-case code is deployed on the mirror server as well, considering mirror instance acts as Principal at any point of time in future] in few seconds, the database configured with the mirroring and will be in sync with the principal database.

Background

This automation solution became vital as it's not clear when the new database is going to be created on the main primary SQL instance and customer requirement is to have the DR copy immediately (within a few seconds) on the secondary database server without any manual DBA effort and change request.

This automation process has completely eliminated the change management process and achieves greater RPO and RTO time as the DR instance will be having the primary DB copy in no time after its creation on the primary server and thus provides High Availability (HA) to an application in the event of primary system failure. This solution could save up to 8 man hours on every new db creation, by eliminating the change management process and manual effort in creating the mirroring setup and more over some databases would be created by an application without the DBA knowledge.

Automation Flow and Code Details

Assume we have the below two servers that are involved in our mirroring scenario (solution):

• SQLSERVER001 – Primary (Principal SQL Instance)
• SQLSERVER002 – Secondary (Mirrored SQL instance)

I am using the above host names in all the listed scripts below, which you would require to update according to your environment. This mirroring automation uses the T-SQL, SQLCMD and Commandline scriptings as mentioned below:

Deploy the below code in – D: \Auto Mirroring Setup\ and make sure your account\DBA team account has got the full control rights on Auto Mirroring Setup folder. First 7 scripts and Script # 18 (From the above table – highlighted in blue) must be executed manually on Primary SQL instance (Principal partner) using SQL Server Management Studio.

Remaining scripts 8 to 17 should be kept as is in the D: \Auto Mirroring Setup\ folder. Scripts that are marked with asterisk [*] must be updated with your mirroring partners and port numbers.

How Automation Works

1. When a new database is created on the Principal SQL Server instance [SQLSERVER001], ON CREATE DATABASE trigger - [ddl_trig_database] will be triggered and it inserts a new record in the "AddDBMirroring" table on Master database. [as in shown in the below screenshot]

   

2. Once an entry is made successfully with the DB name into AddDBMirroring table, an INSERT operation triggers the job SQL Agent job - "Auto DB Mirroring Setup" by trigger “Trig_InvokeSQLMirroringSetupJob” .
3. SQL Agent job "Auto DB Mirroring Setup" has the below 6 steps [as shown in the below screenshot]:

   

   1. Backup Step - This will trigger the full and tlog backup of a newly created database by calling the stored procedure - "spSetUpDBMirroring" from master db. This procedure takes the database name as a parameter from the table - AddDBMirroring. As per the current environment, this procedure is hard coded with the backup location - "D:\Backups\". This triggers both Full and Tlog backup in a single backup file with the current timestamp.
   2. Restore Step - It restores the database [Full and Tlog] on Mirror instance from the backup path (share path of Principal server - \\SQLSERVER001\Backups\) with No Recovery.
   3. Create EndPoint On Prinicpalserver Step - This will create an end point named "Mirroring" with Port # 5022. If it already exists, command will be skipped. The end point will be started if it’s in stop state. Note: If needed, you can update the ports according to your environment.
   4. Create EndPoint On Mirror server Step - This will create an end point named "Mirroring" with Port # 5022. If it already exists, command will be skipped. The end point will be started if it’s in stop state. Note: If needed, you can update the ports according to your environment.
   5. Make principal instance as partner on Mirror server - This will trigger an ALTER database command to create the mirror partner.
   6. Make Mirror instance as partner on principal server - This will trigger an ALTER database command to create the principal partner.

      Note: Similar objects and scripts can be used in-case of reverse mirroring setup.

   2.2. Mirroring End-Point and Port

   In the current automation scenario, we have used all default end point names ["Mirroring"] and the port number ["5022"] that gets created when you setup the database mirroring for the first time using SSMS database mirroring configuration wizard, when Principal and Mirror SQL instances are hosted on different machine.
   Note: Make sure the ports are enabled and working by doing the telnet.

More Explanation on Objects

1. Table AddDBMirroring.SQL - This table updated with the DB name, creation date, backup file name, backup path, etc.
2. Trigger ddl_trig_database.SQL - This SQL trigger will trigger once the new DB is created on the SQL instance and insert a newly created DB in AddDBMirroring table.
3. Trigger Trig_InvokeSQLMirroringSetupJob.SQL - invoke the SQL Agent Job " Auto DB Mirroring Setup" once new record is inserted in AddDBMirroring table with newly created database.
4. Stored Proc SpDBBackup.SQL - This stored procedure is executed as first step from the SQL Agent job "Auto DB Mirroring Setup". This procedure takes a single input parameter (i.e., most recently created user DB name from AddDBMirroring table and it executes the full backup and tlog backup on the backup drive.
5. CMD CreateFileShare .CMD - This batch file creates a folder on D:\ drive named “Backups” and share it with everyone with full control. This should be executed manually on both the mirroring partners.
6. T-SQL CreateLinkServer and SQLLogin – OnPrincipalInstance.SQL - This creates SQL authenticated account with DB reader privileges on master database and a linked server – “LnkSrvDBMirroring” on Principal instance.
7. T-SQL CreateLinkServer and SQLLogin - OnMirroredInstance.SQL - This creates SQL authenticated account with DB reader privileges on master database and a linked server – “LnkSrvDBMirroring” on Mirror instance.
8. Agent Job Job - Auto DB Mirroring Setup.SQL - This SQL Agent job invokes automatically once the new Database is created on the Primary SQL instance through a trigger (Step # 3) which executes the six batch files one after the other from the major step 8.

   Note: This job should be in disabled state and should not be scheduled.

Job - Auto DB Mirroring Setup

Each step executes the batch file (.cmd file) that calls the T-SQL command files stored in D:\Auto Mirroring Setup folder.

1. Backup: This step invokes the stored procedure – spDBBackup, which triggers the full and tlog backup and updates the AddDBMirroring table.
2. Restore: Restores the database on mirroring server in no recovery mode. This executes the Restore.cmd file which calls Restore.SQL file.
3. CreateEndPointOnPrincipalServer: This creates an End Point named as "Mirroring" with Port # 5022 on principal server.
4. CreateEndPointOnMirrorSQLServer: This creates an End Point named as "Mirroring" with Port # 5022 on mirroring server.
5. MakePrinicpalInstanceAsPartnerOnMirrorServer: This makes the Principal instance as partner on mirror server using port # 5022.
6. MakeMirrorInstanceAsPartnerOnPrincipalServer: This makes the Mirror instance as partner on principal server using port # 5022.

Notes: For any troubleshooting, you can refer to the error logs at - D: \Auto Mirroring Setup\Logs folder. Even all successful histories are maintained in this folder.

Error Logging, IBM SQL ITCAM and Microsoft SCOM

Some of the errors will be reported in the SQL error log file and few custom error logging output files are generated from the SQL agent job [Auto DB Mirroring Setup] at D:\Auto Mirroring Setup\Logs folder.
It has the custom error logging mechanism which writes the db creation info in the SQL error log as a critical error on principal server by the trigger “ddl_trig_database“ using xp_logevent command.

This logging information will read by an ITCAM Agent and SCOM agent to generate an alert in alerting tool.
You need to work with your ITM and SCOM engineer to pick these alerts based on error number and severity generated in the error log file.

Coding

NOTE : 
Although complete scripts are attached in the zip file, I still listed out the 
scripts for your quick reference for understanding the automation flow.

Scripts # (1-4 and 6-7 and 18) should be executed from the SQL management studio 
and script # 5 from the command-line ,remaining scripts should be kept as is by 
changing the file extensions (.txt to .sql and .txtcmd to .cmd).

1. Create AddDBMirroringTable

USE [master]
GO

CREATE TABLE [dbo].[AddDBMirroring](
    [DBName] [nvarchar](100) NULL,
    [CreateDate] [datetime] NULL,
    [BackupFileName] [nvarchar](1000) NULL,
    [BackupPath] [nvarchar](1000) NULL
) ON [PRIMARY]

GO

2. ddl_trig_database

USE [master]
GO

CREATE  TRIGGER [ddl_trig_database]   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS
DECLARE @DBNAME NVARCHAR(300)

    PRINT 'Database Created.'  
    SET @DBNAME= (select CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') _
                  as NVarchar(128)))

    DECLARE @@MESSAGE NVARCHAR(1000)
    SELECT @@MESSAGE = 'Critical Info - Update the SQL DBA team, a new websense db ' + _
                       @DBNAME + ' has been created at '+CONVERT(VARCHAR(30),GETDATE(),113) +'' 

    EXEC master..xp_logevent 50002, @@MESSAGE, ERROR;

    INSERT INTO [master].dbo.AddDBMirroring VALUES(@DBNAME,GETDATE(),NULL,NULL)   

GO

ENABLE TRIGGER [ddl_trig_database] ON ALL SERVER
GO

3. Trg_InvokeSQLMirroringSetupJob

USE [master]
GO

CREATE TRIGGER [dbo].[Trg_InvokeSQLMirroringSetupJob] ON [dbo].[AddDBMirroring]

AFTER INSERT
AS
BEGIN

EXEC msdb..sp_start_job @job_name = 'Auto DB Mirroring Setup'

END
GO

4. SpDBBackup

CREATE PROCEDURE [dbo].[spDBBackup]

(@DBName NVARCHAR(300))
as

DECLARE @TODAYSDate datetime
DECLARE @SQL NVARCHAR(1000)
DECLARE @BackupPath NVARCHAR(600)
DECLARE @BackupFileName NVARCHAR(500)

SET @TODAYSDate=GETDATE()
SET @BackupPath='D:\Backups\'

SELECT @BackupFileName = @DBName+''+''+'_'+_
(convert(varchar(20),@TODAYSDate,112))+''+'.bak'
Print @BackupFileName

SELECT @SQL = 'ALTER DATABASE '+@DBName+' SET RECOVERY FULL'
print @SQL
execute (@SQL)

SELECT @SQL = 'BACKUP DATABASE '+@DBName+' _
TO DISK='''+@BackupPath+''+@BackupFileName+''''
print @SQL
execute (@SQL)

SELECT @SQL = 'BACKUP LOG '+@DBName+' _
TO DISK='''+@BackupPath+''+@BackupFileName+''''

print @SQL
execute (@SQL)

SELECT  @BackupPath=@BackupPath+@BackupFileName

UPDATE [master].dbo.AddDBMirroring SET BackupFileName=@BackupFileName,_
                BackupPath=@BackupPath WHERE DBName=@DBName

GO

5. CreateFileShare

md D:\Backups & net share Backups=D:\Backups /remark:"For SQL Database Mirroring" /GRANT:Everyone,FULL

6. CreateLinkServer and SQLLogin - OnPrincipalInstance

USE [master]
GO
CREATE LOGIN [LnkSrvDBMirroring] WITH PASSWORD=N'LnkSrvDBMirroring@@1234', _
       DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
CREATE USER [LnkSrvDBMirroring] FOR LOGIN [LnkSrvDBMirroring]
GO
USE [master]
GO
EXEC sp_addrolemember N'db_datareader', N'LnkSrvDBMirroring'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQLSERVER002', @srvproduct=N'SQL Server'

GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER002', _
@optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLSERVER002', _
@locallogin = NULL , @useself = N'False', _
@rmtuser = N'LnkSrvDBMirroring', @rmtpassword = N'Enter the password'
GO

7. CreateLinkServer and SQLLogin- OnMirroredInstance

USE [master]
GO
CREATE LOGIN [LnkSrvDBMirroring] WITH PASSWORD=N'LnkSrvDBMirroring@@1234', _
       DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [master]
GO
CREATE USER [LnkSrvDBMirroring] FOR LOGIN [LnkSrvDBMirroring]
GO
USE [master]
GO
EXEC sp_addrolemember N'db_datareader', N'LnkSrvDBMirroring'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SQLSERVER001', @srvproduct=N'SQL Server'

GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQLSERVER001', _
@optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SQLSERVER001', _
@locallogin = NULL , @useself = N'False', _
@rmtuser = N'LnkSrvDBMirroring', @rmtpassword = N'LnkSrvDBMirroring@@1234'
GO

18. SQLAgentJob_ToSetupDBMirroring

USE [msdb]
GO

/****** Object:  Job [Auto DB Mirroring Setup]    Script Date: 06-04-2018 11:02:07 ******/ 
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]
Script Date: 06-04-2018 11:02:08 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories _
WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', _
@type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Auto DB Mirroring Setup', 
        @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 TRIGGERED AUTOMATICALLY WHEN A NEW DB IS CREATED.', 
        @category_name=N'Database Maintenance', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup]    Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup', 
        @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'DECLARE @DBName NVARCHAR(300)

DECLARE @SQL NVARCHAR(300)

SET @DBName= (select top 1 DBName from [master].dbo.AddDBMirroring ORDER BY CREATEDate desc)

select  @SQL = ''ALTER DATABASE ''+@DBName+'' SET RECOVERY FULL''

exec (@SQL)

Exec spDBBackup  @DBName', 
        @database_name=N'master', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\BackupDBForMirroring.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Restore]    Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Restore', 
        @step_id=2, 
        @cmdexec_success_code=0, 
        @on_success_action=4, 
        @on_success_step_id=3, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'CmdExec', 
        @command=N'D:\Auto Mirroring Setup\Restore.CMD', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\RestoreDBForMirroring.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CreateEndPointOnPrincipalServer]
    Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _
        @step_name=N'CreateEndPointOnPrincipalServer', 
        @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'CmdExec', 
        @command=N'"D:\Auto Mirroring Setup\CreateEndPointOnPrincipalServer.CMD"', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\CreateEndPointForPrincipal.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [CreateEndPointOnMirrorSQLServer]
    Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
        @step_name=N'CreateEndPointOnMirrorSQLServer', 
        @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'CmdExec', 
        @command=N'"D:\Auto Mirroring Setup\CreateEndPointOnMirrorSQLServer.CMD"', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\CreateEndPointForMirroring.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [MakePrinicpalInstanceAsPartnerOnMirrorServer]
        Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _
        @step_name=N'MakePrinicpalInstanceAsPartnerOnMirrorServer', 
        @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'CmdExec', 
        @command=N'"D:\Auto Mirroring Setup\MakePrinicpalInstanceAsPartnerOnMirrorServer.CMD"', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\MakePrincipalAsPartnerOnMirror.txt', 
        @flags=2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [MakeMirrorInstanceAsPartnerOnPrincipalServer]
        Script Date: 06-04-2018 11:02:08 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, _
        @step_name=N'MakeMirrorInstanceAsPartnerOnPrincipalServer', 
        @step_id=6, 
        @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'CmdExec', 
        @command=N'"D:\Auto Mirroring Setup\MakeMirrorInstanceAsPartnerOnPrincipalServer.CMD"', 
        @output_file_name=N'D:\Auto Mirroring Setup\Logs\
        MakeMirrorInstanceAsPartnerOnPrincipal.txt', 
        @flags=2
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

// Scripts # (8-17) should be just placed in the folder- D: \Auto Mirroring Setup\  

8. Restore

/*
NOTE : YOU SHOUD NOT BE EXECUTING THIS CODE MANUALLY FROM SSMS.
Replace SQLSERVER001 with your principal server name. 
Provide only the HOSTNAME not SQL instance name.
*/

USE [master]

DECLARE @DBName NVARCHAR(300)
DECLARE @BackupFileName NVARCHAR(300)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @SQL NVARCHAR(2000)

/* Replace the principal Host name with SQLSERVER001 */

SET @DBName= (select top 1 DBName from [SQLSERVER001].[master].dbo.AddDBMirroring _
              ORDER BY CREATEDate desc)
SET @BackupFileName= (select top 1 BackupFileName _
from  [SQLSERVER001].[master].dbo.AddDBMirroring ORDER BY CREATEDate desc)


SET @BackupPath='\\SQLSERVER001\Backups\' /* Replace the principal Host name 
                                                  and make sure folder Backups is shared. */

PRINT @DBName
PRINT @BackupFileName
PRINT @BackupPath

SET @SQL= 'RESTORE DATABASE '+ @DBName +' FROM  DISK = N'''+ ''+_
@BackupPath+@BackupFileName+''' WITH  FILE = 1,NORECOVERY,  REPLACE'

Print @SQL
Exec (@SQL)

SET @SQL= 'RESTORE LOG '+ @DBName +' FROM  DISK = N'''+ ''+_
@BackupPath+@BackupFileName+''' WITH  FILE = 2,NORECOVERY'

Print @SQL
Exec (@SQL)
GO

9. Restore Cmd File

SQLCMD -S SQLSERVER002 -E -i "D:\Auto Mirroring Setup\Restore.sql"

10. CreateEndPointOnMirrorSQLServer

/* This code will be executed by the SQL Agent job. 
This creates Mirroring partner on mirrored sql instance (SQLServer002)*/

USE master;  
GO  

IF NOT EXISTS (SELECT state_desc,type_desc FROM sys.database_mirroring_endpoints _
    WHERE name='Mirroring')
BEGIN
--create an endpoint for this instance  
CREATE ENDPOINT Mirroring  STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   FOR DATABASE_MIRRORING (ROLE=PARTNER)    
--Partners under same domain user; login already exists in master.  

print 'creating the endpoint'

END

DECLARE @state_desc nvarchar(100)

SET @state_desc=(SELECT state_desc FROM sys.database_mirroring_endpoints WHERE name='Mirroring')

IF @state_desc<>'STARTED'
BEGIN

    ALTER ENDPOINT Mirroring STATE=STOPPED

    PRINT 'Starting endpoint'

    ALTER ENDPOINT Mirroring STATE=STARTED

END

11. CreateEndPointOnPrincipalServer

/* This code will be executed by the SQL Agent job. 
This creates Mirroring partner on mirrored sql instance (SQLServer001)*/

USE master;  
GO  

DECLARE @state_desc nvarchar(100)

IF NOT EXISTS (SELECT state_desc,type_desc FROM sys.database_mirroring_endpoints _
               WHERE name='Mirroring')

BEGIN
    --create an endpoint for this instance  
    CREATE ENDPOINT Mirroring STATE=STARTED   
        AS TCP (LISTENER_PORT=5022)   FOR DATABASE_MIRRORING (ROLE=PARTNER)  
        print 'creating the endpoint'
        --Partners under same domain user; login already exists in master.  
END

SET @state_desc=(SELECT state_desc _
                 FROM sys.database_mirroring_endpoints WHERE NAME='Mirroring')

IF @state_desc<>'STARTED'
    BEGIN
            ALTER ENDPOINT Mirroring STATE=STOPPED

            PRINT 'Starting endpoint'

            ALTER ENDPOINT Mirroring STATE=STARTED
    END

12. MakePrinicpalInstanceAsPartnerOnMirrorServer

-- On the mirror server instance, 
-- set the principal server instance - SQLServer001 as the partner

DECLARE @SQL NVARCHAR(3000)
DECLARE @DBNAME NVARCHAR(300)
DECLARE @Domain NVARCHAR(300)
DECLARE @FQDN NVARCHAR(300)
DECLARE @PrincipalHost NVARCHAR(300)

SET @PrincipalHost= 'SQLSERVER001'  -- Replace the principal server host name 
                                            -- according to your environment.

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', _
'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT

Print @Domain

SET @FQDN = @PrincipalHost+'.'+@Domain;

SET @DBName= (select top 1 DBName from _
SQLSERVER001.[master].dbo.AddDBMirroring ORDER BY CREATEDate desc)

SET @SQL = 'ALTER DATABASE '+ _
@DBName+ ' SET PARTNER ='+'''TCP://'+@FQDN+':5022'''

print @SQL

Exec (@SQL)

GO  

13. MakeMirrorInstanceAsPartnerOnPrincipalServer

-- Execute it on the Principal server instance [SQLSERVER001], 
-- set the mirror server instance - SQLServer002 as the partner.

DECLARE @SQL NVARCHAR(3000)
DECLARE @DBNAME NVARCHAR(300)
DECLARE @Domain NVARCHAR(300)
DECLARE @FQDN NVARCHAR(300)
DECLARE @MirrorHost NVARCHAR(300)


SET @MirrorHost= 'SQLSERVER002'  -- Replace the Mirror server host name 
                                 -- according to your environment.

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', _
'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT

Print @Domain

SET @FQDN = @MirrorHost+'.'+@Domain;
/*As below SELECT statement executed on principal server, 
  hence it should updated with principal sql instance name */

SET @DBName= (select top 1 DBName from _
             SQLSERVER001.[master].dbo.AddDBMirroring ORDER BY CREATEDate desc)

SET @SQL = 'ALTER DATABASE '+ _
@DBName+ ' SET PARTNER ='+'''TCP://'+@FQDN+':5022'''

print @SQL

Exec (@SQL)

GO

14. CreateEndPointOnMirrorSQLServer

SQLCMD -S SQLSERVER002 -E -i "D:\Auto Mirroring Setup\CreateEndPointOnMirrorSQLServer.SQL"

15. CreateEndPointOnPrincipalServer

SQLCMD -S SQLSERVER001 -E -i "D:\Auto Mirroring Setup\CreateEndPointOnPrincipalServer.SQL"

16.MakeMirrorInstanceAsPartnerOnPrincipalServer

SQLCMD -S SQLSERVER001 -E 
-i "D:\Auto Mirroring Setup\MakeMirrorInstanceAsPartnerOnPrincipalServer.SQL"

17.MakePrinicpalInstanceAsPartnerOnMirrorServer

SQLCMD -S SQLSERVER002 -E 
-i "D:\Auto Mirroring Setup\MakePrinicpalInstanceAsPartnerOnMirrorServer.SQL"

Assumptions and Dependencies

• Assuming Principal and Mirrored SQL servers have identical drives and folder structures.
• SQL Services must be running with the service account, if service accounts are different on each partner, create the service account login and grant sysadmin rights on other partner.
• Make sure SQL DBA AD group\DBA member\Service Account have full control on the mentioned path

  D:\Backups\

  D: \Auto Mirroring Setup\

• As per the mirroring setup process, Transactional log backup job should not be triggered just after the Full and T-Log backup and before the Mirroring setup steps are executed from the SQL Agent job. Some errors thrown from the T-SQL still shows SQL Agent job as succeeded as SQLCMD is executed successfully from the SQLCMD but error thrown in the .SQL file not returned back and make that step as failed state, if you found the job as succeeded but Database not setup with the mirroring, kindly look at the Logs - D: \Auto Mirroring Setup\.
• In few scenarios, SQL agent job shows as successful though some errors in the execution and mirroring setup has not established successfully. This is due to SQLCMD’s successful execution but the SQL script triggered some errors and that won’t return as failure to the job, hence we must look into the D: \Auto Mirroring Setup\Logs folder and fix the problem.
• It's good to have basic knowledge of T-SQL to understand the automation process and for troubleshooting the issues from the error logs and job history.

Points of Interest

In-case of any issues with automation, Database Administrator can disable the trigger [ddl_trig_database] and setup the database mirroring manually.

A new database cannot be created in-case any objects which are referenced in the trigger are not found or incorrect. In this case, either trigger must be disabled or deleted.

Any additional changes made to the T-SQL and batch files could break the automation process and have to act upon the errors generated in the custom error log folder.

History

• 25-07-2018: Initial version 1.0