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:
| Script # | Script Name | Object Type | Script |
| 1 | AddDBMirroring.SQL | Table | T-SQL |
| 2 | ddl_trig_database.SQL | Trigger | T-SQL |
| 3 | Trg_InvokeSQLMirroringSetupJob.SQL | Trigger | T-SQL |
| 4* | SpDBBackup.SQL | StoreProc | T-SQL |
| 5 | CreateFileShare.SQL | Batch file | CMD |
| 6* | CreateLinkServer and SQLLogin – OnPrincipalInstance.sql | SQL | T-SQL |
| 7* | CreateLinkServer and SQLLogin - OnMirroredInstance.sql | SQL | T-SQL |
| 8* | Restore.SQL | SQL | T-SQL |
| 9* | Restore.CMD | Batch file | T-SQL |
| 10 | CreateEndPointOnMirrorSQLServer.SQL | SQL | T-SQL |
| 11 | CreateEndPointOnPrincipalServer.SQL | SQL | T-SQL |
| 12* | MakePrinicpalInstanceAsPartnerOnMirrorServer.SQL | SQL | T-SQL |
| 13* | MakeMirrorInstanceAsPartnerOnPrincipalServer.SQL | SQL | T-SQL |
| 14* | CreateEndPointOnMirrorSQLServer.CMD | Batch file | CMD |
| 15* | CreateEndPointOnPrincipalServer.CMD | Batch file | CMD |
| 16* | MakeMirrorInstanceAsPartnerOnPrincipalServer.CMD | Batch file | CMD |
| 17* | MakePrinicpalInstanceAsPartnerOnMirrorServer.CMD | Batch file | CMD |
| 18 | Auto DB Mirroring Setup.SQL | | |
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
- 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]
- 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” . - SQL Agent job "Auto DB Mirroring Setup" has the below 6 steps [as shown in the below screenshot]:
- 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. - 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.
- 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. - 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. - Make principal instance as partner on Mirror server - This will trigger an
ALTER database command to create the mirror partner. - 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
- Table
AddDBMirroring.SQL - This table updated with the DB name, creation date, backup file name, backup path, etc. - 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. - 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. - 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. - 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. - 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. - 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. - 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.
Backup: This step invokes the stored procedure – spDBBackup, which triggers the full and tlog backup and updates the AddDBMirroring table.Restore: Restores the database on mirroring server in no recovery mode. This executes the Restore.cmd file which calls Restore.SQL file.CreateEndPointOnPrincipalServer: This creates an End Point named as "Mirroring" with Port # 5022 on principal server.CreateEndPointOnMirrorSQLServer: This creates an End Point named as "Mirroring" with Port # 5022 on mirroring server.MakePrinicpalInstanceAsPartnerOnMirrorServer: This makes the Principal instance as partner on mirror server using port # 5022.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
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
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
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
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
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
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
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
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
USE [master]
DECLARE @DBName NVARCHAR(300)
DECLARE @BackupFileName NVARCHAR(300)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @SQL NVARCHAR(2000)
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\'
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
USE master;
GO
IF NOT EXISTS (SELECT state_desc,type_desc FROM sys.database_mirroring_endpoints _
WHERE name='Mirroring')
BEGIN
CREATE ENDPOINT Mirroring STATE=STARTED
AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER)
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
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 ENDPOINT Mirroring STATE=STARTED
AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER)
print 'creating the endpoint'
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
DECLARE @SQL NVARCHAR(3000)
DECLARE @DBNAME NVARCHAR(300)
DECLARE @Domain NVARCHAR(300)
DECLARE @FQDN NVARCHAR(300)
DECLARE @PrincipalHost NVARCHAR(300)
SET @PrincipalHost= 'SQLSERVER001'
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
DECLARE @SQL NVARCHAR(3000)
DECLARE @DBNAME NVARCHAR(300)
DECLARE @Domain NVARCHAR(300)
DECLARE @FQDN NVARCHAR(300)
DECLARE @MirrorHost NVARCHAR(300)
SET @MirrorHost= 'SQLSERVER002'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', _
'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
Print @Domain
SET @FQDN = @MirrorHost+'.'+@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
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
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
