backup database recoverydb
to disk = 'e:\recoverydb.bak'
with format
IF NOT EXISTS(
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'88888888'
CREATE CERTIFICATE CT_Mirror_SrvA --创建证书
WITH
SUBJECT = N'certificate for database recoverydb',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'
CREATE ENDPOINT Db_Mirroring
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022,
LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SrvC,
ENCRYPTION = DISABLED,
ROLE = all)
BACKUP CERTIFICATE CT_Mirror_SrvA --备份证书
TO FILE = 'E:\CT_Mirror_SrvA.cer'
CREATE LOGIN pc_b_login WITH PASSWORD = 'password123456';
CREATE USER pc_b_user FOR LOGIN pc_b_login;
CREATE CERTIFICATE CT_Mirror_SrvB AUTHORIZATION pc_b_user FROM FILE = 'E:\CT_Mirror_SrvB.cer';
CREATE LOGIN pc_c_login WITH PASSWORD = 'password123456';
CREATE USER pc_c_user FOR LOGIN pc_c_login;
CREATE CERTIFICATE CT_Mirror_SrvC AUTHORIZATION pc_c_user FROM FILE = 'E:\CT_Mirror_SrvC.cer';
CREATE LOGIN pc_a_login WITH PASSWORD = 'password123456';
CREATE USER pc_a_user FOR LOGIN pc_a_login;
GRANT CONNECT ON ENDPOINT::Db_Mirroring TO [pc_b_login];
GRANT CONNECT ON ENDPOINT::Db_Mirroring TO [pc_c_login];
GRANT CONNECT ON ENDPOINT::Db_Mirroring TO [pc_a_login];
ALTER DATABASE recoverydb SET PARTNER = N'TCP://192.168.100.101:5022' ALTER DATABASE recoverydb SET witness = N'TCP://192.168.100.103:5022'