SQL SERVER【非域环境】镜像之搭建篇

原创 2017年06月01日 17:22:59

环境搭建


软件环境(操作系统:Windows Server 2008 R2 Standard SP1):

数据库版本

Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 

Feb 20 2014 20:04:26

Copyright (c) Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

 

 设置共享文件夹D盘路径(D:\ShareFile

 

共享文件夹ShareFile用于机器间相互传输文件,右健—共享—特定用户—添加(Everyone)—权限(读/写)

 

1、在主服务器(SerA)上执行:

1.1、搭建环境创建测试数据库MirrorDB

CREATE DATABASE MirrorDB
ON(
NAME = MirrorDB_DATA,
FILENAME = N'D:\Database\MirrorDB.mdf'
)
LOG ON(
NAME = MirrorDB_LOG,
FILENAME = N'D:\Database\MirrorDB.ldf'
)
ALTER DATABASE MirrorDB SET
RECOVERY FULL
GO


1.2、备份到共享文件夹

--  完全备份
BACKUP DATABASE MirrorDB
TO DISK = N'D:\ShareFile\MirrorDB.bak'
WITH FORMAT
go


 

2、切换到作镜像服务器(SerB)执行

2.1、初始化镜像主体数据库,把共享文件从网络路径(\\SerA\ShareFile\MirrorDB.bak)复制到本机路径(E:\BackDB\MirrorDB.bak)

 

RESTORE DATABASE MirrorDB
FROM DISK = N'E:\BackDB\MirrorDB.bak'
WITH REPLACE
, NORECOVERY
-- 镜像数据库文件要放在指定位置, 则启用下面的 Move 选项
, MOVE 'MirrorDB_DATA' TO N'E:\Database\MirrorDB.mdf'
, MOVE 'MirrorDB_LOG' TO N'E:\Database\MirrorDB.ldf'


3、切换到主服务器(SerA)执行

3.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'
 
CREATE CERTIFICATE CT_Mirror_SerA
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'
GO


3.2、备份证书(新添加的文件显示在其它服务显示不出来时把此文件共享权限设置为Everyone读写)

BACKUP CERTIFICATE CT_Mirror_SerA
TO FILE = 'D:\ShareFile\CT_Mirror_SerA.cer'


3.3 创建一个数据库镜像端点

CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
    FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerA, -- 证书身份验证
ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)


4、切换到作镜像服务器(SerB)执行(同步骤3相同)

4.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'
 
CREATE CERTIFICATE CT_Mirror_SerB
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'
GO


 

4.2、备份证书[把备份证书从(E:\BackDB\CT_Mirror_SerB.cer)复制到共享网络路径(\\SerA\ShareFile\CT_Mirror_SerB.cer]

BACKUP CERTIFICATE CT_Mirror_SerB
TO FILE = 'E:\BackDB\CT_Mirror_SerB.cer'
4.3、创建数据库镜像端点
CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
    FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerB, -- 证书身份验证
ENCRYPTION = DISABLED,                       -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                  -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)


GO

4.4、在共享文件夹里(\\SerA\ShareFile\CT_Mirror_SerA.cer')复制到本机(E:\BackDB\CT_Mirror_SerA.cer),建立主体服务器上的证书,注:共享证书文件未显示时把文件权限改为Everyone读写即可

CREATE CERTIFICATE CT_Mirror_SerA
FROM FILE = 'C:\CT_Mirror_SerA.cer'


4.5、建立登录

CREATE LOGIN LOGIN_Mirror_SerA
FROM CERTIFICATE CT_Mirror_SerA


4.6、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror

TO LOGIN_Mirror_SerA

 

5、切换主服务器SerA)执行

5.1、用共享文件夹里(D:\ShareFile\CT_Mirror_SerB.cer),建立主体服务器上的证书

CREATE CERTIFICATE CT_Mirror_SerB
FROM FILE = 'D:\ShareFile\CT_Mirror_SerB.cer'


5.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerB
FROM CERTIFICATE CT_Mirror_SerB


5.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerB


6、在镜像服务器上启用数据库镜像此操作镜像服务器上执行

ALTER DATABASE MirrorDB SET
PARTNER = 'TCP://SerA:5022'


 

7、在主体服务器上启用数据库镜像(默认为高安全性模式,所以不用进行模式设置)

7.1、此操作主体服务器上执行

ALTER DATABASE MirrorDB SET
PARTNER = 'TCP://SerB:5022'


7.2、在SSMS连接SerB实例,查看数据库显示状态

7.3、在主体数据库(MirrorDB)—右健—任务—启动数据库镜像监视器,显示如图:


