自动化 SQL 数据库镜像设置

Dathuraj Pasarge

5.00/5 (5投票s)

2018年7月27日

CPOL

10分钟阅读

21777

438

创建每个新数据库时，无需 DBA 操作即可进行数据库镜像设置。

下载源文件 - 11.9 KB

引言

在一次客户需求中，我需要设计一个解决方案，该方案能够在主服务器[主体实例]上创建新数据库时，无需任何手动操作，即可在短时间内通过在主服务器[主体实例]和辅助服务器[镜像实例]之间建立镜像会话，将数据库快速带入灾难恢复站点（或辅助SQL Server）上的镜像状态。

一旦在主服务器上创建了数据库，整个数据库镜像创建过程应在没有DBA任何手动干预的情况下进行。每次在SQL实例上创建新数据库时，无论是在主体实例上[或者镜像实例上，如果代码也部署在镜像服务器上，考虑到镜像实例将来可能在任何时候充当主体实例]，几秒钟内，数据库就会配置好镜像并与主体数据库保持同步。

背景

这种自动化解决方案变得至关重要，因为不清楚何时会在主SQL主实例上创建新数据库，而客户的要求是立即（在几秒钟内）在辅助数据库服务器上拥有灾难恢复副本，无需任何DBA手动操作和变更请求。

此自动化过程完全消除了变更管理过程，并实现了更高的RPO和RTO时间，因为在主服务器上创建数据库后，灾难恢复实例将立即拥有主数据库副本，从而在主系统发生故障时为应用程序提供高可用性（HA）。此解决方案通过消除变更管理过程和创建镜像设置所需的手动工作，可以在每次创建新数据库时节省多达8个工时，而且有些数据库可能由应用程序创建，DBA对此并不知情。

自动化流程和代码详情

假设我们的镜像场景（解决方案）涉及以下两台服务器

SQLSERVER001 – 主（主体SQL实例）
SQLSERVER002 – 辅助（镜像SQL实例）

我在下面列出的所有脚本中都使用了上述主机名，您需要根据您的环境进行更新。此镜像自动化使用T-SQL、SQLCMD和命令行脚本，如下所述：

