工作组条件下建立MS SQL 2005 镜像

--------一 准备工作----------

-------(一) 安装三个实例,分别为:-----

SZDXBAK/SQLSERVER(默认实例), 作为主体服务器

SZDXBAK/SQL2 作为镜像服务器

SZDXBAK/WITNESS 作为鉴证服务器

注: 所有实例服务使用SQLService账户启动

 

------(二) 数据库准备-------

--1 在主体服务上创建测试数据库 RecoveryDb

并创建一个完全备份

--2 在镜像服务上使用NORECOVERY选项恢复数据库,

恢复后数据库状态为'正在还原'

 

 

----- 二 创建数据库镜像端点----

--1 在主体数据库(SQLSERVER实例)上创建端点,用于伙伴通讯

CREATE ENDPOINT DbMirroring

STATE=STARTED

AS TCP(LISTENER_PORT=5022)

FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)

go

 

--2 在镜像数据库(SQL2实例)上创建端点,用于伙伴通讯

CREATE ENDPOINT DbMirroring

STATE=STARTED

AS TCP(LISTENER_PORT=5033)

FOR DATABASE_MIRRORING(ROLE=PARTNER,ENCRYPTION=SUPPORTED)

go

 

--3 在鉴证服务器(WITNESS实例)上创建端点,用于鉴证通讯

CREATE ENDPOINT DbMirroring

STATE=STARTED

AS TCP(LISTENER_PORT=5022)

FOR DATABASE_MIRRORING(ROLE=WITNESS,ENCRYPTION=SUPPORTED)

go

 

ALTER ENDPOINT DbMirroring STATE =STARTED

go

 

--4 检查端点配置,可分别在不同服务器上运行以下查询

SELECT * FROM sys.database_mirroring_endpoints

 

----- 三 配置数据库镜像安全性--------

 

--1 主体数据库(SQLSERVER实例)

use master

go

GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK/SQLService"

go

 

--2 镜像数据库(SQL2实例)

use master

go

GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK/SQLService"

go

 

--3 鉴证服务器(WITNESS实例)

use master

go

GRANT CONNECT ON ENDPOINT::"DbMirroring" TO "SZDXBAK/SQLService"

go

 

 

----- -四 启动数据库镜像-----

--注意配置顺序

--1 在镜像数据库(SQL2实例)上,指定到主体服务器(SQLSERVER实例)的伙伴端点

ALTER DATABASE RecoveryDb

SET PARTNER=N'TCP://SZDXBAK:5022'

go

 

--2 在主数据库(SQLSERVER实例)上,指定到镜像服务器(SQL2实例)的伙伴端点

ALTER DATABASE RecoveryDb

SET PARTNER=N'TCP://SZDXBAK:5033'

go

 

--注: 以上两步完成后,已经配置为高保护级别性模式

--3 如果要创建高可用性模式:在主数据库上(SQLSERVER实例),指定鉴证服务器端点

ALTER DATABASE RecoveryDb

SET WITNESS=N'TCP://SZDXBAK:5044

go

 

--4 在高可用性模式下:配置数据库镜像事务安全级别

ALTER DATABASE RecoveryDb SET SAFETY FULL

go

 

--- 五 查看数据库镜像的配置状态

--1 通过Management Studio 对象资源管理器,查看主体数据库,镜像数据库状态

--2 通过Management Studio 对象资源管理器中的数据库属性查看状态

--3 通过系统目录视图查看数据库镜像配置情况

SELECT * FROM sys.database_mirroring_endpoints

SELECT * FROM sys.database_mirroring_endpoints

WHERE database_id = (SELECT database_id FROM sys.database

WHERE name='RecoveryDb')

 

以上配置完成后,主体数据库的状态为:主体,已同步

镜像数据库的状态为:镜像,已同步/正在还原..

 

 

--- 六 服务器角色切换测试----

预备知识

数据库镜像角色切换

自动故障转移

 只针对高可用性模式

 SAFETY=FULL

手动故障转移

 针对高可用性模式和高保护级别性模式

 SAFETY=FULL

强制服务

 只针对高性能模式

 SAFETY=OFF

 

---(一) 自动故障转移

在主体服务器上,断开网络连接,通过数据库资源管理器查看主体/镜像服务器上数据库的状态

镜像服务器状态: 主体,已断开连接

恢复主体服务器

此时原来主体服务器(SQLSERVER实例)变为镜像服务器状态为:镜像,已同步/正在还原..

