--==================================================================
--该文档主要用于内部配置模板
---------------------------------------------------------------------
--为partner创建证书
USE master;
GO
--=========================================================================================================
--创建Master key
IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name=‘##MS_DatabaseMasterKey##‘)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘master@key123‘
END
GO
IF NOT EXISTS(SELECT 1 FROM sys.databases db WHERE db.[is_master_key_encrypted_by_server]=1)
BEGIN
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
END
GO
 
--=========================================================================================================
--创建和备份证书
IF NOT EXISTS (SELECT 1 FROM sys.certificates C WHERE C.Name=‘HOST_3_32_cert‘ )
BEGIN
CREATE CERTIFICATE HOST_3_32_cert WITH SUBJECT =‘HOST_3_32_cert‘,
START_DATE = ‘01/01/2010‘ , EXPIRY_DATE = ‘01/01/2199‘;
END
GO
BACKUP CERTIFICATE HOST_3_32_cert TO FILE=‘D:\HOST_3_32_cert.cer‘
GO
 
--=========================================================================================================
--创建镜像专用的端点，并使用证书加密
--同一个实例上只能存在一个镜像端点
IF NOT EXISTS(SELECT * FROM sys.endpoints e WHERE e.[Type]=4)
BEGIN
CREATE ENDPOINT [Endpoint_Mirroring]
STATE=STARTED AS
TCP(LISTENER_PORT=5022)
FOR DATABASE_MIRRORING(AUTHENTICATION=CERTIFICATE HOST_3_32_cert, ENCRYPTION=REQUIRED,ROLE=ALL)
END
GO
 
---------------------------------------------------------------------
--为partnert创建login和user,and grant the right of connection
USE master;
 
GO
 
IF NOT EXISTS(SELECT 1 FROM sys.syslogins l WHERE l.[Name] =‘HOST_3_32_login‘)
 
BEGIN
 
CREATE LOGIN HOST_3_32_login WITH PASSWORD =‘Auto@sql‘
 
END
 
GO
 
IF NOT EXISTS(SELECT 1 FROM sys.sysusers u WHERE u.[Name]= ‘HOST_3_32_User‘)
 
BEGIN
 
CREATE USER HOST_3_32_User FOR LOGIN HOST_3_32_login
 
END
 
GO
 
IF NOT EXISTS(SELECT 1 FROM sys.certificates c WHERE c.[Name]= ‘HOST_3_32_cert‘)
 
BEGIN
 
CREATE CERTIFICATE HOST_3_32_cert AUTHORIZATION HOST_3_32_User FROM FILE=‘D:\HOST_3_32_cert.cer‘
 
END
 
GO
 
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO HOST_3_32_login
 
GO
 
------------------------------------------------------------------
--从服务器设置镜像
USE [master]
GO
ALTER DATABASE promotion_datacenter SET PARTNER=‘TCP://192.168.3.32:5022‘
GO
------------------------------------------------------------------
--主服务器设置镜像
USE [master]
GO
ALTER DATABASE promotion_datacenter SET PARTNER=‘TCP://192.168.3.32:5022‘
GO
USE [master]
GO
ALTER DATABASE promotion_datacenter SET PARTNER SAFETY OFF
GO

Mirror--使用证书配置镜像模板