19. SQL -- 数据库复制

数据库复制

• 概述

SQL Server 2008 数据库复制是通过发布/订阅的机制进行多台服务器之间的数据同步，我们把它用于数据库的同步备份。这里的同步备份指的是备份服务器与主服务器进行实时数据同步，正常情况下只使用主数据库服务器，备份服务器只在主服务器出现故障时投入使用。它是一种优于文件备份的数据库备份解决方案。

在选择数据库同步备份解决方案时，有两种方式：

SQL Server 2008 的数据库镜像

SQL Server 2008 数据库复制

数据库镜像的优点是系统能自动发现主服务器故障，并且自动切换至镜像服务器。

但缺点是配置复杂，镜像数据库中的数据不可见（在SQL Server Management Studio中，只能看到镜像数据库处于镜像状态，无法进行任何数据库操作，最简单的查询也不行。想眼见为实，看看镜像数据库中的数据是否正确都不行。只有将镜像数据库切换主数据库

才可见）。

• 复制的概念

复制是将一组数据从一个数据源拷贝到多个数据源的技术，是将一份数据发布到多个存储站点上的有效方式。使用复制技术，用户可以将一份数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。复制技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性。

复制的基本元素

出版服务器（也叫发布服务器—原服务器）Publisher

订阅服务器（目标服务器） Subscriber

分发服务器（负责从原服务器中取数据、事务日志，并发送到目标服务器的服务器）Distributor

复制的用途

实现多个数据库间的单向、双向同步，从而可实现MSSQL 的集群，优化服务器集群的架构（例如：通过复制来实现——负载均衡，读写分离，系统按业务模块分库等）。

复制的类型：

快照复制（不断的更新所保存的快照内的内容，并将该快照全部复制到订阅服务器）。

事务复制（普通事务复制，对等事务复制，双向事务复制），仅生成更新一次快照文件，之后将通过事务的方式更新订阅服务器，而不传递快照数据。

合并复制，仅生成更新一次快照文件，之后定期合并数据。

复制的前提：使用数据库快照，无论是任何类型的复制，均必须使用数据库快照

• 复制功能中的代理类型：

快照代理——应用于快照复制：

快照代理在分发者上创建并存储快照文件，任务是：在分发数据库中准备已发布表的架构和初始数据以及其他对象、存储快照文件并记录出版数据库和订阅服务器之间的同步信息，快照代理运行在分发者服务器上，并与出版者相连接。每一个出版物都有自己的快照代理。与各种类型的复制一起使用。

日志阅读代理——应用于事务复制

日志读取器代理与事务性复制一起使用。它将发布服务器上的事务日志中标记为复制的事务移至分发数据库中。使用事务性复制发布的每个数据库都有自己的日志读取器代理，该代理运行于分发服务器上并与发布服务器连接（分发服务器与发布服务器可以是同一台计算机）。

分发代理——应用于快照复制，事务复制

分发代理与快照复制和事务性复制一起使用。它将初始快照应用于订阅服务器，并将分发数据库中保存的事务移至订阅服务器。分发代理既可以运行于分发服务器（对于推送订阅），也可运行于订阅服务器（对于请求订阅）。

合并代理——应用于合并复制

合并代理与合并复制一起使用。它将初始快照应用于订阅服务器，并移动和协调所发生的增量数据更改。每个合并订阅都有自己的合并代理，该代理同时连接到发布服务器和订阅服务器并对它们进行更新。合并代理既可以运行于分发服务器（对于推送订阅），也可以运行于订阅服务器（对于请求订阅）。默认情况下，合并代理将订阅服务器上的更

改上载到发布服务器，然后将发布服务器上的更改下载到订阅服务器。

队列阅读代理——应用于事务复制

队列读取器代理与包含排队更新选项的事务性复制一起使用。该代理运行于分发服务器，并将订阅服务器上所做更改移回至发布服务器。与分发代理和合并代理不同，只有一个队列读取器代理的实例为给定分发数据库的所有发布服务器和发布提供服务。

• 复制的机制：请求订阅，推送订阅

请求订阅：

将上面提到的代理运行于订阅服务器内，由订阅服务器向发布服务器发送复制请求（这个过程使用日志阅读代理），并将日志或者数据发送到订阅服务器内（这个过程使用分发代理）。

推送订阅：

