非域环境下使用证书部署数据库（SqlServer2008R2）镜像
前言
部署数据库镜像一般有两种方式域环境下部署 和 非域环境下 证书部署参考地址：
这里是自己在三个虚拟机非域环境下部署测试的
一、环境
| 数据库 | 系统 | IP | 角色 |
| SqlServer2008R2 | Server 2008R2 | 10.10.0.52 | 主体 |
| SqlServer2008R2 | Server 2008R2 | 10.10.0.53 | 镜像 |
| SqlServer2008R2 | Server 2008R2 | 10.10.0.54 | 见证 |
配置数据库镜像之前 需要打开端口1433和5022 是三台服务器的都要
下面将图示说明开启1433和5022的步骤：
- 打开防火墙-》高级设置
二、镜像部署
1证书与端点（出站连接）
1.1主题服务器
USE master; --DROP MASTER KEY CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ'; GO --为主体服务器 host_A 实例制作一个证书。 --DROP CERTIFICATE host_A_cert CREATE CERTIFICATE host_A_cert WITH SUBJECT = 'host_A certificate',START_DATE = '3/12/2015',EXPIRY_DATE = '01/01/2099'; GO --使用该证书为服务器实例创建一个镜像端点。 --DROP ENDPOINT Endpoint_Mirroring CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = PARTNER ); GO --备份host_A 证书，并将其复制到镜像服务器 host_B 和见证服务器 host_C BACKUP CERTIFICATE host_A_cert TO FILE = 'c:\DbMirror\host_A.cer'; GO
1.2镜像服务器
/***********************************************在镜像服务器 host_B 执行此脚本***********************************************/USE master;--DROP MASTER KEYCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ';GO--为镜像服务器 host_B 实例制作一个证书。--DROP CERTIFICATE host_B_certCREATE CERTIFICATE host_B_certWITH SUBJECT = 'host_B certificate',START_DATE = '3/12/2015',EXPIRY_DATE = '01/01/2099';GO--在 host_B 中为服务器实例创建一个镜像端点。--DROP ENDPOINT Endpoint_MirroringCREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDAS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE host_B_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = PARTNER);GO--备份 host_B 证书，并将其复制到主体服务器 host_A 和见证服务器 host_C 上BACKUP CERTIFICATE host_B_cert TO FILE = 'c:\DbMirror\host_B.cer';GO
1.3见证服务器
/****************************见证服务器 host_C 执行*****************************/--ALTER DATABASE MirrorDB SET PARTNER OFFUSE master;--DROP MASTER KEYCREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ';GO--为此服务器实例制作一个证书。--DROP CERTIFICATE host_C_certCREATE CERTIFICATE host_C_certWITH SUBJECT = 'host_C certificate',START_DATE = '3/12/2015',EXPIRY_DATE = '01/01/2099';GO--使用该证书为服务器实例创建一个镜像端点。--DROP ENDPOINT Endpoint_MirroringCREATE ENDPOINT Endpoint_MirroringSTATE = STARTEDAS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_C_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = WITNESS);GO--备份 host_C 证书，并将其复制到主体服务器 host_A 和镜像服务器 host_C 上BACKUP CERTIFICATE host_C_cert TO FILE = 'c:\dbmirror\host_C.cer';GO
2配置出站连接
2.1主体服务器
--在 host_A 上为镜像服务器 host_B 创建一个登录名。USE master;--DROP LOGIN host_B_loginCREATE LOGIN host_B_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_B_userCREATE USER host_B_user FOR LOGIN host_B_login;GO--使证书与该用户关联。--DROP CERTIFICATE host_B_certCREATE CERTIFICATE host_B_certAUTHORIZATION host_B_userFROM FILE = 'c:\DbMirror\host_B.cer'GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];GO--在主体服务器 host_A 上为见证服务器 host_C 创建一个登录名。USE master;--DROP LOGIN host_C_loginCREATE LOGIN host_C_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_C_userCREATE USER host_C_user FOR LOGIN host_C_login;GO--使证书与该用户关联。--DROP CERTIFICATE host_C_certCREATE CERTIFICATE host_C_certAUTHORIZATION host_C_userFROM FILE = 'c:\DbMirror\host_C.cer'GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];GO--主体服务器上创建用于本机的登录名(MS可以省略)USE master;--DROP LOGIN host_A_loginCREATE LOGIN host_A_login WITH PASSWORD= '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_A_userCREATE USER host_A_user FOR CERTIFICATE host_A_cert;GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];GO
2.2镜像服务器
--在镜像服务器 host_B 上为主体服务器 host_A 创建一个登录名。USE master;--DROP LOGIN host_A_loginCREATE LOGIN host_A_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_A_userCREATE USER host_A_user FOR LOGIN host_A_login;GO--使证书与该用户关联。--DROP CERTIFICATE host_A_certCREATE CERTIFICATE host_A_certAUTHORIZATION host_A_userFROM FILE = 'c:\Dbmirror\host_A.cer'GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];GO--在镜像服务器 host_B 上为见证服务器 host_C 创建一个登录名。USE master;--DROP LOGIN host_C_loginCREATE LOGIN host_C_login WITH PASSWORD = '1qaz!QAZ';GO----创建一个使用该登录名的用户。--DROP USER host_C_user CREATE USER host_C_user FOR LOGIN host_C_login;GO----使证书与该用户关联。--DROP CERTIFICATE host_C_certCREATE CERTIFICATE host_C_certAUTHORIZATION host_C_userFROM FILE = 'c:\Dbmirror\host_C.cer'GO----授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_C_login];GO--在镜像服务器 host_B 上为本机创建一个登录名(MS可以省略)USE master;--DROP LOGIN host_B_loginCREATE LOGIN host_B_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_B_userCREATE USER host_B_user FOR CERTIFICATE host_B_cert;GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];GO
2.3见证服务器
--在见证服务器 host_C 上为主体服务器 host_A 创建一个登录名。USE master;--DROP LOGIN host_A_loginCREATE LOGIN host_A_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_A_userCREATE USER host_A_user FOR LOGIN host_A_login;GO--使证书与该用户关联。--DROP CERTIFICATE host_A_certCREATE CERTIFICATE host_A_certAUTHORIZATION host_A_userFROM FILE = 'c:\DbMirror\host_A.cer'GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_A_login];GO--在见证服务器 host_C 上为镜像服务器 host_B 创建一个登录名。USE master;--DROP LOGIN host_B_loginCREATE LOGIN host_B_login WITH PASSWORD = '1qaz!QAZ';GO--创建一个使用该登录名的用户。--DROP USER host_B_userCREATE USER host_B_user FOR LOGIN host_B_login;GO--使证书与该用户关联。--DROP CERTIFICATE host_B_certCREATE CERTIFICATE host_B_certAUTHORIZATION host_B_userFROM FILE = 'c:\DbMirror\host_B.cer'GO--授予对远程镜像端点的登录名的 CONNECT 权限。GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [host_B_login];GO
3配置数据库登录信息
在主体数据库中查询 sid和name
USE master;select sid,name from syslogins;
在备份数据库执行
USE master;exec sp_addlogin @loginame = 'xiejun', @passwd = '1qaz!QAZ', @sid = 0x9E2D3238732D264483489528B0DC0D9F ;
4备份数据库
主体数据库中执行
在主机上备份数据库，先做完整备份，再做日志事务备份。
在镜像数据库
还原的时候必须把数据库和事务日志以NoRecovery的形式还原
同SQL：
RESTORE database [dbname] from DISK='c:\DbMirror\dbname.bak' WITH FILE=1,NORECOVERY;
RESTORE log [dbname] from DISK='c:\DbMirror\dbname.bak' WITH FILE=2,NORECOVERY;5配置伙伴服务器
执行顺序为 镜像-》主体-》见证
在镜像服务器配置
ALTER DATABASE xiejunSET PARTNER = 'TCP://10.10.0.52:5022';GO
在主体服务器配置
ALTER DATABASE xiejunSET PARTNER = 'TCP://10.10.0.53:5022';GO
在主体服务器配置
ALTER DATABASE [xiejun]SET WITNESS = 'TCP://10.10.0.54:5022';GO
到此服务器配置成功
四、常见命令
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | --切换主备 use master; alter database testdb set partner failover; -- 备机强制切换 use master; alter database testdb set partner force_service_allow_data_loss; --恢复镜像 use master; alter database testdb set partner resume; --取消见证服务器 ALTER DATABASE testdb SET WITNESS OFF ; --取消镜像 ALTER DATABASE testdb SET PARTNER OFF ; --设置镜像数据库还原为正常 RESTORE DATABASE testdb WITH RECOVERY; |