SQL镜像
来源:互联网 发布:传奇霸业宝石数据 编辑:程序博客网 时间:2024/06/05 04:41
DECLARE @SERVERTYP AS INT ;
SET @SERVERTYP =0; --主机
IF(@SERVERTYP =0)
BEGIN
USE master;
--主机流程
DROP CERTIFICATE A_CERT;
DROP MASTER KEY;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE A_CERT WITH SUBJECT = 'HOST A CERTIFICATE' ,
START_DATE = ' 01/01/1990',EXPIRY_DATE='01/01/2099';
CREATE ENDPOINT ENDPOINT_MIRRORING STATE = STARTED AS TCP ( LISTENER_PORT=5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE
A_CERT, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL );
BACKUP CERTIFICATE A_CERT TO FILE = 'D:\A_CERT.CER';
--主机上登录到备机的用户
CREATE LOGIN B_LOGIN WITH PASSWORD = '123';
CREATE USER B_USER FOR LOGIN B_LOGIN;
CREATE CERTIFICATE B_CERT AUTHORIZATION B_USER FROM FILE = 'D:\B_CERT.CER';
GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO [B_LOGIN];
--USE master;
--SELECT sid ,name FROM syslogins WHERE name = 'HOST_B_LOGIN';
--0X8C6B7DF875AC6842B8D77095048427AE
ALTER DATABASE TTY SET PARTNER = 'TCP://192.168.1.3:5023'
END
ELSE
BEGIN
DROP CERTIFICATE B_CERT;
DROP MASTER KEY;
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE B_CERT WITH SUBJECT = 'B CERTIFICATE',
START_DATE = '01/01/1999' ,EXPIRY_DATE='01/01/2099';
CREATE ENDPOINT ENDPOINT_MIRRORING STATE = STARTED AS TCP ( LISTENER_PORT=5023,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE B_CERT, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE =
ALL );
BACKUP CERTIFICATE B_CERT TO FILE = 'D:\B_CERT.CER';
--备机上登录到主机的用户
CREATE LOGIN A_LOGIN WITH PASSWORD = '123';
CREATE USER A_USER FOR LOGIN A_LOGIN;
CREATE CERTIFICATE A_CERT AUTHORIZATION A_USER FROM FILE = 'D:\A_CERT.CER';
GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO [A_LOGIN];
--USE master;
--SELECT sid ,name FROM syslogins WHERE name = 'HOST_A_LOGIN';
--0X47EA83FB61DA7D4684A36B6D4234AB2A
--USE MASTER;
--EXEC SP_ADDLOGIN
--@LOGINAME = 'HOST_A_LOGIN',
--@PASSWD = '123',
--@SID = 0X8C6B7DF875AC6842B8D77095048427AE
ALTER DATABASE TTY SET PARTNER = 'TCP://192.168.1.3:5022';
END
----主/镜 互换 (只能主机执行)
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER FAILOVER;;
----强制成为主机 (只能在备机用)
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
----回复挂起状态
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER RESUME;
SET @SERVERTYP =0; --主机
IF(@SERVERTYP =0)
BEGIN
USE master;
--主机流程
DROP CERTIFICATE A_CERT;
DROP MASTER KEY;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE A_CERT WITH SUBJECT = 'HOST A CERTIFICATE' ,
START_DATE = ' 01/01/1990',EXPIRY_DATE='01/01/2099';
CREATE ENDPOINT ENDPOINT_MIRRORING STATE = STARTED AS TCP ( LISTENER_PORT=5022,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE
A_CERT, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL );
BACKUP CERTIFICATE A_CERT TO FILE = 'D:\A_CERT.CER';
--主机上登录到备机的用户
CREATE LOGIN B_LOGIN WITH PASSWORD = '123';
CREATE USER B_USER FOR LOGIN B_LOGIN;
CREATE CERTIFICATE B_CERT AUTHORIZATION B_USER FROM FILE = 'D:\B_CERT.CER';
GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO [B_LOGIN];
--USE master;
--SELECT sid ,name FROM syslogins WHERE name = 'HOST_B_LOGIN';
--0X8C6B7DF875AC6842B8D77095048427AE
ALTER DATABASE TTY SET PARTNER = 'TCP://192.168.1.3:5023'
END
ELSE
BEGIN
DROP CERTIFICATE B_CERT;
DROP MASTER KEY;
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123';
CREATE CERTIFICATE B_CERT WITH SUBJECT = 'B CERTIFICATE',
START_DATE = '01/01/1999' ,EXPIRY_DATE='01/01/2099';
CREATE ENDPOINT ENDPOINT_MIRRORING STATE = STARTED AS TCP ( LISTENER_PORT=5023,
LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
CERTIFICATE B_CERT, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE =
ALL );
BACKUP CERTIFICATE B_CERT TO FILE = 'D:\B_CERT.CER';
--备机上登录到主机的用户
CREATE LOGIN A_LOGIN WITH PASSWORD = '123';
CREATE USER A_USER FOR LOGIN A_LOGIN;
CREATE CERTIFICATE A_CERT AUTHORIZATION A_USER FROM FILE = 'D:\A_CERT.CER';
GRANT CONNECT ON ENDPOINT::ENDPOINT_MIRRORING TO [A_LOGIN];
--USE master;
--SELECT sid ,name FROM syslogins WHERE name = 'HOST_A_LOGIN';
--0X47EA83FB61DA7D4684A36B6D4234AB2A
--USE MASTER;
--EXEC SP_ADDLOGIN
--@LOGINAME = 'HOST_A_LOGIN',
--@PASSWD = '123',
--@SID = 0X8C6B7DF875AC6842B8D77095048427AE
ALTER DATABASE TTY SET PARTNER = 'TCP://192.168.1.3:5022';
END
----主/镜 互换 (只能主机执行)
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER FAILOVER;;
----强制成为主机 (只能在备机用)
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
----回复挂起状态
--USE MASTER;
--ALTER DATABASE TTY SET PARTNER RESUME;
0 0
- SQL镜像
- sql server 2005 镜像
- SQl 2008/2005 镜像
- SQl 2008/2005 镜像
- SQL Server 数据库镜像
- sql server 2008 数据库镜像---镜像实战
- SQL Server 2005数据库镜像简介
- SQL Server 2005数据库镜像(1)
- SQL Server 镜像服务简述
- 实战SQL Server2005数据库镜像
- SQL Server 2005 镜像构建手册
- 实战SQL 2008 数据库镜像功能
- SQL Server 2005 数据库镜像介绍
- SQL Server数据库镜像配置步骤
- SQL SERVER 2005数据库镜像
- SQL Server 2005 镜像构建手册
- SQL Server 2005 镜像功能实现
- SQL Server 2005数据库镜像简介
- C语言字符串查找替换
- 分数化小数(decimal)完整版
- Forwar Rendering vs. Deferred Rendering
- .NET c# EF DB first 连接mysql 实施
- 史上最详细Windows版本搭建安装React Native环境配置
- SQL镜像
- HDU 5575 Discover Water Tank
- filesystem-e2fsprog
- 并查集(删除节点,就是增加一个新的爹)
- poj2376
- 11g R2 节点系统重建后,删除节点及添加节点 过程和问题解决
- PopupWindow简单使用
- 自定义CircleProgressBar
- Tyvj P2002 扑克牌