将上面提到的代理运行于分发服务器（或者发布服务器），由分发（发布服务器）连续的读取数据或日志（这个过程使用日志阅读代理），并将日志或者数据发送到订阅服务器（这个过程使用分发代理）。

注：请求订阅与推送订阅各有优点：推送订阅使集中管理订阅同步变得更加简单。而请求订阅降低了分发服务器数据处理工作的开销，并使每个订阅服务器管理其订阅的同步。所以如果订阅服务器过多，请适用请求订阅的机制。

• 各种复制技术概述

快照复制：快照复制传递的是数据

快照复制原理：发布服务器按预定的时间间隔向订阅服务器发送已发布数据的快照。

快照复制实施步骤：

（1）、先在发布服务器上建立针对全部/具体表，视图，存储过程，函数的发布本地发布。

（2）、在订阅服务器内，右击《本地订阅》—》新建订阅。

（3）、后面也就不叙述了

快照复制的优点：

（1）、由于不是实时同步，所以可以大大提高订阅服务器与发布服务器的性能

（2）、在快照复制中，由于相邻两次复制数据的传递间隔时间较长，则允许订购者与出版者不必保持永久连接。

快照复制的缺点：

（1）、不是实时同步，会导致两台服务器见的数据库数据不一致，适合变更不频繁的业务。

（2）、如订阅服务器中有10G 的数据，而在一个快照复制的周期内，只有1M 的数据发生了更改。此时发生快照复制的话，数据库系统会将10G 的数据都传送到订阅服务器上。所以适合小数据量的同步。

（3）、快照复制是单相的，无法实现两台机器的双向复制。

快照复制的适用范围：

（1）、适合小数据量的同步。

（2）、适合不需要实时数据的业务逻辑。（如：报表等）

对等复制

对等复制设计的初衷：是用来针对在不同时间, 只有对应时区有业务数据的情况，如果你是多个地方同时有数据发生变化或增加，那么一定会出现冲突。

注：MSSQL2005 对等复制无法解决多台数据库服务器并发同步的问题，而在MSSQL2008 中虽然加入了对等复制的冲突检测功能，依然无法避免冲突的产生：以下情况均会产生冲突：

（

· 插入-插入

每个表中所有参与对等复制的行都使用主键值进行唯一标识。在将具有相同键值的行插入到多个节点时，会发生插入-插入冲突。

· 更新-更新

在多个节点上更新了同一行时发生。

· 插入-更新

在以下情况下发生：在一个节点上更新了某行，但是在另一个节点上删除了该行，然后又重新插入该行。

· 插入-删除

在以下情况下发生：在一个节点上删除了某行，但是在另一个节点上删除了该行，然后又重新插入该行。

· 更新-删除

在以下情况下发生：在一个节点上更新了某行，但是在另一个节点上删除了该行。

· 删除-删除

在多个节点上删除了同一行时发生。

）

• 事务复制：

在订阅服务器收到已发布数据的初始快照后，发布服务器将事务流式传输到订阅服务器。

（1）、普通事务复制：

普通事务复制的优点：

只传递事务日志文件，节省性能。

可连续实时的更新，也可设定具体的时间进行更新。

普通事务复制的缺点：

单向同步，而非双向同步。

连续传递，增加了数据库与网络的负载。

适用范围：适用快照复制的业务均适用于普通事务复制

（2）、对等事务复制：

对等事务复制实施步骤：

http://msdn.microsoft.com/zh-cn/library/ms152536.aspx

对等事务复制的优点：

实时同步，千行插入同步时间在1 秒以内（100m 独享带宽）。

可通过对等复制来实现 多系统的分库 以及 数据库的双向读写分离。

对等事务复制的缺点：

对等复制是双向且连续的，所以会增加N 台服务器的性能开销与带宽。

出现数据间的冲突（不一致），因而需要一个合理的解决冲突的办法。

适用范围：

在未采购任何负载均衡硬件设备的前提下，可适用对等复制实现简单的负载均衡集群架构。

如何为对等事务复制配置冲突检测：

http://msdn.microsoft.com/zh-cn/library/bb934228.aspx

• 双向复制：

双向事务复制是一种特定的事务复制拓扑，它允许两台服务器相互交换更改：每台服务器均发布数据，然后从另一台服务器订阅包含相同数据的发布对等事务复制也支持也拓扑，但双向复制可提高性能。

