有三个表，A ，B，C
A、B表中含有: A1,B1,C1 三个字段,
C 表中存放A、B表中的A1、B1、C1 的集合,
字段类型都为nvarchar(10),
当表A的数据被更新、删除、插入后要反映到C表。
当表B的数据被更新、删除、插入后要反映到C表。
假定A,B表中在a1,b1,c1上有唯一索引
这个问题如果纯属从理论来说，是很容易解决的，因为从要求可知，实质上C表存放的数据即为A、B表的并集。可以在A、B表上创建相同的trigger，一旦A、B表上有变化，比如插入、删除或更新时，即清空C表数据，然后把A、B表的数据union后插入C表中即可实现目的:)呵呵呵。。。
下面的trigger的实现原理是：
当A表插入数据时，检查C表中是否有A表将要插入的数据，如果无，则将这行数据插入到C表中，反之，则不需要操作。
当A表update时, 检查B表中是否有更新前这行数据，如果有，则C表中应该保留这行数据且把A表中更新后的数据也插入到C表中去。如果B表中没有A表更新前的这行数据且C表中没有A表更新后的这行数据，则需要用A表更新后的数据来更新C表中与A表更新前这行数据相同的数据；如果B表中没有A表更新的的这行数据且C表中有A表更新后的这行数据，则需要从C表中删除跟A表更新前相同的那行数据（因为更新A表后，A表和B表都没有A表更新前的那行数据了，则这行数据显然在C表中不应该再存在了）。
当A表中删除时，检查B表是否还存在A表要删除的这行数据，如果有，则不能删除C表中与A表要删除的数据相同的行。反之，则执行删除操作。
B表中的trigger跟A表中的原理相同。
CREATE TRIGGER SYNC_C_BY_A
ON A
AFTER INSERT,UPDATE,DELETE
AS
Declare @DmlTinyInt --1:Insert 2:Update 3:Delete
Declare @RowsD Int
Declare @RowsI Int
Declare @A1_D nvarchar(10)
Declare @B1_D Nvarchar(10)
Declare @C1_D Nvarchar(10)
--确定是哪一种dml操作
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI=0
Goto Exit_
If @RowsD=0 And @RowsI>0
Set @Dml=1
Else
If @RowsD>0 And @RowsI>0
Set @Dml=2
Else
If @RowsD>0 And @RowsI=0
Set @Dml=3
IF @DML=1
BEGIN
--检查c表中是否已经有A表中新插入的数据行,如果没有,则也插入
IF NOT EXISTS(SELECT TOP 1 1 FROM c,inserted i where c.a1=i.a1 and c.b1=i.b1 and c.c1=i.c1)
insert into c select * from inserted
END
IF @DML=2
BEGIN
--检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
IF NOT EXISTS(SELECT TOP 1 1 FROM B,DELETED d where b.a1=d.a1 and b.b1=d.b1 and b.c1=d.c1)
BEGIN
--如果C表中不存在A表更新后的这行数据,则更新C表中跟A表更新前那行数据相同的数据
IF NOT EXISTS(SELECT TOP 1 1 FROM C,INSERTED I WHERE C.A1=I.A1 AND C.B1=I.B1 AND C.C1=I.C1)
BEGIN
UPDATE C SET A1=I.A1,B1=I.B1,C1=I.C1 FROM C,INSERTED I,DELETED D WHERE C.A1=D.A1 AND C.B1=D.B1 AND C.C1=D.C1
END
--如果C表中存在A表更新后的这行数据,则需要删除C表中跟A表更新前相同的那行数据
ELSE
BEGIN
SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
END
END
ELSE
insert into c select * from inserted i where not exists(select 1 from c where i.a1=c.a1 and i.b1=c.b1 and i.c1=c.c1)
END
IF @DML=3
BEGIN
--如果B表中不存在A表要删除的这行数据,则需要从C表中删除这行数据
IF not exists(select top 1 1 from b,deleted d where b.a1=d.a1 and b.b1=d.b1 and b.c1=d.c1)
DELETE FROM C WHERE EXISTS(SELECT 1 FROM deleted d where c.a1=d.a1 and c.b1=d.b1 and c.c1=d.c1)
END
EXIT_:
CREATE TRIGGER SYNC_C_BY_B
ON B
AFTER INSERT,UPDATE,DELETE
AS
Declare @DmlTinyInt --1:Insert 2:Update 3:Delete
Declare @RowsD Int
Declare @RowsI Int
Declare @A1_D nvarchar(10)
Declare @B1_D Nvarchar(10)
Declare @C1_D Nvarchar(10)
--确定是哪一种dml操作
Select @RowsD=Count(*) From Deleted
Select @RowsI=Count(*) From Inserted
If @RowsD=0 And @RowsI=0
Goto Exit_
If @RowsD=0 And @RowsI>0
Set @Dml=1
Else
If @RowsD>0 And @RowsI>0
Set @Dml=2
Else
If @RowsD>0 And @RowsI=0
Set @Dml=3
IF @DML=1
BEGIN
--检查c表中是否已经有B表中新插入的数据行,如果没有,则也插入
IF NOT EXISTS(SELECT TOP 1 1 FROM c,inserted i where c.a1=i.a1 and c.b1=i.b1 and c.c1=i.c1)
insert into c select * from inserted
END
IF @DML=2
BEGIN
--检查B表中是否有A表中更新前的这行数据,如果有,则不需要更新C表中的数据,而是要把A表中更新后的这行数据插入到C表中
IF NOT EXISTS(SELECT TOP 1 1 FROM A,DELETED d where a.a1=d.a1 and a.b1=d.b1 and a.c1=d.c1)
BEGIN
--如果C表中不存在B表更新后的这行数据,则更新C表中跟b表更新前那行数据相同的数据
IF NOT EXISTS(SELECT TOP 1 1 FROM C,INSERTED I WHERE C.A1=I.A1 AND C.B1=I.B1 AND C.C1=I.C1)
BEGIN
UPDATE C SET A1=I.A1,B1=I.B1,C1=I.C1 FROM C,INSERTED I,DELETED D WHERE C.A1=D.A1 AND C.B1=D.B1 AND C.C1=D.C1
END
--如果C表中存在更新B表后的这行数据,则需要删除C表中跟B表更新前相同的那行数据
ELSE
BEGIN
SELECT @A1_D=A1,@B1_D=B1,@C1_D=C1 FROM DELETED
DELETE FROM C WHERE @A1_D=A1 AND @B1_D=B1 AND @C1_D=C1
End
END
ELSE
insert into c select * from inserted i where not exists(select 1 from c where i.a1=c.a1 and i.b1=c.b1 and i.c1=c.c1)
END
IF @DML=3
BEGIN
--如果A表中不存在B表要删除的这行数据,则需要从C表中删除这行数据
if not exists(select top 1 1 from a,deleted d where a.a1=d.a1 and a.b1=d.b1 and a.c1=d.c1)
DELETE FROM C WHERE EXISTS(SELECT 1 FROM deleted d where c.a1=d.a1 and c.b1=d.b1 and c.c1=d.c1)
END
EXIT_:
复制是将一组数据从一个数据源拷贝到多个数据源的技术，是将一份数据发布到多个存储站点上的有效方式。使用复制技术，用户可以将一份数据发布到多台服务器上，从而使不同的服务器用户都可以在权限的许可的范围内共享这份数据。复制技术可以确保分布在不同地点的数据自动同步更新，从而保证数据的一致性。
SQL复制的基本元素包括
出版服务器、订阅服务器、分发服务器、出版物、文章
SQL复制的工作原理
SQL SERVER 主要采用出版物、订阅的方式来处理复制。源数据所在的服务器是出版服务器，负责发表数据。出版服务器把要发表的数据的所有改变情况的拷贝复制到分发服务器，分发服务器包含有一个分发数据库，可接收数据的所有改变，并保存这些改变，再把这些改变分发给订阅服务器
SQL SERVER复制技术类型
SQL SERVER提供了三种复制技术，分别是：
1、快照复制（呆会我们就使用这个）
2、事务复制
3、合并复制
只要把上面这些概念弄清楚了那么对复制也就有了一定的理解。接下来我们就一步一步来实现复制的步骤。
第一先来配置出版服务器
(1)选中指定[服务器]节点
(2)从[工具]下拉菜单的[复制]子菜单中选择[发布、订阅服务器和分发]命令
(3)系统弹出一个对话框点[下一步]然后看着提示一直操作到完成。
(4)当完成了出版服务器的设置以后系统会为该服务器的树形结构中添加一个复制监视器。同时也生成一个分发数据库(distribution)
第二创建出版物
(1)选中指定的服务器
(2)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令。此时系统会弹出一个对话框
(3)选择要创建出版物的数据库，然后单击[创建发布]
(4)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
(5)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型，SQLSERVER允许在不同的数据库如 ORACLE或ACCESS之间进行数据复制。但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(6)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
(7)然后[下一步]直到操作完成。当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。
第三设计订阅
(1)选中指定的订阅服务器
(2)从[工具]下拉菜单中选择[复制]子菜单的[请求订阅]
(3)按照单击[下一步]操作直到系统会提示检查SQL SERVER 代理服务的运行状态，执行复制操作的前提条件是SQL SERVER 代理服务必须已经启动。
(4)单击[完成]。完成订阅操作。
完成上面的步骤其实复制也就是成功了。但是如何来知道复制是否成功了呢？这里可以通过这种方法来快速看是否成功。展开出版服务器下面的复制——发布内容——右键发布内容——属性——击活——状态然后点立即运行 代理程序接着点 代理程序属性击活调度把调度设置为每一天发生，每一分钟，在0：00：00和23：59：59之间。接下来就是判断复制是否成功了打开C:/Program Files/Microsoft SQL Server/MSSQL/REPLDATA/unc/XIAOWANGZI_database_database下面看是不是有一些以时间做为文件名的文件夹差不多一分中就产生一个。要是你还不信的话就打开你的数据库看在订阅的服务器的指定订阅数据库下看是不是看到了你刚才所发布的表—
一个手工同步的方案
--定时同步服务器上的数据
--例子:
--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test
--服务器上的表(查询分析器连接到服务器上创建)
create table [user](id int primary key,number varchar(4),name varchar(10))
go
--以下在局域网(本机操作)
--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录
if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [user]
GO
create table [user](id int identity(1,1),number varchar(4),name varchar(10),state bit)
go
--创建触发器,维护state字段的值
create trigger t_state on [user]
after update
as
update [user] set state=1
from [user] a join inserted b on a.id=b.id
where a.state is not null
go
--为了方便同步处理,创建链接服务器到要同步的服务器
--这里的远程服务器名为:xz,用户名为:sa,无密码
if exists(select 1 from master..sysservers where srvname='srv_lnk')
exec sp_dropserver 'srv_lnk','droplogins'
go
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','xz'
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa'
go
--创建同步处理的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_synchro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_synchro]
GO
create proc p_synchro
as
--set XACT_ABORT on
--启动远程服务器的MSDTC服务
--exec master..xp_cmdshell 'isql /S"xz" /U"sa" /P"" /q"exec master..xp_cmdshell ''net start msdtc'',no_output"',no_output
--启动本机的MSDTC服务
--exec master..xp_cmdshell 'net start msdtc',no_output
--进行分布事务处理,如果表用标识列做主键,用下面的方法
--BEGIN DISTRIBUTED TRANSACTION
--同步删除的数据
delete from srv_lnk.test.dbo.[user]
where id not in(select id from [user])
--同步新增的数据
insert into srv_lnk.test.dbo.[user]
select id,number,name from [user] where state is null
--同步修改的数据
update srv_lnk.test.dbo.[user] set
number=b.number,name=b.name
from srv_lnk.test.dbo.[user] a
join [user] b on a.id=b.id
where b.state=1
--同步后更新本机的标志
update [user] set state=0 where isnull(state,1)=1
--COMMIT TRAN
go
--创建作业,定时执行数据同步的存储过程
if exists(SELECT 1 from msdb..sysjobs where name='数据处理')
EXECUTE msdb.dbo.sp_delete_job @job_name='数据处理'
exec msdb..sp_add_job @job_name='数据处理'
--创建作业步骤
declare @sql varchar(800),@dbname varchar(250)
select @sql='exec p_synchro' --数据处理的命令
,@dbname=db_name() --执行数据处理的数据库名
exec msdb..sp_add_jobstep @job_name='数据处理',
@step_name = '数据同步',
@subsystem = 'TSQL',
@database_name=@dbname,
@command = @sql,
@retry_attempts = 5, --重试次数
@retry_interval = 5 --重试间隔
--创建调度
EXEC msdb..sp_add_jobschedule @job_name = '数据处理',
@name = '时间安排',
@freq_type = 4, --每天
@freq_interval = 1, --每天执行一次
@active_start_time = 00000 --0点执行
go
本文引用地址：http://data.chinaser.net/2006/1027/1762.htm
程序代码可以有版本管理CVS进行同步管理,可是数据库同步就非常麻烦,只能自己改了一个后再去改另一个,如果忘记了更改另一个经常造成两个数据库的结构或内容上不一致.
各位有什么好的方法吗?
分发与复制
用强制订阅实现数据库同步操作
大量和批量的数据可以用数据库的同步机制处理:
//
说明：
为方便操作,所有操作均在发布服务器(分发服务器)上操作,并使用推模式
在客户机器使用强制订阅方式。
有疑问联系作者：zlp321001@hotmail.com
测试通过
//
--1:环境
服务器环境:
机器名称： ZehuaDb
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
客户端
机器名称：Zlp
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
--2:建用户帐号
在服务器端建立域用户帐号
我的电脑管理->本地用户和组->用户->建立
UserName:zlp
UserPwd:zlp
--3:重新启动服务器MSSQLServer
我的电脑->控制面版->管理工具->服务->MSSQLServer 服务
(更改为：域用户帐号,我们新建的zlp用户 ./zlp,密码:zlp)
--4:安装分发服务器
A:配置分发服务器
工具->复制->配置发布、订阅服务器和分发->下一步->下一步(所有的均采用默认配置)
B:配置发布服务器
工具->复制->创建和管理发布->选择要发布的数据库(SZ)->下一步->快照发布->下一步->
选择要发布的内容->下一步->下一步->下一步->完成
C:强制配置订阅服务器(推模式,拉模式与此雷同)
工具->复制->配置发布、订阅服务器和分发->订阅服务器->新建->SQL Server数据库->输入客户端服务器名称(ZLP)->使用SQL Server 身份验证(sa,空密码)->确定->应用->确定
D:初始化订阅
复制监视器->发布服务器(ZEHUADB)->双击订阅->强制新建->下一步->选择启用的订阅服务器->ZLP->
下一步->下一步->下一步->下一步->完成
--5:测试配置是否成功
复制监视器->发布服务器(ZEHUADB)->双击SZ:SZ->点状态->点立即运行代理程序
查看：
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->选择ZLP:SZ(类型强制)->鼠标右键->启动同步处理
如果没有错误标志(红色叉)，恭喜您配置成功
--6:测试数据
--在服务器执行:
选择一个表，执行如下SQL
insert into WQ_NEWSGROUP_S select '测试成功',5
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->快照->启动代理程序
->ZLP:SZ(强制)->启动同步处理
去查看同步的 WQ_NEWSGROUP_S 是否插入了一条新的记录
测试完毕，通过。
--7修改数据库的同步时间,一般选择夜晚执行数据库同步处理
(具体操作略) :D
/*
注意说明：
服务器一端不能以(local)进行数据的发布与分发,需要先删除注册，然后新建注册本地计算机名称
卸载方式：工具->复制->禁止发布->是在"ZehuaDb"上静止发布,卸载所有的数据库同步配置服务器
注意：发布服务器、分发服务器中的SQLServerAgent服务必须启动
采用推模式: "D:/Microsoft SQL Server/MSSQL/REPLDATA/unc" 目录文件可以不设置共享
拉模式:则需要共享~!
*/
少量数据库同步可以采用触发器实现,同步单表即可。
配置过程中可能出现的问题
在SQL Server 2000里设置和使用数据库复制之前，应先检查相关的几台SQL Server服务器下面几点是否满足：
1、MSSQLserver和Sqlserveragent服务是否是以域用户身份启动并运行的（./administrator用户也是可以的）
如果登录用的是本地系统帐户local，将不具备网络功能，会产生以下错误:
进程未能连接到Distributor '@Server name'
(如果您的服务器已经用了SQL Server全文检索服务, 请不要修改MSSQLserver和Sqlserveragent服务的local启动。
会照成全文检索服务不能用。请换另外一台机器来做SQL Server 2000里复制中的分发服务器。)
修改服务启动的登录用户，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
2、检查相关的几台SQL Server服务器是否改过名称(需要srvid=0的本地机器上srvname和datasource一样)
在查询分析器里执行:
use master
select srvid,srvname,datasource from sysservers
如果没有srvid=0或者srvid=0（也就是本机器）但srvname和datasource不一样, 需要按如下方法修改:
| USE master GO -- 设置两个变量 DECLARE @serverproperty_servername varchar(100), @servername varchar(100) -- 取得Windows NT 服务器和与指定的 SQL Server 实例关联的实例信息 SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName')) -- 返回运行 Microsoft SQL Server 的本地服务器名称 SELECT @servername = CONVERT(varchar(100), @@SERVERNAME) -- 显示获取的这两个参数 select @serverproperty_servername,@servername --如果@serverproperty_servername和@servername不同(因为你改过计算机名字),再运行下面的 --删除错误的服务器名 EXEC sp_dropserver @server=@servername --添加正确的服务器名 EXEC sp_addserver @server=@serverproperty_servername, @local='local' |
修改这项参数，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
这样一来就不会在创建复制的过程中出现18482、18483错误了。
3、检查SQL Server企业管理器里面相关的几台SQL Server注册名是否和上面第二点里介绍的srvname一样
不能用IP地址的注册名。
（我们可以删掉IP地址的注册，新建以SQL Server管理员级别的用户注册的服务器名）
这样一来就不会在创建复制的过程中出现14010、20084、18456、18482、18483错误了。
4、检查相关的几台SQL Server服务器网络是否能够正常访问
如果ping主机IP地址可以，但ping主机名不通的时候，需要在
winnt/system32/drivers/etc/hosts (WIN2000)
windows/system32/drivers/etc/hosts (WIN2003)
文件里写入数据库服务器IP地址和主机名的对应关系。
例如：
| 127.0.0.1 localhost 192.168.0.35 oracledb oracledb 192.168.0.65 fengyu02 fengyu02 202.84.10.193 bj_db bj_db |
或者在SQL Server客户端网络实用工具里建立别名，例如:
5、系统需要的扩展存储过程是否存在(如果不存在，需要恢复):
| sp_addextendedproc 'xp_regenumvalues',@dllname ='xpstar.dll' go sp_addextendedproc 'xp_regdeletevalue',@dllname ='xpstar.dll' go sp_addextendedproc 'xp_regdeletekey',@dllname ='xpstar.dll' go sp_addextendedproc xp_cmdshell ,@dllname ='xplog70.dll'
|
->[配置发布、订阅服务器和分发]的图形界面来配置数据库复制了。
下面是按顺序列出配置复制的步骤:
一、建立发布和分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
->[使"@servername"成为它自己的分发服务器,SQL Server将创建分发数据库和日志]
->[制定快照文件夹]-> [自定义配置] -> [否,使用下列的默认配置] -> [完成]
上述步骤完成后, 会在当前"@servername" SQL Server数据库里建立了一个distribion库和
一个distributor_admin管理员级别的用户(我们可以任意修改密码)
服务器上新增加了四个作业:
[ 代理程序历史记录清除: distribution ]
[ 分发清除: distribution ]
[ 复制代理程序检查 ]
[ 重新初始化存在数据验证失败的订阅 ]
SQL Server企业管理器里多了一个复制监视器, 当前的这台机器就可以发布、分发、订阅了。
我们再次在SQL Server企业管理器里[复制]-> 右键选择
->[配置发布、订阅服务器和分发],可以看到类似下图:
我们可以在 [发布服务器和分发服务器的属性] 窗口
-> [发布服务器] -> [新增] -> [确定]
-> [发布数据库] -> [事务]/[合并] -> [确定]
-> [订阅服务器] -> [新增] -> [确定]
把网络上的其它SQL Server服务器添加成为发布或者订阅服务器.
新增一台发布服务器的选项：
我这里新建立的JIN001发布服务器是用管理员级别的数据库用户test连接的，
到发布服务器的管理链接要输入密码的可选框, 默认的是选中的，
在新建的JIN001发布服务器上建立和分发服务器FENGYU/FENGYU的链接的时需要输入distributor_admin用户的密码
到发布服务器的管理链接要输入密码的可选框，也可以不选，
也就是不需要密码来建立发布到分发服务器的链接(这当然欠缺安全，在测试环境下可以使用)
二、新建立的网络上另一台发布服务器(例如JIN001)选择分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
-> 使用下列服务器(选定的服务器必须已配置为分发服务器) -> [选定服务器](例如FENGYU/FENGYU)
-> [下一步] -> [输入分发服务器（例如FENGYU/FENGYU）的distributor_admin用户的密码两次]
-> [下一步] -> [自定义配置] -> [否，使用下列的默认配置]
-> [下一步] -> [完成] -> [确定]
建立一个数据库复制发布的过程:
[复制] -> [发布内容] -> 右键选择 -> [新建发布]
-> [下一步] -> [选择发布数据库] -> [选中一个待发布的数据库]
-> [下一步] -> [选择发布类型] -> [事务发布]/[合并发布]
-> [下一步] -> [指定订阅服务器的类型] -> [运行SQL Server 2000的服务器]
-> [下一步] -> [指定项目] -> [在事务发布中只可以发布带主键的表] -> [选中一个有主键的待发布的表]
->[在合并发布中会给表增加唯一性索引和 ROWGUIDCOL 属性的唯一标识符字段[rowguid],默认值是newid()]
(添加新列将: 导致不带列列表的 INSERT 语句失败,增加表的大小,增加生成第一个快照所要求的时间)
->[选中一个待发布的表]
-> [下一步] -> [选择发布名称和描述] ->
-> [下一步] -> [自定义发布的属性] -> [否，根据指定方式创建发布]
-> [下一步] -> [完成] -> [关闭]
发布属性里有很多有用的选项：设定订阅到期(例如24小时)
设定发布表的项目属性:
常规窗口可以指定发布目的表的名称，可以跟原来的表名称不一样。
下图是命令和快照窗口的栏目
( SQL Server 数据库复制技术实际上是用insert,update,delete操作在订阅服务器上重做发布服务器上的事务操作
看文档资料需要把发布数据库设成完全恢复模式，事务才不会丢失
但我自己在测试中发现发布数据库是简单恢复模式下，每10秒生成一些大事务，10分钟后再收缩数据库日志，
这期间发布和订阅服务器上的作业都暂停，暂停恢复后并没有丢失任何事务更改 )
发布表可以做数据筛选，例如只选择表里面的部分列:
例如只选择表里某些符合条件的记录, 我们可以手工编写筛选的SQL语句:
发布表的订阅选项，并可以建立强制订阅:
成功建立了发布以后,发布服务器上新增加了一个作业: [ 失效订阅清除 ]
分发服务器上新增加了两个作业:
[ JIN001-dack-dack-5 ] 类型[ REPL快照 ]
[ JIN001-dack-3 ] 类型[ REPL日志读取器 ]
上面蓝色字的名称会根据发布服务器名,发布名及第几次发布而使用不同的编号
REPL快照作业是SQL Server复制的前提条件,它会先把发布的表结构,数据,索引,约束等生成到发布服务器的OS目录下文件
(当有订阅的时候才会生成, 当订阅请求初始化或者按照某个时间表调度生成)
REPL日志读取器在事务复制的时候是一直处于运行状态。(在合并复制的时候可以根据调度的时间表来运行)
建立一个数据库复制订阅的过程:
[复制] -> [订阅] -> 右键选择 -> [新建请求订阅]
-> [下一步] -> [查找发布] -> [查看已注册服务器所做的发布]
-> [下一步] -> [选择发布] -> [选中已经建立发布服务器上的数据库发布名]
-> [下一步] -> [指定同步代理程序登录] -> [当代理程序连接到代理服务器时:使用SQL Server身份验证]
(输入发布服务器上distributor_admin用户名和密码)
-> [下一步] -> [选择目的数据库] -> [选择在其中创建订阅的数据库名]/[也可以新建一个库名]
-> [下一步] -> [允许匿名订阅] -> [是，生成匿名订阅]
-> [下一步] -> [初始化订阅] -> [是，初始化架构和数据]
-> [下一步] -> [快照传送] -> [使用该发布的默认快照文件夹中的快照文件]
(订阅服务器要能访问发布服务器的REPLDATA文件夹，如果有问题，可以手工设置网络共享及共享权限)
-> [下一步] -> [快照传送] -> [使用该发布的默认快照文件夹中的快照文件]
-> [下一步] -> [设置分发代理程序调度] -> [使用下列调度] -> [更改] -> [例如每五分钟调度一次]
-> [下一步] -> [启动要求的服务] -> [该订阅要求在发布服务器上运行SQLServerAgent服务]
-> [下一步] -> [完成] -> [确定]
成功建立了订阅后，订阅服务器上新增加了一个类别是[REPL-分发]作业(合并复制的时候类别是[REPL-合并])
它会按照我们给的时间调度表运行数据库同步复制的作业
三、SQL Server复制配置好后, 可能出现异常情况的实验日志:
1.发布服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制没有多大影响
中断期间,分发和订阅都接收到没有复制的事务信息
2.分发服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制有一些影响
中断期间,发布服务器的事务排队堆积起来
(如果设置了较长时间才删除过期订阅的选项, 繁忙发布数据库的事务日志可能会较快速膨胀),
订阅服务器会因为访问不到发布服务器,反复重试
我们可以设置重试次数和重试的时间间隔(最大的重试次数是9999, 如果每分钟重试一次,可以支持约6.9天不出错)
分发服务器sql server服务启动,网络接通以后,发布服务器上的堆积作业将按时间顺序作用到订阅机器上:
会需要一个比较长的时间(实际上是生成所有事务的insert,update,delete语句,在订阅服务器上去执行)
我们在普通的PC机上实验的58个事务100228个命令执行花了7分28秒.
3.订阅服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制影响比较大,可能需要重新初试化
我们实验环境(订阅服务器)从18:46分意外停机以, 第二天8:40分重启动后,
已经设好的复制在8:40分以后又开始正常运行了, 发布服务器上的堆积作业将按时间顺序作用到订阅机器上
但复制管理器里出现快照的错误提示, 快照可能需要重新初试化,复制可能需要重新启动.
(我们实验环境的机器并没有进行快照初试化,复制仍然是成功运行的)
四、删除已经建好的发布和定阅可以直接用delete删除按钮
我们最好总是按先删定阅，再删发布，最后禁用发布的顺序来操作。
如果要彻底删去SQL Server上面的复制设置, 可以这样操作:
[复制] -> 右键选择 [禁用发布] -> [欢迎使用禁用发布和分发向导]
-> [下一步] -> [禁用发布] -> [要在"@servername"上禁用发布]
-> [下一步] -> [完成禁用发布和分发向导] -> [完成]
我们也可以用T-SQL命令来完成复制中发布及订阅的创建和删除, 选中已经设好的发布和订阅, 按属标右键
可以[生成SQL脚本]。(这里就不详细讲了, 后面推荐的网站内有比较详细的内容)
当你试图删除或者变更一个table时，出现以下错误
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'object_name' because it is being used for replication.
比较典型的情况是该table曾经用于复制，但是后来又删除了复制
处理办法：
| select * from sysobjects where replinfo >'0' sp_configure 'allow updates', 1 |
疑问:
在合并复制配置完全后，如果同步代理停止了。我要在程序中去重新启动合并复制的同步代理。请问使用什么命令或存储过程呢？
解决办法:
sp_start_job
指示 SQL Server 代理程序立即执行作业。
示例
下例启动名为 Nightly Backup 的作业。
USE msdb
EXEC sp_start_job @job_name = 'Nightly Backup'
2、日志还原功能、、、
| /*--说明： 下面的代码演示了如何利用日志还原功能，将主数据库中的数据变化及时反馈到备用数据库中 备用数据库的数据可以随时用于查询，但不能被更新（备用数据库只读）。 --*/ --首先,创建一个演示用的数据库(主数据库) --对数据库进行备份 --把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步) --启动 SQL Agent 服务 --创建主服务器数据训与备用服务器数据库之间同步的作业 --创建同步处理步骤 --备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化 --创建调度(每分钟执行一次) -- 添加目标服务器 --通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成 --在主数据库中创建一个测试用的表 --等待1分钟30秒（由于同步的时间间隔设置为1分钟,所以要延时才能看到效果） --查询一下备用数据库,看看同步是否成功 /*--结果: （所影响的行数为 0 行） --测试成功 --最后删除所有的测试 /*===========================================================*/ /*--服务器档机处理说明 |
--1. 如果主数据库损坏，无法备份出最新的日志，可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）。
--RESTORE LOG Db_test_bak WITH RECOVERY
--2. 如果主数据库可以备份出最新日志，则可以使用下面的语句。
--先备份主数据库的最新的事务日志
--BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT
--再在备用数据库中恢复最新的事务日志，并且使备用数据库可读写（升级为主数据库）
--RESTORE LOG Db_test_bak FROM DISK='c:/test_log.bak'
简单地说:
1. 你的sql服务要使用指定的windows用户登陆, 而不能使用"本地系统帐户"
2. 用于登陆sql服务的用户要求对共享目录具有所有权限
3. 如果你的电脑没有加入到域, 还必须保证源和目标服务器的sql服务设置的登陆用户是一样的(用户名和密码都一样)
网络备份主要是权限设置问题, 参考下面的备份文件共享目录权限设置方法去解决目录的共享权限就可以了
下面假设是假设A服务器上的数据库备份到B服务器上的共享目录权限设置(两台服务器应该在局域网内,允许目录共享访问)::
1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:
我的电脑
--控制面板
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户
2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作:
我的电脑--D:/ 新建一个目录,名为: BAK
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
--确定
3.设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户
开始--程序--管理工具--服务
--右键 MSSQLSERVER
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名
--"密码"中输入该用户的密码
--确定
--同样的方法设置 SQLSERVERAGENT
4.在A机器上完成对B机器BAK目录的映射
5.查询分析器中执行下面的语句,检验是否成功:
exec master..xp_cmdshell 'dir 映射的盘符'
6.A服务器上做备份计划
备注:创建一个新的用户只是为了让MSSQLSERVER服务的启动帐户与共享目录的有效访问同名且密码相同,这样才能通过验证(所以你也可以用其他有效的用户来代替,只需要满足用户名和密码相同,并且拥有足够的权限)
同步两个Sql server
程序代码可以有版本管理CVS进行同步管理,可是数据库同步就非常麻烦,只能自己改了一个后再去改另一个,如果忘记了更改另一个经常造成两个数据库的结构或内容上不一致。各位有什么好的方法吗?
分发与复制
用强制订阅实现数据库同步操作
大量和批量的数据可以用数据库的同步机制处理:
//
说明：
为方便操作,所有操作均在发布服务器(分发服务器)上操作,并使用推模式
在客户机器使用强制订阅方式。
有疑问联系作者： zlp321001@hotmail.com
测试通过
//
--1:环境
服务器环境:
机器名称： ZehuaDb
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
客户端
机器名称：Zlp
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
--2:建用户帐号
在服务器端建立域用户帐号
我的电脑管理->本地用户和组->用户->建立
UserName:zlp
UserPwd:zlp
--3:重新启动服务器MSSQLServer
我的电脑->控制面版->管理工具->服务->MSSQLServer 服务
(更改为：域用户帐号,我们新建的zlp用户 ./zlp,密码:zlp)
--4:安装分发服务器
A:配置分发服务器
工具->复制->配置发布、订阅服务器和分发->下一步->下一步(所有的均采用默认配置)
B:配置发布服务器
工具->复制->创建和管理发布->选择要发布的数据库(SZ)->下一步->快照发布->下一步->
选择要发布的内容->下一步->下一步->下一步->完成
C:强制配置订阅服务器(推模式,拉模式与此雷同)
工具->复制->配置发布、订阅服务器和分发->订阅服务器->新建->SQL Server数据库->输入客户端服务器名称(ZLP)->使用SQL Server 身份验证 (sa,空密码)->确定->应用->确定
D:初始化订阅
复制监视器->发布服务器(ZEHUADB)->双击订阅->强制新建->下一步->选择启用的订阅服务器->ZLP->
下一步->下一步->下一步->下一步->完成
--5:测试配置是否成功
复制监视器->发布服务器(ZEHUADB)->双击SZ:SZ->点状态->点立即运行代理程序
查看：
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->选择ZLP:SZ(类型强制)->鼠标右键->启动同步处理
如果没有错误标志(红色叉)，恭喜您配置成功
--6:测试数据
--在服务器执行:
选择一个表，执行如下SQL
insert into WQ_NEWSGROUP_S select '测试成功',5
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->快照->启动代理程序
->ZLP:SZ(强制)->启动同步处理
去查看同步的 WQ_NEWSGROUP_S 是否插入了一条新的记录
测试完毕，通过。
--7修改数据库的同步时间,一般选择夜晚执行数据库同步处理
(具体操作略) :D
/*
注意说明：
服务器一端不能以(local)进行数据的发布与分发,需要先删除注册，然后新建注册本地计算机名称
卸载方式：工具->复制->禁止发布->是在"ZehuaDb"上静止发布,卸载所有的数据库同步配置服务器
注意：发布服务器、分发服务器中的SQLServerAgent服务必须启动
采用推模式: "D:/Microsoft SQL Server/MSSQL/REPLDATA/unc" 目录文件可以不设置共享
拉模式:则需要共享~!
*/
少量数据库同步可以采用触发器实现,同步单表即可。
配置过程中可能出现的问题
在SQL Server 2000里设置和使用数据库复制之前，应先检查相关的几台SQL Server服务器下面几点是否满足：
1、MSSQLserver和Sqlserveragent服务是否是以域用户身份启动并运行的（./administrator用户也是可以的）
如果登录用的是本地系统帐户local，将不具备网络功能，会产生以下错误:
进程未能连接到Distributor '@Server name'
(如果您的服务器已经用了SQL Server全文检索服务, 请不要修改MSSQLserver和Sqlserveragent服务的local启动。
会照成全文检索服务不能用。请换另外一台机器来做SQL Server 2000里复制中的分发服务器。)
修改服务启动的登录用户，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
2、检查相关的几台SQL Server服务器是否改过名称(需要srvid=0的本地机器上srvname和datasource一样)
在查询分析器里执行:
use master
select srvid,srvname,datasource from sysservers
如果没有srvid=0或者srvid=0（也就是本机器）但srvname和datasource不一样, 需要按如下方法修改:
USE master
GO
-- 设置两个变量
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)
-- 取得Windows NT 服务器和与指定的 SQL Server 实例关联的实例信息
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))
-- 返回运行 Microsoft SQL Server 的本地服务器名称
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)
-- 显示获取的这两个参数
select @serverproperty_servername,@servername
--如果@serverproperty_servername和@servername不同(因为你改过计算机名字),再运行下面的
--删除错误的服务器名
EXEC sp_dropserver @server=@servername
--添加正确的服务器名
EXEC sp_addserver @server=@serverproperty_servername, @local='local'
修改这项参数，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
这样一来就不会在创建复制的过程中出现18482、18483错误了。
3、检查SQL Server企业管理器里面相关的几台SQL Server注册名是否和上面第二点里介绍的srvname一样
不能用IP地址的注册名。
（我们可以删掉IP地址的注册，新建以SQL Server管理员级别的用户注册的服务器名）
这样一来就不会在创建复制的过程中出现14010、20084、18456、18482、18483错误了。
4、检查相关的几台SQL Server服务器网络是否能够正常访问
如果ping主机IP地址可以，但ping主机名不通的时候，需要在
winnt/system32/drivers/etc/hosts (WIN2000)
windows/system32/drivers/etc/hosts (WIN2003)
文件里写入数据库服务器IP地址和主机名的对应关系。
例如：
127.0.0.1 localhost
192.168.0.35 oracledb oracledb
192.168.0.65 fengyu02 fengyu02
202.84.10.193 bj_db bj_db
或者在SQL Server客户端网络实用工具里建立别名，例如:
5、系统需要的扩展存储过程是否存在(如果不存在，需要恢复):
sp_addextendedproc 'xp_regenumvalues',@dllname ='xpstar.dll'
go
sp_addextendedproc 'xp_regdeletevalue',@dllname ='xpstar.dll'
go
sp_addextendedproc 'xp_regdeletekey',@dllname ='xpstar.dll'
go
sp_addextendedproc xp_cmdshell ,@dllname ='xplog70.dll'
接下来就可以用SQL Server企业管理器里[复制]-> 右键选择
->[配置发布、订阅服务器和分发]的图形界面来配置数据库复制了。
下面是按顺序列出配置复制的步骤:
一、建立发布和分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
->[使"@servername"成为它自己的分发服务器,SQL Server将创建分发数据库和日志]
->[制定快照文件夹]-> [自定义配置] -> [否,使用下列的默认配置] -> [完成]
上述步骤完成后, 会在当前"@servername" SQL Server数据库里建立了一个distribion库和
一个distributor_admin管理员级别的用户(我们可以任意修改密码)
服务器上新增加了四个作业:
[ 代理程序历史记录清除: distribution ]
[ 分发清除: distribution ]
[ 复制代理程序检查 ]
[ 重新初始化存在数据验证失败的订阅 ]
SQL Server企业管理器里多了一个复制监视器, 当前的这台机器就可以发布、分发、订阅了。
我们再次在SQL Server企业管理器里[复制]-> 右键选择
->[配置发布、订阅服务器和分发],可以看到类似下图:
我们可以在 [发布服务器和分发服务器的属性] 窗口
-> [发布服务器] -> [新增] -> [确定]
-> [发布数据库] -> [事务]/[合并] -> [确定]
-> [订阅服务器] -> [新增] -> [确定]
把网络上的其它SQL Server服务器添加成为发布或者订阅服务器.
新增一台发布服务器的选项：
我这里新建立的JIN001发布服务器是用管理员级别的数据库用户test连接的，
到发布服务器的管理链接要输入密码的可选框, 默认的是选中的，
在新建的JIN001发布服务器上建立和分发服务器FENGYU/FENGYU的链接的时需要输入distributor_admin用户的密码
到发布服务器的管理链接要输入密码的可选框，也可以不选，
也就是不需要密码来建立发布到分发服务器的链接(这当然欠缺安全，在测试环境下可以使用)
二、新建立的网络上另一台发布服务器(例如JIN001)选择分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
-> 使用下列服务器(选定的服务器必须已配置为分发服务器) -> [选定服务器](例如FENGYU/FENGYU)
-> [下一步] -> [输入分发服务器（例如FENGYU/FENGYU）的distributor_admin用户的密码两次]
-> [下一步] -> [自定义配置] -> [否，使用下列的默认配置]
-> [下一步] -> [完成] -> [确定]
建立一个数据库复制发布的过程:
[复制] -> [发布内容] -> 右键选择 -> [新建发布]
-> [下一步] -> [选择发布数据库] -> [选中一个待发布的数据库]
-> [下一步] -> [选择发布类型] -> [事务发布]/[合并发布]
-> [下一步] -> [指定订阅服务器的类型] -> [运行SQL Server 2000的服务器]
-> [下一步] -> [指定项目] -> [在事务发布中只可以发布带主键的表] -> [选中一个有主键的待发布的表]
->[在合并发布中会给表增加唯一性索引和 ROWGUIDCOL 属性的唯一标识符字段[rowguid],默认值是newid()]
(添加新列将: 导致不带列列表的 INSERT 语句失败,增加表的大小,增加生成第一个快照所要求的时间)
->[选中一个待发布的表]
-> [下一步] -> [选择发布名称和描述] ->
-> [下一步] -> [自定义发布的属性] -> [否，根据指定方式创建发布]
-> [下一步] -> [完成] -> [关闭]
发布属性里有很多有用的选项：设定订阅到期(例如24小时)
设定发布表的项目属性:
常规窗口可以指定发布目的表的名称，可以跟原来的表名称不一样。
下图是命令和快照窗口的栏目
( SQL Server 数据库复制技术实际上是用insert,update,delete操作在订阅服务器上重做发布服务器上的事务操作
看文档资料需要把发布数据库设成完全恢复模式，事务才不会丢失
但我自己在测试中发现发布数据库是简单恢复模式下，每10秒生成一些大事务，10分钟后再收缩数据库日志，
这期间发布和订阅服务器上的作业都暂停，暂停恢复后并没有丢失任何事务更改 )
发布表可以做数据筛选，例如只选择表里面的部分列:
例如只选择表里某些符合条件的记录, 我们可以手工编写筛选的SQL语句:
发布表的订阅选项，并可以建立强制订阅:
成功建立了发布以后,发布服务器上新增加了一个作业: [ 失效订阅清除 ]
分发服务器上新增加了两个作业:
[ JIN001-dack-dack-5 ] 类型[ REPL快照 ]
[ JIN001-dack-3 ] 类型[ REPL日志读取器 ]
上面蓝色字的名称会根据发布服务器名,发布名及第几次发布而使用不同的编号
REPL快照作业是SQL Server复制的前提条件,它会先把发布的表结构,数据,索引,约束等生成到发布服务器的OS目录下文件
(当有订阅的时候才会生成, 当订阅请求初始化或者按照某个时间表调度生成)
REPL日志读取器在事务复制的时候是一直处于运行状态。(在合并复制的时候可以根据调度的时间表来运行)
建立一个数据库复制订阅的过程:
[复制] -> [订阅] -> 右键选择 -> [新建请求订阅]
-> [下一步] -> [查找发布] -> [查看已注册服务器所做的发布]
-> [下一步] -> [选择发布] -> [选中已经建立发布服务器上的数据库发布名]
所属分类： MS-SQL Server 应用实例
楼主：dejoy(燕青)
发表时间：2006-4-17 9:36:06
如何同步两个SQLServer数据库的内容?
程序代码可以有版本管理CVS进行同步管理,可是数据库同步就非常麻烦,只能自己改了一个后再去改另一个,如果忘记了更改另一个经常造成两个数据库的结构或内容上不一致.
各位有什么好的方法吗?
1、分发与复制
用强制订阅实现数据库同步操作
大量和批量的数据可以用数据库的同步机制处理:
//
说明：
为方便操作,所有操作均在发布服务器(分发服务器)上操作,并使用推模式
在客户机器使用强制订阅方式。
有疑问联系作者：zlp321001@hotmail.com
测试通过
//
--1:环境
服务器环境:
机器名称： ZehuaDb
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
客户端
机器名称：Zlp
操作系统：Windows 2000 Server
数据库版本：SQL 2000 Server 个人版
--2:建用户帐号
在服务器端建立域用户帐号
我的电脑管理->本地用户和组->用户->建立
UserName:zlp
UserPwd:zlp
--3:重新启动服务器MSSQLServer
我的电脑->控制面版->管理工具->服务->MSSQLServer 服务
(更改为：域用户帐号,我们新建的zlp用户 ./zlp,密码:zlp)
--4:安装分发服务器
A:配置分发服务器
工具->复制->配置发布、订阅服务器和分发->下一步->下一步(所有的均采用默认配置)
B:配置发布服务器
工具->复制->创建和管理发布->选择要发布的数据库(SZ)->下一步->快照发布->下一步->
选择要发布的内容->下一步->下一步->下一步->完成
C:强制配置订阅服务器(推模式,拉模式与此雷同)
工具->复制->配置发布、订阅服务器和分发->订阅服务器->新建->SQL Server数据库->输入客户端服务器名称(ZLP)->使用SQL Server 身份验证(sa,空密码)->确定->应用->确定
D:初始化订阅
复制监视器->发布服务器(ZEHUADB)->双击订阅->强制新建->下一步->选择启用的订阅服务器->ZLP->
下一步->下一步->下一步->下一步->完成
--5:测试配置是否成功
复制监视器->发布服务器(ZEHUADB)->双击SZ:SZ->点状态->点立即运行代理程序
查看：
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->选择ZLP:SZ(类型强制)->鼠标右键->启动同步处理
如果没有错误标志(红色叉)，恭喜您配置成功
--6:测试数据
--在服务器执行:
选择一个表，执行如下SQL
insert into WQ_NEWSGROUP_S select '测试成功',5
复制监视器->发布服务器(ZEHUADB)->SZ:SZ->快照->启动代理程序
->ZLP:SZ(强制)->启动同步处理
去查看同步的 WQ_NEWSGROUP_S 是否插入了一条新的记录
测试完毕，通过。
--7修改数据库的同步时间,一般选择夜晚执行数据库同步处理
(具体操作略) :D
/*
注意说明：
服务器一端不能以(local)进行数据的发布与分发,需要先删除注册，然后新建注册本地计算机名称
卸载方式：工具->复制->禁止发布->是在"ZehuaDb"上静止发布,卸载所有的数据库同步配置服务器
注意：发布服务器、分发服务器中的SQLServerAgent服务必须启动
采用推模式: "D:/Microsoft SQL Server/MSSQL/REPLDATA/unc" 目录文件可以不设置共享
拉模式:则需要共享~!
*/
少量数据库同步可以采用触发器实现,同步单表即可：
========================================================
配置过程中可能出现的问题,总结如下:(感谢作者: 余枫 提供的帮助)
========================================================
在SQL Server 2000里设置和使用数据库复制之前，应先检查相关的几台SQL Server服务器下面几点是否满足：
1、MSSQLserver和Sqlserveragent服务是否是以域用户身份启动并运行的（./administrator用户也是可以的）
如果登录用的是本地系统帐户local，将不具备网络功能，会产生以下错误:
进程未能连接到Distributor '@Server name'
(如果您的服务器已经用了SQL Server全文检索服务, 请不要修改MSSQLserver和Sqlserveragent服务的local启动。
会照成全文检索服务不能用。请换另外一台机器来做SQL Server 2000里复制中的分发服务器。)
修改服务启动的登录用户，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
2、检查相关的几台SQL Server服务器是否改过名称(需要srvid=0的本地机器上srvname和datasource一样)
在查询分析器里执行:
use master
select srvid,srvname,datasource from sysservers
如果没有srvid=0或者srvid=0（也就是本机器）但srvname和datasource不一样, 需要按如下方法修改:
USE master
GO
-- 设置两个变量
DECLARE @serverproperty_servername varchar(100),
@servername varchar(100)
-- 取得Windows NT 服务器和与指定的 SQL Server 实例关联的实例信息
SELECT @serverproperty_servername = CONVERT(varchar(100), SERVERPROPERTY('ServerName'))
-- 返回运行 Microsoft SQL Server 的本地服务器名称
SELECT @servername = CONVERT(varchar(100), @@SERVERNAME)
-- 显示获取的这两个参数
select @serverproperty_servername,@servername
--如果@serverproperty_servername和@servername不同(因为你改过计算机名字),再运行下面的
--删除错误的服务器名
EXEC sp_dropserver @server=@servername
--添加正确的服务器名
EXEC sp_addserver @server=@serverproperty_servername, @local='local'
修改这项参数，需要重新启动MSSQLserver和Sqlserveragent服务才能生效。
这样一来就不会在创建复制的过程中出现18482、18483错误了。
3、检查SQL Server企业管理器里面相关的几台SQL Server注册名是否和上面第二点里介绍的srvname一样
不能用IP地址的注册名。
（我们可以删掉IP地址的注册，新建以SQL Server管理员级别的用户注册的服务器名）
这样一来就不会在创建复制的过程中出现14010、20084、18456、18482、18483错误了。
4、检查相关的几台SQL Server服务器网络是否能够正常访问
如果ping主机IP地址可以，但ping主机名不通的时候，需要在
winnt/system32/drivers/etc/hosts (WIN2000)
windows/system32/drivers/etc/hosts (WIN2003)
文件里写入数据库服务器IP地址和主机名的对应关系。
例如：
127.0.0.1 localhost
192.168.0.35 oracledb oracledb
192.168.0.65 fengyu02 fengyu02
202.84.10.193 bj_db bj_db
或者在SQL Server客户端网络实用工具里建立别名，例如:
5、系统需要的扩展存储过程是否存在(如果不存在，需要恢复):
sp_addextendedproc 'xp_regenumvalues',@dllname ='xpstar.dll'
go
sp_addextendedproc 'xp_regdeletevalue',@dllname ='xpstar.dll'
go
sp_addextendedproc 'xp_regdeletekey',@dllname ='xpstar.dll'
go
sp_addextendedproc xp_cmdshell ,@dllname ='xplog70.dll'
接下来就可以用SQL Server企业管理器里[复制]-> 右键选择
->[配置发布、订阅服务器和分发]的图形界面来配置数据库复制了。
下面是按顺序列出配置复制的步骤:
一、建立发布和分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
->[使"@servername"成为它自己的分发服务器,SQL Server将创建分发数据库和日志]
->[制定快照文件夹]-> [自定义配置] -> [否,使用下列的默认配置] -> [完成]
上述步骤完成后, 会在当前"@servername" SQL Server数据库里建立了一个distribion库和
一个distributor_admin管理员级别的用户(我们可以任意修改密码)
服务器上新增加了四个作业:
[ 代理程序历史记录清除: distribution ]
[ 分发清除: distribution ]
[ 复制代理程序检查 ]
[ 重新初始化存在数据验证失败的订阅 ]
SQL Server企业管理器里多了一个复制监视器, 当前的这台机器就可以发布、分发、订阅了。
我们再次在SQL Server企业管理器里[复制]-> 右键选择
->[配置发布、订阅服务器和分发],可以看到类似下图:
我们可以在 [发布服务器和分发服务器的属性] 窗口
-> [发布服务器] -> [新增] -> [确定]
-> [发布数据库] -> [事务]/[合并] -> [确定]
-> [订阅服务器] -> [新增] -> [确定]
把网络上的其它SQL Server服务器添加成为发布或者订阅服务器.
新增一台发布服务器的选项：
我这里新建立的JIN001发布服务器是用管理员级别的数据库用户test连接的，
到发布服务器的管理链接要输入密码的可选框, 默认的是选中的，
在新建的JIN001发布服务器上建立和分发服务器FENGYU/FENGYU的链接的时需要输入distributor_admin用户的密码
到发布服务器的管理链接要输入密码的可选框，也可以不选，
也就是不需要密码来建立发布到分发服务器的链接(这当然欠缺安全，在测试环境下可以使用)
新增一台订阅服务器的选项：
二、新建立的网络上另一台发布服务器(例如JIN001)选择分发服务器
[欢迎使用配置发布和分发向导]->[选择分发服务器]
-> 使用下列服务器(选定的服务器必须已配置为分发服务器) -> [选定服务器](例如FENGYU/FENGYU)
-> [下一步] -> [输入分发服务器（例如FENGYU/FENGYU）的distributor_admin用户的密码两次]
-> [下一步] -> [自定义配置] -> [否，使用下列的默认配置]
-> [下一步] -> [完成] -> [确定]
建立一个数据库复制发布的过程:
[复制] -> [发布内容] -> 右键选择 -> [新建发布]
-> [下一步] -> [选择发布数据库] -> [选中一个待发布的数据库]
-> [下一步] -> [选择发布类型] -> [事务发布]/[合并发布]
-> [下一步] -> [指定订阅服务器的类型] -> [运行SQL Server 2000的服务器]
-> [下一步] -> [指定项目] -> [在事务发布中只可以发布带主键的表] -> [选中一个有主键的待发布的表]
->[在合并发布中会给表增加唯一性索引和 ROWGUIDCOL 属性的唯一标识符字段[rowguid],默认值是newid()]
(添加新列将: 导致不带列列表的 INSERT 语句失败,增加表的大小,增加生成第一个快照所要求的时间)
->[选中一个待发布的表]
-> [下一步] -> [选择发布名称和描述] ->
-> [下一步] -> [自定义发布的属性] -> [否，根据指定方式创建发布]
-> [下一步] -> [完成] -> [关闭]
发布属性里有很多有用的选项：设定订阅到期(例如24小时)
设定发布表的项目属性:
常规窗口可以指定发布目的表的名称，可以跟原来的表名称不一样。
下图是命令和快照窗口的栏目
( SQL Server 数据库复制技术实际上是用insert,update,delete操作在订阅服务器上重做发布服务器上的事务操作
看文档资料需要把发布数据库设成完全恢复模式，事务才不会丢失
但我自己在测试中发现发布数据库是简单恢复模式下，每10秒生成一些大事务，10分钟后再收缩数据库日志，
这期间发布和订阅服务器上的作业都暂停，暂停恢复后并没有丢失任何事务更改 )
发布表可以做数据筛选，例如只选择表里面的部分列:
例如只选择表里某些符合条件的记录, 我们可以手工编写筛选的SQL语句:
发布表的订阅选项，并可以建立强制订阅:
成功建立了发布以后,发布服务器上新增加了一个作业: [ 失效订阅清除 ]
分发服务器上新增加了两个作业:
[ JIN001-dack-dack-5 ] 类型[ REPL快照 ]
[ JIN001-dack-3 ] 类型[ REPL日志读取器 ]
上面蓝色字的名称会根据发布服务器名,发布名及第几次发布而使用不同的编号
REPL快照作业是SQL Server复制的前提条件,它会先把发布的表结构,数据,索引,约束等生成到发布服务器的OS目录下文件
(当有订阅的时候才会生成, 当订阅请求初始化或者按照某个时间表调度生成)
REPL日志读取器在事务复制的时候是一直处于运行状态。(在合并复制的时候可以根据调度的时间表来运行)
建立一个数据库复制订阅的过程:
[复制] -> [订阅] -> 右键选择 -> [新建请求订阅]
-> [下一步] -> [查找发布] -> [查看已注册服务器所做的发布]
-> [下一步] -> [选择发布] -> [选中已经建立发布服务器上的数据库发布名]
-> [下一步] -> [指定同步代理程序登录] -> [当代理程序连接到代理服务器时:使用SQL Server身份验证]
(输入发布服务器上distributor_admin用户名和密码)
-> [下一步] -> [选择目的数据库] -> [选择在其中创建订阅的数据库名]/[也可以新建一个库名]
-> [下一步] -> [允许匿名订阅] -> [是，生成匿名订阅]
-> [下一步] -> [初始化订阅] -> [是，初始化架构和数据]
-> [下一步] -> [快照传送] -> [使用该发布的默认快照文件夹中的快照文件]
(订阅服务器要能访问发布服务器的REPLDATA文件夹，如果有问题，可以手工设置网络共享及共享权限)
-> [下一步] -> [快照传送] -> [使用该发布的默认快照文件夹中的快照文件]
-> [下一步] -> [设置分发代理程序调度] -> [使用下列调度] -> [更改] -> [例如每五分钟调度一次]
-> [下一步] -> [启动要求的服务] -> [该订阅要求在发布服务器上运行SQLServerAgent服务]
-> [下一步] -> [完成] -> [确定]
成功建立了订阅后，订阅服务器上新增加了一个类别是[REPL-分发]作业(合并复制的时候类别是[REPL-合并])
它会按照我们给的时间调度表运行数据库同步复制的作业
查看它的历史记录运行情况，例图：
在分发服务器的[复制监视器]->[发布服务器]->[发布名称]->[日志读取器]->右键选择->[代理程序历史记录], 例图：
三、SQL Server复制配置好后, 可能出现异常情况的实验日志:
1.发布服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制没有多大影响
中断期间,分发和订阅都接收到没有复制的事务信息
2.分发服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制有一些影响
中断期间,发布服务器的事务排队堆积起来
(如果设置了较长时间才删除过期订阅的选项, 繁忙发布数据库的事务日志可能会较快速膨胀),
订阅服务器会因为访问不到发布服务器,反复重试
我们可以设置重试次数和重试的时间间隔(最大的重试次数是9999, 如果每分钟重试一次,可以支持约6.9天不出错)
分发服务器sql server服务启动,网络接通以后,发布服务器上的堆积作业将按时间顺序作用到订阅机器上:
会需要一个比较长的时间(实际上是生成所有事务的insert,update,delete语句,在订阅服务器上去执行)
我们在普通的PC机上实验的58个事务100228个命令执行花了7分28秒.
3.订阅服务器断网,sql server服务关闭,重启动,关机的时候,对已经设置好的复制影响比较大,可能需要重新初试化
我们实验环境(订阅服务器)从18:46分意外停机以, 第二天8:40分重启动后,
已经设好的复制在8:40分以后又开始正常运行了, 发布服务器上的堆积作业将按时间顺序作用到订阅机器上
但复制管理器里出现快照的错误提示, 快照可能需要重新初试化,复制可能需要重新启动.
(我们实验环境的机器并没有进行快照初试化,复制仍然是成功运行的)
四、删除已经建好的发布和定阅可以直接用delete删除按钮
我们最好总是按先删定阅，再删发布，最后禁用发布的顺序来操作。
如果要彻底删去SQL Server上面的复制设置, 可以这样操作:
[复制] -> 右键选择 [禁用发布] -> [欢迎使用禁用发布和分发向导]
-> [下一步] -> [禁用发布] -> [要在"@servername"上禁用发布]
-> [下一步] -> [完成禁用发布和分发向导] -> [完成]
我们也可以用T-SQL命令来完成复制中发布及订阅的创建和删除, 选中已经设好的发布和订阅, 按属标右键
可以[生成SQL脚本]。(这里就不详细讲了, 后面推荐的网站内有比较详细的内容)
当你试图删除或者变更一个table时，出现以下错误
Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'object_name' because it is being used for replication.
比较典型的情况是该table曾经用于复制，但是后来又删除了复制
处理办法：
select * from sysobjects where replinfo >'0'
sp_configure 'allow updates', 1
go
reconfigure with override
go
begin transaction
update sysobjects set replinfo = '0' where replinfo >'0'
commit transaction
go
rollback transaction
go
sp_configure 'allow updates', 0
go
reconfigure with override
go
疑问:
在合并复制配置完全后，如果同步代理停止了。我要在程序中去重新启动合并复制的同步代理。请问使用什么命令或存储过程呢？
解决办法:(朱二)
sp_start_job
指示 SQL Server 代理程序立即执行作业。
示例
下例启动名为 Nightly Backup 的作业。
USE msdb
EXEC sp_start_job @job_name = 'Nightly Backup'
2、日志还原功能、、、
/*--说明：
下面的代码演示了如何利用日志还原功能，将主数据库中的数据变化及时反馈到备用数据库中
备用数据库的数据可以随时用于查询，但不能被更新（备用数据库只读）。
--*/
--首先,创建一个演示用的数据库(主数据库)
CREATE DATABASE Db_test
ON
( NAME = Db_test_DATA,
FILENAME = 'c:/Db_test.mdf' )
LOG ON
( NAME = Db_test_LOG,
FILENAME = 'c:/Db_test.ldf')
GO
--对数据库进行备份
BACKUP DATABASE Db_test TO DISK='c:/test_data.bak' WITH FORMAT
GO
--把数据库还原成备用数据库(演示主数据库与这个备用数据库之间的同步)
RESTORE DATABASE Db_test_bak FROM DISK='c:/test_data.bak'
WITH REPLACE,STANDBY='c:/db_test_bak.ldf'
,MOVE 'Db_test_DATA' TO 'c:/Db_test_data.mdf'
,MOVE 'Db_test_LOG' TO 'c:/Db_test_log.ldf'
GO
--启动 SQL Agent 服务
EXEC master..xp_cmdshell 'net start sqlserveragent',no_output
GO
--创建主服务器数据训与备用服务器数据库之间同步的作业
DECLARE @jogid uniqueidentifier
EXEC msdb..sp_add_job
@job_id = @jogid OUTPUT,
@job_name = N'数据同步处理'
--创建同步处理步骤
EXEC msdb..sp_add_jobstep
@job_id = @jogid,
@step_name = N'数据同步',
@subsystem = 'TSQL',
@command = N'
--主数据库中进行日志备份
BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT
--备用数据库中还原主数据库的日志备份(应用主数据库中的最新变化
--实际应该时主数据库备份与备用数据库的还原作业应该分别在主服务器和备用服务器上建立，并且备份文件应该放在主服务器和备用都能访问的共享目录中
RESTORE LOG Db_test_bak FROM DISK=''c:/test_log.bak'' WITH STANDBY=''c:/test_log.ldf''',
@retry_attempts = 5,
@retry_interval = 5
--创建调度(每分钟执行一次)
EXEC msdb..sp_add_jobschedule
@job_id = @jogid,
@name = N'时间安排',
@freq_type=4,
@freq_interval=1,
@freq_subday_type=0x4,
@freq_subday_interval=1,
@freq_recurrence_factor=1
-- 添加目标服务器
EXEC msdb.dbo.sp_add_jobserver
@job_id = @jogid,
@server_name = N'(local)'
GO
--通过上述处理,主数据库与备用数据库之间的同步关系已经设置完成
--下面开始测试是否能实现同步
--在主数据库中创建一个测试用的表
CREATE TABLE Db_test.dbo.TB_test(ID int)
GO
--等待1分钟30秒（由于同步的时间间隔设置为1分钟,所以要延时才能看到效果）
WAITFOR DELAY '00:01:30'
GO
--查询一下备用数据库,看看同步是否成功
SELECT * FROM Db_test_bak.dbo.TB_test
/*--结果:
ID
-----------
（所影响的行数为 0 行）
--*/
--测试成功
GO
--最后删除所有的测试
DROP DATABASE Db_test,Db_test_bak
EXEC msdb..sp_delete_job @job_name=N'数据同步处理'
GO
/*===========================================================*/
/*--服务器档机处理说明
使用这种方式建立的数据库同步，当主数据库不可用时（例如，主数据库损坏或者停机检修）
可以使用以下两种方法使备用数据库可用。
--*/
--1. 如果主数据库损坏，无法备份出最新的日志，可以直接使用下面的语句使备用数据库可读写（丢失最近一次日志还原后的所有数据）。
--RESTORE LOG Db_test_bak WITH RECOVERY
--2. 如果主数据库可以备份出最新日志，则可以使用下面的语句。
--先备份主数据库的最新的事务日志
--BACKUP LOG Db_test TO DISK=''c:/test_log.bak'' WITH FORMAT
--再在备用数据库中恢复最新的事务日志，并且使备用数据库可读写（升级为主数据库）
--RESTORE LOG Db_test_bak FROM DISK='c:/test_log.bak'
简单地说:
1. 你的sql服务要使用指定的windows用户登陆, 而不能使用"本地系统帐户"
2. 用于登陆sql服务的用户要求对共享目录具有所有权限
3. 如果你的电脑没有加入到域, 还必须保证源和目标服务器的sql服务设置的登陆用户是一样的(用户名和密码都一样)
网络备份主要是权限设置问题, 参考下面的备份文件共享目录权限设置方法去解决目录的共享权限就可以了
下面假设是假设A服务器上的数据库备份到B服务器上的共享目录权限设置(两台服务器应该在局域网内,允许目录共享访问)::
1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:
我的电脑
--控制面板
--管理工具
--计算机管理
--用户和组
--右键用户
--新建用户
--建立一个隶属于administrator组的登陆windows的用户
2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作:
我的电脑--D:/ 新建一个目录,名为: BAK
--右键这个新建的目录
--属性--共享
--选择"共享该文件夹"
--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限
--确定
3.设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户
开始--程序--管理工具--服务
--右键 MSSQLSERVER
--属性--登陆--选择"此账户"
--输入或者选择第一步中创建的windows登录用户名
--"密码"中输入该用户的密码
--确定
--同样的方法设置 SQLSERVERAGENT
4.在A机器上完成对B机器BAK目录的映射
5.查询分析器中执行下面的语句,检验是否成功:
exec master..xp_cmdshell 'dir 映射的盘符'
6.A服务器上做备份计划
备注:创建一个新的用户只是为了让MSSQLSERVER服务的启动帐户与共享目录的有效访问同名且密码相同,这样才能通过验证(所以你也可以用其他有效的用户来代替,只需要满足用户名和密码相同,并且拥有足够的权限)
9141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
