sqlserver镜像简介

发布时间：2020-12-12 12:58:08 所属栏目：MsSql教程来源：网络整理

导读：http://blog.csdn.net/hailang9027/article/details/8440212 一、?镜像简介 1、?简介数据库镜像是将数据库事务处理从一个SQL?Server数据库移动到不同SQL?Server环境中的另一个SQL?Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

http://blog.csdn.net/hailang9027/article/details/8440212

一、?镜像简介

1、?简介

数据库镜像是将数据库事务处理从一个SQL?Server数据库移动到不同SQL?Server环境中的另一个SQL?Server数据库中。镜像不能直接访问;它只用在错误恢复的情况下才可以被访问。

要进行数据库镜像所需的最小需求包括了两个不同的SQL?Server运行环境。主服务器被称为“主机”，第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。

除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。见证服务器是第三个SQL?Server?2005运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2比1投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

2、?优点

下表是SQL?Server可用性官方解决方案的一个对照表，现时我中心使用的恢复模式是“冷备份”中的“备份/恢复”，通常来说“热备份”比“冷备份”的可用性更高，恢复更快，更适合我中心现时的实际情况。如果不从成本考虑的话，“热备份”中的“故障转移群集”的可用性是最高的，但是故障转移群集需要借助磁盘阵列而且建设本身复杂性较高。数据库镜像的建立并没有太多的硬件要求，最起码没有像“故障转移群集”需要共享存储这么高的要求。

? ? ?

2、?缺点

（1）由于SQL?Server是一个实例多个数据库的产品，数据库镜像技术是基于数据库级别的，因此每次主数据库新增数据库都必须为备机增加数据库并且为新增的数据库建立镜像关系。

（2）数据库的登录名和用户是存储在master数据库，master数据库是不能做镜像的，所以每次操作数据库的登录名和用户也是需要多维护一份，

（3）数据库作业不能得到相应的维护。

（4）微软号称镜像可以让客户端对故障透明，但是实际测试中发现只有满足特定的条件才能实现透明化，而且透明化得客户端支持才可行（.net?Framework?2.0以上，Microsoft?jdbc驱动?1.1以上）。

（5）跨数据库事务和分布式事务均不支持数据库镜像。

纵观其他几种方式，仅有“热备份”的“故障转移群集”没有这些问题。

一、配置主备机

1、?物理连接

将主备数据库按照如图所示连接：

2、?检查SQL?Server?2005数据库

只有SQL?Server?2005?标准版、企业版和开发版才可以建立数据镜像。其他版本即Express只能作为见证服务器。如果实在不清楚什么版本，执行如下语句查看：

select?@@version;

若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用?Transact-SQL?实现此目的，请使用?ALTER?DATABASE?语句：

USE?master;
ALTER?DATABASE?<DatabaeName>?
SET?RECOVERY?FULL;

二、主备实例互通

实现互通可以使用域或证书来实现，考虑实现的简单，以下选取证书的方式实现。注意：实现“主备数据库实例互通”的操作只需要做一次，例如为了将两个SQL?Server?2005的实例中的5个数据库建成镜像关系，则只需要做一次以下操作就可以了；或者这样理解：每一对主备实例（不是数据库）做一次互通。

1、创建证书（主备可并行执行）

--主机执行：

CREATE?MASTER?KEY?ENCRYPTION?BY?PASSWORD?=?'killkill';
CREATE?CERTIFICATE?HOST_A_cert?WITH?SUBJECT?'HOST_A?certificate'?,?
START_DATE?'01/01/2008';

--备机执行：

CREATE?CERTIFICATE?HOST_B_cert?'HOST_B?certificate',51); font-family:Arial; font-size:13.63636302947998px; line-height:26px"> 2、创建连接的端点（主备可并行执行）

CREATE?ENDPOINT?Endpoint_Mirroring?
STATE?=?STARTED?
AS?
TCP?(?LISTENER_PORT=5022?,?LISTENER_IP?=?ALL?)?
FOR?
DATABASE_MIRRORING?
(?AUTHENTICATION?=?CERTIFICATE?HOST_A_cert?,?ENCRYPTION?=?REQUIRED?ALGORITHM?AES?,?ROLE?ALL?);

=?CERTIFICATE?HOST_B_cert?,51); font-family:Arial; font-size:13.63636302947998px; line-height:26px"> 3、备份证书以备建立互联（主备可并行执行）

