SQLServer2012高可用镜像数据库
实施方案(非域环境)
目录
SQLServer2012 高可用镜像数据库 ... 1
实施方案 ( 非域环境 ) 1
一:环境准备 ... 2
1.1 服务器、数据库准备 ... 2
1.2 创建目录 ... 2
1.3 环境检查 ... 2
二:执行相关脚本 ... 2
2.1 创建主数据库密钥 ... 3
2.2 创建证书 ... 3
2.3 创建端点 ... 3
2.4 备份并分发证书 ... 3
2.5 创建登陆账号 ... 3
2.6 创建用户 ... 3
2.7 授权用户 ... 3
2.8 授权端口 ... 3
三:数据初始化 ... 3
3.1 备份数据库 ( 主 ) 3
3.2 还原数据库 ( 镜像 ) 3
四:配置镜像 ... 4
五:故障转移测试 ... 8
5.1 查看主库、镜像库状态 ... 8
5.2 手动切换 ... 9
5.3 自动切换 ... 12
六:查看数据库镜像监视器 ... 15
七:搭建过程中遇到的问题 ... 16
八:参考 ... 16
九:应用客户端连接数据库 ... 17
十:脚本附件 ... 17
一:环境准备
主数据库、镜像数据库、见证服务器之间的身份验证方式
一:域帐户
二:证书
本次采用证书验证方式;
所有数据库镜像会话都只支持一台主体服务器和一台镜像服务器。
1.1 服务器、数据库准备
IP 、主机、密码等敏感信息已脱敏
服务器角色 | 主机名 | 版本 | IP |
主体服务器 | principal | Windows server 2012 | 192.168.100.101 |
镜像服务器 | mirror | Windows server 2012 | 192.168.100.102 |
见证服务器 | witness | Windows server 2012 | 192.168.100.103 |
1.2 创建目录
三台服务器本地,分别创建 D:\sharedir 目录,并设置共享 , 权限 everyone 可读写;
三台服务器本地,分别创建 D:\certifications 目录,用于导出证书;
1.3 环境检查
(1) 检查三台服务器网络和端口是否测通;
(2) 检查三台服务器防火墙是否关闭,或 1023,5022 等端口是否放开;
(3) 检查三台服务器上数据库版本和补丁版本是否一致;
二:执行相关脚本
具体脚本内容见最后 十:脚本附件
2.1 创建主数据库密钥
2.2 创建证书
2.3 创建端点
2.4 备份并分发证书
2.5 创建登陆账号
2.6 创建用户
2.7 授权用户
2.8 授权端口
三:数据初始化
3.1 备份数据库 ( 主 )
主库备份数据库和日志,并将备份文件备注到镜像数据库服务器上
use master
BACKUP DATABASE testdb
TO DISK = 'D:\sharedir\testdb0730.bak'
WITH COMPRESSION
GO
use master
BACKUP LOG testdb
TO DISK = 'D:\sharedir\testlog0730.trn'
GO
3.2 还原数据库 ( 镜像 )
还原数据库和日志 (RESTORE WITH NORECOVERY)
四:配置镜像
在主数据库上操作
五:故障转移测试
5.1 查看主库、镜像库状态
主库
镜像库
5.2 手动切换
在主库上执行
镜像数据库 102 ,已经切换成主库
102 切换回来镜像数据库
5.3 自动切换
101 主故障,测试自动切换
101 启动防火墙, 1023 和 5022 不通
use testdb
102
use testdb
select count (*) from a0730 ;
--101 关闭防火墙
use testdb
---102
use testdb
select * from test0730 ;
select count (*) from a0730 ;
insert into test0730 values ( 7 );
update test0730 set id = 100 where id = 1 ;
手动将主切回 101
---101
use testdb
select * from test0730 ;
六:查看数据库镜像监视器
七:搭建过程中遇到的问题
问题一:
服务器网络地址 :TCP://192.168.100.102:5022 无法访问或不存在。
数据库 testdb 的伙伴服务器实例和见证服务器实例均不可用。
问题原因:
主服务器上启用了防火墙,并且 1023 和 5022 端口没有开放。
解决方案:
关闭防火墙,重新点击 开始镜像
八:应用客户端连接数据库
Microsoft .NET Data Provider for SQL Server 提供了对数据库镜像会话的客户端连接支持。
九:参考
数据库镜像 (SQL Server)
https://blog.csdn.net/dba_huangzj/article/details/27652857
第三篇——第二部分——第四文 配置 SQL Server 镜像——非域环境
十:脚本附件
---101 主服务器
-- 创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需,否则不要设置这么简单的密码
GO
/*
-- 删除主密钥
USE master;
DROP MASTER KEY
*/
--- 创建证书
USE master ;
CREATE CERTIFICATE HOST_A_cert
WITH SUBJECT = 'HOST_A certificate' -- 在主实例上创建证书,命名为HOST_A_cert,这个选项是描述证书
, EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间,可以适当设置长一点,具体按实际需要设置
GO
/*
-- 删除证书
USE master;
DROP CERTIFICATE HOST_A_cert
*/
--- 创建端点
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022 -- 使用5022端口,这个端口可以改成未被使用的端口,但是镜像过程中的所有合作者都应该使用相同的端口
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_A_cert -- 使用证书来授权端点
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL -- 表示这个端点可以作为任何角色,包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。
);
GO
/*
-- 删除镜像端点
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\certifications\HOST_A_cert.cer' ;
GO
-- 在主实例上创建一个登录名给Mirror实例
USE master ;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'D:\Certifications\HOST_B_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;
GO
/*
-- 删除账号
DROP LOGIN HOST_B_user
*/
-- 在主实例上创建一个登录名给见证实例
USE master ;
CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'D:\Certifications\HOST_C_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;
---102 镜像服务器
-- 创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需,否则不要设置这么简单的密码
GO
/*
-- 删除主密钥
USE master;
DROP MASTER KEY
*/
USE master ;
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate' -- 在Winess实例上创建证书,命名为HOST_C_cert,这个选项是描述证书
, EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间,可以适当设置长一点,具体按实际需要设置
GO
/*
-- 删除证书
USE master;
DROP CERTIFICATE HOST_B_cert
*/
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022 -- 使用5022端口,这个端口可以改成未被使用的端口,但是镜像过程中的所有合作者都应该使用相同的端口
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert -- 使用证书来授权端点
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL -- 表示这个端点可以作为任何角色,包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。
);
GO
/*
-- 删除镜像端点
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\certifications\HOST_B_cert.cer' ;
GO
-- 在镜像实例上创建一个登录名给Principal实例
USE master ;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'D:\Certifications\HOST_A_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;
GO
/*
-- 删除账号
DROP LOGIN HOST_A_user
*/
USE master ;
CREATE LOGIN HOST_C_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_C_user FOR LOGIN HOST_C_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_C_cert
AUTHORIZATION HOST_C_user
FROM FILE = 'C:\Certifications\HOST_C_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_C_login ;
---103 见证服务器
-- 创建主密钥
USE master ;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'testpasword)(*' ; -- 演示所需,否则不要设置这么简单的密码
GO
/*
-- 删除主密钥
USE master;
DROP MASTER KEY
*/
USE master ;
CREATE CERTIFICATE HOST_C_cert
WITH SUBJECT = 'HOST_C certificate' -- 在Winess实例上创建证书,命名为HOST_C_cert,这个选项是描述证书
, EXPIRY_DATE = '2999-1-1' ; -- 证书过期时间,可以适当设置长一点,具体按实际需要设置
GO
/*
-- 删除证书
USE master;
DROP CERTIFICATE HOST_C_cert
*/
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT = 5022 -- 使用5022端口,这个端口可以改成未被使用的端口,但是镜像过程中的所有合作者都应该使用相同的端口
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_C_cert -- 使用证书来授权端点
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL -- 表示这个端点可以作为任何角色,包括主服务器、镜像服务器、见证服务器。具体可看联机丛书。
);
GO
/*
-- 删除镜像端点
IF EXISTS (SELECT * FROM sys.endpoints e WHERE e.name = N'Endpoint_Mirroring')
DROP ENDPOINT [Endpoint_Mirroring]
GO
*/
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'D:\certifications\HOST_C_cert.cer' ;
GO
--- 在见证服务器上为主体、镜像服务器创建以证书为验证的账号、用户名及端点。
-- 在Witness实例上创建一个登录名给Principal实例
USE master ;
CREATE LOGIN HOST_A_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_A_user FOR LOGIN HOST_A_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'D:\certifications\HOST_A_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_A_login ;
GO
/*
-- 删除账号
DROP LOGIN HOST_A_user
*/
-- 在Witness实例上创建一个登录名给Mirror实例
USE master ;
CREATE LOGIN HOST_B_login WITH PASSWORD = 'testpasword)(*' ;
GO
-- 创建一个用于给这个登录名
CREATE USER HOST_B_user FOR LOGIN HOST_B_login ;
GO
-- 让该帐号使用证书授权
CREATE CERTIFICATE HOST_B_cert
AUTHORIZATION HOST_B_user
FROM FILE = 'D:\certifications\HOST_B_cert.cer'
GO
-- 授予这个新账号连接端点的权限
GRANT CONNECT ON ENDPOINT :: Endpoint_Mirroring TO HOST_B_login ;
GO
/*
-- 删除账号
DROP LOGIN HOST_B_user
*/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2652387/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2652387/