8、配置见证服务器,切换到作镜像服务器(SerCWitness)执行(同步骤3相同)

8.1、使用数据库主密钥加密证书

IF NOT EXISTS(  
SELECT * FROM sys.symmetric_keys
WHERE name = N'##MS_DatabaseMasterKey##')
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'Roy@123'
--创建证书
CREATE CERTIFICATE CT_Mirror_SerCWitness
WITH
SUBJECT = N'certificate for database mirror',
START_DATE = '19990101',
EXPIRY_DATE = '99991231'


8.2、备份证书并复制到网络共享路径(\\SerA\ShareFile\CT_Mirror_SerCWitness.cer

BACKUP CERTIFICATE CT_Mirror_SerCWitness
TO FILE = 'E:\DBBak\CT_Mirror_SerCWitness.cer'


8.3、数据库镜像端点

CREATE ENDPOINT EDP_Mirror
STATE = STARTED 
AS TCP(
LISTENER_PORT = 5022,  -- 镜像端点使用的通信端口
LISTENER_IP = ALL)     -- 侦听的IP地址
    FOR DATABASE_MIRRORING(
AUTHENTICATION = CERTIFICATE CT_Mirror_SerCWitness, -- 证书身份验证
ENCRYPTION = DISABLED,                             -- 不对传输的数据加密,如果需要加密,可以配置为 SUPPORTED 或 REQUIRED, 并可选择加密算法
ROLE = ALL)                                        -- 端点支持所有的数据库镜像角色, 也可以设置为 WITNESS(仅见证服务器),或 PARTNER(仅镜像伙伴)
GO


8.4、建立主体服务器上的证书(主体服务器上备份的证书已经复制到 E:\DBBak\CT_Mirror_SerA.cer)

8.4.1、创建证书

CREATE CERTIFICATE CT_Mirror_SerA
FROM FILE = 'E:\DBBak\CT_Mirror_SerA.cer'


8.4.2、 建立登录

CREATE LOGIN LOGIN_Mirror_SerA
FROM CERTIFICATE CT_Mirror_SerA


8.4.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerA


8.5、完成镜像服务器上数据库镜像端点的传输安全模式配置

8.5.1、建立镜像服务器上的证书(假设镜像服务器上备份的证书已经复制到 E:\DBBak\CT_Mirror_SerB.cer)

CREATE CERTIFICATE CT_Mirror_SerB
FROM FILE = 'E:\DBBak\CT_Mirror_SerB.cer'


8.5.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerB
FROM CERTIFICATE CT_Mirror_SerB


8.5.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerB


GO

 

9、切换镜像服务器(SerB)添加安全配置

9.1、建立见证服务器上的证书

CREATE CERTIFICATE CT_Mirror_SerCWitness
FROM FILE = 'E:\BackDB\CT_Mirror_SerCWitness.cer'


9.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerCWitness
FROM CERTIFICATE CT_Mirror_SerCWitness


9.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerCWitness


 

10、切换主服务器(SerA)添加安全配置

10.1、建立见证服务器上的证书(见证服务器上备份的证书已经复制到 共享网络路径D:\ShareFile\CT_Mirror_SerCWitness.cer)

CREATE CERTIFICATE CT_Mirror_SerCWitness
FROM FILE = 'D:\ShareFile\CT_Mirror_SerCWitness.cer'


10.2、建立登录

CREATE LOGIN LOGIN_Mirror_SerCWitness
FROM CERTIFICATE CT_Mirror_SerCWitness


10.3、授予对数据库镜像端点的 connect 权限

GRANT CONNECT ON ENDPOINT::EDP_Mirror
TO LOGIN_Mirror_SerCWitness


10.4、主体服务器上执行

ALTER DATABASE MirrorDB SET
WITNESS = 'TCP://SerCWitness:5022'


10.5、查询数据库状态

以下脚本可以在主体服务器和镜像服务器上执行,执行结果为镜像的状态

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'MirrorDB')


 

11、测试环境

11.1、切换主服务器(SerA),创建表+新增数据

CREATE TABLE MirrorDB.dbo.Tab(
ID INT IDENTITY(1,1),Name NVARCHAR(50));
go
INSERT INTO  MirrorDB.dbo.Tab(Name)VALUES('Roy');
Go


 

11.2、主备互换

ALTER DATABASE [MirrorDB] SET PARTNER FAILOVER;
Go


--在切换镜像服务器(SerB)显示为主体数据库查看数据

select * from MirrorDB.dbo.Tab


12、删除示例配置(证书和数据库的备份需要在操作系统的资源管理器中删除)

12.1、主体服务器(SerA)上执行的操作

-- 12.1.1. 停止镜像和删除主体数据库
USE master
GO

