SQL镜像

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;