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

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:\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 = 10GO

【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   USE master  GO  CREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer';  GO   CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';  GO    --  镜像(还原 主体和见证 的证书)：  USE master    GO    CREATE LOGIN [KK\UserForMirror] FROM WINDOWS;    GO    CREATE USER UserForMirror FOR LOGIN [KK\UserForMirror];    GO  USE master  GO  CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer';  GO  CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db2_mssqlserver.cer';  GO    --  见证(还原 主体和镜像 的证书)：USE master    GO    CREATE LOGIN [KK\UserForMirror] FROM WINDOWS;    GO    CREATE USER UserForMirror FOR LOGIN [KK\UserForMirror];    GO     USE master  GO  CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserver.cer';  GO  CREATE CERTIFICATE [Cert_kk_db1_mssqlserverA]  AUTHORIZATION [UserForMirror]   FROM FILE = 'C:\Databases\CERTIFICATE\Cert_kk_db1_mssqlserverA.cer';  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  GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];  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  GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];  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  GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserForMirror];  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,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.certificatesselect * from sys.endpointsselect * from sys.database_mirroring_endpointsselect * 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;GOALTER 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 */