域环境中完整镜像脚本配置-数据库-火龙果软件工程

捐助

域环境中完整镜像脚本配置

作者：哈哈KK 来源：CSDN 发布于：2015-04-20

次浏览

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

测试环境：

主服务器:

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:\Databases\MSSQLSERVER\DBName.BAK' WITH INIT,FORMAT    
GO    
  
--  镜像：还原数据库(NORECOVERY)  
USE master;  
RESTORE DATABASE [DBName]   
FROM  DISK = N'C:\Databases\MSSQLSERVER\DBName.BAK'   
WITH  FILE = 1,   
MOVE N'DBName' TO N'C:\Databases\MSSQLSERVERA\DBName.mdf',    
MOVE N'DBName_log' TO N'C:\Databases\MSSQLSERVERA\DBName_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:\Databases\CERTIFICATE\Cert_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:\Databases\CERTIFICATE\Cert_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:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';  
  
  
--  交换证书(相互拷贝证书):  
/*  
主体证书(拷贝到)————>镜像、见证  
镜像证书(拷贝到)————>主体、见证  
见证证书(拷贝到)————>主体、镜像  
*/

【3. 创建数据库登录账户和用户并还原证书】

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

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

--  主体：(域账户：KK\UserForMirror,UTHENTICATION = CERTIFICATE 证书)  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KK\UserForMirror]  
    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  
  
--  镜像(主体镜像同一服务器，端口不一样：LISTENER_PORT = 5023)：  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KK\UserForMirror]  
    STATE=STARTED  
    AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)  
    FOR DATA_MIRRORING   
    (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserverA,
 ENCRYPTION = REQUIRED ALGORITHM AES)  
GO  
  
--  见证(ROLE = WITNESS)：  
USE master;  
CREATE ENDPOINT [Endpoint_For_Mirror]   
    AUTHORIZATION [KK\UserForMirror]  
    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

【5. 开始镜像】

--  注意执行顺序:镜像——>主体——>见证  
  
--  镜像:(PARTNER为主体服务器)  
USE master;  
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5022'  
GO  
  
--  主体:(PARTNER为镜像服务器)  
USE master;  
ALTER DATABASE [DBName] SET PARTNER = 'TCP://192.168.2.10:5023'  
GO  
  
--  主体:(WITNESS为见证服务器)  
USE master;  
ALTER DATABASE [DBName] SET WITNESS = 'TCP://192.168.2.11:5022';  
GO

配置完成！界面查看如图：

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

--在主备执行  
use msdb;    
exec sys.sp_dbmmonitoraddmonitoring    
--exec sys.sp_dbmmonitorhelpmonitoring    
--exec sys.sp_dbmmonitorresults DBName,0,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

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

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

【8. 相关脚本】

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;     --在线数据库

【9. 问题】

/*以上可能出现的问题：  
  
【问题】开始镜像时出现的问题（注：上面的脚本是正确的，这里为错误案例）：  
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   
*/

次浏览