USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO
主节点备份数据库
BACKUP DATABASE TEST TO DISK = N'D:\backup\test.bak' WITH NOFORMAT, NOINIT, NAME = N'TEST-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
镜像节点恢复数据库(NORECOVERY)
RESTORE DATABASE test FROM disk='D:\backup\test.bak' WITH NORECOVERY, MOVE 'test_Data' TO 'D:\data\test_Data.mdf', MOVE 'test_Log' TO 'F:\log\test_Log.ldf'; GO
主节点创建端点
CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER) GO --Partners under same domain user; login already exists in master. --Create a login for the witness server instance, --which is running as Somedomain\witnessuser: USE master ; GO CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ; GO -- Grant connect permissions on endpoint to login account of witness. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser]; --Grant connect permissions on endpoint to login account of partners. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername]; GO
镜像节点创建端点
CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=ALL) GO --Partners under same domain user; login already exists in master. --Create a login for the witness server instance, --which is running as Somedomain\witnessuser: USE master ; GO CREATE LOGIN [Somedomain\witnessuser] FROM WINDOWS ; GO --Grant connect permissions on endpoint to login account of witness. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Somedomain\witnessuser]; --Grant connect permissions on endpoint to login account of partners. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername]; GO
见证节点创建端点
CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=WITNESS) GO --Create a login for the partner server instances, --which are both running as Mydomain\dbousername: USE master ; GO CREATE LOGIN [Mydomain\dbousername] FROM WINDOWS ; GO --Grant connect permissions on endpoint to login account of partners. GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Mydomain\dbousername]; GO
在镜像节点设置主节点成为伙伴
ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://192.168.25.10:5022' GO
在主节点设置镜像节点成为伙伴
ALTER DATABASE AdventureWorks SET PARTNER = 'TTCP://192.168.25.11:5022' GO
在主节点设置见证节点
ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://192.168.25.12:5022' GO
镜像群集配置(非域)
先决条件
数据库必须处于完全恢复模式,如果不是请用下列语句修改
USE master; GO ALTER DATABASE AdventureWorks SET RECOVERY FULL; GO
主节点备份数据库
BACKUP DATABASE TEST TO DISK = N'D:\backup\test.bak' WITH NOFORMAT, NOINIT, NAME = N'TEST-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO
镜像节点恢复数据库(NORECOVERY)
RESTORE DATABASE test FROM disk='D:\backup\test.bak' WITH NORECOVERY, MOVE 'test_Data' TO 'D:\data\test_Data.mdf', MOVE 'test_Log' TO 'F:\log\test_Log.ldf'; GO
主节点创建证书
IF NOT EXISTS( SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Roy@123'; --KEY密码 GO CREATE CERTIFICATE CA_MIRROR_SQL01 --证书名称 WITH SUBJECT = N'certificate for database mirror', START_DATE = '19990101', EXPIRY_DATE = '99991231'; GO
镜像节点创建证书
IF NOT EXISTS( SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Roy@123'; --KEY密码 GO CREATE CERTIFICATE CA_MIRROR_SQL01 --证书名称 WITH SUBJECT = N'certificate for database mirror', START_DATE = '19990101', EXPIRY_DATE = '99991231'; GO
见证节点创建证书
IF NOT EXISTS( SELECT * FROM sys.symmetric_keys WHERE name = N'##MS_DatabaseMasterKey##') CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'Roy@123'; --KEY密码 GO CREATE CERTIFICATE CA_MIRROR_SQL01 --证书名称 WITH SUBJECT = N'certificate for database mirror', START_DATE = '19990101', EXPIRY_DATE = '99991231'; GO
备份主节点证书到其它节点
BACKUP CERTIFICATE CA_MIRROR_SQL01 TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'
备份镜像节点证书到其它节点
BACKUP CERTIFICATE CA_MIRROR_SQL01 TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'
备份见证节点证书到其它节点
BACKUP CERTIFICATE CA_MIRROR_SQL01 TO FILE ='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL01.cer'
主节点创建端点
CREATE ENDPOINT EDP_Mirror STATE = STARTED AS TCP( LISTENER_PORT = 5022, -- 镜像端点使用的通信端口 LISTENER_IP = ALL) -- 侦听的IP地址 FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE CA_MIRROR_SQL01, -- 证书身份验证 ENCRYPTION = DISABLED, -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法 ROLE = ALL)
镜像节点创建端点
CREATE ENDPOINT EDP_Mirror STATE = STARTED AS TCP( LISTENER_PORT = 5022, -- 镜像端点使用的通信端口 LISTENER_IP = ALL) -- 侦听的IP地址 FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE CA_MIRROR_SQL02, -- 证书身份验证 ENCRYPTION = DISABLED, -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法 ROLE = ALL)
见证节点创建端点
CREATE ENDPOINT EDP_Mirror STATE = STARTED AS TCP( LISTENER_PORT = 5022, -- 镜像端点使用的通信端口 LISTENER_IP = ALL) -- 侦听的IP地址 FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE CA_Mirror_WITNESS, -- 证书身份验证 ENCRYPTION = DISABLED, -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法 ROLE = ALL)
主节点创建镜像节点证书
CREATE LOGIN CA_MIRROR_SQL02 FROM FILE='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL02.cer';
主节点创建见证节点证书
create CERTIFICATE CA_MIRROR_WITNESS FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';
镜像节点创建主节点证书
create CERTIFICATE CA_MIRROR_WITNESS FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';
镜像节点创建见证节点证书
create CERTIFICATE CA_MIRROR_WITNESS FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';
见证节点创建主节点证书
create CERTIFICATE CA_MIRROR_WITNESS FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_WITNESS.cer';
见证节点创建镜像节点证书
CREATE CERTIFICATE CA_MIRROR_SQL02 FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\CA_MIRROR_SQL02.cer';
主节点创建登陆用户
CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02; CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;
镜像节点创建登陆用户
CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02; CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;
见证节点创建登陆用户
CREATE LOGIN LOGIN_TO_SQL02 FROM CERTIFICATE CA_MORROR_SQL02; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_SQL02; CREATE LOGIN LOGIN_TO_WITNESS FROM CERTIFICATE CA_MIRROR_WITNESS; GRANT CONNECT ON ENDPOINT::EDP_Mirror TO LOGIN_TO_WITNESS;
镜像节点设置主节点为伙伴
alter database mirror set PARTNER='TCP://192.168.25.10:5022';
主节点设置镜像节点为伙伴
alter database mirror set PARTNER='TCP://192.168.25.11:5022';
主节点设置见证节点为伙伴
ALTER DATABASE mirror SET WITNESS = 'TCP://192.168.25.13:5022'
同步验证
查看镜像服务状态
SELECT mirroring_role_desc, -- 数据库在镜像会话中当前的角色 mirroring_state_desc, -- 镜像当前状态 mirroring_safety_level_desc, -- 镜像运行模式 mirroring_witness_state_desc -- 与见证服务器的连接情况 FROM sys.database_mirroring WHERE database_id = DB_ID(N'mirror');
镜像暂停与恢复
--暂停 ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND; --恢复 ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;
镜像切换
ALTER DATABASE [MirrorDB] SET PARTNER FAILOVER; Go