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镜像——非域环境

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

C#调用SQL Server分页存储过程

C#调用SQL Server分页存储过程
  • roy_88
  • roy_88
  • 2016年05月04日 22:26
  • 3475

[SQL Server]非域环境下,配置数据库镜像时的1418错误处理.

在非域环境下,配置SQL Server 2008 数据库镜像服务的时候,在最后一步(即下图中,点击"开始镜像"按钮之后),遇到报错1418错误, 1418:  服务器网络地址 ...

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

原文出处: 本文将演示如何在域环境下部署镜像,在域中部署相对来说简单很多,但是很多企业并不真正使用域来管理服务器(本人所在的公司就是其一),所以有必要演示非域环境,并且重点放在非域环境下。但是作为实...

SQL SERVER【非域环境】同步复制(事务)之搭建篇(2)

SQL SERVER2014 同步复制--事务
  • roy_88
  • roy_88
  • 2017年04月21日 17:10
  • 924

SQL SERVER【非域环境】同步复制(合并)之搭建篇

SQL Server 2014 合并事务搭建
  • roy_88
  • roy_88
  • 2017年04月26日 16:13
  • 1140

SQL SERVER【非域环境】同步复制(对等)之搭建篇

SQL Server 2014 同步复制(对等)
  • roy_88
  • roy_88
  • 2017年05月02日 17:00
  • 1076

SQL SERVER【非域环境】同步复制(事务)之准备篇(1)

SQL SERVER2014同步复制--事务
  • roy_88
  • roy_88
  • 2017年04月21日 16:50
  • 1332

SQL SERVER 2005数据库镜像搭建

SQL SERVER 2005数据库镜像搭建 一 概述     数据库镜像是SQL SERVER 2005用于提高数据库可用性的新技术。数据库镜像将事务日志记录直接从一台服务器传输到另一台服务器...

配置SQL Server 2005事务日志传送(非单机,非域环境,共享文件夹在主库)

事务日志传送这个功能有点像Oracle的DataGuard,具体介绍就不在这里废话了,本文主要记录我在实施过程中所遇到的问题以及解决方法。 事务日志传送的“传送”这个功能是通过Windows共享实现...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL SERVER【非域环境】镜像之搭建篇
举报原因:
原因补充:

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