关闭

SQL2005镜像同步备份实作

1076人阅读 评论(0) 收藏 举报

SQL2005镜像同步备份实作

实现SQL主服务器与SQL备份服务器同步镜像备份(不含见证服务器配置),执行步骤与语句如下:

 

-----主机上执行,主机为HOST_A/SQL2005

--创建证书

--删除密钥

--DROP MASTER KEY

 

--删除证书

--DROP CERTIFICATE Host_A_cert

 

--创建密钥

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

 

--创建证书

CREATE CERTIFICATE Host_A_cert WITH SUBJECT = 'Host_A_certificate',START_DATE = '01/01/2011'

 

 

--创建连接的端点

--DROP ENDPOINT Endpoint_NAME_A

create ENDPOINT Endpoint_NAME_A

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE Host_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )

 

 

-- 备份证书 把备份的证书文件复制到备机(HOST_B/SQL2005)的某个目录

BACKUP CERTIFICATE Host_A_cert TO FILE = 'D:/Host_A_cert.cer'; 

 

-- 添加登陆名、用户(主备可并行执行)

--drop login Host_A_login

--drop CERTIFICATE Host_B_cert

--drop user Host_A_user

 

 

CREATE LOGIN Host_A_login WITH PASSWORD = 'password';

 

CREATE USER Host_A_user FOR LOGIN Host_A_login;

 

--添加备机访问主机的证书,执行前需要把备机的证书复制到指定的某个目录

CREATE CERTIFICATE Host_B_cert AUTHORIZATION Host_A_user FROM FILE = 'D:/Host_B_cert.cer';

--授于用户连接端点的权限

GRANT CONNECT ON ENDPOINT::Endpoint_NAME_A TO [Host_A_login];

 

--建立镜像 --此句需要先执行备机

ALTER DATABASE ERPXY SET PARTNER = 'TCP://HOST_B:5022'

 

-- 查询密钥对和证书

/*

select * from sys.symmetric_keys

SELECT * FROM sys.certificates;

 

SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc FROM sys.database_mirroring_endpoints;

 

BACKUP CERTIFICATE PARTNER TO FILE = 'C:/Host_A_cert.cer';

delete sys.symmetric_keys where symmetric_Key_ID ='101'

*/

 

--备机执行 备机为HOST_B/SQL2005

--创建证书

 

--删除密钥

--DROP MASTER KEY

 

--删除证书

--DROP CERTIFICATE Host_B_cert

 

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' --创建密钥

 

CREATE CERTIFICATE Host_B_cert WITH SUBJECT = 'Host_B_certificate',START_DATE = '01/01/2011' --创建证书

 

 

--创建连接的端点

 

--DROP ENDPOINT Endpoint_NAME_A

 

create ENDPOINT Endpoint_NAME_A

STATE = STARTED

AS

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE Host_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )

 

 

-- 备份证书 把备份的证书文件复制到主机(HOST_A/SQL2005)的某个目录

BACKUP CERTIFICATE Host_B_cert TO FILE = 'D:/Host_B_cert.cer'; 

 

-- 添加登陆名、用户(主备可并行执行)

--drop login Host_B_login

--drop CERTIFICATE Host_A_cert

--drop user Host_B_user

 

CREATE LOGIN Host_B_login WITH PASSWORD = 'password';

 

CREATE USER Host_B_user FOR LOGIN Host_B_login;

 

--添加主机访问备机的证书,执行前需要把主机的证书复制到指定的某个目录

CREATE CERTIFICATE Host_A_cert AUTHORIZATION Host_B_user FROM FILE = 'D:/Host_A_cert.cer';

--授于用户连接端点的权限

GRANT CONNECT ON ENDPOINT::Endpoint_NAME_A TO [Host_B_login];

 

 

--建立镜像 --此句需要先执行备机

ALTER DATABASE ERPXY SET PARTNER = 'TCP://Host_A:5022'

 

--restore database SJDD from disk='d:/SJDD' with NORECOVERY ,

 

--MOVE 'SJDD' to 'C:/砂浆系统/数据库/SJDD.mdf',

 

--Move 'SJDD_log' to 'C:/砂浆系统/数据库/SJDD_log.mdf'

 

 

-- 查询密钥对和证书

/*

select * from sys.symmetric_keys

 

SELECT * FROM sys.certificates;

 

SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc FROM sys.database_mirroring_endpoints;

 

BACKUP CERTIFICATE PARTNER TO FILE = 'C:/Host_B_cert.cer';

 

delete sys.symmetric_keys where symmetric_Key_ID ='101'

*/

 

 

--其它相关语句

--主机连接失败

alter database ERPXY set partner FAILOVER  --连接失败

--执行此语句后 原来的主机会自动转换为备机,备机自动转换为主机

/*

  PARTNER { = 'partner_server'

            | FAILOVER

            | FORCE_SERVICE_ALLOW_DATA_LOSS

            | OFF

            | RESUME

            | SAFETY { FULL | OFF }

            | SUSPEND

            | TIMEOUT integer

            }

 

*/

 

 

--备机

--关闭备机镜像角色

alter database ERPXY set partner off

--恢复备机recovery模式

restore database ERPXY with recovery

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:205974次
    • 积分:2765
    • 等级:
    • 排名:第13041名
    • 原创:58篇
    • 转载:80篇
    • 译文:0篇
    • 评论:24条
    最新评论