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

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

 

脚本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/