BACKUP?CERTIFICATE?HOST_A_cert?TO?FILE?'D:SQLBackupHOST_A_cert.cer';

BACKUP?CERTIFICATE?HOST_B_cert?'D:SQLBackupHOST_B_cert.cer';

4、互换证书

将备份到D:SQLBackup的证书进行互换，即HOST_A_cert.cer复制到备机的D:SQLBackup。HOST_B_cert.cer复制到主机的D:SQLBackup

5、添加登陆名、用户（主备可并行执行）

以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束，SQL?Server2005的不定号为SP2）

CREATE?LOGIN?HOST_B_login?WITH?PASSWORD?CREATE?USER?HOST_B_user?FOR?LOGIN?HOST_B_login;
AUTHORIZATION?HOST_B_user?FROM?'D:SQLBackupHOST_B_cert.cer';
GRANT?CONNECT?ON?ENDPOINT::Endpoint_Mirroring?TO?[HOST_B_login];

CREATE?LOGIN?HOST_A_login?USER?HOST_A_user?FOR?LOGIN?HOST_A_login;
AUTHORIZATION?HOST_A_user?'D:SQLBackupHOST_A_cert.cer';
[HOST_A_login];

三、建立镜像关系

以下步骤是针对每个数据库进行的，例如：现有主机中有5个数据库以下过程就要执行5次。

1、?手工同步登录名和密码

在第一章中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"

在主数据库中执行如下语句：

select?sid,name?from?syslogins;

查找出相应的用户名和sid，例如：上述的’myuser’

在备数据库中执行如下语句：

exec?sp_addlogin?
@loginame?'<LoginName>',?
@passwd?'<Password>',0)">@sid?<sid>?;

这里的’LoginName’即主数据库中的登录名，sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

sid?name
0x074477739DCA0E499C29394FFFC4ADE4?cz_account

则建立登录名的SQL语句：

'cz_account',0)">'password',0)">0x074477739DCA0E499C29394FFFC4ADE4;

到此为止可以认为备机数据库的环境已经与主机同步了，还差数据库内的数据未同步。

2、?准备备机数据库

承接上文，该节是描述如何同步主备数据库内的数据。

可以尝试从刚刚使用的全备文件进行还原，在还原数据的时候需要使用选上“with?non?recover”。如图所示：

如果执行成功数据库将会变成这个样子： 3、?建立镜像

由于是实验，没有为服务器配置双网卡，IP地址与图有点不一样，但是原理一样。

DATABASE?shishan?SET?PARTNER?'TCP://10.168.6.45:5022';

--如果主体执行不成功，尝试在备机中执行如下语句：

'TCP://10.168.6.49:5022';

如果执行成功，则主备数据库将会呈现如上图所示的图标。

如果建立失败，提示类似数据库事务日志未同步，则说主备数据库的数据（日志）未同步，为保证主备数据库内的数据一致，应在主数据库中实施一次“事务日志”备份，并还原到备数据库上。备份“事务日志”如图所示：

还原事务日志时需在选项中选择“restore?with?norecovery”，如图所示：

成功还原以后再执行建立镜像的SQL语句。

四、测试操作

1、主备互换

12<DatabaseName>?SET?PARTNER?FAILOVER;
3

2、主服务器Down掉,备机紧急启动并且开始服务

SET?PARTNER?FORCE_SERVICE_ALLOW_DATA_LOSS;
3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行：
3SET?PARTNER?RESUME;?恢复镜像4SET?PARTNER?FAILOVER;?切换主备5

4、原来的主服务器恢复,可以继续工作

--默认情况下，事务安全级别的设置为?FULL，即同步运行模式，而且SQL?Server?2005?标准版只支持同步模式。

--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

SET?PARTNER?SAFETY?FULL;?事务安全,同步模式OFF;?事务不安全异步模式4

[置顶]配置SQLServer2005数据库镜像实践操作记录

2011-03-06?22:1797人阅读评论(0)收藏举报

最近公司要对系统做数据库镜像，因此进行了相关方面的技术试验，把配置过程整理一下。

一、取消主体/镜像关系

1、?取消主体服务器实例

在主体数据库实例master数据库下，执行：?

Alter?database?pqsys_config?set?partner?off

Alter?database?pqsys_history?set?partner?off

2、?取消镜像服务器实例

在镜像数据库实例master数据库下，执行：

待完全解除镜像关系后，对数据库进行恢复

Restore?database?pqsys_config?with?recovery

Restore?database?pqsys_history?with?recovery

