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 = 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
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.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
*/