合并复制

例：在某一大型企业的分销系统中，销售经理或一些销售骨干人员经常要外出处理业务，将签订的合同通过手边的笔记本电脑传递到总部销售信息数据库。在这一例子中有两个主要的特征；任何销售经理和销售骨干都可以修改销售信息数据库；只有在进行数据传递时才将源数据库与目标数据库相连。在SQL Server 中，合并复制为这一情况提供了较好的

解决方案。

合并复制的一种，允许站点对已复制数据进行匿名更改，并在晚些时候合并更改和根据需要解决冲突。

（1）、合并复制原理：

在订阅服务器收到已发布数据的初始快照后，发布服务器和订阅服务器可以独立更新已发布数据。更改会定期合并。Microsoft SQLServer Compact Edition 只能订阅合并发布。

（2）、合并复制实施步骤：

http://msdn.microsoft.com/zh-cn/library/ms365205.aspx

（3）、合并复制的优点：

解决在客户端应用程序上，向服务器传递数据、定时更新数据提供解决方案。

双向同步。

（4）、合并复制的缺点：

不是实时同步，而是定时、用户自主更新。

数据量过大的时候，需要使用 bulk insert。

所有合并项目都必须包含带有唯一索引的 uniqueidentifier 列和 ROWGUIDCOL属性。SQL Server 将在生成第一个快照时向没有 uniqueidentifier 列的已发布表添加该列。

（5）、合并复制的适用范围：

客户端应用程序需要与数据库服务器进行交互的比较适合构建此类数据库架构

• 创建数据库复制

测试环境：限于环境要求，仅提供一台Windows Server 2008 R2 + SQL Server 2008 服务器，在同一台服务器中的2 个数据库间进行复制。一个主数据库服务器DB1，一台备份数据库服务器DB2。

复制原理：我们采用的是基于快照的事务复制。主数据库服务器生成快照，备份库服务器读取并加载该快照，然后不停地从主数据库服务器复制事务日志。

图片来自SQL Server 联机丛书

配置步骤：

A、创建2 个实验数据库，并在DB1 中创建一张测试表格

createdatabase db1

go

createdatabase db2

go

use db1

go

CREATETABLE customers

(

Customeridvarchar(5) NOT NULL,

CompanyNamevarchar(50) NOT NULL,

ContactNamevarchar(30) NULL,

CONSTRAINT[PK_customers] PRIMARY KEY CLUSTERED (Customerid)

)

B、启动SQL 代理服务

SQLSERVER 配置管理器→ SQL SERVER 代理 → 右键 → 启动

C、创建发布

--1、在SERVER 上建立一个用于存放快照的文件夹并共享，共享权限配置为everyone完全控制权限，NTFS 权限同样配置为everyone 完全控制权限

--2、选择复制 → 本地发布 → 新建发布

出现一个向导，先选择分发服务器，然后配置SQL SERVER 代理启动选项，之后选择快照保存目录，选择之前创建好的快照存储文件夹。

选择发布数据库，这里选择DB1，然后选择发布类型，这里选择事务发布

选择发表的项目（表或视图），这里选择创建好的测试表customers

点击一下步，进入筛选表行窗口，这里不需要筛选，直接下一步。进入快照代理配置，这里选择立即创建快照保持可用状态，以初始化订阅

点击下一步，进入代理安全性配置

点击安全设置，进入相应的帐户设置

一个是设置运行Snapshot Agent 的Windows 帐户，我们这里选择与SQL ServerAgent 同样的帐户。一个是设置连接发布服务器的SQL 帐户，我们这里就用主数据库服务器的sa 帐户。

点击一下步，选择创建发布，并生成包含创建发布的步骤的脚本文件

点击下一步，保存脚本文件，这里我们保存到桌面上。

点击一下步，完成向导，并为该发布起一个名字，并完成向导。

点击完成，出下如下窗口：

执行脚本如下：

/******正在编写复制配置的脚本。脚本日期: 2011/9/20 16:48:51 ******/

/******请注意: 出于安全原因，所有密码参数均使用NULL 或空字符串代替。******/

/******正在将服务器作为分发服务器安装。脚本日期: 2011/9/20 16:48:51 ******/

usemaster

exec sp_adddistributor

@distributor= N'SQL2008',