3、?删除各个数据库实例下的端点

分别在主体、镜像、鉴证数据库实例master数据库下执行：?

Drop?endpoint?[端点名称]

以上操作，可以解除主体/镜像关系

一、配置主体/镜像数据库实例（同一台服务器）

1、安装三个数据库实例

分别为主体、镜像、见证实例，在主体和镜像数据库实例上创建PQSYS数据库，pqsys_config和pqsys_history。

2、设置主体数据库与镜像数据库的恢复模式为"完整"

USE?master;

DATABASE?PQSYS_CONFIG?

SET?RECOVERY?FULLDATABASE?PQSYS_HISTORY?

3、备份主体数据库实例中的pqsys_config和pqsys_history数据库

BACKUP?DATABASE?PQSYS_CONFIG

DISK?'d:/PC.BAK'?--根据实际需求修改路径

WITH?FORMAT

DATABASE?PQSYS_HISTORY

'd:/PH.BAK'? 4、在镜像服务器上恢复数据库备份，并使用NO?RECOVERY选项

RESTORE?WITH?NORECOVERY? 5、在主体服务器上创建数据库镜像端点，用于伙伴通讯

CREATE?ENDPOINT?DbMirroringEP

AS?TCP?(LISTENER_PORT?=?5022)

FOR?DATABASE_MIRRORING?ROLE?=?PARTNERENCRYPTION?SUPPORTED);

ENDPOINT?DbMirroringEP?STATE?STARTED

6、在镜像服务器上创建数据库镜像端点，用于伙伴通讯

=?5023 7、在见证服务器上创建数据库镜像端点，用于见证通讯

=?5024WITNESS 8、检查端点配置

SELECT?*?sys.database_mirroring_endpoints

9、在镜像服务器上指定伙伴端点

PARTNER?=?N'TCP://BOAT:5022'?--?boat主机名

10、在主体服务器上指定伙伴端点

'TCP://BOAT:5023'? 注意：

运行该步时，可能出现如下错误：“镜像数据库"pqsys_config"?包含的事务日志数据不足，无法保留主体数据库的日志备份链。如果没有从主体数据库进行日志备份或者没有在镜像数据库上还原日志备份，则可能会出现这种情况。”

该问题产生的原因在于在配置期间又对主体数据库实例进行了操作，导致两者日志不一致。出现这种情况需重新对主体数据库进行事务日志备份，再对镜像数据库进行事务日志还原，并使用norecovery选项。

11、在主体服务器上指定见证服务器端点

WITNESS?'TCP://BOAT:5024'? 12、配置数据库镜像事务安全级别

DATABASE?PQSYS_CONFIG?SAFETY?FULL

一、配置主体/镜像数据库实例（主体和镜像处于不同服务器）

主体和镜像位于不同服务器上，实现方式与上面类似，但是由于受到安全的限制，需要配置证书才能指定伙伴关系。

在第二点8步执行完毕后，增加配置证书的部分即可。

配置证书包括两个方面：

1、配置出站连接

（1）、在?master?数据库上，创建数据库主密钥（如果不存在）。若要查看数据库的现有密钥，请使用?sys.symmetric_keys?目录视图。

若要创建数据库主密钥，请使用下面的?Transact-SQL?命令：

MASTER?KEY?ENCRYPTION?BY?PASSWORD?'<1_Strong_Password!>' 使用唯一的强密码，并将其记录到一个安全的位置。

（2）、在?master?数据库中，对服务器实例创建一个用于其数据库镜像出站连接的加密证书。

例如，为?HOST_A?系统创建一个证书。

CERTIFICATE?HOST_A_cert?

SUBJECT?'HOST_A?certificate?for?database?mirroring'Start_DATE?'01/07/2009'EXPIRY_DATE?'07/07/2030';

（3）、确保每个服务器实例上都存在数据库镜像端点。

如果端点不存在，请创建一个端点，该端点使用此证书进行出站连接，并使用此证书的凭据通过其他系统的验证。这是一个服务器范围内的端点，供服务器实例参与的所有镜像会话使用。

