可下载附件
/*
**********************主机ANNATROV******************************
*/
/*
数据库镜像
主机:ANNATROV
备机:JOHN
见证机:KATE
以上三台机器均:XP SP3;SQL SERVER 2008 SP1.备机,见证机均为VM虚拟机,主机为本机
由于系统是XP,所以没有做域.因此采用证书认证方式来连接.所以,如果要用于透明数据加密的请注意.MASTER证书可以共用,需要建的是数据库证书.
数据库要能够互相访问,不懂的端口的可以把防火墙关掉.
由于数据库镜像只对数据库内数据进行同步,因此主库上的(msdb)作业,(master)登陆名需要手工同步,以免造成不必要的麻烦.
*/
/*
如果数据库MASTER有加密码了,可以删除.
删除证书
--SET ENCRYPTION OFF
DROP CERTIFICATE HOST_C_cert --删除加密的证书,就是加密码的数据库证书.
drop MASTER KEY --删除主密钥,也就是MASTER的
drop database encryption key
*/
--
创建证书
USE
master;
CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
'
OOOooo
'
;
CREATE
CERTIFICATE ANNATROV
WITH
SUBJECT
=
'
ANNATROV
'
,
START_DATE
=
'
2010-07-23
'
;
--
创建链接端点
CREATE
ENDPOINT Endpoint_Mirroring
STATE
=
STARTED
AS
TCP ( LISTENER_PORT
=
5022
, LISTENER_IP
=
ALL
)
FOR
DATABASE_MIRRORING
( AUTHENTICATION
=
CERTIFICATE ANNATROV , ENCRYPTION
=
REQUIRED ALGORITHM AES , ROLE
=
ALL
);
--
备份证书,并拷贝证书至各机确保互联
BACKUP
CERTIFICATE ANNATROV
TO
FILE
=
'
g:\test\ANNATROV.cer
'
;
/*
主机,备机,见证机三机都备份完证书以后,然后再将各自的证书拷贝到其他两台机器上,因为下面的添加登陆名创建的账号需要各自的
证书来验证.比如主机ANNATROV上,就需要有JOHN,KATE的证书
*/
--
添加登陆名,用户
--
备机用户--JOHN
CREATE
LOGIN JOHN
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
JOHN
FOR
LOGIN JOHN;
CREATE
CERTIFICATE JOHN
AUTHORIZATION
JOHN
FROM
FILE
=
'
g:\test\JOHN.cer
'
;
--
证书验证
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
JOHN
]
;
--
见证机用户--KATE
CREATE
LOGIN KATE
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
KATE
FOR
LOGIN KATE;
CREATE
CERTIFICATE KATE
AUTHORIZATION
KATE
FROM
FILE
=
'
g:\test\KATE.cer
'
;
--
证书验证
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
KATE
]
;
/*
--手工同步登陆名,密码
--镜像的缺点就是不能同步数据库用户名和作业,因为需要手工同这两项.
--在主机上找出合建名
USE master;
select sid,name from syslogins;
--在备机上创建登陆名
USE master;
exec sp_addlogin
@loginame = 'Data_Syn',
@passwd = '123,./',
@sid = 0x9FD492E8D353394AA8893CE7B0EC1E08;
*/
--
等各主机都执行以上步骤再可以执行以下语句.
--
建立镜像
ALTER
DATABASE
PpP
SET
PARTNER
=
'
TCP://john:5022
'
--
先在备机执行然后再这个
ALTER
DATABASE
PpP
SET
WITNESS
=
'
TCP://kate:5022
'
--
见证机上不需要执行
/*
*************************备机 JOHN**************************
*/
/*
如果数据库MASTER有加密码了,可以删除.
删除证书
--SET ENCRYPTION OFF
DROP CERTIFICATE HOST_C_cert --删除加密的证书,就是加密码的数据库证书.
drop MASTER KEY --删除主密钥,也就是MASTER的
drop database encryption key
*/
--
创建证书
USE
master;
CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
'
OOOooo
'
;
CREATE
CERTIFICATE JOHN
WITH
SUBJECT
=
'
JOHN
'
,
START_DATE
=
'
2010-07-23
'
;
--
-------------------
--
创建点链接
CREATE
ENDPOINT Endpoint_Mirroring
STATE
=
STARTED
AS
TCP ( LISTENER_PORT
=
5022
, LISTENER_IP
=
ALL
)
FOR
DATABASE_MIRRORING
( AUTHENTICATION
=
CERTIFICATE JOHN , ENCRYPTION
=
REQUIRED ALGORITHM AES , ROLE
=
ALL
);
--
备份证书,并拷贝证书至各机确保互联
BACKUP
CERTIFICATE JOHN
TO
FILE
=
'
c:\sqlt\JOHN.cer
'
;
--
添加登陆名,用户
--
主机用户--ANNATROV
CREATE
LOGIN ANNATROV
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
ANNATROV
FOR
LOGIN ANNATROV;
CREATE
CERTIFICATE ANNATROV
AUTHORIZATION
ANNATROV
FROM
FILE
=
'
c:\sqlt\ANNATROV.cer
'
;
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
ANNATROV
]
;
--
见证机用户
CREATE
LOGIN KATE
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
KATE
FOR
LOGIN KATE;
CREATE
CERTIFICATE KATE
AUTHORIZATION
KATE
FROM
FILE
=
'
c:\sqlt\KATE.cer
'
;
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
KATE
]
;
--
在备机上创建登陆名
/*
USE master;
exec sp_addlogin
@loginame = 'Data_Syn',
@passwd = '123,./',
@sid = 0x9FD492E8D353394AA8893CE7B0EC1E08;
*/
--
建立镜像
ALTER
DATABASE
PpP
SET
PARTNER
=
'
TCP://annatrov:5022
'
--
先在备机执行再在主机执行
/*
**********************见证机 KATE*********************************
*/
/*
如果数据库MASTER有加密码了,可以删除.
删除证书
--SET ENCRYPTION OFF
DROP CERTIFICATE HOST_C_cert --删除加密的证书,就是加密码的数据库证书.
drop MASTER KEY --删除主密钥,也就是MASTER的
drop database encryption key
*/
--
创建证书
USE
master;
CREATE
MASTER
KEY
ENCRYPTION
BY
PASSWORD
=
'
OOOooo
'
;
CREATE
CERTIFICATE KATE
WITH
SUBJECT
=
'
KATE
'
,
START_DATE
=
'
2010-07-23
'
;
--
---------------
--
创建端点链接
CREATE
ENDPOINT Endpoint_Mirroring
STATE
=
STARTED
AS
TCP ( LISTENER_PORT
=
5022
, LISTENER_IP
=
ALL
)
FOR
DATABASE_MIRRORING
( AUTHENTICATION
=
CERTIFICATE KATE , ENCRYPTION
=
REQUIRED ALGORITHM AES , ROLE
=
ALL
);
--
备份证书,并拷贝证书至各机确保互联
BACKUP
CERTIFICATE KATE
TO
FILE
=
'
c:\sqlt\KATE.cer
'
;
--
添加登陆名,用户
--
主机登陆用户
CREATE
LOGIN ANNATROV
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
ANNATROV
FOR
LOGIN ANNATROV;
CREATE
CERTIFICATE ANNATROV
AUTHORIZATION
ANNATROV
FROM
FILE
=
'
c:\sqlt\ANNATROV.cer
'
;
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
ANNATROV
]
;
--
备机登陆用户
CREATE
LOGIN JOHN
WITH
PASSWORD
=
'
123456
'
;
CREATE
USER
JOHN
FOR
LOGIN JOHN;
CREATE
CERTIFICATE JOHN
AUTHORIZATION
JOHN
FROM
FILE
=
'
c:\sqlt\JOHN.cer
'
;
GRANT
CONNECT
ON
ENDPOINT::Endpoint_Mirroring
TO
[
JOHN
]
;
/*
************************主备手工切换*****************************
*/
--
测试:主备互换
USE
master;
ALTER
DATABASE
PpP
SET
PARTNER FAILOVER;
USE
master;
ALTER
DATABASE
PpP
SET
PARTNER SAFETY
FULL
;
--
事务安全,同步模式
ALTER
DATABASE
PpP
SET
PARTNER SAFETY
OFF
;
--
事务不安全,异步模式,高性能
--
--------------
--
测试:主备互换
USE
master;
ALTER
DATABASE
PpP
SET
PARTNER FAILOVER;
--
主服务器Down掉,备机紧急启动并且开始服务
--
备机执行
USE
master;
ALTER
DATABASE
PpP
SET
PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
--
备机执行：
USE
master;
ALTER
DATABASE
PpP
SET
PARTNER RESUME;
--
恢复镜像
ALTER
DATABASE
PpP
SET
PARTNER FAILOVER;
--
切换主备
--
结果图
作者:ANNATROV
时间:
2010
-
8
-
4
