SQL 镜像操作文档 有备机.docVIP

SQL 镜像 主机(192.168.200.160) 第一步：证书及密钥 ///////主机执行 --主机上创建证书及密钥 --创建密钥 CREATE MASTER KEY ENCRYPTION BY PASSWORD = huangjunge --创建密钥 --创建证书 create certificate host_105_A with subject=host_105_A,START_DATE =2012-08-16 --主备名字不同 --创建端口 5022 ROLE 主备机都为ALL 或者PARTNER 见证机为 WITNESS create ENDPOINT Endpoint_NameERROR STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_105_A , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ) --备份证书 BACKUP CERTIFICATE host_105_A TO FILE = D:\DB_Text\host_105_A.cer //// 备机执行 --首先把主机需要镜像的数据库的备份 拷贝到指定地址 然后在备机上执行下列语句 RESTORE DATABASE DB_ERROR FROM DISK = ND:\DB_Text\DB_ERROR.bak WITH REPLACE, NORECOVERY --备机上创建证书及密钥 --创建密钥 CREATE MASTER KEY ENCRYPTION BY PASSWORD = huangjunge --创建密钥 --创建证书 create certificate host_105_B with subject=host_105_B,START_DATE =2012-08-16 --创建端口 端口与主机 及见证机 相同 建议端口名也相同 create ENDPOINT Endpoint_NameERROR STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_105_B , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ) --备份证书 BACKUP CERTIFICATE host_105_B TO FILE = D:\DB_Text\host_105_B.cer ///见证机执行 --创建密钥及证书 CREATE MASTER KEY ENCRYPTION BY PASSWORD = huangjunge --创建密钥 create certificate host_105_C with subject=host_105_C,START_DATE =2012-08-16 --主备名字不同 --创建端口 与主备一致 相同 注：ROLE=WITNESS create ENDPOINT Endpoint_NameERROR STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_105_C, ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = WITNESS) --备份证书 BACKUP CERTIFICATE host_105_C TO FILE = D:\host_105_C.cer; ////第二步 为主备机 创建用户 角色 并授权证书 首先先把 备机证书拷贝到主机指定位置 再把主机证书 拷贝到备机指定位置 （如果有见证机,最终结果为主机上3个证书 备机上3个证书 见证机上3个证书 如果无见证机 最终主机上2 个证书 备机上2个证书） 为主机用户授权备机证书 再为备机用户授权主机证书 ///主机执行 --为备机证书授权 --创建用户 CREATE LOGIN HOST_AERR_login WITH PASSWORD = huangjunge; CREATE USER HOST_AERR_user FOR LOGIN HOST_AERR_login; CREATE CERT