|
+'_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=5022 , 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=5022 , 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=5022 , 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 DB_02_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_02_Mirror FOR LOGIN DB_02_Mirror;
CREATE CERTIFICATE HOST_'
+@mirroriptail
+'_cert AUTHORIZATION DB_02_Mirror FROM FILE ='''+@certpath+'HOST_'+@mirroriptail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DB_02_Mirror];'+CHAR(13)
PRINT @stat
SET @stat='CREATE LOGIN DB_03_Mirror WITH PASSWORD = '''+@LOGINPWD+''';
CREATE USER DB_03_Mirror FOR LOGIN DB_03_Mirror;
CREATE CERTIFICATE HOST_'
+@witnesstail
+'_cert AUTHORIZATION DB_03_Mirror FROM FILE ='''+@certpath+'HOST_'+@witnesstail+'_cert.cer'';'
+'GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [D |