ALTER DATABASE MirrorDB SET
	PARTNER OFF
DROP DATABASE MirrorDB
GO

-- 12.1.2. 删除镜像端点
DROP ENDPOINT EDP_Mirror
GO

-- 12.1.3. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerB
DROP LOGIN LOGIN_Mirror_SerCWitness
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO

12.2、 镜像服务器(SerB)上执行的操作

-- 12.2.1. 删除镜像数据库
USE master
GO

DROP DATABASE MirrorDB
GO

-- 12.2.2. 删除镜像端点
DROP ENDPOINT EDP_Mirror
GO

-- 12.2.3. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerA
DROP LOGIN LOGIN_Mirror_SerCWitness
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO

12.3、见证服务器(SerCWitness)上执行的操作
-- 12.3.1. 删除端点
DROP ENDPOINT EDP_Mirror
GO

-- 12.3.2. 删除登录及证书
DROP LOGIN LOGIN_Mirror_SerA
DROP LOGIN LOGIN_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerA
DROP CERTIFICATE CT_Mirror_SerB
DROP CERTIFICATE CT_Mirror_SerCWitness
GO


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

版权声明:本文为博主原创文章,未经博主允许不得转载。

第三篇——第二部分——第四文 配置SQL Server镜像——非域环境

前面已经演示了域环境下的镜像搭建,本文将使用非域环境来搭建镜像,同样,先按照不带见证服务器的高安全模式(同步)的方式搭建,然后 演示异步模式,最后会演示带有见证服务器的高安全模式。...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2014年05月30日 10:51
  • 13863

SQL Server 2008 镜像操作心得(非域控环境中创建数据库镜像)

下面的文章,我是在网上找的(非域控环境中创建数据库镜像),针对这篇文章,我只想补充几句: 1、主机ip: 192.168.9.181        备机ip:192.168.9.182 2、删除...
  • wangqi0079
  • wangqi0079
  • 2013年02月22日 16:50
  • 522

SQL Server扫盲系列——镜像篇

为方便查看,并以专题形式展示,所以我会把一些文章
  • DBA_Huangzj
  • DBA_Huangzj
  • 2014年06月30日 14:19
  • 13772

SQL Server 2012 数据库镜像配置完整篇

“数据库镜像”是一种提高 SQL Server 数据库的可用性的解决方案。 镜像基于每个数据库实现,并且只适用于使用完整恢复模式的数据库。数据库镜像维护一个数据库的两个副本,这两个副本必须驻留在不同的...
  • stephenh1991
  • stephenh1991
  • 2017年09月01日 13:50
  • 424

SQLServer 复制和数据库镜像 详细配置部署

SQLserver 可以把镜像和复制同时部署,结合了双方的高可用性,可以使数据库得到更好的高可用性和容灾的保证。 关于镜像:数据库镜像 关于复制:数据库镜像 本章的复制为事务可更新订阅:事务复制...
  • kk185800961
  • kk185800961
  • 2015年06月05日 00:29
  • 7673

第三篇——第二部分——第一文 SQL Server镜像简介

原文出处: 镜像是什么?说白了就是个镜子(没用过镜子?没镜子你总要小便吧?开个玩笑),这里镜子的含义主要有两个:1、一模一样,下面会详细介绍,包括库名、数据文件和日志文件的存放路径都要一样。2、看得到...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2014年05月26日 12:02
  • 14484

SQLServer数据库镜像配置

目录 一、目标... 2 二、前提条件、限制和建议... 2 三、设置概述... 2 四、安装Sql Server 2008 enterprise X64. 3 4.1、安装.NET3.5. 3 4....
  • u012684343
  • u012684343
  • 2016年11月23日 16:31
  • 700

SQL SERVER【非域环境】镜像之搭建篇

SQL SERVER 2014 镜像 搭建,操作步骤同旧版本没有区别
  • roy_88
  • roy_88
  • 2017年06月01日 17:22
  • 1392

SQL Server 2008 R2数据库镜像部署

  • 2013年11月12日 17:53
  • 1.79MB
  • 下载

第三篇——第二部分——第三文 配置SQL Server镜像——域环境

原文出处: 本文将演示如何在域环境下部署镜像,在域中部署相对来说简单很多,但是很多企业并不真正使用域来管理服务器(本人所在的公司就是其一),所以有必要演示非域环境,并且重点放在非域环境下。但是作为实...
  • DBA_Huangzj
  • DBA_Huangzj
  • 2014年06月06日 17:32
  • 8191
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL SERVER【非域环境】镜像之搭建篇
举报原因:
原因补充:

(最多只允许输入30个字)