SQL数据同步

如何同步两个数据,包括同一个SQL服务器和不同一个SQL服务.

一般采用发布/订阅的方法,这个联机帮助上说得很清楚了,不是本贴的内容.

本贴提供两种手工同步的方法.
即时同步和定时同步.


已知的问题:
由于使用了分布式事务处理,而这个在部分情况(具体原因未查到,有很多贴子讨论这个问题,都没有找到原因)下是不能生效的.
因此,在不能正常使用的情况下,可以取消分布式事务处理
这个不影响处理,只是无法保证数据处理的完整性.

#2  

--即时同步两个表的实例:

--测试环境:SQL2000,远程主机名:xz,用户名:sa,密码:无,数据库名:test

--创建测试表,不能用标识列做主键,因为不能进行正常更新
--在本机上创建测试表,远程主机上也要做同样的建表操作,只是不写触发器
if exists (select * from dbo.sysobjects where id = object_id(N'[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [test]

create table test(id int not null constraint PK_test primary key
,name varchar(10))
go

--创建同步的触发器
create trigger t_test on test
for insert,update,delete
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 openrowset('sqloledb','xz';'sa';'',test.dbo.test)
where id in(select id from deleted)
insert into openrowset('sqloledb','xz';'sa';'',test.dbo.test)
select * from inserted
commit tran
go

--插入数据测试
insert into test
select 1,'aa'
union all select 2,'bb'
union all select 3,'c'
union all select 4,'dd'
union all select 5,'ab'
union all select 6,'bc'
union all select 7,'ddd'

--删除数据测试
delete from test where id in(1,4,6)

--更新数据测试
update test set name=name+'_123' where id in(3,5)

--显示测试的结果
select * from test a full join
openrowset('sqloledb','xz';'sa';'',test.dbo.test) b on a.id=b.id

#3  

--定时同步服务器上的数据

--例子:
--测试环境,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

??SQLServer2000同步复制技术实现
??===================================================
??具体实施步骤：
??一、 预备工作
??1.发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
??我的电脑
?? --控制面板
?? --管理工具
?? --计算机管理
?? --用户和组
?? --右键用户
?? --新建用户
?? --建立一个隶属于administrator组的登陆windows的用户（SynUser）
??
??2.在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
??
??我的电脑--D:\ 新建一个目录,名为: PUB
?? --右键这个新建的目录
?? --属性--共享
?? --选择"共享该文件夹"
?? --通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
?? --确定
??
??3.设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
??
??开始--程序--管理工具--服务
?? --右键SQLSERVERAGENT
?? --属性--登陆--选择"此账户"
?? --输入或者选择第一步中创建的windows登录用户名（SynUser）
?? --"密码"中输入该用户的密码
??
??4.设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
??
??企业管理器
?? --右键SQL实例--属性
?? --安全性--身份验证
?? --选择"SQL Server 和 Windows"
?? --确定
??
??5.在发布服务器和订阅服务器上互相注册
??企业管理器
?? --右键SQL Server组
?? --新建SQL Server注册...
?? --下一步--可用的服务器中,输入你要注册的远程服务器名 --添加
?? --下一步--连接使用,选择第二个"SQL Server身份验证"
?? --下一步--输入用户名和密码（SynUser）
?? --下一步--选择SQL Server组,也可以创建一个新组
?? --下一步--完成
??
??6.对于只能用IP,不能用计算机名的,为其注册服务器别名（此步在实施中没用到）
?? (在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
??开始--程序--Microsoft SQL Server--客户端网络实用工具
?? --别名--添加
?? --网络库选择"tcp/ip"--服务器别名输入SQL服务器名
?? --连接参数--服务器名称中输入SQL服务器ip地址
?? --如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
??
??二、 正式配置
??
??1、配置发布服务器
??
??打开企业管理器，在发布服务器（B、C、D）上执行以下步骤:
??
??(1) 从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导
??(2) [下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器（选择自己）
??(3) [下一步] 设置快照文件夹
??采用默认\\servername\Pub
??**(4) [下一步] 自定义配置
??可以选择:
??是,让我设置分发数据库属性启用发布服务器或设置发布设置
??否,使用下列默认设置（推荐）
??(5) [下一步] 设置分发数据库名称和位置 采用默认值
??(6) [下一步] 启用发布服务器 选择作为发布的服务器
??(7) [下一步] 选择需要发布的数据库和发布类型
??(8) [下一步] 选择注册订阅服务器
??(9) [下一步] 完成配置
??
??2、创建出版物
??
??发布服务器B、C、D上
??(1)从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令
??(2)选择要创建出版物的数据库，然后单击[创建发布]
??(3)在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。
??对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
??
??(4)单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,
??SQLSERVER允许在不同的数据库如 ORACLE或ACCESS之间进行数据复制。
??但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
??
??(5)单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
??
??注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表
??
??
??(6)选择发布名称和描述
??
??**(7)自定义发布属性
??
??向导提供的选择:
??是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性
??否 根据指定方式创建发布
??
??建议采用自定义的方式
??
??(8)[下一步] 选择筛选发布的方式
??(9)[下一步] 可以选择是否允许匿名订阅
??
??1)如果选择署名订阅,则需要在发布服务器上添加订阅服务器
??方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
??
??否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
??如果仍然需要匿名订阅则用以下解决办法
??[企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅
??
??2)如果选择匿名订阅,则配置订阅服务器时不会出现以上提示
??
??
??(10)[下一步] 设置快照 代理程序调度
??(11)[下一步] 完成配置
??
??
??当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库。

UNION ALL SELECT 2,'BB'

CREATE TABLE #b(id int,b text)
INSERT #b SELECT 1,'XX'

--用#a.a替换#b.b的数据,条件是#a.id=#b.id
UPDATE b SET b=a.a
FROM #a a,#b b
WHERE a.id=b.id

--把#a的数据复制到#b,条件是#a.id不在#b.id中
INSERT #b(id,b) SELECT id,a FROM #a a
WHERE NOT EXISTS(SELECT * FROM #b WHERE id=a.id)

--显示处理结果
SELECT * FROM #b
DROP TABLE #a,#b
/*--结果
id b
---------------- -------
1 AA
2 BB
--*/
GO


/*===============================================*/


--2. 将tb表中的col1及col2列中的数据合并到col列中，并且在合并的数据前后加上列标识。
--测试数据
CREATE TABLE tb(col text,col1 text,col2 text)
INSERT tb SELECT 'a' ,NULL,'c2'
UNION ALL SELECT NULL,'b2','c2'
UNION ALL SELECT '' ,'b3',NULL
GO

--更新处理
DECLARE @p binary(16),@p1 binary(16),@p2 binary(16)
DECLARE tb CURSOR LOCAL
FOR
SELECT TEXTPTR(col),TEXTPTR(col1),TEXTPTR(col2) FROM tb
OPEN tb
FETCH tb INTO @p,@p1,@p2
WHILE @@FETCH_STATUS=0
BEGIN
IF TEXTVALID('tb.col',@p)=0
BEGIN
UPDATE tb SET col='' WHERE CURRENT OF tb
UPDATE tb SET @p=TEXTPTR(col) WHERE CURRENT OF tb
END
IF TEXTVALID('tb.col1',@p1)=1
BEGIN
UPDATETEXT tb.col @p NULL 0 ' <col1> '
UPDATETEXT tb.col @p NULL 0 tb.col1 @p1
UPDATETEXT tb.col @p NULL 0 ' </col1> '
END
IF TEXTVALID('tb.col2',@p2)=1
BEGIN
UPDATETEXT tb.col @p NULL 0 ' <col2> '
UPDATETEXT tb.col @p NULL 0 tb.col2 @p2
UPDATETEXT tb.col @p NULL 0 ' </col2> '
END
FETCH tb INTO @p,@p1,@p2
END
CLOSE tb
DEALLOCATE tb
GO

--显示结果
SELECT * FROM tb
DROP TABLE tb
/*--结果
col col1 col2
-------------------------------------- ----------------------- ----------------------
a <col2> c2 </col2> NULL c2
<col1> b2 </col1> <col2> c2 </col2> b2 c2
<col1> b3 </col1> b3 NULL
--*/

网友回复:选择发布数据库中表的时候。


就些表不能被选上。。。

这是什么原因

复制的时候 提示数据复制太大 不能被复制。

这又怎么办


网友回复:请问一下潇洒老乌龟更新文本类型数据时不是用updatetext吗？
为什么在这里用update也可以？
网友回复:主键一般是不能更新的.更新是要注重下
网友回复:1. 你用的什么复制类型? 除快照复制外, 其他复制类型均要求有主键, 检查是否满足上述条件
2. 说数据复制太大。。 这是复制已经配置的情况下出现的, 还是在复制配置过程中出现的. 你的表有多大? 像我现在复制的复制表, 几千万数据的也有不少, 但没有碰到过说数据复制太大的问题
网友回复:我有个客户的数据库都30个G了，用快照还没问题呢。
网友回复:为什么我用快照很多表不能够发布??

用快照复制一直默认的配置不行吗??
网友回复: 有没有人知道啊.
网友回复:回者有分..
绝对结帖..
大家帮帮忙
网友回复:帮顶一下，关注。

没见过这个“数据复制太大 不能被复制”的问题。
能否把原本的错误信息贴出来？

复制对表有些额外的要求，你那些不能被选的表应该就是不符合某些条件的。
对照楼上各位的帖子检查一下。

网友回复:是不是做一次快照发布就行了??
以后订阅就会自动更新数据?
网友回复:你用的事务复制吧
本篇文章来源于 www.itzhe.cn 原文链接：http://www.itzhe.cn/article/20080104/43907_5.html

上海力源职业培训中心的,下面是我们培训中心的数据库管理员培训课程介绍. 如果您现在失业或应届毕业生，实习，而且还是上海户口，今年没有接受过政府补贴培训．您能享受政府补贴１００％，如考试优秀我们还推荐工作，如在职可享受５０％政府补贴.
*目前SQL中级针对在职人员学费全免,考务费和书费自理.

以下是政府补贴数据库管理员课程：
上海力源职业技术培训中心是上海市职业培训指导中心和上海市劳动局的引进办学单位、也是上海市数据库用户协会内部培训机构、IBM技术培训合作伙伴。重点从事数据库、Java、.NET等以及其它IT技术的专业培训。培训总人数和总体质量，位居各培训机构之首。 合格率占整个上海市培训中心的95%

力源的宗旨: 能力 = 实用 + 质量 + 实践 + 经验

课程名称:        SQL Server数据库管理员(中级/四级)
课程目的:        掌握中小型企业数据库操作与开发,高效管理中小型企业数据库
课程周期:        A班：每周一,三
             B班：每周双休
考核发证:        国家职业资格SQL Server数据库管理员（中级/四级）
上课时间：   周六或周日全天
=============================================================
课程名称:        SQL Server数据库高级管理员(高级/三级)
课程目的:        高效规划建设中小型企业数据库,获得国家数据库助理管理师资质
课程周期:        A班：每周二,四
             B班：每周双休
考核发证:        国家职业资格SQL Server数据库助理设计师（高级/三级）
上课时间：   周六或周日全天
=============================================================
课程名称:        Oracle数据库管理员(中级/四级)
课程目的:        掌握大型企业数据库操作与开发,高效管理大型企业数据库   
课程周期:        A班：每周一,三
             B班：每周双休      
考核发证:        国家职业资格Oracle数据库管理员（中级/四级）
上课时间：   周六或周日全天
=============================================================
课程名称:        Oracle数据库高级管理员(高级/三级)
课程目的:        高效规划建设大型企业数据库,获得国家数据库助理管理师资质
课程周期:        A班：每周二,四
             B班：每周双休
考核发证:        国家职业资格Oracle数据库助理设计师（高级/三级）
上课时间：   周六或周日全天
*力源培训的网站:www.360pass.com
*上海劳动局查询网站: http://www.12333sh.gov.cn/wsbs/zypxjd/pxjg/index.shtml
联系方式：15801929576   (陈老师)
*报名地址: 上海市静安区南京西路1025弄83号甲203室  （地铁2号线南京西路站附近）
政府补贴培训课程

方法挺全的
好好看看
谢谢楼主
还有
顺便说一下
楼上的” carrie“发的什么广告，太不好了