数据库镜像实践
数据库镜像原理：https://blog.csdn.net/jessicaiu/article/details/81906899
参考转载自：http://www.mssqlmct.cn/dba/?post=67
https://blog.csdn.net/dba_huangzj/article/details/27652857
联机丛书：
https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/monitoring-database-mirroring-sql-server?view=sql-server-2017
https://docs.microsoft.com/zh-cn/sql/database-engine/database-mirroring/use-warning-thresholds-and-alerts-on-mirroring-performance-metrics-sql-server?view=sql-server-2017
准备工作：（详细参考：https://blog.csdn.net/dba_huangzj/article/details/27652857）
由于在非域环境内，所以需要做的检查相对来说多很多，下面按照演示环境，逐个测试下面的条件：
3、 5、 9 是必须，其他的视情况考虑
1.Windows 账号。（共享文件夹权限，UNC路径访问权限）
2.网络是否能联通，并且端口可用。
3.主体服务器和镜像服务器的磁盘配置是否正确。（主从库数据所在目录/路径要完全一样，数据库版本也需要完全一样） ----必须
4.SQL Server版本、补丁是否满足镜像要求。
5.SQL Server数据库的恢复模式、兼容级别。（恢复级别必须是 full ，数据库兼容级别至少是2005 sp2以上） ---必须
6.SQL Server上是否有常规的备份作业，特别是日志备份。
7.主体服务器和镜像服务器的SQL Server能否互通。（通过互相连接对方的实例即可）
8.主体服务器和镜像服务器中是否有共享文件夹。
------------
9.备份还原数据库最新的文件（需要把全备+事务备以norecovery的方式还原到 镜像DB） ---必须
完整备份AdventureWork2008R2到共享文件夹C:\ShareFolders
复制备份文件到镜像服务器（如果权限足够，直接使用共享路径来还原即可）
以Nonrecovery选项还原AdventureWork2008R2到镜像服务器（RepB）
日志备份AdventureWork2008R2，并同样方式还原到RepB
---------
实际操作：
1.创建数据库主密钥（如果主密钥不存在）。
2.在Master数据库中创建证书并用主密钥加密。
3.使用证书授权创建端点（endpoint）。
4.备份证书成为证书文件。
5.在服务器上创建登录账号，用于提供其他实例访问。
6.在master库中创建用户，并映射到上一步的登录账号中。
7.把证书授权给这些用户。
8.在端点上授权。
9.设置主体服务器的镜像伙伴。
10.设置镜像服务器的主体伙伴。
11.配置见证服务器。
---------------------
查看：
1. 查看终端点 select * from sys.endpoints
SELECT state_desc FROM sys.database_mirroring_endpoints
更多查阅参考：联机丛书--镜像
删除： 2. 删除某终端点(终端点不带引号) drop endpoint <endpoint_name> 3. 删除证书 在master | Security | Certificates (drop master key...) 4. 删除用户 在master | User 5. 然后可以删除登录名 drop login <login_name> 6. 修改master key : alter master key drop encryption by service master key 7. 删除master key : drop master key 8. 删除镜像的命令: alter databse <dbname> set partner off
主库操作，所有步骤从库同步，注意修改名称
--USE master; --DROP MASTER KEY -----------创建 master 数据库主密钥 Use master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; GO ------------ USE master; -------------然后对服务器实例创建一个用于其数据库镜像出站连接的加密证书。 --drop CERTIFICATE SQLSVR1_cert CREATE CERTIFICATE SQLSVR1_cert WITH SUBJECT = 'SQLSVR1 certificate for database mirroring', start_date='2019-03-01',expiry_date='2030-01-01'; GO ----------- 备份主体服务器 SQLSVR1 的加密证书。请确保此证书保存在安全可靠的存储介质上。 BACKUP CERTIFICATE SQLSVR1_cert TO FILE = 'd:\temp_bak\SQLSVR1.cer'; GO ------------- 使用主体服务器实例的证书 SQLSVR1_cert 为主体服务器 SQLSVR1 创建端点。 CREATE ENDPOINT [默认的镜像端点] STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQLSVR1_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO --drop ENDPOINT 镜像 --select * from sys.endpoints
--创建登录名
--在主体服务器实例的 master 数据库中为镜像服务器创建一个登录名，然后为新创建的登录名创建一个用户。再将用户与镜像服务器的证书相关联，并授予其对数据库镜像端点的 CONNECT 权限。
USE master; CREATE LOGIN SQLSVR2_login WITH PASSWORD = 'Sample@#'; GO CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login; GO CREATE CERTIFICATE SQLSVR2_cert AUTHORIZATION SQLSVR2_user FROM FILE = 'D:\temp_bak\SQLSVR2.cer'
GO GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login]; GO --设定镜像伙伴（这一步需要从库先操作） ALTER DATABASE AdventureWorks2012 SET PARTNER = 'TCP://192.168.214.12:5022'; GO
----------附加begin
--设置镜像的模式
USE [master]
GO
--ON为高安全模式，OFF为高性能模式
ALTER DATABASE [test1] SET SAFETY OFF/ON
GO
--创建快照
create database snap_demo_10amon (name=Demo01,filename='C:\SQLTest\SNAPSHOT\Snap_advlt.snap')as SNAPSHOT of Demo01go----------附加end
从库同步操作，注意名称
Use master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; GO ------------ USE master; --drop CERTIFICATE SQLSVR2_cert CREATE CERTIFICATE SQLSVR2_cert WITH SUBJECT = 'SQLSVR2 certificate for database mirroring', start_date='2019-03-01',expiry_date='2030-01-01';
GO ----------- BACKUP CERTIFICATE SQLSVR2_cert TO FILE = 'd:\temp_bak\SQLSVR2.cer'; GO --------- CREATE ENDPOINT [默认的镜像端点] STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQLSVR2_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO --drop ENDPOINT 镜像 --select * from sys.endpoints USE master; CREATE LOGIN SQLSVR1_login WITH PASSWORD = 'Sample@#'; GO CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login; GO CREATE CERTIFICATE SQLSVR1_cert AUTHORIZATION SQLSVR1_user FROM FILE = 'd:\temp_bak\SQLSVR1.cer' GO GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR1_login]; GO ---- ALTER DATABASE AdventureWorks2012 SET PARTNER = 'TCP://192.168.214.11:5022'; GO
加入见证服务器
--USE master; --DROP MASTER KEY -----------创建 master 数据库主密钥 Use master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'; GO ------------ USE master; -------------然后对服务器实例创建一个用于其数据库镜像出站连接的加密证书。 --drop CERTIFICATE SQLSVR3_cert CREATE CERTIFICATE SQLSVR3_cert WITH SUBJECT = 'SQLSVR3 certificate for database mirroring', start_date='2019-03-01',expiry_date='2030-01-01'; GO ----------- 备份主体服务器 SQLSVR3 的加密证书。请确保此证书保存在安全可靠的存储介质上。 BACKUP CERTIFICATE SQLSVR3_cert TO FILE = 'C:\db\share_file\SQLSVR3.cer'; GO ------------- 使用主体服务器实例的证书 SQLSVR3_cert 为主体服务器 SQLSVR3 创建端点。 CREATE ENDPOINT [默认的镜像端点] STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SQLSVR3_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); GO --drop ENDPOINT 镜像 --select * from sys.endpoints --创建登录名 --在主体服务器实例的 master 数据库中为镜像服务器创建一个登录名，然后为新创建的登录名创建一个用户。再将用户与镜像服务器的证书相关联，并授予其对数据库镜像端点的 CONNECT 权限。 USE master; CREATE LOGIN SQLSVR2_login WITH PASSWORD = 'Sample@#'; GO CREATE USER SQLSVR2_user FOR LOGIN SQLSVR2_login; GO CREATE CERTIFICATE SQLSVR2_cert AUTHORIZATION SQLSVR2_user FROM FILE = 'C:\db\share_file\SQLSVR2.cer' GO GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR2_login]; GO ----------- USE master; CREATE LOGIN SQLSVR1_login WITH PASSWORD = 'Sample@#'; GO CREATE USER SQLSVR1_user FOR LOGIN SQLSVR1_login; GO CREATE CERTIFICATE SQLSVR1_cert AUTHORIZATION SQLSVR1_user FROM FILE = 'C:\db\share_file\SQLSVR1.cer' GO GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR1_login]; GO
--然后在主、从库上都运行
USE master;
CREATE LOGIN SQLSVR3_login
WITH PASSWORD = 'Sample@#';
GO
CREATE USER SQLSVR3_user FOR LOGIN SQLSVR3_login;
GO
CREATE CERTIFICATE SQLSVR3_cert
AUTHORIZATION SQLSVR3_user
FROM FILE = 'C:\db\share_file\SQLSVR3.cer'
GO
GRANT connect on endpoint::[默认的镜像端点] TO [SQLSVR3_login];
GO
--然后在主库上运行
ALTER DATABASE AdventureWorks2012 SET WITNESS = 'TCP://192.168.214.13:5022'
-- 验证结果
然后关掉主库的引擎服务，查看从库是否实现故障转移~
成功~
-----------------分割线------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
以下内容转自：http://blog.51cto.com/fengwan/1865070
SQL Server高可用方案--镜像搭建(证书)
环境:
|
角色 |
IP |
操作系统 |
数据库版本 |
数据库 |
|
主 |
192.168.10.8 |
Windows Server 2008 R2 |
SQL Server 2008 R2 |
fdb |
|
备 |
192.168.10.9 |
Windows Server 2008 R2 |
SQL Server 2008 R2 |
fdb |
一.环境检查
1.网络是否互通
2.防火墙端口是否开放(需要开放1433、5022端口)
3.数据库软件版本是否有差异(软件版本为SQL Server 2008 R2)
4.SQL Server数据库的恢复模式确认，需要为“完整模式”
如果不是完整模式可以使用
USE [master]
GO
ALTER DATABASE [fdb]SET RECOVERY FULL WITH NO_WAIT
GO
进行更改，当然也可以通过上面的图形界面进行更改
二.使用证书配置镜像，并备份还原数据库
|
主服务器(192.168.10.8) |
备服务器(192.168.10.9) |
|
1.创建数据库主密钥 USE master GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##') BEGIN CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rd'; end
/* 删除主密钥 USE master; DROP MASTER KEY */
2.创建证书并使用主密钥加密 USE master GO CREATE CERTIFICATE Host_8_Cert WITH Subject = 'Host_8 Certificate', Expiry_Date = '2055-1-1'; --过期日期
/*删除证书 USE master; DROP CERTIFICATE Host_8_Cert */
3.创建端点: 创建端点之前，先查找下看是否已经存在端点: select * from sys.database_mirroring_endpoints 如果需要删除端点 drop endpoint 端点名
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_8_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE = ALL ); END
4.备份证书 在D盘创建Cert目录 BACKUP CERTIFICATE Host_8_Cert TO FILE = 'D:\Cert\Host_8_Cert.cer'; |
1.创建数据库主密钥 USE master GO IF NOT EXISTS(SELECT 1 FROM sys.symmetric_keys k WHERE k.Name='##MS_DatabaseMasterKey##') BEGIN CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rd'; end
/* 删除主密钥 USE master; DROP MASTER KEY */
2.创建证书并使用主密钥加密 USE master GO CREATE CERTIFICATE Host_9_Cert WITH Subject = 'Host_9 Certificate', Expiry_Date = '2055-1-1'; --过期日期
/*删除证书 USE master; DROP CERTIFICATE Host_9_Cert */
3.创建端点: 创建端点之前，先查找下看是否已经存在端点: select * from sys.database_mirroring_endpoints 如果需要删除端点 drop endpoint 端点名
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_9_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE = ALL ); END
4.备份证书 在D盘创建Cert目录 BACKUP CERTIFICATE Host_9_Cert TO FILE = 'D:\Cert\Host_9_Cert.cer'; |
通过以上我们分别在10.8创建以Host_8_Cert证书认证的端点，以及在10.9创建了以Host_9_Cert证书认证的端点。
将D盘Cert目录下的Host_8_Cert证书复制到10.9服务器的对应的位置，同时将10.9上的Host_9_Cert证书复制到10.8服务器上
|
192.168.10.8 |
192.168.10.9 |
|
5.创建一个登录给镜像服务器10.9使用 CREATE LOGIN Host_9_Login WITH PASSWORD = 'pa$$w0rd';
6.创建一个用户以映射到上面创建的登录中 CREATE USER Host_9_User For Login Host_9_Login;
7.使用证书进行授权 CREATE CERTIFICATE Host_9_Cert AUTHORIZATION Host_9_User FROM FILE = 'D:\Cert\Host_9_Cert.cer';
8.对登录进行连接端点的授权 GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_9_Login]; |
5.创建一个登录给镜像服务器10.8使用 CREATE LOGIN Host_8_Login WITH PASSWORD = 'pa$$w0rd';
6.创建一个用户以映射到上面创建的登录中 CREATE USER Host_8_User For Login Host_8_Login;
7.使用证书进行授权 CREATE CERTIFICATE Host_8_Cert AUTHORIZATION Host_8_User FROM FILE = 'D:\Cert\Host_8_Cert.cer';
8.对登录进行连接端点的授权 GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_8_Login]; |
9.在主服务器对数据库进行备份，以及日志进行备份(192.168.10.8)
查看数据库“恢复模式”，是否为“完整”。否则执行如下语句修改为“完整”恢复模式
ALTER DATABASE fdb SET RECOVERY FULL;
数据库备份
日志备份
数据库和日志备份是可以备份到一个文件。
10.在备机上对数据库进行还原(192.168.10.9)
a.选中源设备，并选中备份集中的“完整数据库备份”，并选择目录数据库为fdb
b.点击“选项”进行不回滚事务的恢复
使用同样的方法对事务日志也进行“不回滚提交事务的恢复”
恢复完之后可以看到数据库，提示“正在还原”
三.配置镜像
在192.168.10.9中执行
ALTER DATABASE fdb
SET PARTNER = 'TCP://192.168.10.8:5022';
GO
然后再在192.168.10.8中执行
ALTER DATABASE fdb
SET PARTNER = 'TCP://192.168.10.9:5022';
GO
到这里数据库的镜像就配置成功了
四.主备切换
四、主备切换
1.在高安全模式下：
在主机执行:
use master;
alter database fdb set partner failover;
即完成主备切换
2.在高性能模式下，需要先切换到高安全模式下再执行切换
use master;
alter database fdb set partner safety full;
alter database fdb set partner failover;
3.在主机（10.8）宕机的情况下在备机（10.9）进行强制切换：
use master;
alter database fdb set partner FORCE_SERVICE_ALLOW_DATA_LOSS;
当主机(10.8)重新开机后，在10.9机器上执行
use master;
alter database fdb set partner resume;
此时10.8成为了备机，而10.9成为了主机。
再到10.9机器上执行
alter database fdb set partner failover;
就成了10.8成为主机，10.9成为备机
4.切换镜像在高性能模式下(慎用，可能会丢失数据)
use master;
alter database fdb set partner safety off;
5.关闭数据库镜像
ALTER DATABASE fdb SET PARTNER OFF
6.暂停与恢复数据库镜像
在主体镜像服务器上，若是不小心日志过大，可以进行暂停来设置日志上限
【1】暂停：ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;
【2】恢复：ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;
五.账号
SQL Server默认是不同步master数据库，因此需要在备库新建账号，以期在主备切换后出现无法登录的情形。
USE fdb;
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'password';
在此步骤如果依然无法登录，则可能是SQL Server的身份验证只允许“Windows身份验证模式”。
右击“数据库服务器”---“属性”---“安全性”---“服务器身份验证”---“SQL Server和Windows身份验证模式”
问题处理：
在执行完镜像操作后，镜像为挂起状态。这个时候就需要检查日志
Database mirroring will be suspended. Server instance 'SH01TEST144' encountered error 948, state 2, severity 20 when it was acting as a mirroring partner for database 'fdb'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.
这个时候需要查看服务器--“应用程序日志”，检查发现为主库使用的是SQL Server 2008 R2 sp3，而备库使用的是SQL Server 2008 R2.导致镜像出错，将备机打上sp3补丁。
同时检查下主备库的防火墙情况，是否打开1433，5022端口
