加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MsSql教程 > 正文

SQLServer 数据库镜像（二）域环境中完整镜像脚本配置

发布时间：2020-12-12 12:48:36 所属栏目：MsSql教程 来源：网络整理
导读：SQLServer 数据库镜像（一）单服务器无见证的镜像脚本配置 SQLServer 数据库镜像（二）域环境中完整镜像脚本配置 断断续续弄了几天，搭建好了域，服务器加入域后现在测试这种情况。 测试环境： 主服务器:?? IP = 192.168.2.10 InstanceName?=?MSSQLSERVER??

SQLServer 数据库镜像（一）单服务器无见证的镜像脚本配置

SQLServer 数据库镜像（二）域环境中完整镜像脚本配置


断断续续弄了几天，搭建好了域，服务器加入域后现在测试这种情况。


测试环境：



主服务器:??

IP = 192.168.2.10

InstanceName?=?MSSQLSERVER??

LISTENER_PORT?=?5022??

??

镜像服务器:???

IP = 192.168.2.10

InstanceName?=?MSSQLSERVERA??

LISTENER_PORT?=?5023??

?

见证服务器：

IP = 192.168.2.11

InstanceName?=?MSSQLSERVER??

LISTENER_PORT?=?5022 ?



【1. 数据库备份还原】

--	主体：设置数据库“恢复模式”为“完整”模式
USE master;
ALTER DATABASE [DBName] SET RECOVERY FULL  
GO

--	主体：备份数据库
USE master;
BACKUP DATABASE [DBName]  
TO DISK = 'C:DatabasesMSSQLSERVERDBName.BAK' WITH INIT,FORMAT  
GO  

--	镜像：还原数据库(NORECOVERY)
USE master;
RESTORE DATABASE [DBName] 
FROM  DISK = N'C:DatabasesMSSQLSERVERDBName.BAK' 
WITH  FILE = 1,MOVE N'DBName' TO N'C:DatabasesMSSQLSERVERADBName.mdf',MOVE N'DBName_log' TO N'C:DatabasesMSSQLSERVERADBName_log.ldf',NOUNLOAD,NORECOVERY,STATS = 10
GO

【2. 创建数据库主密钥和证书，备份交换证书】 

--	主体：
USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';

CREATE CERTIFICATE Cert_kk_db1_mssqlserver 
WITH SUBJECT = 'Cert_kk_db1_mssqlserver',START_DATE = '2015-03-20';

BACKUP CERTIFICATE Cert_kk_db1_mssqlserver 
TO FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserver.cer';

--	镜像：
USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';

CREATE CERTIFICATE Cert_kk_db1_mssqlserverA 
WITH SUBJECT = 'Cert_kk_db1_mssqlserverA',START_DATE = '2015-03-20';

BACKUP CERTIFICATE Cert_kk_db1_mssqlserverA 
TO FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserverA.cer';

--	见证：
USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'kk_2012@';

CREATE CERTIFICATE Cert_kk_db2_mssqlserver 
WITH SUBJECT = 'Cert_kk_db2_mssqlserver',START_DATE = '2015-03-20';

BACKUP CERTIFICATE Cert_kk_db2_mssqlserver 
TO FILE = 'C:DatabasesCERTIFICATECert_kk_db2_mssqlserver.cer';


--	交换证书(相互拷贝证书):
/*
主体证书(拷贝到)————>镜像、见证
镜像证书(拷贝到)————>主体、见证
见证证书(拷贝到)————>主体、镜像
*/


【3. 创建用户和还原证书】

--  创建域用户：UserForMirror    
--  SQLServer 使用 [network service] 启动实例服务  
  
--  主体(还原 镜像和见证 的证书)：
USE master    
GO    
CREATE LOGIN [KKUserForMirror] FROM WINDOWS;    
GO    
CREATE USER UserForMirror FOR LOGIN [KKUserForMirror];    
GO   
USE master  
GO  
CREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserverA.cer';  
GO   
CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db2_mssqlserver.cer';  
GO  
  
--  镜像(还原 主体和见证 的证书)：  
USE master    
GO    
CREATE LOGIN [KKUserForMirror] FROM WINDOWS;    
GO    
CREATE USER UserForMirror FOR LOGIN [KKUserForMirror];    
GO  
USE master  
GO  
CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserver.cer';  
GO  
CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db2_mssqlserver.cer';  
GO  
  
--  见证(还原 主体和镜像 的证书)：
USE master    
GO    
CREATE LOGIN [KKUserForMirror] FROM WINDOWS;    
GO    
CREATE USER UserForMirror FOR LOGIN [KKUserForMirror];    
GO     
USE master  
GO  
CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserver.cer';  
GO  
CREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]  
AUTHORIZATION [UserForMirror]   
FROM FILE = 'C:DatabasesCERTIFICATECert_kk_db1_mssqlserverA.cer';  
GO