@password= N''

GO

exec sp_adddistributiondb

@database= N'distribution',

@data_folder= N'C:\Program Files\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\Data',

@log_folder= N'C:\Program Files\Microsoft SQLServer\MSSQL10.MSSQLSERVER\MSSQL\Data',

@log_file_size= 2,

@min_distretention= 0,

@max_distretention= 72,

@history_retention= 48,

@security_mode= 1

GO

use [distribution]

if (not exists

(select *

from sysobjects

wherename = 'UIProperties' and type = 'U '))

createtable UIProperties(id int)

if (exists

(select *

from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null,

null)))

EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\ReplData', 'user', dbo, 'table',

'UIProperties'

else

EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\ReplData', 'user', dbo, 'table', 'UIProperties'

GO

exec sp_adddistpublisher

@publisher= N'SQL2008',

@distribution_db= N'distribution',

@security_mode= 0,

@login= N'sa',

@password= N'',

@working_directory= N'C:\ReplData',

@trusted= N'false',

@thirdparty_flag= 0,

@publisher_type= N'MSSQLSERVER'

GO

use [db1]

exec sp_replicationdboption

@dbname= N'db1',

@optname= N'publish',

@value= N'true'

GO

--添加事务发布

use [db1]

exec sp_addpublication

@publication= N'publication_db1',

@description= N'来自发布服务器―SQL2008‖的数据库―db1‖的事务发布。',

@sync_method= N'concurrent',

@retention= 0,

@allow_push= N'true',

@allow_pull= N'true',

@allow_anonymous= N'true',

@enabled_for_internet= N'false',

@snapshot_in_defaultfolder= N'true',

@compress_snapshot= N'false',

@ftp_port= 21,

@ftp_login= N'anonymous',

@allow_subscription_copy= N'false',

@add_to_active_directory= N'false',

@repl_freq= N'continuous',

@status= N'active',

@independent_agent= N'true',

@immediate_sync= N'true',

@allow_sync_tran= N'false',

@autogen_sync_procs= N'false',

@allow_queued_tran= N'false',

@allow_dts= N'false',

@replicate_ddl= 1,

@allow_initialize_from_backup= N'false',

@enabled_for_p2p= N'false',

@enabled_for_het_sub= N'false'

GO

exec sp_addpublication_snapshot

@publication= N'publication_db1',

@frequency_type= 1,

@frequency_interval= 0,

@frequency_relative_interval= 0,

@frequency_recurrence_factor= 0,

@frequency_subday= 0,

@frequency_subday_interval= 0,

@active_start_time_of_day= 0,

@active_end_time_of_day= 235959,

@active_start_date= 0,

@active_end_date= 0,

@job_login= null,

@job_password= null,

@publisher_security_mode= 0,

@publisher_login= N'sa',

@publisher_password= N''

use [db1]

exec sp_addarticle

@publication= N'publication_db1',

@article= N'customers',

@source_owner= N'dbo',

@source_object= N'customers',

@type= N'logbased',

@description= null,

@creation_script= null,

@pre_creation_cmd= N'drop',

@schema_option= 0x000000000803509F,

@identityrangemanagementoption= N'manual',

@destination_table= N'customers',

@destination_owner= N'dbo',

@vertical_partition= N'false',

@ins_cmd= N'CALL sp_MSins_dbocustomers',

@del_cmd= N'CALL sp_MSdel_dbocustomers',

@upd_cmd= N'SCALL sp_MSupd_dbocustomers'

GO

注：此处若之前配置过一次，但由于其它原因，未创建成功，这时会提示‘SQL2008’已配置成为分发服务器，若需创建新的分发服务器，需卸载现有的分发服务器或使用存储过程sp_dropdistpublisher 禁用或关闭分发向导解决办法：

使用存储过程：

sp_dropdistributor--删除分发服务器

sp_dropdistributiondb--删除分发数据库

D、创建订阅

选择复制 → 本地订阅 → 新建订阅

首先选择发布服务器

点击下一步，进入分发代理位置，这里选择请求订阅

点击下一步，配置订阅服务器及订阅数据库，这里我们选择DB2

点击下一步，进入分发代理安全性向导，

点击红框内的按钮，进入设置窗口,配置帐户信息

点击下下一步，配置同步计划，这里配置连续运行

