SQL Server镜像自动生成脚本
SQL Server镜像自动生成脚本
镜像的搭建非常繁琐,花了一点时间写了这个脚本,方便大家搭建镜像
执行完这个镜像脚本之后,最好在每台机器都绑定一下hosts文件,不然的话,镜像可能会不work
192.168.1.1 WSQL01
192.168.1.2 WSQL02
192.168.1.3 WWEB03
SQL2008R2升级到SQL2014,升级之前先对数据库进行完整和日志备份,以免升级失败
请注意:--★Do部分都是需要填写的
-- ============================================= -- Author: <桦仔> -- Blog: <http://www.cnblogs.com/lyhabc/> -- Create date: <2013/8/18> -- Description: <镜像自动生成脚本> -- ============================================= --环境:非域环境 DECLARE @DBName NVARCHAR(255) DECLARE @masterip NVARCHAR(255) DECLARE @mirrorip NVARCHAR(255) DECLARE @witness NVARCHAR(255) DECLARE @masteriptail NVARCHAR(255) DECLARE @mirroriptail NVARCHAR(255) DECLARE @witnesstail NVARCHAR(255) DECLARE @certpath NVARCHAR(MAX) DECLARE @Restorepath NVARCHAR(MAX) DECLARE @Restorepath1 NVARCHAR(MAX) DECLARE @Restorepath2 NVARCHAR(MAX) DECLARE @MKPASSWORD NVARCHAR(500) DECLARE @LOGINPWD NVARCHAR(500) DECLARE @LISTENER_PORT NVARCHAR(500) DECLARE @SQL NVARCHAR(MAX) DECLARE @MASTERHOST_NAME NVARCHAR(50) DECLARE @SLAVEHOST_NAME NVARCHAR(50) DECLARE @WITNESSHOST_NAME NVARCHAR(50) if OBJECT_ID ('tempdb..#temp')is not null BEGIN DROP TABLE #BackupFileList END CREATE TABLE #BackupFileList ( LogicalName NVARCHAR(100) , PhysicalName NVARCHAR(100) , BackupType CHAR(1) , FileGroupName NVARCHAR(50) , SIZE BIGINT , MaxSize BIGINT , FileID BIGINT , CreateLSN BIGINT , DropLSN BIGINT NULL , UniqueID UNIQUEIDENTIFIER , ReadOnlyLSN BIGINT NULL , ReadWriteLSN BIGINT NULL , BackupSizeInBytes BIGINT , SourceBlockSize INT , FileGroupID INT , LogGroupGUID UNIQUEIDENTIFIER NULL , DifferentialBaseLSN BIGINT NULL , DifferentialBaseGUID UNIQUEIDENTIFIER , IsReadOnly BIT , IsPresent BIT , TDEThumbprint NVARCHAR(100) ) SET NOCOUNT ON SET @masterip='172.16.198.254' --★Do SET @mirrorip='172.16.198.253' --★Do SET @witness='999999' --★Do --目录后面不要带分隔符: \ SET @certpath='D:\DBBackup' --★Do SET @Restorepath='D:\DBBackup' --★Do SET @DBName='testmirror' --★Do SET @MKPASSWORD='master@2015key123' --★Do SET @LOGINPWD='User_Pass@2015key123' --★Do SET @LISTENER_PORT='5022' --★Do SET @MASTERHOST_NAME='A' --★Do SET @SLAVEHOST_NAME='B' --★Do SET @WITNESSHOST_NAME='C' --★Do select @masteriptail= PARSENAME(@masterip,2)+'_'+PARSENAME(@masterip,1) select @mirroriptail= PARSENAME(@mirrorip,2)+'_'+PARSENAME(@mirrorip,1) select @witnesstail= PARSENAME(@witness,2)+'_'+PARSENAME(@witness,1) -------------------------------------------------------------------------------- DECLARE @stat NVARCHAR(MAX) SET @stat='--自动生成镜像脚本V1 By huazai' PRINT @stat PRINT CHAR(13)+CHAR(13) SET @stat='--0、首先确定要做镜像的库的恢复模式为完整,用以下sql语句来查看'+CHAR(13) +'--主机'+CHAR(13) +'SELECT [name], [recovery_model_desc] FROM sys.[databases]'+CHAR(13)+CHAR(13)+CHAR(13) PRINT '--主:'+@masterip PRINT '--备:'+@mirrorip PRINT '--见证:'+@witness PRINT CHAR(13)+CHAR(13) PRINT @stat -------------------------------------------------------------------- PRINT '-- =============================================' SET @stat='--1、 在主服务器和镜像服务器上和见证服务器上创建Master Key 、创建证书 '+CHAR(13) +'--主机'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';'+CHAR(13) +'CREATE CERTIFICATE HOST_' +@masteriptail +'_cert WITH SUBJECT = ''HOST_' +@masteriptail +'_certificate'',' +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13) PRINT @stat SET @stat='--备机'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';' +'CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert WITH SUBJECT = ''HOST_' +@mirroriptail +'_certificate'','+CHAR(13) +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13) PRINT @stat SET @stat='--见证'+CHAR(13) +'USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '''+@MKPASSWORD+''';' +'CREATE CERTIFICATE HOST_' +@witnesstail +'_cert WITH SUBJECT = ''HOST_' +@witnesstail +'_certificate'','+CHAR(13) +'START_DATE = ''09/20/2010'',EXPIRY_DATE = ''01/01/2099'';'+CHAR(13)+CHAR(13)+CHAR(13)+CHAR(13) PRINT @stat ----------------------------------------------------------- PRINT '-- =============================================' SET @stat='--2、创建镜像端点,同一个实例上只能存在一个镜像端点 '+CHAR(13) +'--主机'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@masteriptail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13) PRINT @stat SET @stat='--备机'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@mirroriptail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13) PRINT @stat SET @stat='--见证'+CHAR(13) +'CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT='+@LISTENER_PORT+' , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_' +@witnesstail +'_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );'+CHAR(13)+CHAR(13)+CHAR(13) PRINT @stat ---------------------------------------------------------------------------------------- PRINT '-- =============================================' SET @stat='--3、备份证书,然后互换 '+CHAR(13) +'--主机'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@masteriptail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13) PRINT @stat SET @stat='--备机'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@mirroriptail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13) PRINT @stat SET @stat='--见证'+CHAR(13) +'BACKUP CERTIFICATE HOST_' +@witnesstail +'_cert TO FILE = '+''''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13)+CHAR(13)+CHAR(13) PRINT @stat ---------------------------------------------------------------------------------- PRINT '-- =============================================' SET @stat='--4、新增主备登陆用户 '+CHAR(13) +'--主机'+CHAR(13) +'CREATE LOGIN ['+@SLAVEHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@SLAVEHOST_NAME+'User] FOR LOGIN ['+@SLAVEHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert AUTHORIZATION ['+@SLAVEHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@SLAVEHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat SET @stat='CREATE LOGIN ['+@WITNESSHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@WITNESSHOST_NAME+'User] FOR LOGIN ['+@WITNESSHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@witnesstail+ +'_cert AUTHORIZATION ['+@WITNESSHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@WITNESSHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat SET @stat='--备机'+CHAR(13) +'CREATE LOGIN ['+@MASTERHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@MASTERHOST_NAME+'User] FOR LOGIN ['+@MASTERHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@masteriptail +'_cert AUTHORIZATION ['+@MASTERHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@MASTERHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat SET @stat='CREATE LOGIN ['+@WITNESSHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@WITNESSHOST_NAME+'User] FOR LOGIN ['+@WITNESSHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@witnesstail+ +'_cert AUTHORIZATION ['+@WITNESSHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@witnesstail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@WITNESSHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat SET @stat='--见证'+CHAR(13) +'CREATE LOGIN ['+@MASTERHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@MASTERHOST_NAME+'User] FOR LOGIN ['+@MASTERHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@masteriptail +'_cert AUTHORIZATION ['+@MASTERHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@masteriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@MASTERHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat SET @stat='CREATE LOGIN ['+@SLAVEHOST_NAME+'LoginUser] WITH PASSWORD = '''+@LOGINPWD+'''; CREATE USER ['+@SLAVEHOST_NAME+'User] FOR LOGIN ['+@SLAVEHOST_NAME+'LoginUser]; CREATE CERTIFICATE HOST_' +@mirroriptail +'_cert AUTHORIZATION ['+@SLAVEHOST_NAME+'User] FROM FILE ='''+@certpath+'\HOST_'+@mirroriptail+'_cert.cer'';'+CHAR(13) +'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO ['+@SLAVEHOST_NAME+'LoginUser];'+CHAR(13) PRINT @stat ------------------------------------------------------------------------------ PRINT '-- =============================================' SET @stat='--5、各个机器都开放5022端口,并且用telnet测试5022端口是否开通 将下面三个脚本各自粘贴到bat文件里'+CHAR(13) PRINT @stat SET @stat='echo 主库'+CHAR(13) +'telnet '+@mirrorip+' 5022'+CHAR(13) +'telnet '+@witness+' 5022'+CHAR(13) +'pause' PRINT @stat+CHAR(13)+CHAR(13) SET @stat='echo 镜像库'+CHAR(13) +'telnet '+@masterip+' 5022'+CHAR(13) +'telnet '+@witness+' 5022'+CHAR(13) +'pause' PRINT @stat+CHAR(13)+CHAR(13) SET @stat='echo 见证'+CHAR(13) +'telnet '+@masterip+' 5022'+CHAR(13) +'telnet '+@mirrorip+' 5022'+CHAR(13) +'pause' PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13) -------------------------------------------------------------- PRINT '-- =============================================' SET @stat='--6、备份数据库(完整备份+事务日志备份)在主机执行'+CHAR(13) PRINT @stat SET @stat='DECLARE @FileName NVARCHAR(MAX)'+CHAR(13)+CHAR(13) PRINT @stat SET @stat='--('+@DBName+'数据库完整备份)在主机执行'+CHAR(13) +'SET @FileName = ''D:\DBBackup\'+@DBName+'_FullBackup_1.bak'' BACKUP DATABASE ['+@DBName+'] TO DISK=@FileName WITH FORMAT ,COMPRESSION'+CHAR(13)+CHAR(13) PRINT @stat SET @stat='--('+@DBName+'数据库日志备份)在主机执行'+CHAR(13) +'SET @FileName = ''D:\DBBackup\'+@DBName+'_logBackup_2.bak'' BACKUP LOG ['+@DBName+'] TO DISK=@FileName WITH FORMAT ,COMPRESSION' PRINT @stat+CHAR(13)+CHAR(13)+CHAR(13) ------------------------------------------------------------------------------ PRINT '-- =============================================' SET @stat='--7、还原数据库(指定norecovery方式还原)在备机执行'+CHAR(13) PRINT @stat SET @Restorepath1='' SET @Restorepath2=@Restorepath+@DBName+'_FullBackup_1.bak' SET @SQL = 'RESTORE FILELISTONLY FROM DISK = '''+@Restorepath2+'''' INSERT INTO #BackupFileList EXEC (@SQL); DECLARE @LNAME NVARCHAR(2000) DECLARE @PNAME NVARCHAR(2000) DECLARE CurTBName CURSOR FOR SELECT LogicalName,PhysicalName FROM #BackupFileList OPEN CurTBName FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME WHILE @@FETCH_STATUS = 0 BEGIN SET @Restorepath1=' MOVE N'''+@LNAME+''' TO N'''+@PNAME+''', '+CHAR(13)+@Restorepath1 FETCH NEXT FROM CurTBName INTO @LNAME,@PNAME END CLOSE CurTBName DEALLOCATE CurTBName SET @stat='USE [master] RESTORE DATABASE '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_FullBackup_1.bak'' WITH FILE = 1,'+CHAR(13) +@Restorepath1 +'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5 GO' SET @stat='USE [master] RESTORE LOG '+@DBName+' FROM DISK = N'''+@Restorepath+@DBName+'_logBackup_2.bak'' WITH FILE = 1,'+CHAR(13) +'NOUNLOAD,NORECOVERY, REPLACE, STATS = 5 GO' PRINT @stat+CHAR(13)+CHAR(13) DROP TABLE #BackupFileList -------------------------------------------------------------------------------- PRINT '-- =============================================' SET @stat='--8、增加镜像伙伴,需要先在备机上执行,再执行主机,镜像弄好之后,默认为事务安全等级为FULL'+CHAR(13) PRINT @stat SET @stat='--备机上执行'+CHAR(13) +'USE [master] GO ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@masterip+':5022''; --主机服务器的ip'+CHAR(13)+CHAR(13) PRINT @stat SET @stat='--主机上执行'+CHAR(13) +'USE [master] GO ALTER DATABASE ['+@DBName+'] SET PARTNER = '''+'TCP://'+@mirrorip+':5022''; --镜像服务器的ip'+CHAR(13)+CHAR(13) PRINT @stat SET @stat='ALTER DATABASE ['+@DBName+'] SET WITNESS = '''+'TCP://'+@witness+':5022''; --见证服务器的ip'+CHAR(13)+CHAR(13) PRINT @stat
希望对大家有帮助
最后附上镜像相关脚本
--================================= --拆除镜像 SELECT DB_NAME([database_id]) as 'dbname',* FROM sys.[database_mirroring] GO ALTER DATABASE [test] SET PARTNER OFF ALTER DATABASE [test] SET WITNESS OFF --================================= --恢复镜像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER RESUME GO --================================= --挂起镜像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SUSPEND GO --=================================================== --未发送的日志和未重做的日志情况 WITH tmp AS( SELECT DB_NAME(Database_id) AS DatabaseName, ROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) AS RID, * FROM msdb.dbo.dbm_monitor_data ) SELECT * FROM tmp WHERE RID=1 --看一下redo_queue 和send_queue --================================= --删除镜像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER OFF GO --================================= --移除见证服务器 USE [master] GO ALTER DATABASE [Demo1] SET WITNESS OFF GO --================================= --修改为高性能模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY OFF GO --================================= --修改为高安全模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY FULL GO --================================= --在高安全下手动转移镜像(在主服务器上) USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FAILOVER GO --================================= --在高性能下手动转移镜像(在从服务器上),此时主服务器已停止 --同样适用高安全 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS GO --================================= --在镜像被挂起后恢复镜像回话 --如镜像服务器停止后又重启时,主体服务器会被挂起,使用以下SQL来恢复镜像 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER RESUME GO --================================= --将数据库从还原状态转化成正常模式 USE [master] GO RESTORE DATABASE [Demo1] WITH RECOVERY GO --================================= --修改为高安全模式 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER SAFETY FULL GO --================================= --在高性能下手动转移镜像(在从服务器上),此时主服务器已停止 --同样适用高安全 USE [master] GO ALTER DATABASE [Demo1] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS GO
升级之后
USE [master] GO ALTER DATABASE [testmirror] SET COMPATIBILITY_LEVEL = 120 GO /****** Object: Endpoint [Endpoint_Mirroring] Script Date: 2016/12/29 9:23:18 ******/ DROP ENDPOINT [Endpoint_Mirroring] GO
相关视图
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-mirroring-transact-sql
需要私钥
在SQL Server的高可用性配置中,如数据库镜像或AlwaysOn可用性组,PRIVATE KEY 主要用于加密和解密证书的私钥。 证书就是公钥 PRIVATE KEY 就是私钥 这是一个非常重要的部分,特别是在跨服务器或跨副本之间交换和保护证书时。其作用可以从以下几个方面理解: 1、加密保护 PRIVATE KEY 用于加密证书的私钥,确保它仅对授权用户可用。在高可用性部署中,所有的副本需要访问共享的证书以建立安全的连接,PRIVATE KEY 保护了这个证书,使得没有正确密码的用户无法使用这个证书。 2、跨节点安全通信 在数据库镜像或AlwaysOn集群的设置中,证书被用来进行数据库实例间的身份验证。 当数据库实例通过证书进行身份验证时,它们会加密通信内容,包括端点的身份验证。 如果没有私钥,证书将无法正常工作,可能会导致连接失败。 3、数据库高可用性配置 在数据库镜像和AlwaysOn配置中,证书和其私钥的使用确保了数据库之间安全的事务日志传输以及高可用性和灾难恢复方案的可靠性。 是否可以不使用 PRIVATE KEY? 严格来说,在搭建数据库镜像或AlwaysOn集群时, PRIVATE KEY 并不是必须的,但是它提供了更高的安全性。 没有私钥的情况下,数据库可能无法正确地进行跨副本或跨节点的安全通信, 特别是在需要证书保护的连接(如镜像端点的身份验证)时。 在没有【加密证书私钥】的情况下,证书仍然可以用作身份验证,但会面临潜在的安全风险。 因此,虽然理论上可以不使用私钥,但为了保证系统的安全性和高可用性,强烈建议使用私钥来保护证书。 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1111.aaa'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'; BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', ENCRYPTION BY PASSWORD = '1111.aaa' ); CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1111.aaa'; CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', DECRYPTION BY PASSWORD = '1111.aaa' );
本文版权归作者所有,未经作者同意不得转载。
分类:
SQLSERVER高可用
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
2013-09-14 SQLSERVER NULL和空字符串的区别 使用NULL是否节省空间
2013-09-14 SQLSERVER中NULL位图的作用