【4. 创建数据库镜像端点】

--  主体：(域账户：KKUserForMirror,UTHENTICATION = CERTIFICATE 证书)  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KKUserForMirror]  
    STATE=STARTED  
    AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)  
    FOR DATA_MIRRORING   
    (ROLE = PARTNER,AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserver,ENCRYPTION = REQUIRED ALGORITHM AES)  
GO  
GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KKUserForMirror];  
GO

--  镜像(主体镜像同一服务器，端口不一样：LISTENER_PORT = 5023)：  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KKUserForMirror]  
    STATE=STARTED  
    AS TCP (LISTENER_PORT = 5023,AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserverA,ENCRYPTION = REQUIRED ALGORITHM AES)  
GO  
GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KKUserForMirror];  
GO  

--  见证(ROLE = WITNESS)：  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KKUserForMirror]  
    STATE=STARTED  
    AS TCP (LISTENER_PORT = 5022,LISTENER_IP = ALL)  
    FOR DATA_MIRRORING   
    (ROLE = WITNESS,AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver,ENCRYPTION = REQUIRED ALGORITHM AES)  
GO  
GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KKUserForMirror];  
GO

【5.?开始镜像】

--开始镜像 

--在【镜像服务器实例】执行,PARTNER为主服务器  
USE [master]  
GO  
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.1.10:5022';  
GO  
  
--在【主服务器实例】执行,PARTNER为镜像服务器  
USE [master]  
GO  
ALTER DATABASE [DBNameB] SET PARTNER = 'TCP://192.168.1.10:5023';  
GO  
ALTER DATABASE [DBNameB] SET WITNESS = 'TCP://192.168.1.11:5022'; 
GO
--在主体执行:设置为高安全模式  
ALTER DATABASE [DBName] SET SAFETY FULL


如图：




【6.?创建数据库镜像监视器作业】

--在主备执行
use msdb;  
exec sys.sp_dbmmonitoraddmonitoring  
--exec sys.sp_dbmmonitorhelpmonitoring  
--exec sys.sp_dbmmonitorresults DBName,0  
--exec sys.sp_dbmmonitorchangemonitoring  
--exec sys.sp_dbmmonitordropmonitoring



【7. 测试】

--	主体：随意更改，等下切换后是否已同步
USE DBName;  
SELECT * FROM [dbo].[MyTable]
  
UPDATE [dbo].[MyTable] SET NAME = 'master'  
DELETE TOP(1) FROM [dbo].[MyTable]  
  
  
--	主体：手动方式进行主备切换  
USE [master]  
GO  
ALTER DATABASE DBName SET PARTNER FAILOVER;
GO


手动停止主体的服务，可以看到，“镜像”变为了“主体”




当原来主体的服务启动后，发现它变为了“镜像”。（此时也可以把它切换回主体）



【7. 相关脚本】

select * from sys.certificates
select * from sys.endpoints
select * from sys.database_mirroring_endpoints
select * from sys.database_mirroring 
select * from sys.database_mirroring_witnesses 

USE master; 
ALTER DATABASE [DBName] SET SAFETY FULL;		--设置为高安全模式 
ALTER DATABASE [DBName] SET PARTNER RESUME;		--恢复镜像
ALTER DATABASE [DBName] SET PARTNER FAILOVER;	--切换主备
ALTER DATABASE [DBName] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;	--强制恢复镜像
ALTER DATABASE [DBName] SET ONLINE;		--在线数据库

【8. 问题】 

/*以上可能出现的问题：

【问题】开始镜像时出现的问题（注：上面的脚本是正确的，这里为错误案例）：
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.1.10:5023'

提示错误如下：
消息 1418，级别 16，状态 1，第 2 行
服务器网络地址 "TCP://192.168.1.10:5023" 无法访问或不存在。
请检查网络地址名称，并检查本地和远程端点的端口是否正常运行。

【问题】发现IP弄错了，把192.168.1.10改为192.168.2.10再执行：
USE master;
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5022'
GO

提示错误如下：
已为数据库镜像启用数据库 "DBName"。

【解决】停止镜像，重新连接
ALTER DATABASE [DBName] SET PARTNER OFF;
GO
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5022'
GO


【问题】链接镜像时又错误：（ip 和 端口都能连接到）
ALTER DATABASE [DBName] SET WITNESS = 'TCP://192.168.2.11:5022'

提示错误如下：
消息 1456，级别 16，状态 3，第 1 行
无法将 ALTER DATABASE 命令发送到远程服务器实例 'TCP://192.168.2.11:5022'。
数据库镜像配置未更改。请确保该服务器已连接，然后重试。

【解决】AUTHENTICATION当时为Windows授权，改为证书
CREATE ENDPOINT [Endpoint_For_Mirror] 
……
 AUTHENTICATION = CERTIFICATE 
*/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同