点击下一步，进行初始化订阅，这里选择立即

点击下一步，选择创建向导，并生成创建订阅步骤的脚本文件

点击下一步，选择脚本文件保存的路径

点击下一步，并点击完成，如果没问题，如现如下窗口

脚本文件如下：

-----------------开始: 要在发布服务器―SQL2008‖上运行的脚本-----------------

use [db1]

exec sp_addsubscription

@publication= N'publication_db1',

@subscriber= N'SQL2008',

@destination_db= N'db2',

@sync_type= N'Automatic',

@subscription_type= N'pull',

@update_mode= N'read only'

GO

-----------------结束: 要在发布服务器―SQL2008‖上运行的脚本-----------------

-----------------开始: 要在订阅服务器―SQL2008‖上运行的脚本-----------------

use [db2]

exec sp_addpullsubscription

@publisher= N'SQL2008',

@publication= N'publication_db1',

@publisher_db= N'db1',

@independent_agent= N'True',

@subscription_type= N'pull',

@description= N'',

@update_mode= N'read only',

@immediate_sync= 1

exec sp_addpullsubscription_agent

@publisher= N'SQL2008',

@publisher_db= N'db1',

@publication= N'publication_db1',

@distributor= N'SQL2008',

@distributor_security_mode= 0,

@distributor_login= N'sa',

@distributor_password= null,

@enabled_for_syncmgr= N'False',

@frequency_type= 64,

@frequency_interval= 0,

@frequency_relative_interval= 0,

@frequency_recurrence_factor= 0,

@frequency_subday= 0,

@frequency_subday_interval= 0,

@active_start_time_of_day= 0,

@active_end_time_of_day= 235959,

@active_start_date= 20110920,

@active_end_date= 99991231,

@alt_snapshot_folder= N'',

@working_directory= N'',

@use_ftp= N'False',

@job_login= null,

@job_password= null,

@publication_type= 0

GO

-----------------结束: 要在订阅服务器―SQL2008‖上运行的脚本-----------------

E、测试：

查看复制结果：可以看到，DB2 数据库中从DB1 数据库中复制了一张表格customers

--首先查询2 个数据库中表格内容

use db1

go

select* from customers

use db2

go

select* from customers

返回：

--在DB1 数据库中的customers 表中插入一条数据，查看2 个数据库表格类容

use db1

go

insertinto customers

values('bcd','guangdong company','bob')

use db1

go

select* from customers

use db2

go

select* from customers

返回：

可以看到，DB1 中的数据已经实时的复制到了DB2 中

• 创建数据库镜像

本DEMO 来源于：

http://www.cnblogs.com/killkill/archive/2008/05/23/1205792.html

简介：

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

要进行数据库镜像所需的最小需求包括了两个不同的SQL Server 运行环境。主服务器被称为“主机”，第二个服务器被称作“备机”。主机数据库就是你实际用着的数据库，镜像数据库就是你的数据库的备用拷贝。当事务写入你的基本服务器的时候，他们也同样被传送到并写入你的镜像数据库中。除了基本和镜像之外，你还可以引入另一个可选的组件，名为“见证”。见证服务器是第三个SQL Server2005 运行实例，它是在判断什么时候进行错误恢复的时候，用于基本和镜像之间内部交流。只有当你想实现自动错误恢复的时候用到这个选项。它实现了2 比1 投票的能力，当我的一个组件不可达，并因此需要进行错误恢复的时候。见证服务器只有在你想实现自动错误恢复的时候才需要用到。

优点：

从表中可以看出 “热备份”比“冷备份”的可用性更高，恢复更快，更适合现时的实际情况。如果不从成本考虑的话，“热备份”中的“故障转移群集”的可用性是最高的，但是故障转移群集需要借助磁盘阵列而且建设本身复杂性较高。数据库镜像的建立并没有太多的硬件要求，最起码没有像“故障转移群集”需要共享存储这么高的要求.

缺点：

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

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

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

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

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

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

DEMO：创建数据库镜像

(1)、配置主备机

--1、 物理连接

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

--2、 检查SQL Server2005 数据库

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

1 select @@version;

若要对此数据库进行数据库镜像，必须将它更改为使用完整恢复模式。若要用

Transact-SQL 实现此目的，请使用 ALTERDATABASE 语句：

1 USE master;

