镜像的搭建非常繁琐，花了一点时间写了这个脚本，方便大家搭建镜像
执行完这个镜像脚本之后，最好在每台机器都绑定一下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+''';'
+'CREATE CERTIFICATE HOST_'
+@masteriptail
+'_cert WITH SUBJECT = ''HOST_'
+@masteriptail
+'_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_'
+@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