镜像三机
--主体
USE master;
--DROP MASTER KEY;
--统一主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JiuBang_ZB_2008@3g.cn';
GO
--统一的登录密码
CREATE LOGIN MIR_M_LOGIN WITH PASSWORD = 'JiuBang_JB_2008@3g.cn';
GO
--统一的登录用户名
CREATE USER MIR_M_USER FOR LOGIN MIR_M_LOGIN;
GO
---------------------------
USE master;
CREATE CERTIFICATE Cert_001
WITH SUBJECT = 'Cert_001 certificate for database mirroring',
start_date = '01/01/2008',EXPIRY_DATE = '10/31/2099' ;
GO
--建立出站端点
CREATE ENDPOINT EMP_001
STATE = STARTED
AS TCP (LISTENER_PORT=5025, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE Cert_001
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
--配置端点
GRANT CONNECT ON ENDPOINT::EMP_001 TO [MIR_M_LOGIN];
GO
--激活端点
ALTER ENDPOINT EMP_001 STATE=STARTED
GO
--保存导出证书，拷贝到主服务器，将需要在主服务器导入
BACKUP CERTIFICATE Cert_001 TO FILE = 'c:\share\Cert_001.cer';
GO
---------------------
USE master;
CREATE CERTIFICATE Cert_002
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_002.cer'
GO
CREATE CERTIFICATE Cert_003
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_003.cer'
GO
---------------------
USE MASTER;
ALTER DATABASE test
SET PARTNER = 'TCP://192.168.1.170:5025'
GO
ALTER DATABASE test
SET WITNESS = 'TCP://192.168.1.171:5025'
GO
/*
USE MASTER;
ALTER DATABASE test
SET PARTNER off
GO
*/
--查看是否成功
SELECT * FROM sys.database_mirroring WHERE database_id=(
SELECT database_id FROM sys.databases WHERE name='test')
select * from 费用表
update 费用表 set 金额=444566
select * from 费用表
---------------------------------------------------------------------------------------------------------------------
--镜像
USE master;
--DROP MASTER KEY;
--统一主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JiuBang_ZB_2008@3g.cn';
GO
--统一的登录密码
CREATE LOGIN MIR_M_LOGIN WITH PASSWORD = 'JiuBang_JB_2008@3g.cn';
GO
--统一的登录用户名
CREATE USER MIR_M_USER FOR LOGIN MIR_M_LOGIN;
GO
----------------------------------
USE master;
CREATE CERTIFICATE Cert_002
WITH SUBJECT = 'Cert_002 certificate for database mirroring',
start_date = '01/01/2008',EXPIRY_DATE = '10/31/2099' ;
GO
CREATE ENDPOINT EMP_002
STATE = STARTED
AS TCP (LISTENER_PORT=5025, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE Cert_002
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
--配置端点
GRANT CONNECT ON ENDPOINT::EMP_002 TO [MIR_M_LOGIN];
GO
--激活端点
ALTER ENDPOINT EMP_002 STATE=STARTED
GO
--保存导出证书，拷贝到主服务器，将需要在主服务器导入
BACKUP CERTIFICATE Cert_002 TO FILE = 'c:\share\Cert_002.cer';
GO
----------------------
--使指定证书与指定用户相关联
USE master;
CREATE CERTIFICATE Cert_001
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_001.cer'
GO
CREATE CERTIFICATE Cert_003
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_003.cer'
GO
--------------------------
USE MASTER;
ALTER DATABASE test
SET PARTNER = 'TCP://192.168.1.169:5025'
GO
/*
USE MASTER;
ALTER DATABASE test
SET PARTNER off
GO
*/
CREATE DATABASE test_ss_new ON (
NAME = test,
FILENAME = 'C:\test_ss_new.ss' ) AS SNAPSHOT OF test ;
--查询刚才修改的记录
SELECT * FROM test_ss_new.dbo.费用表
----------------------------------------------------------------------------------------------------------------
--见证
USE master;
--DROP MASTER KEY;
--统一主密钥
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JiuBang_ZB_2008@3g.cn';
GO
--统一的登录密码
CREATE LOGIN MIR_M_LOGIN WITH PASSWORD = 'JiuBang_JB_2008@3g.cn';
GO
--统一的登录用户名
CREATE USER MIR_M_USER FOR LOGIN MIR_M_LOGIN;
GO
-----------
USE master;
CREATE CERTIFICATE Cert_003
WITH SUBJECT = 'Cert_003 certificate for database mirroring',
start_date = '01/01/2008',EXPIRY_DATE = '10/31/2099' ;
GO
CREATE ENDPOINT EMP_003
STATE = STARTED
AS TCP (LISTENER_PORT=5025, LISTENER_IP = ALL )
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE Cert_003
, ENCRYPTION = REQUIRED ALGORITHM RC4
, ROLE = ALL
);
GO
--配置端点
GRANT CONNECT ON ENDPOINT::EMP_003 TO [MIR_M_LOGIN];
GO
--激活端点
ALTER ENDPOINT EMP_003 STATE=STARTED
GO
--保存导出证书，拷贝到主服务器，将需要在主服务器导入
BACKUP CERTIFICATE Cert_003 TO FILE = 'c:\share\Cert_003.cer';
GO
--------------------------
USE master;
CREATE CERTIFICATE Cert_001
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_001.cer'
GO
CREATE CERTIFICATE Cert_002
AUTHORIZATION MIR_M_USER
FROM FILE = 'c:\share\Cert_002.cer'
GO
