serverSQL Server 2000之日志传送功能 - 设定（1）
日志传送功能可自动复制数据库的交易日志文件，并回存到备援服务器 (standby server) 的另外一个数据库。因此可大幅提高SQL Server数据库之可用性。因为备援数据库完整地接收来源数据库的异动情况，所以它就是一份来源数据库的复本 - 差别仅在于资料复制与加载过程所产生的时间差。然而，当主要服务器停摆时，您就可以将备援服务器更改为新的主要服务器。如果原来的主要服务器可重新上线使用，那么您可以将其设定为新的备援服务器 - 事实上就是对调两台服务器的角色。
在SQL Server 2000企业版或开发版之中，Microsoft在Enterprise Manager内提供了一项日志传送(Log Shipping)的功能 - 为数据库维护计划精灵的其中一部份。在使用之前的SQL Server时，您需要自行建立日志传送系统。
设定日志传送
主要服务器(primary server) 即是实际处理资料的正式服务器；此服务器内拥有来源数据库。次要服务器(secondary server)上存放目的数据库，用来复制与回存来源数据库的交易日志文件。监控服务器(monitor server)用来监控主要服务器与次要服务器。与SQL Server 7.0不同的是(SQL Server 7.0是在次要服务器上监控日志传送动作)，SQL Server 2000使用Enterprise Manager的日志传送监控工具来监控每一组传送中的日志资料。Microsoft建议您在另外一台监控用服务器安装这个工具程序。
您可以利用Enterprise Manager的数据库维护计划精灵设定SQL Server 2000的日志传送。但是在您激活精灵之前，您必须先进行某些准备工作。一开始请先遵循下列步骤：
1.决定一组要设定日志传送的服务器(即日志传送过程之中，主要服务器与次要服务器为何)。
2. 选择一台监控服务器。最好不同于主要服务器或次要服务器。
3. 设定所有服务器之安全性。您用来设定日志传送的Windows帐号必须拥有所有服务器上SQL Server系统管理者(sa)的权限。
4. 在主要/次要服务器上建立分享资料夹。首先，将来源数据库交易日志文件所在的目录设定为分享目录。接着在次要服务器上，将您打算回存交易日志文件的目录也分享出来。为了清楚辨别各分享目录，请在分享名称内注明服务器与数据库之名称。如果分享目录名称已存在，您可能需要从分享目录中删除或是搬移其它档案，特别是旧的日志备份文件。然后再将这些分享目录的权限开放给每一台服务器上SQL Agent所使用的Windows帐号。
5. 决定如何建立并初始化目的地数据库。您可以在日志传送设定过程就先建立与初始同步化目的地数据库，否则您必须手动进行初始数据库之回存动作。
6. 在Enterprise Manager注册此三台服务器(即主要、次要与监控服务器)。
在您完成这些准备动作时，您就可以准备激活数据库维护计划精灵来设定日志传送。您可以先检视日志传送过程的五个连续步骤，如图1所示：
图1：SQL Server 2000日志传送的设定步骤。
前两个为选择性(optional)步骤。如果您尚未同步化来源与目的数据库，则步骤1会为您先备份来源数据库，然后执行同步化动作。在步骤2时，精灵会将备份文件复制到次要服务器，并回存到目的地数据库。
精灵一定会执行其余三项步骤。在步骤3时，精灵将在主要服务器上建立一个SQL Agent工作(job)。此工作将会周期性地把交易日志文件内容备份到磁盘档案内。精灵也会在次要服务器上建立一个传送日志的数据库维护计划；此计画包含两个SQL Agent工作：一个是将交易日志文件复制到次要服务器(步骤4)，另一个则是将交易日志文件回存到目的数据库(步骤5)。这些步骤将建立一组日志传送服务器(互相有日志传送关系的两个数据库)。如果您想要额外提供容错功能或是设定一台报表服务器，那么您可以将主要服务器与另外一台次要服务器组合在一起，再设定一组日志传送配对服务器。
准备工作
1. 准备 Primary Server (以下为Ztao-1) 及 Secondary Server (以下为IntronTest)
2. 将要作 Log Shipping 的数据库(以下为IntronERP)之还原模型(Recovery Model)设定为完整(FULL)。
3. 将两台计算机的SQL Server服务账号加入Administrator群组
4. 建立Primary Server 备份Log的数据夹
a. 建立C:\Logfile，以存放Primary Server数据库Transaction Log的备份
b. 将C:\Logfile作数据分享，分享目录的权限开放给SQL Agent所使用的Windows帐号。
5. 建立 Secondary Server 还原的数据夹(在Secondary Server)
a. 建立C:\Shippedlog数据夹以存放从Primary Server传送过来的Transaction log 的备份
b. 建立 C:\Logfile数据夹，当角色交换后，可存放新Primary Server的数据库Transaction Log
c. 将C:\Logfile数据夹作资源共享，分享目录的权限开放给SQL Agent所使用的Windows帐号。
6. 在Primary Server中，新增 Secondary Server的注册信息
if exists(
select * from sysobjects
where name='pr_backup_db' and xtype='p'
)
begin
drop proc pr_backup_db
end
go
/*备份数据库*/
create proc pr_backup_db
flag varchar(10) out,
backup_db_name varchar(128),
filename varchar(1000) --路径＋文件名字
as
declare sql nvarchar(4000),par nvarchar(1000)
select par='filename varchar(1000)'
select sql='BACKUP DATABASE '+backup_db_name+' to disk=filename with init'
execute sp_executesql sql,par,filename
select flag='ok'
go
if exists(
select * from sysobjects
where name='fn_GetFilePath' and xtype='fn'
)
begin
drop function fn_GetFilePath
end
go
/*创建函数,得到文件得路径*/
create function fn_GetFilePath(filename nvarchar(260))
returns nvarchar(260)
as
begin
declare file_path nvarchar(260)
declare filename_reverse nvarchar(260)
select filename_reverse=reverse(filename)
select file_path=substring(filename,1,len(filename)+1-charindex('\',filename_reverse))
return file_path
end
go
if exists(
select * from sysobjects
where name='pr_restore_db' and xtype='p'
)
begin
drop proc pr_restore_db
end
go
create proc pr_restore_db /*恢复数据库*/
flag varchar(20) out, /*过程运行的状态标志,是输入参数*/
restore_db_name nvarchar(128), /*要恢复的数据名字*/
filename nvarchar(260) /*备份文件存放的路径+备份文件名字*/
as
declare proc_result tinyint /*返回系统存储过程运行结果*/
declare loop_time smallint /*循环次数*/
declare max_ids smallint /*tem表的ids列最大数*/
declare file_bak_path nvarchar(260) /*原数据库存放路径*/
declare flag_file bit /*文件存放标志*/
declare master_path nvarchar(260) /*数据库master文件路径*/
declare sql nvarchar(4000),par nvarchar(1000)
declare sql_sub nvarchar(4000)
declare sql_cmd nvarchar(4000)
/*
判断参数filename文件格式合法性，以防止用户输入类似d: 或者 c:\a\ 等非法文件名
参数filename里面必须有'\'并且不以'\'结尾
*/
if right(filename,1)<>'\' and charindex('\',filename)<>0
begin
select sql_cmd='dir '+filename
EXEC proc_result = master.. sql_cmd,no_output
IF (proc_result<>0) /*系统存储过程返回代码值:0(成功）或1（失败）*/
begin
select flag='not exist' /*备份文件不存在*/
return /*退出过程*/
end
/*创建临时表,保存由备份集内包含的数据库和日志文件列表组成的结果集*/
create table #tem(
LogicalName nvarchar(128), /*文件的逻辑名称*/
PhysicalName nvarchar(260) , /*文件的物理名称或操作系统名称*/
Type char(1), /*数据文件 (D) 或日志文件 (L)*/
FileGroupName nvarchar(128), /*包含文件的文件组名称*/
[Size] numeric(20,0), /*当前大小（以字节为单位）*/
[MaxSize] numeric(20,0) /*允许的最大大小（以字节为单位）*/
)
/*
创建表变量，表结构与临时表基本一样
就是多了两列,
列ids（自增编号列）,
列file_path,存放文件的路径
*/
declare tem table(
ids smallint identity, /*自增编号列*/
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
File_path nvarchar(260),
Type char(1),
FileGroupName nvarchar(128)
)
insert into #tem
execute('restore filelistonly from disk='''+filename+'''')
/*将临时表导入表变量中,并且计算出相应得路径*/
insert into tem(LogicalName,PhysicalName,File_path,Type,FileGroupName)
select LogicalName,PhysicalName,dbo.fn_GetFilePath(PhysicalName),Type,FileGroupName
from #tem
if rowcount>0
begin
drop table #tem
end
select loop_time=1
select max_ids=max(ids) /*tem表的ids列最大数*/
from tem
while loop_time<=max_ids
begin
select file_bak_path=file_path
from tem where ids=loop_time
select sql_cmd='dir '+file_bak_path
EXEC proc_result = master.. sql_cmd,no_output
/*系统存储过程返回代码值:0(成功）或1（失败）*/
IF (proc_result<>0)
select loop_time=loop_time+1
else
BREAK /*没有找到备份前数据文件原有存放路径，退出循环*/
end
select master_path=''
if loop_time>max_ids
select flag_file=1 /*备份前数据文件原有存放路径存在*/
else
begin
select flag_file=0 /*备份前数据文件原有存放路径不存在*/
select master_path=dbo.fn_GetFilePath(filename)
from master..sysdatabases where name='master'
end
select sql_sub=''
/*type='d'是数据文件,type='l'是日志文件 */
/*flag_file=1时新的数据库文件还是存放在原来路径，否则存放路径和master数据库路径一样*/
select sql_sub=sql_sub+'move '''+LogicalName+''' to '''
+case type
when 'd' then case flag_file
when 1 then File_path
else master_path
end
when 'l' then case flag_file
when 1 then File_path
else master_path
end
end
+case type
when 'd' then restore_db_name+'_'+LogicalName+'_data.mdf'','
when 'l' then restore_db_name+'_'+LogicalName+'_log.ldf'','
end
from tem
select sql='RESTORE DATABASE db_name FROM DISK=filename with '
select sql=sql+sql_sub+'replace'
select par='db_name nvarchar(128),filename nvarchar(260)'
print sql
execute sp_executesql sql,par,db_name=restore_db_name,filename=filename
select flag='ok' /*操作成功*/
end
else
begin
SELECT flag='file type error' /*参数filename输入格式错误*/
end
--备份数据库test_database
declare fl varchar(10)
execute pr_backup_db fl out,'test_database','c:\test_database.bak'
select fl
--恢复数据库,输入的参数错误
declare fl varchar(20)
exec pr_restore_db fl out,'sa','c:\'
select fl
--恢复数据库,即创建数据库test_database的复本test_db
declare fl varchar(20)
exec pr_restore_db fl out,'test_db','c:\test_database.bak'
select fl
更改日志传送之组态设定
您可以使用数据库维护计划之【属性】对话盒来更改日志传送相关设定。在【交易记录文件备份】设定页提供的选项可更改日志传送过程中交易日志文件备份的组态。
【记录传送】设定页显示出您先前在维护计划内设定的日志传送配对服务器；如果您设定了其它组日志传送配对服务器，也会列在此处。本设定页也包含下列选项：新增目的数据库(用以建立新的日志传送配对服务器)、删除既有日志传送配对服务器、编辑目前的日志传送配对服务器之属性，以及移除整个日志传送功能。
当您在【记录传送】设定页之中点选【编辑】时，将开启【编辑目的数据库】对话盒。您可以在对话盒内【一般】设定页检视与修改次要服务器的交易日志文件之目录位置，以及未来做为主要服务器时分享目录之路径。【初始化】设定页则可让您更改复原模式，以及次要服务器上复制与回存之频率。【临界值】页可以设定日志传送之临界周期。
在【超出同步临界值】项目可设定：当日志传送监控程序产生警示讯息之前所能允许的最大时间间隔 (介于最近一次来源数据库交易日志文件备份以及最新的交易日志文件回存动作之间)。您也可以在日志传送监控程序之中设定此参数。【在入时间延迟】、【档案保留期限】以及【历程记录保留期限】则是与次要服务器相关的设定。
注：监控服务器在这些组态选项中扮演相当重要的角色。因为【记录传送】设定页的大部分信息取决于监控服务器，所以一但监控服务器停摆时，您将无法更改日志传送的组态设定值。在监控服务器执行SQL Server 2000 Profiler时，主要服务器会连到监控服务器，然后从日志传送资料表中取得既有的日志传送计划。因此，要改变日志传送计划的设定时，您必须确定在Enterprise Manager内可以连接到监控服务器。
检查与监控日志传送动作
SQL Server 2000的日志传送功能还提供了一项日志传送监控程序，可让您安装在另一台独立监控用服务器。
在SQL Server企业版与开发版的msdb数据库中共有七个关于日志传送的资料表：
log_shipping_plans
log_shipping_plan_databases
log_shipping_databases
log_shipping_plan_history
log_shipping_monitor
log_shipping_primaries
log_shipping_secondaries
上述每一个资料表都存在于主要、次要以及监控服务器上。各服务器也会使用某些资料表储存资料，视该服务器在日志传送系统的角色为何。
在主要服务器上检视日志传送动作 从Enterprise Manager 里，您可以登入主要服务器，并观察与监控日志传送动作。如果某个数据库已设定要进行日志传送，在数据库【内容】对话盒的【一般】页可得知该数据库的角色(来源数据库；或是目的数据库)，也可知道日志传送监控程序是位于那一台服务器上。您可以在Enterprise Manager内SQL Server Agent的【作业】节点，检视日志传送与交易日志文件备份工作所执行的状态与历史纪录。主要服务器只使用msdb数据库的两个日志传送资料表。在log_shipping_databases资料表中，SQL Server新增的每一笔资料将会把数据库维护计划ID以及日志传送来源数据库连结在一起。在log_shipping_monitor资料表中，SQL Server新增的每一笔资料包含了监控服务器的名称，以及登入数据库的方式。
在次要服务器上检视日志传送动作 日志传送计划存在于次要服务器。您可在次要服务器监控SQL Agent工作(复制交易日志文件到次要服务器，并回存至目的数据库)。 您也可检视目的数据库的属性对话盒，以决定该数据库在日志传送过程所扮演的角色。
在次要服务器上，SQL Server使用msdb数据库的四个日志传送资料表。当SQL Server建立一个日志传送计划之后，它会新增一笔资料到log_shipping_plan资料表，用以纪录：主要与次要服务器的名称、档案位置、复制与回存工作ID(来自于次要服务器之sysjobs系统资料表)。在log_shipping_plan_databases资料表，SQL Server会连结维护计划以及来源/目的数据库名称，而且储存最后一次进行档案复制与加载动作的相关信息。log_shipping_plan_history资料表则是将每次日志传送的复制与回存事件纪录下来，连同该工作是否成功的信息。SQL Server也会新增一笔资料在log_shipping_monitor资料表，用以参照监控服务器。
如果您勾选了【Allow database to assume primary role】复选框，您将在次要服务器上看到一个重要的额外项目：另一个数据库维护计划(与您先前所建立的维护计划名称相同)，但是并没有激活日志传送。您也会看到一个非作用中(disabled)的SQL Agent工作(备份该数据库的交易日志)。也许您会被这些项目所混淆。尽管它们的名字相同，但是此额外产生的维护计划却不同于当初所建立的那个。SQL Server保留第二个逆向维护计划是为了以后可能发生的主要/次要服务器角色对调动作所准备。
在监控服务器上检视日志传送动作 当您正确设定日志传送之后，SQL Server 会激活监控服务器上Enterprise Manager 的日志传送监控工具程序。此外，SQL Server会建立两个SQL Agent 警示工作(alert job)：一个用来执行工作，另一个处理out-of-sync情况。
使用监控工具程序的方式是，开启Enterprise Manager并连至监控服务器，展开【Management】节点，然后点选【记录传送监视器(Log Shipping Monitor)】。当您点选此工具程序时，其内会列出日志传送配对服务器的清单。您可在配对服务器上按下鼠标右键，检视其备份、复制与回存等工作的执行历史纪录。这些历史纪录十分有用，因为您从这里得到的错误讯息会比从次要服务器上(SQL Agent 复制与回存工作)得到的更为详尽。
如图所示：当您开启配对服务器之属性对话盒，并进入【Status】设定页时，您可检视此配对服务器执行备份与回存程序之状态。
其状态(Status)可以是Normal 或是Out-of-Sync。如果SQL Server Agent尚未复制或回存交易日志文件，对话盒内将会显示日志文件名为first_file_000000000000.trn。这并不是实际的文件名称，只不过是用来标示SQL Server Agent尚未处理任何档案而已。在【Status】设定页也会显示备份、复制以及加载(回存)等动作执行时所耗费的时间。此设定页之信息不会自动更新，所以您必须将此对话盒关闭后再开启，才能更新其资料。
SQL Server只使用msdb数据库内两个资料表来储存日志传送服务器之相关资料。SQL Server在这两个资料表中都给予一个ID做为连结，以及一个外来键(foreign key)。该外来键是设定在log_shipping_secondaries资料表上，并参照log_shipping_primaries资料表的primary_id字段(这两个是所有日志传送资料表中唯一具有外来键关系的资料表)。在log_shipping_primaries资料表内的每笔资料都包含日志传送的相关信息，例如：来源数据库名称、交易日志文件备份工作执行之状态，以及已规划的停摆信息(可避免不必要的警示讯息)。而log_shipping_secondaries 资料表之每笔资料关于目的数据库之信息；每个目的数据库附属于特定的日志传送来源数据库。这两个资料表互相连结的结果就是日志传送监控程序内所显示的配对服务器信息。
移除与重新组态日志传送功能
如果您想从数据库维护计划中移除日志传送功能，可参考下列方式：开启该计划的属性对话盒，选择【记录传送】设定页，然后点选【移出记录传送】。此动作将从次要服务器上移除SQL Server Agent的备份与回存工作，并清除日志传送资料表内的所有相关资料。此外，日志传送监控程序的相关信息也会一并被清除。然而此动作将会适当地保留主要服务器上SQL Server Agent的交易日志备份工作。只有在删除数据库维护计划时，该工作才会被移除。假如您想从监控服务器内移除掉日志传送监控程序，请用手动方式将log_shipping_primaries与log_shipping_secondaries这两个资料表(位于监控服务器的msdb数据库)的资料删除即可。
如果您在数据库维护计划内设定日志传送时，就已允许目的数据库可以做为新的日志传送来源数据库。当您删除主要服务器的维护计划时，次要服务器上仍然会保留其数据库维护计划，以及交易日志文件备份工作。删除这些项目的方式是将次要服务器上与日志传送相关的数据库维护计划直接删除。