脚本 #	脚本名称	对象类型	脚本
1	`AddDBMirroring.SQL`	表格	T-SQL
2	`ddl_trig_database.SQL`	触发器	T-SQL
3	`Trg_InvokeSQLMirroringSetupJob.SQL`	触发器	T-SQL
4*	`SpDBBackup.SQL`	存储过程	T-SQL
5	`CreateFileShare.SQL`	批处理文件	CMD
6*	`CreateLinkServer` 和 `SQLLogin` – OnPrincipalInstance.sql	SQL	T-SQL
7*	`CreateLinkServer` 和 `SQLLogin` - OnMirroredInstance.sql	SQL	T-SQL
8*	`Restore.SQL`	SQL	T-SQL
9*	`Restore.CMD`	批处理文件	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`	批处理文件	CMD
15*	`CreateEndPointOnPrincipalServer.CMD`	批处理文件	CMD
16*	`MakeMirrorInstanceAsPartnerOnPrincipalServer.CMD`	批处理文件	CMD
17*	`MakePrinicpalInstanceAsPartnerOnMirrorServer.CMD`	批处理文件	CMD
18	自动数据库镜像`Setup.SQL`

将以下代码部署到 – D:\Auto Mirroring Setup\ 并确保您的账户/DBA团队账户对“Auto Mirroring Setup”文件夹具有完全控制权限。前7个脚本和脚本# 18（从上表-蓝色突出显示）必须使用SQL Server Management Studio在主SQL实例（主体伙伴）上手动执行。

剩余的脚本8到17应原样保存在 D:\Auto Mirroring Setup\ 文件夹中。标有星号[*]的脚本必须更新为您的镜像伙伴和端口号。

自动化工作原理

当在主体SQL Server实例[SQLSERVER001]上创建新数据库时，ON CREATE DATABASE 触发器 - [ddl_trig_database]将被触发，它会在主数据库的“AddDBMirroring”表中插入一条新记录。[如下图所示]
一旦数据库名称成功输入到 AddDBMirroring 表中，INSERT 操作将通过触发器 “Trig_InvokeSQLMirroringSetupJob” 触发 SQL Agent 作业 - “Auto DB Mirroring Setup” 。
SQL Agent 作业“Auto DB Mirroring Setup”包含以下6个步骤[如下图所示]：
1. 备份步骤 - 这将通过调用主数据库中的存储过程“spSetUpDBMirroring”来触发新创建数据库的完整备份和事务日志备份。该过程从表 AddDBMirroring 中获取数据库名称作为参数。根据当前环境，该过程硬编码了备份位置 - “D:\Backups\”。这会在单个备份文件中以当前时间戳触发完整备份和事务日志备份。
2. 恢复步骤 - 它将数据库[完整和事务日志]从备份路径（主体服务器的共享路径 - \\SQLSERVER001\Backups\）以不恢复模式恢复到镜像实例。
3. 在主体服务器上创建端点步骤 - 这将创建一个名为“Mirroring”的端点，端口号为5022。如果已存在，则跳过该命令。如果端点处于停止状态，则会启动。注意：如有需要，您可以根据您的环境更新端口。
4. 在镜像服务器上创建端点步骤 - 这将创建一个名为“Mirroring”的端点，端口号为5022。如果已存在，则跳过该命令。如果端点处于停止状态，则会启动。注意：如有需要，您可以根据您的环境更新端口。
5. 将主体实例设为镜像服务器上的伙伴 - 这将触发 ALTER 数据库命令以创建镜像伙伴。
6. 将镜像实例设为主体服务器上的伙伴 - 这将触发 ALTER 数据库命令以创建主体伙伴。
  注意：在反向镜像设置的情况下，可以使用类似的对象和脚本。
2.2. 镜像端点和端口

在当前的自动化场景中，我们使用了所有默认的端点名称["Mirroring"]和端口号["5022"]，这些是在您首次使用SSMS数据库镜像配置向导设置数据库镜像时创建的，当主体和镜像SQL实例托管在不同机器上时。
注意：请确保通过telnet测试端口已启用并正常工作。

对象更多说明

表 AddDBMirroring.SQL - 此表用数据库名称、创建日期、备份文件名、备份路径等信息更新。
触发器 ddl_trig_database.SQL - 一旦在SQL实例上创建新数据库，此SQL触发器就会触发，并将新创建的数据库插入到 AddDBMirroring 表中。
触发器 Trig_InvokeSQLMirroringSetupJob.SQL - 一旦新记录连同新创建的数据库插入到 AddDBMirroring 表中，就会调用SQL Agent作业“Auto DB Mirroring Setup”。
存储过程 SpDBBackup.SQL - 该存储过程作为SQL Agent作业“Auto DB Mirroring Setup”的第一步执行。该过程接受一个输入参数（即，从 AddDBMirroring 表中最新创建的用户数据库名称），并在备份驱动器上执行完整备份和事务日志备份。
CMD CreateFileShare .CMD - 此批处理文件在 D:\ 驱动器上创建一个名为“Backups”的文件夹，并将其共享给所有人，具有完全控制权限。这应该在两个镜像伙伴上手动执行。
T-SQL CreateLinkServer 和 SQLLogin – OnPrincipalInstance.SQL - 这会在主体实例上创建具有主数据库DB读取权限的SQL身份验证账户和一个链接服务器 – “LnkSrvDBMirroring”。
T-SQL CreateLinkServer 和 SQLLogin - OnMirroredInstance.SQL - 这会在镜像实例上创建具有主数据库DB读取权限的SQL身份验证账户和一个链接服务器 – “LnkSrvDBMirroring”。
代理作业 - Auto DB Mirroring Setup.SQL - 此SQL Agent作业在通过触发器（步骤#3）在主SQL实例上创建新数据库后自动调用，该触发器依次执行主要步骤8中的六个批处理文件。
注意：此作业应处于禁用状态，不应安排。

作业 - 自动数据库镜像设置

每个步骤都执行批处理文件（.cmd 文件），该文件调用存储在 D:\Auto Mirroring Setup 文件夹中的 T-SQL 命令文件。

备份：此步骤调用存储过程 – spDBBackup，它触发完整备份和事务日志备份，并更新 AddDBMirroring 表。
恢复：以无恢复模式在镜像服务器上恢复数据库。这会执行 Restore.cmd 文件，该文件调用 Restore.SQL 文件。
CreateEndPointOnPrincipalServer：这会在主体服务器上创建一个名为“Mirroring”的端点，端口号为5022。
CreateEndPointOnMirrorSQLServer：这会在镜像服务器上创建一个名为“Mirroring”的端点，端口号为5022。
MakePrinicpalInstanceAsPartnerOnMirrorServer：这会使用端口号5022将主体实例设为镜像服务器上的伙伴。
MakeMirrorInstanceAsPartnerOnPrincipalServer：这会使用端口号5022将镜像实例设为主体服务器上的伙伴。

备注：如需任何故障排除，您可以参考 - D:\Auto Mirroring Setup\Logs 文件夹中的错误日志。所有成功的历史记录也保存在此文件夹中。

错误日志记录、IBM SQL ITCAM 和 Microsoft SCOM

部分错误将报告在SQL错误日志文件中，并且在 D:\Auto Mirroring Setup\Logs 文件夹中，SQL代理作业[Auto DB Mirroring Setup]会生成一些自定义错误日志输出文件。
它具有自定义错误日志记录机制，该机制使用 xp_logevent 命令，通过触发器“ddl_trig_database”将数据库创建信息作为关键错误写入主体服务器的SQL错误日志中。

此日志信息将由ITCAM代理和SCOM代理读取，以在警报工具中生成警报。
您需要与您的ITM和SCOM工程师合作，根据错误日志文件中生成的错误号和严重性来筛选这些警报。

编码

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. 创建 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 和 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 和 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. 恢复

/*
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. 恢复Cmd文件

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"

假设和依赖项

假设主体和镜像SQL服务器具有相同的驱动器和文件夹结构。
SQL服务必须使用服务账户运行；如果每个伙伴上的服务账户不同，请创建服务账户登录名并授予其他伙伴上的sysadmin权限。
确保SQL DBA AD组\DBA成员\服务账户对指定路径具有完全控制权限
D:\Backups\

D:\Auto Mirroring Setup\
根据镜像设置过程，事务日志备份作业不应在完整备份和事务日志备份之后以及SQL Agent作业执行镜像设置步骤之前触发。T-SQL抛出的一些错误仍然显示SQL Agent作业成功，因为SQLCMD已成功执行，但.SQL文件中抛出的错误未返回并使该步骤处于失败状态。如果您发现作业成功但数据库未设置镜像，请查看日志 - D:\Auto Mirroring Setup\。
在少数情况下，尽管执行中存在一些错误且镜像设置未成功建立，SQL代理作业仍显示成功。这是由于SQLCMD成功执行，但SQL脚本触发了一些错误，并且不会作为失败返回给作业，因此我们必须查看 D:\Auto Mirroring Setup\Logs 文件夹并解决问题。
最好具备T-SQL的基础知识，以便理解自动化过程并从错误日志和作业历史记录中排除故障。

关注点

如果自动化出现任何问题，数据库管理员可以禁用触发器 [ddl_trig_database] 并手动设置数据库镜像。

如果触发器中引用的任何对象未找到或不正确，则无法创建新数据库。在这种情况下，必须禁用或删除触发器。

对 T-SQL 和批处理文件进行的任何额外更改都可能破坏自动化过程，并且必须根据自定义错误日志文件夹中生成的错误进行处理。

历史

2018年7月25日：初始版本1.0