SQL Server R2 mirroring configuration

Preparation Conditions

1. Server Planning

Server Role	machine name/role name	Software	IP
Principal server	Repa	SQL Server R2	192.168.0.10
mirror server	Repb	SQL Server R2	192.168.0.20
Witness server	Win7	SQL Server R2	192.168.0.30

2. Server-Related Settings

1、创建文件夹     Certifications 用来存放认证证书     ShareFolders 用来存放共享文件，方便服务器之间传递文件2、保证主体服务器与镜像服务器的数据文件夹路径相同3、保证数据库的恢复模式为FULL

Configure mirroring with certificates, and back up Repa databases, restore to REPB

1. Create a Database master key

   USE master;GOCREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd‘;/*删除主密钥USE master;DROP MASTER KEY使用相同方式在RepB闯将主密钥

2. Create a certificate and encrypt it with the master key

    USE master;    GO    CREATE CERTIFICATE Host_A_Cert    WITH Subject = ‘Host_C Certificate‘,    Expiry_Date = ‘99991231‘  /*    删除证书    USE master;    DROP CERTIFICATE HOST_A_Cert    在RepB使用同样的方法创建证书HOST_B_Cert

3. Create an endpoint (that is, listen)

    CREATE ENDPOINT EDP_Mirror    STATE = STARTED     AS TCP(    LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口    LISTENER_IP = ALL)     -- 侦听的IP地址            FOR DATABASE_MIRRORING(    AUTHENTICATION = CERTIFICATE Host_A_Cert, -- 证书身份验证    ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法    ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)    在RepB使用相同的方法，×××名，创建端点

4. Backup Certificate

    BACKUP CERTIFICATE Host_A_Cert    TO FILE = ‘C:\ShareFolders\Host_A_Cert.cer‘;    在RepB上执行相同的操作，备份Host_B_Cert    RepA 和 RepB 的Certifications文件夹上都要有所有服务器的证书，可以通过共享文件夹复制

5. Create user and login account

    创建登录账号给镜像服务器    USE master;    CREATE LOGIN Host_B_Login WITH PASSWORD = ‘Pa$$w0rd‘;    创建用户并映射到登录账号上    CREATE USER Host_B_User For Login Host_B_Login;    在RepB上使用相同的方法给RepB创建用户和登录账号，注意更改名称

6. Use the certificate to authorize users

    创建一个新的证书，并用从伙伴服务器复制来的证书导入，然后授权上面创建的账号    CREATE CERTIFICATE Host_B_Cert    AUTHORIZATION Host_B_User    FROM FILE = ‘C:\Certifications\Host_B_Cert.cer‘;    在RepB上使用同样的操作，注意更改名称

7. Login account Authorized Access port

    GRANT CONNECT ON ENDPOINT::EDP_Mirror TO Host_B_Login;    在 RepB上使用同样的操作，注意更改名称

8. Backup RESTORE Database

完整备份RepA上的数据库，并以Norecovery选项还原到RepB（使用还原文件和文件组）

9. Boot image

    注意顺序，现在RepB上执行    ALTER DATABASE MirrorDB SET    PARTNER = ‘TCP://RepA:5022‘;    GO    然后在RepA上执行    ALTER DATABASE MirrorDB SET    PARTNER = ‘TCP://RepB:5022‘;    GO     完毕，接下来是配置见证服务器

Configuring the witness server

1. Configure the witness server's master key certificate, endpoint, and authorization similar to Repa and REPB. and copy the certificate to ensure that each server has three server certificates

2. Create endpoint permissions for the witness server by executing the following statement on Repa and REPB

USE master;CREATE LOGIN Host_C_Login WITH PASSWORD = ‘Pa$$w0rd‘;GOCREATE USER Host_C_User For Login Host_C_Login;GOCREATE CERTIFICATE Host_C_CertAUTHORIZATION Host_C_UserFROM FILE = ‘C:\Certifications\Host_C_Cert.cer‘;GOGRANT CONNECT ON ENDPOINT::EDP_Mirror TO Host_C_Login;GO

3, the principal server REPA join the witness server

    ALTER DATABASE MirrorDB SET WITNESS = ‘TCP://win7:5022‘

SQL Server R2 mirroring configuration