(

LISTENER_PORT=5022

LISTENER_IP?ALL

AUTHENTICATION?CERTIFICATE?HOST_A_cert

REQUIRED?ALGORITHM?RC4

如果端点存在，则使用

alter? 为端点配置证书。

（4）、备份证书并将其复制到其他系统。若要在其他系统上配置入站连接，此步骤是必需的。

CERTIFICATE?HOST_A_cert?'d:/HOST_A_cert.cer' （5）、配置完毕第一台数据库实例，再按照上面步骤配置其它数据库服务器实例，脚本如下：

--Create?the?database?Master?Key,?if?needed.

'<Strong_Password_#2>'--?Make?a?certifcate?on?HOST_B?server?instance.

CERTIFICATE?HOST_B_cert?

'HOST_B?certificate?for?database?mirroring''07/07/2030'?--Create?or?Alter?a?mirroring?endpoint?for?the?server?instance?on?HOST_B.

=5023

CERTIFICATE?HOST_B_cert

--Backup?HOST_B?certificate.

CERTIFICATE?HOST_B_cert?'d:/HOST_B_cert.cer' GO?

2、配置入站连接

（1）、创建另一个系统的登录名。

下面的示例在?HOST_A?上的服务器实例的?master?数据库中为系统?HOST_B?创建登录名，在此示例中，登录名为?HOST_B_login。请用自己的密码替换示例密码。

下面的示例为上述步骤中创建的登录名创建了一个用户?HOST_B_user。

AUTHORIZATION?HOST_B_user

'D:/HOST_B_cert.cer'

（4）、授予对远程镜像端点的登录名的?CONNECT?权限。

例如，若要将对?HOST_A?的权限授予?HOST_B?上的远程服务器实例，以连接到其本地登录名，即连接到?HOST_B_login，请使用以下?Transact-SQL?语句：

ON?ENDPOINT::?DbMirroringEP?TO?[HOST_B_login] （5）、在HOST_A上为见证服务器实例执行相同的入站步骤，重复上面1-4。

（6）、现在需要在?HOST_B?上为?HOST_A?执行相同的入站步骤。下面示例部分中的入站部分说明了这些步骤。

--On?HOST_B,?create?a?login?for?HOST_A.

AUTHORIZATION?HOST_A_user

'D:/HOST_A_cert.cer'--Grant?CONNECT?permission?for?the?server?instance?on?HOST_A.

TO?HOST_A_login

（7）、在HOST_B上为见证服务器实例执行上述入站操作。

（8）、在见证服务器实例上为HOST_A和HOST_B配置入站操作。

（9）、配置完毕后，再继续执行第二部分以后的步骤

四、设置SQL?AGENT计划

1、使用SQL?AGENT的原因

采取主体/镜像数据库方式实现数据库双机热备功能必须将数据库的恢复模式设置为完整模式，在完整模式下，对数据库所作的每一个数据操作都会写入到数据库的事务日志文件中，这样就导致在需要频繁操作数据库的情况下，事务日志文件增长的很快，在磁盘资源有限的情况下必须考虑限制事务日志的方法。

可以采取定期对事务日志文件进行备份方式来实现，而通过SQL?SERVER?2005自带的AGENT功能来实现事务日志的定期备份。

2、设置SQL?AGENT计划

打开MSSMS（Microsoft?SQL?Server?Management?Studio），展开SQL?SERVER?代理，并右键“作业”，选择“新建作业”，弹出如图4-1对话框，配置作业。

选中“常规”，填入基本信息；

选中“步骤”，点击对话框下方的“新建”按钮，输入“常规”选项卡上的信息，包括步骤名称、类型、数据库和命令，然后点击确定。如图4-2所示，命令内容是一组SQL脚本，以PQSYS_CONFIG数据库为例：

declare?@role?int

set?@role?=?(select?mirroring_role?from?sys.database_mirroring?

where?database_id?=?

(select?database_id?from?sys.databases?where?name?=?N'PQSYS_CONFIG'))

if?@role?=?1?--对于主体数据库才执行备份操作

begin

BACKUP?LOG?[PQSYS_CONFIG]?TO?DISK?=?N'E:/wenjian/PC.BAK'?

WITH?NOFORMAT,?INIT,?NAME?=?N'PC?',?

SKIP,?NOREWIND,?NOUNLOAD,?STATS?=?10

end

选中“计划”，点击对话框下方的“新建”按钮，输入计划执行的“名称”、“计划类型”以及执行时间设置等，设置完毕后点击确定。?

点击“确定”按钮，完成SQL?AGENT设置。

针对需要进行定期备份的其他数据库设置SQL?代理。

五、注意事项

1、数据库版本要求

必须使用SQL?SERVER?2005标准版或以上版本（企业版、开发版、评估版）；

必须在数据库中安装SP1或以上补丁（目前一般使用SP2,版本号为3042）；

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!