脚本分享：别人家的镜像自动化部署

脚本1：

实例名：

主服务器：X

镜像服务器：Y

步骤：

1. 在主服务器和镜像服务器之间创建链接服务器，配置为使用RPC（Remote procedure call）调用，为了执行SP。

例如：lnk_X_Y_mirroring（链接服务器名）

2. 在主服务器上创建以下SP。

3. 根据需要使用作业每30分钟或1小时调用SP（usp_Mirroring_Configuration）。

4. 一旦一个新数据库创建，镜像会通过作业的执行被部署。

5. 将触发“Mirroring Configuration”为名的告警，内容为“Mirroring Configured for db_55555 and safety level is OFF”

/*******************************************************************************
DESCRIPTION: Automatic Mirroring_Configuration for new databases
AUTHOR: Vasan
DATE WRITTEN: 17 MARCH 2015
Automatic Mirroring configuration for newly created user databases:
-------------------------------------------------------------------
Instance names:
---------------
Principle server : X
Mirroring Server : Y
Steps to follow:
1. Create link server between principle server and mirror server with RPC (Remote procedure call) set as true in order to execute the procedure.
Eg : lnk_X_Y_mirroring (Link server name)
2. Create below stored procedure in to principle server.
3. Schedule this Stored procedure (usp_Mirroring_Configuration) with 30mins/1hr interval via job
4. Once the new database created in server mirroring will be configure.
5. Alert will be trigger with the subject anme of Mirroring Configuration
Mail contect:“Mirroring Configured for db_55555 and safety level is OFF”
*******************************************************************************/
Begin
SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..##tbl_new_databases') IS NOT NULL
BEGIN
DROP TABLE ##tbl_new_databases
END
Declare @dbname nvarchar(max)
Declare @Path nvarchar(500)
Declare @todiskbkp nvarchar(max)
Declare @todisklogbkp nvarchar(max)
Declare @SQL nvarchar(max)
Declare @count SMALLINT
Declare @SQLpartnerprimary nvarchar(max)
Declare @SQLmirror nvarchar(max)
Declare @Mirrorsaftey nvarchar(max)
-- Restore Part Variables
Declare @RestorePath nvarchar(500)
Declare @fromdiskbkp nvarchar(max)
Declare @fromdisklogbkp nvarchar(max)
Declare @SQLrestore nvarchar(max)
Declare @SQLpartnermirror nvarchar(max)
--Mail Part
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
SELECT A.name into ##tbl_new_databases
FROM
sys.databases A INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4 and B.mirroring_state is NULL and A.is_read_only=0 and state_desc='ONLINE'
if (Select COUNT(*) from ##tbl_new_databases)>0
Begin
DECLARE DB CURSOR FOR
-- Fetching newly created database to configure mirroring
Select name from ##tbl_new_databases
OPEN DB
FETCH NEXT FROM DB INTO @dbname
WHILE @@FETCH_STATUS=0
BEGIN
-- changing recovery model from Simple to Full for new database since new database created with simple recovery model
set @sql= 'if (SELECT recovery_model_desc FROM sys.databases where name = '''+@dbname+''' ) = ''SIMPLE''
begin
Use master
ALTER DATABASE '+@dbname+' SET RECOVERY FULL WITH NO_WAIT
end'
--print @sql
EXEC master.dbo.sp_executesql @SQL
-- Setting Backup folder path for primary server
--(Backup should be placed some common path or with in mirror server for easy recovery process) here i chose mirror server local drive
Set @dbname=@dbname
Set @Path='\\Mirror Server Name (Y)\d$\backup\'+@dbname
set @todiskbkp=@Path+'\'+@dbname+'_'+ replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','')+'.bak'
set @todisklogbkp=@Path+'\'+@dbname+'_'+ replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','')+'.trn'
-- Creating new backup folder for new db in Mirror server
EXEC master.dbo.xp_create_subdir @Path
--Create Database Backups for new db
set @SQL= '
BACKUP DATABASE ' +@dbname+'
TO DISK = '''+@todiskbkp+'''
WITH FORMAT;
BACKUP LOG ' +@dbname+'
TO DISK ='''+@todisklogbkp+'''
WITH FORMAT;
'
--Print @SQL
EXEC master.dbo.sp_executesql @SQL
--Restoring the new dataabse to Mirror server
-- Setting Restor path for mirror server
Set @RestorePath='D:\backup\'+@dbname
set @fromdiskbkp=@RestorePath+'\'+@dbname+'_'+ replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','')+'.bak'
set @fromdisklogbkp=@RestorePath+'\'+@dbname+'_'+ replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','')+'.trn'
--Create the Mirror Database (restoring the new database backup into mirror server)
set @SQLrestore='
RESTORE DATABASE '+@dbname+' FROM DISK = N'''+@fromdiskbkp+''' WITH FILE = 1,
MOVE N'''+@dbname+''' TO N''D:\data\'+@dbname+'.mdf'',
MOVE N'''+@dbname+'_log'' TO N''D:\log\'+@dbname+'.LDF'',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
RESTORE LOG '+@dbname+' FROM DISK = N'''+@fromdisklogbkp+'''
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
'
--print @SQLrestore
EXEC lnk_X_Y_mirroring.master.dbo.sp_executesql @SQLrestore
--Listener Port on mirror server
set @SQLpartnermirror= '
ALTER DATABASE '+@dbname+'
SET PARTNER=''tcp://Domain Name:Port Number'';
'
--print @SQLpartnermirror
EXEC lnk_X_Y_mirroring.master.dbo.sp_executesql @SQLpartnermirror
--Listener Port on principal server
set @SQLpartnerprimary='
ALTER DATABASE '+@dbname+'
SET PARTNER=''tcp://Domain Name:Port Number'';'
EXEC master.dbo.sp_executesql @SQLpartnerprimary
--Print @SQLpartnerprimary
--Set Mirroring Operating Mode to “High Performance”
set @Mirrorsaftey='
ALTER DATABASE '+@dbname+' SET SAFETY OFF
'
--print @SQLpartnermirror
EXEC master.dbo.sp_executesql @Mirrorsaftey
FETCH NEXT FROM DB INTO @DBNAME
END
CLOSE DB
DEALLOCATE DB
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBAProfile',
@recipients = 'xyz@gmail.com',
@subject = 'Mirroring Configuration',
@query='set nocount on SELECT ''Mirroring Configured for ''+ A.name +'' and safety level is ''+ B.[mirroring_safety_level_desc] COLLATE Latin1_General_CI_AI
FROM
sys.databases A
INNER JOIN sys.database_mirroring B
ON A.database_id=B.database_id
WHERE a.database_id > 4 AND A.name in (Select name from ##tbl_new_databases)',
@query_result_no_padding =0,
@query_result_width = 32767,
@query_no_truncate = 1,
@attach_query_result_as_file = 0,
@Body_format = 'text' ,
@append_query_error = 1
Drop table ##tbl_new_databases
END
END

脚本2：

准备步骤：

1. 安装和一个辅助SQL Server实例（版本、补丁和LICENSE类型跟主服务器实例一样）。

2. SQL Server连接启用TCP/IP协议。

3. 为备份和恢复操作配置网络共享，类似\\dfsroot\sqlbackup

4. 允许使用xp_cmdshell扩展存储过程。

仅通过一个触发器访问执行所有的操作是不行的。这个方法包含四个组件：1个事件接收器表，1个工作负载存储过程，1个DDL触发器和1个以一定间隔执行之前提到的存储过程的SQL Server代理作业。

步骤1：创建1个事件接收器表

该表将会保存CREATE数据库动作发生时的名字和SQL事件。事件类型不重要，我们只对数据库名感兴趣。

USE [master]
GO
/****** Object: Table [dbo].[new_db_table] Script Date: 05/19/2008 15:26:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[new_db_table](
[name] [nvarchar](100) NULL,
[event] [nvarchar](100) NULL
) ON [PRIMARY]

步骤2：创建自动化存储过程

这是主要的脚本，用于检查动作表，执行备份初始化。在SQL Server 2005，对于附加数据库动作是没有数据库事件的，即便事实上它是一个CREATE_DATABASE事件。在SQL Server 2008解决了，导致触发器对于ATTACH和CREATE动作，在动作表都添加一个条目。

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_auto_mirror_config] Script Date: 07/10/2008 10:56:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— ============================================================================
— Author: <Jeff Sani,jeffs@sitemasher.com>
— Create date: <4/9/2008>
— Description: <This stored proc automates the configuration of db mirroring>
— Syntax: <exec sp_auto_mirror_config>
— =============================================================================
CREATE proc [dbo].[sp_auto_mirror_config]
as
declare @dbname sysname, @bckstmt nvarchar(500), @cmd varchar(250), @bupath varchar(100)
declare @mirrorsp nvarchar(100), @mirrorsql nvarchar(500), @altersql nvarchar(250)
declare @primarysrvr nvarchar(50), @mirrorsrvr nvarchar(50), @witnes***vr nvarchar(50), @domain nvarchar(50)
–set your sql server and backup paths here
set @bupath = ‘\\sql1\sqlbackup’
set @primarysrvr = ‘sql1’
set @mirrorsrvr = ‘sql2’
set @domain = ‘.staging.local’
set @witnes***vr = ‘smres2’
set @mirrorsp = @mirrorsrvr + ‘.master.dbo.sp_executesql ‘
begin
if (select count(*) from new_db_table where event = ‘CREATE_DATABASE’) > 0
begin
create table #userdbs (name sysname)
insert into #userdbs select name from new_db_table
declare cdb cursor for select name from #userdbs
open cdb
fetch cdb into @dbname
while @@fetch_status = 0
begin
–Check to make sure that Auto_Close and Auto_Shrink DB Properties are correct and that Recovery is Full
set @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_CLOSE off’
exec (@altersql)
set @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set AUTO_SHRINK on’
exec (@altersql)
set @altersql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set RECOVERY full’
exec (@altersql)
–perform initial database backup
set @bckstmt = ‘backup database ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39) + @bupath + ‘\’ + @dbname + ‘.bak’ + char(39)
exec (@bckstmt)
–perform initial database log backup
set @bckstmt = ‘backUp log ‘ + char(91) + @dbname + char(93)+ ‘ to ‘ +
‘disk = N’ + char(39) + @bupath + ‘\’ + @dbname + ‘_log.bak’ + char(39)
exec (@bckstmt)
–perform database restore on linked remote mirror sql server
set @bckstmt = ‘restore database ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39) + @bupath + ‘\’ + @dbname + ‘.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt
–perform database log restore on linked remote mirror sql server
set @bckstmt = ‘restore log ‘ + char(91) + @dbname + char(93) + ‘ from ‘ +
‘Disk = N’ + char(39) + @bupath + ‘\’ + @dbname + ‘_log.bak’ + char(39) + ‘ with norecovery, replace’
exec @mirrorsp @bckstmt
–Initiate the mirroring on The Mirror server:
set @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @primarysrvr + @domain + ‘:5022’ + char(39)
exec @mirrorsp @mirrorsql
–Initiate the mirroring on The Primary server:
set @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set partner= N’+ char(39) + ‘TCP://’ + @mirrorsrvr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)
–Enable the mirroring session on the Witness server:
set @mirrorsql = ‘alter database ‘ + char(91) + @dbname + char(93) + ‘ set witness= N’+ char(39) + ‘TCP://’ + @witnes***vr + @domain + ‘:5022’ + char(39)
exec (@mirrorsql)
–cleanup
delete from new_db_table where name = @dbname
set @cmd = ‘del ‘ + @bupath + ‘\’ + @dbname + ‘.bak’
exec xp_cmdshell @cmd
set @cmd = ‘del ‘ + @bupath + ‘\’ + @dbname + ‘_log.bak’
exec xp_cmdshell @cmd
fetch cdb into @dbname
end
close cdb
deallocate cdb
drop table #userdbs
end
end

步骤3：创建触发器

当匹配CREATE_DATABASE的事件发生时，它将触发，将数据库名写入事件接收器表。

/****** Object: DdlTrigger [trg_MirrorDDL] Script Date: 05/19/2008 15:31:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [trg_MirrorDDL]
ON ALL SERVER
FOR CREATE_DATABASE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @data XML;
DECLARE @eventType sysname;
DECLARE @dbname varchar(100);
DECLARE @mirrorsql varchar(500);
SET @data = EVENTDATA();
SET @eventType = @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘sysname’);
SET @dbname = @data.value(‘(/EVENT_INSTANCE/ DatabaseName)[1]’, ‘sysname’);
–Add to new_db_table
Insert new_db_table(name,event) select @dbname,@eventType where not exists (select * from new_db_table where name = @dbname);
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [trg_MirrorDDL] ON ALL SERVER

步骤4：创建SQL Server代理作业

这个作业主要的目的是监控事件接收器表的新增条目。我原本想在事件接收器表上用一个触发器，但是你可能不想自动化，而是想通过作业来按需调度存储过程的执行可能会更好。

USE [msdb]
GO
/****** Object: Job [Mirroring Agent] Script Date: 05/19/2008 15:32:54 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 05/19/2008 15:32:54 ******/
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’Mirroring Agent’,
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N’No description available.’,
@category_name=N'[Uncategorized (Local)]’,
@owner_login_name=N’SMNET\administrator’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Run Mirroring Stored Procedure] Script Date: 05/19/2008 15:32:55 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run Mirroring Stored Procedure’,
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N’TSQL’,
@command=N’USE [master]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_auto_mirror_config]
SELECT ”Return Value” = @return_value
GO’,
@database_name=N’master’,
@flags=4
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Mirror Agent Schedule’,
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20080410,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

后记：

这两个脚本都是将数据直接备份到远程UNC路径。笔者之前在Bash下写过镜像自动化部署，日志传送自动化部署、检查和切换的脚本，数据传输用的winscp.exe，思路也就是把这些核心SQL封装调用。

参考：

http://www.sqlservercentral.com/scripts/Mirroring/132938/

https://jeffsani.wordpress.com/2009/03/17/how-to-automate-sql-mirroring/