2 ALTER DATABASE<DatabaeName>

3 SET RECOVERY FULL;

4

（2）、主备实例互通

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

每一对主备实例（不是数据库）做一次互通。

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

--主机执行：

1 USE master;

2 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';

3 CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_Acertificate' ,

4 START_DATE = '01/01/2008';

5

--备机执行：

1 USE master;

2 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'killkill';

3 CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_Bcertificate',

4 START_DATE = '01/01/2008';

5

--2、创建连接的端点（主备可并行执行）

--主机执行：

1 CREATE ENDPOINT Endpoint_Mirroring

2 STATE = STARTED

3 AS

4 TCP (LISTENER_PORT=5022 , LISTENER_IP = ALL )

5 FOR

6 DATABASE_MIRRORING

7 (AUTHENTICATION = CERTIFICATEHOST_A_cert , ENCRYPTION = REQUIREDALGORIT

HM AES , ROLE = ALL );

8

--备机执行：

1 CREATE ENDPOINT Endpoint_Mirroring

2 STATE = STARTED

3 AS

4 TCP (LISTENER_PORT=5022 , LISTENER_IP = ALL )

5 FOR

6 DATABASE_MIRRORING

7 (AUTHENTICATION = CERTIFICATEHOST_B_cert , ENCRYPTION = REQUIREDALGORIT

HM AES , ROLE = ALL );

8

--3、备份证书以备建立互联（主备可并行执行）

--主机执行：

1 BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';

--备机执行：

1 BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';

--4、互换证书

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

D:\SQLBackup\。HOST_B_cert.cer复制到主机的D:\SQLBackup\

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

以下操作只能通过命令行运行，通过图形界面无法完成。（截至文档编写结束，SQL

Server2005 的不定号为SP2）

--主机执行：

1 CREATE LOGIN HOST_B_login WITH PASSWORD = 'killkill';

2 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

3 CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQ

LBackup\HOST_B_cert.cer';

4 GRANT CONNECT ON ENDPOINT::Endpoint_MirroringTO [HOST_B_login];

5

--备机执行：

1 CREATE LOGIN HOST_A_login WITH PASSWORD = 'killkill';

2 CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

3 CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\SQ

LBackup\HOST_A_cert.cer';

4 GRANT CONNECT ON ENDPOINT::Endpoint_MirroringTO [HOST_A_login];

5

（3）、建立镜像关系

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

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

在第一章中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"在主数据库中执行如下语句：

1 USE master;

2 select sid,name from syslogins;

3

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

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

1 USE master;

2 exec sp_addlogin

3 @loginame = '<LoginName>',

4 @passwd = '<Password>',

5 @sid = <sid> ;

6

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

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

1 sid name

2

3---------------------------------- -----------------

4

5 0x074477739DCA0E499C29394FFFC4ADE4 cz_account

6

7

则建立登录名的SQL 语句：

1 USE master;

2 exec sp_addlogin

3 @loginame = 'cz_account',

4 @passwd = 'password',

5 @sid = 0x074477739DCA0E499C29394FFFC4ADE4;

6

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

--2、 准备备机数据库

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

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

如果执行成功数据库将会变成这个样子：

--3、 建立镜像

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

--主机执行：

1 ALTER DATABASEshishan SET PARTNER = 'TCP://10.168.6.45:5022';

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

1 ALTER DATABASEshishan SET PARTNER = 'TCP://10.168.6.49:5022';

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

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

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

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

(4)、测试操作

--1、主备互换

--主机执行：

1 USE master;

2 ALTER DATABASE<DatabaseName> SET PARTNERFAILOVER;

3

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

--备机执行：

1 USE master;

2 ALTER DATABASE<DatabaseName> SET PARTNERFORCE_SERVICE_ALLOW_DATA_LOS

S;

3

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

1 --备机执行：

2 USE master;

3 ALTER DATABASE<DatabaseName> SET PARTNERRESUME; --恢复镜像

4 ALTER DATABASE<DatabaseName> SET PARTNERFAILOVER; --切换主备

5

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

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

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

1 USE master;

2 ALTER DATABASE<DatabaseName> SET PARTNERSAFETY FULL; --事务安全,同步模式

3 ALTER DATABASE<DatabaseName> SET PARTNERSAFETY OFF; --事务不安全,异步模式

10、监视SQL SER__