原来镜像服务器(SQL2实例)变为主体服务器状态:主体,已同步

 

 

---(二) 手动故障转移

-- 主体服务器上执行故障转移过程

USE MASTER

go

ALTER DATABASE RecoveryDb SET PARTNER FAILOVER

go

执行完后主体服务器状态变为: 镜像,已同步/正在还原..

镜像服务器状态变为 主体,已同步

 

---(三) 强制服务,有可能造成数据丢失

--1) 在镜像服务器上,取消对鉴证服务器的配置

ALTER DATABASE RecoveryDb SET WINTESS OFF

 

--2) 在镜像服务器上,配置事务安全性为OFF

ALTER DATABASE RecoveryDb SET SAFETY OFF

 

--3) 断开主体服务器,在镜像服务器上,强制服务 进行角色切换

ALTER DATABASE RecoveryDb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

完成后数据库状态为 主体,已断开连接

 

 

-----七 如何查询镜像服务器上的数据: 在镜像服务器上创建数据库快照

--1 在镜像服务器上,创建数据库快照

CREATE DATABASE ReconveryDb_snap_1

ON (NAME=RecoveryDb,FILENAM='H:/seconddb/recovery_snap1.ss')

AS SNAPSHOT OF RecoveryDb

 

--2 访问镜像数据库

USE ReconveryDb_snap_1

go

SELECT * FROM test

 

注：

1）      做镜像时，必须保证主体和镜像数据库的日志一致性，如果在镜像服务器使用NORECOVERY选项恢复数据库时，主体数据库日志有更新，需要在主体数据库备份日志然后在镜像服务器上使用NORECOVERY选项恢复主体服务器更新的日志，否则在主体数据库上指定到镜像服务器的伙伴端点时会报错

2）      镜像完成后，对主体数据库新建/修改/删除表，镜像服务器将同步更新

3）      镜像完成后，对主体数据库执行收缩文件，镜像服务器将同步更新

 

 

----------------------------------------------------------------------------------------------------------------------

 

==Demo 2==

配置高可用性数据库镜像

[服务器初始配置]
MIR-A:    主体服务器
MIR-B:    镜像服务器
MIR-W:    见证服务器

[镜像数据库]
Northwind

[过程]
配置过程一共分为4个部分

*/

--=================(I)准备镜像数据库=================

-- 1.在MIR-A上，对Northwind数据库做完全备份
BACKUP DATABASE Northwind
TO  DISK = 'C:/Tools/Nwbackup.bak'
GO

-- 2.在MIR-B上，将MIR-A上恢复Northwind数据库的备份，并使用NORECOVERY选项
RESTORE DATABASE Northwind
FROM  DISK = 'C:/Tools/Nwbackup.bak' WITH  NORECOVERY
GO


--=================II)创建数据库镜像端点=================
--    1.在MIR-A上，创建数据库镜像端点，用于伙伴通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 2.在MIR-B上，创建数据库镜像端点，用于伙伴通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 3.在MIR-W上，创建数据库镜像端点，用于见证通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 4.检查端点配置
SELECT * FROM sys.database_mirroring_endpoints
GO

--=================(III)配置数据库镜像安全性=================

        /*
            说明：
            当前的所有服务器使用相同的服务账户SQL2k5SP1/SQLService名称和口令
        */


-- 1.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1/SQLService"
GO

-- 2.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1/SQLService"
GO

-- 3.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1/SQLService"
GO

--=================(IV)启动数据库镜像=================

--注意顺序，需要在首先在镜像服务器上配置伙伴

-- 1.在MIR-B上，指定伙伴端点
ALTER DATABASE Northwind
SET PARTNER = N'TCP://MIR-A:5022'
GO

-- 2.在MIR-A上，指定伙伴端点
ALTER DATABASE Northwind
SET PARTNER = N'TCP://MIR-B:5022'
GO

-- 3.在MIR-A上，指定证服务器端点
ALTER DATABASE Northwind
SET WITNESS = N'TCP://MIR-W:5022'
GO

-- 3.配置数据库镜像事务安全级别
ALTER DATABASE Northwind SET SAFETY FULL
GO


--=================查看数据库镜像的配置状态=================

-- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态

-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态

-- 3.)通过系统目录视图查看数据库镜像配置情况
SELECT * FROM sys.database_mirroring_endpoints
SELECT * FROM sys.database_mirroring WHERE database_id =
    (SELECT database_id FROM sys.databases WHERE name = 'Northwind')