1. 测试验证环境
服务器角色 | 机器名 | IP | SQL Server Ver |
主体服务器 | WIN-TestDB4O | 172.83.XXX.XXX | SQL Server 2012 - 11.0.5058.0 (X64) |
镜像服务器 | WIN-TestDB5O | 172.73.XXX.XXX | SQL Server 2012 - 11.0.5058.0 (X64) |
2. 创建前环境检查
(1) 网络是否能联通, 并且端口可用.
(2)SQL Server 版本, 补丁是否满足镜像要求.
(3)SQL Server 数据库的恢复模式, 兼容级别.
(4)SQL Server 上是否有常规的备份作业, 特别是日志备份.
(5) 主体服务器和镜像服务器的 SQL Server 能否互通.
3. 使用证书配置镜像, 并备份还原数据库
在这一步中, 我们将做两件事, 第一件是使用证书来配置镜像, 第二件是备份还原数据库. 在非域环境下, 必须使用证书来搭建镜像, 所以把搭建证书放在第一步. 有些资料上会把备份还原操作放在证书搭建之前, 但是根据个人经验, 当磁盘 IO, 网络性能不佳的时候, 备份, 传输, 还原都会浪费大量的时间 (个人操作过 2 个小时), 并且期间服务器几乎不能操作. 这种时候, 我会选择先搭建好, 再还原, 然后马上进行同步, 减少主从差异, 需要同步更多的数据.
第一部分 创建证书:
[如果服务器使用 Local System 作为 SQL Server 服务账号, 就需要使用证书授权.]
使用证书搭建镜像的步骤如下:
(1) 创建数据库主密钥 (如果主密钥不存在).
(2) 在 Master 数据库中创建证书并用主密钥加密.
(3) 使用证书授权创建端点 (endpoint).
(4) 备份证书成为证书文件.
(5) 在服务器上创建登录账号, 用于提供其他实例访问.
(6) 在 master 库中创建用户, 并映射到上一步的登录账号中.
(7) 把证书授权给这些用户.
(8) 在端点上授权.
(9) 设置镜像服务器的主体伙伴.
(10) 设置主体服务器的镜像伙伴.
(11) 配置见证服务器.
Step 1: 创建数据库主密钥
主密钥的用处在这里是用于加密证书, 当然主密钥不仅仅只有这个作用. 对数据库主密钥的密码及存储保护要小心, 这是实例级别的对象, 影响面非常广. 可以使用下面语句来创建:
- USE master
- GO
- CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
通过系统表查看, 确认.
使用相同方式在镜像服务器创建数据库主密钥.
Step 2: 创建证书, 并用主密钥加密
创建证书时, 默认在创建日期开始一年后过期, 所以针对证书的创建, 要注意其过期时间. 下面是在 "主体服务器" 上创建 HOST_P_cert 证书的创建
- USE master
- GO
- CREATE CERTIFICATE Host_A_Cert
- WITH Subject = 'Host_P Certificate',
Expiry_Date = '2050-1-1'; -- 过期日期
使用相同的方法在镜像服务器上实现对 HOST_S_cert 证书的创建.
Step 3: 创建端点
可以使用下面的代码在主体服务器中创建端点, 并且指定使用 5022, 端口, 端口在镜像配置过程中不强制使用特定端口 (被占用或者特定端口如 1433 除外).
-- 使用 Host_A_Cert 证书创建端点
- IF NOT EXISTS ( SELECT 1
- FROM sys.database_mirroring_endpoints )
- BEGIN
- CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,
- LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =
- CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =
- ALL );
- END
在镜像服务器对证书名稍作修改, 创建镜像服务器的端点.
Step 4: 备份证书
备份证书的目的是发送到别的服务器并导入证书, 以便别的服务器能通过证书访问这台服务器 (主体服务器).
- BACKUP CERTIFICATE Host_A_Cert
- TO FILE = 'D:\ShareFoldersMirror\Host_A_Cert.cer';
同理, 在镜像服务器上重复一次, 注意证书名和路径. 备份之后可以在目标文件夹上看到有一个 cer 文件:
备份证书文件互相 Copy 至对方文件中.
Step 5: 创建登录账号
针对每个服务器单独创建一个服务器登录账号, 这里只需要创建一个登录给镜像服务器即可:
CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa$$w0rd';
同理, 在镜像服务器上创建 Host_A_Login 给主体服务器.
Step 6: 创建用户, 并映射到 Step 5 中创建的登录账号中
在主体服务器上运行:
CREATE USER Host_B_User For Login Host_B_Login;
同理在镜像服务器也创建.
Step 7: 使用证书授权用户
创建一个新的证书, 并使用从伙伴服务器中复制过来的证书导入, 然后映射 step 6 中的账号到这个新证书上.
- CREATE CERTIFICATE Host_B_Cert
- AUTHORIZATION Host_B_User
- FROM FILE = 'D:\ShareFoldersMirror\Host_B_Cert.cer';
注意镜像服务器上也同样.
Step 8: 把 Step 5 中的登录账号授权访问端口
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];
镜像服务器也一样.
到此为止, 配置镜像的步骤已经完毕, 后续会给出尽可能自动化的配置脚本.
第二部分 备份还原数据库:
这一部分没有什么特别强调的, 在此次试验过程中, 使用了界面配置.
注意: 本次还原是为 Mirror 做准备, 所以, 点击 [选项] 按钮 .
所以需要选择 [不对数据库执行任何操作, 不回滚未提交的事务....]
还原成功
第三步: 启动镜像
依次分别在镜像 Server 和主 Server 上运行以下命令就可以了 [最好在 Master DB 上执行以下命令]
在镜像 Server 上线运行
- ALTER DATABASE [Test_Mirror]
- SET PARTNER = 'TCP://172.83.XXX.XXX:5022';
- GO
在主 Server 上运行
- ALTER DATABASE [Test_Mirror]
- SET PARTNER = 'TCP://172.73.XXX.XXX:5022';
- GO
配置成功, 此时显示如下:
主体服务器上显示
镜像服务器上 DB 显示
4. 补充说明
以下内容用来学习
问题 1 查询判断数据库是否已添加主密钥
---sys.databases 的 is_master_key_encrypted_by_server 得到是否有加密
select top 100 is_master_key_encrypted_by_server,* from sys.databases
---- 如果没有就看不到数据 [需定义到数据库]
---- 解释说明:##MS_ServiceMasterKey##---- 是说的整个服务, 而 ##MS_DatabaseMasterKey## 是说的 Master 数据库, 需留意.
---- 我们 使用证书搭建镜像 是需要在 master 数据库上创建数据库主密钥 (如果主密钥不存在).
SELECT * FROM sys.symmetric_keys
以下截图查询的数据显示 Master 数据库尚未创建主密钥.
以下截图的数据显示 Master 数据库已有主密钥
问题 2 由日志传送更改为镜像.
希望直接更改, 即不再需要备份和还原.
Step 1 [注意: 此时先手动执行一下此 DB 的 Log 备份的 Job, 然后停掉此 Job, 接下来再执行 Copy Log 文件的 Job(如果有此 Job 的话), 再停掉此 Job, 最后执行 Restore 此 Log 文件的 Job, 接着停掉此 Job]
Step 2 选择指定 DB, 取消 [将此数据库启用为日志传送配置中的主数据库...] , 就是把勾去掉.
点击确定后, 会要求我们再次连接一下.
Step 3 开始建立伙伴关系
先在备份 Server 的 DB 上去做
- ALTER DATABASE [YYYY_Mob]
- SET PARTNER = 'TCP://172.87.XXX.XX2:10001';
- GO
然后再在主 DB 上运行
- ALTER DATABASE [YYYY_Mob]
- SET PARTNER = 'TCP://172.89.XXX.XX4:10002';
- GO
问题 3 删除主密钥
解决方案:
DROP CERTIFICATE 证书名
但此时 还有报错了
Step 1 删除映射的登录账号和用户名
查看登入名
删除标识的登入名 , 此时执行还会报同样的错误.
注意登入名和用户名是 2 个概念,
- DROP LOGIN For_HOST_B_user
- (有时还要查询 select top 100* from sys.sysusers 是否还有这个用户, 有的话, 还要执行 DROP User For_HOST_B_user)
Step 2 删除端口
SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring'
存在则删除端口
Step 3 删除
此时, 就 OK 了. 去删除证书和主密钥
问题 4 在建立伙伴关系时, 需注意设置伙伴的顺序
如果按照网址上介绍的步骤 , 现在主服务器上执行, 设置伙伴.
则可能报错, 提示的错误信息如下:
我们先在 Mirror 服务器上执行
然后再在主服务器中执行, 则不报错
参考文献
http://blog.csdn.net/dba_huangzj/article/details/27652857
来源: https://www.cnblogs.com/xuliuzai/p/11436460.html