数据库增量同步.doc

数据库增量同步(二台SQL Server服务器的数据库之间增量传输数据) 数据库增量同步(MS SQL Server)
------------------------------------------------------------------------------------------------------------------------------------------------------------------
--支持原创,转载时请保留下面,以供大家加我MSN,增强交流,共同学习.
--姜庭华 msn: jaimejth@
--博客:

数据库增量同步必须具备以下条件
一.每张表必须有主键
二.每张表必须有一个最后更新日期栏位(时间类型).(也就是说.在程序及业务处理中,如果对某条数据做了更新就必须更新最后更新日期栏位.这在现有很多系统都有这个栏位,主要便于后台管理.)

执行步骤:

一.首先建立表sys_tran_info,这张表是传输配置的基本表.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_tran_info]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sys_tran_info]

CREATE TABLE [dbo].[sys_tran_info] (
[id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , --自增列
[source_server] [varchar] (50) NULL , --源服务器(链接服务器名或本机为空)
[source_db] [varchar] (50) NULL , --源数据库名称
[target_server] [varchar] (50) NULL , --目的服务器(链接服务器名)
[target_db] [varchar] (50) NULL , --目的数据库名称
[table_name] [varchar] (100) NOT NULL , --需要同步的表
[is_close] [int] NOT NULL , --是否关闭该记录
[is_tranall] [int] NOT NULL , --是否传输该表整个表记录
[tag_column] [char] (100) NULL , --标志列，一般是时间类型列，记录最后更新日期,如果不输入，则是每次都传全表数据。（主要用此列做为增量更新的标志）
[is_complete] [int] NULL, --是否完成。当同步时，会同步更新此栏位。确定该行该表是否同步完成。
[complete_date] datetime null
) ON [PRIMARY]

ALTER TABLE [dbo].[sys_tran_info] WITH NOCHECK ADD
CONSTRAINT [DF_sys_tran_info_is_disable] DEFAULT (0) FOR [is_close],
CONSTRAINT [DF_sys_tran_info_is_alldata] DEFAULT (1) FOR [is_tranall],
CONSTRAINT [DF_sys_tran_info_is_increment] DEFAULT (0) FOR [is_complete]
*/

二.配置表sys_tran_info中的信息,将需要传输同步的表插入此表中。
(当然该表的中数据你可以手动一条一条的增加，以下只是提供批量增加的方法)
1.先批量将有主键的表的数据插入。
insert into sys_tran_info(source_server,source_db,target_server,target_db,table_name,is_close,is_tranall,is_complete)
select distinct
'[192.168.0.1]', --源链接服务器名(如果以下存储过程的参数@is_local为1，此外为空)
'[erp_db]', --源数据库名
'[192.168.0.2]', --目的链接服务器名
'[erp_db_bak]', --目的数据库名
a.name as table_name,
1,
1,
0
from sysobjects a
join syscolumns b ON a.id = b.id
where a.xtype = 'U'
and exists(SELECT 1 FROM sysobjects WHERE xtype = 'PK'
AND name IN (SELECT name FROM sysindexes
WHERE indid IN (SELECT indid
FROM sysindexkeys WHERE id = b.id AND colid = b.colid)))
2.注意，这样批量插入数据是没有配置标志列tag_column，所以根据表sys_tran_info中栏位is_tranalle,默认是1传输全部，只有为0时则会根据标志列增量传输。
需要手动根据具体情况配置（例如：有的最后更新日期栏位是update_date）你可以根据你的实际情况配置最后更新时间的栏位，因为你的名字也许不会取名为update_date。

三.创建以下存储过程。（跟表sys_tran_info创建在一个数据库下面）

create procedure usp_tran_data
@is_local int=1,
@begin_date datetime,
@is_continue int=0
as

declare @id numeric(18,0),
@source_server varchar(50),
@source_db varchar(50),
@target_server varchar(50),
@target_db varchar(50),
@table_name varchar(100),
@is_tranall int,
@tag_column varchar(100),
@exec_sql varchar(8000),
@begin_date_str varchar(30),
@error_txt varchar(100)

set @begin_date_str=convert(varchar(30),@begin_date,120)
set @exec_sql=''
if @is_continue=0
update sys_tran_info set is_complete=0,complete_date=null

declare table_cursor CURSOR FAST_FORWARD FOR
select id, source_server, source_db, target_server, target_db, table_name, is_tranall, tag_column
from sys_tran_info
where is_close=0
and is_complete in (select case when @is_continue=1 then 0 when @is_continue=0 then 1 end
union
select case when @is_continue=0 then 0 end )
order by id

OPEN table_cursor
FETCH NEXT FROM table_cursor
INTO @id,@source_server,@source_db,@target_server,@target_db,@table_name,@is_tranall,@tag_column
WHILE @@FETCH_STATUS = 0
begin
if @is_tranall=1
begin
waitfor delay '00:00:03'
set @exec_sql='execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'' truncate table '
+@target_db+'.dbo.'+@table_name+''''
execute (@exec_sql)
if @@error<>0
goto tran_error
set @exec_sql= ' insert into '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' select * from '+case when @is_local=1 then '' else @source_server+'.' end+@source_db+'.dbo.'+@table_name+' WITH (NOLOCK)'
execute(@exec_sql)
if @@error<>0
goto tran_error
end
else
begin
if @tag_column is null
continue
waitfor delay '00:00:03'
--取主键关系
set @exec_sql=
' declare @master_key varchar(100)'+
' declare @mkey_sql varchar(1000)'+
' declare @mkey varchar(500)'+
' set @mkey_sql='''''+
' set @mkey='''''+
' declare master_key_cursor cursor fast_forward for'+
' select b.name AS field'+
' from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
' join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''+@table_name+''''+' and '+
' a.xtype = ''U'''+
' and exists(SELECT 1'+
' FROM '+@target_server+'.'+@target_db+'.dbo.sysobjects c'+
' WHERE c.xtype = ''PK'''+
' AND c.name IN (SELECT name '+
' FROM '+@target_server+'.'+@target_db+'.dbo.sysindexes e'+
' WHERE e.indid IN (SELECT indid'+
' FROM '+@target_server+'.'+@target_db+'.dbo.sysindexkeys f'+
'WHERE f.id = b.id AND f.colid = b.colid))) '+
' OPEN master_key_cursor'+
' FETCH NEXT FROM master_key_cursor INTO @master_key'+
' WHILE @@FETCH_STATUS = 0'+
' begin'+
' set @mkey_sql=@mkey_sql+'' s.''+@master_key+''=d.''+@master_key+'' and'''+
' set @mkey=@mkey+@master_key+'','''+
' FETCH NEXT FROM master_key_cursor INTO @master_key'+
' end'+
' CLOSE master_key_cursor'+
' DEALLOCATE master_key_cursor'+
' set @mkey_sql=left(@mkey_sql,len(@mkey_sql)-3)'+
' set @mkey=left(@mkey,len(@mkey)-1)'+
' select * into #temp_date from '+case when @is_local=1 then '' else @source_server+'.' end+@source_db+'.dbo.'+@table_name+' WITH (NOLOCK) where '+isnull(@tag_column,'')+'>='''+@begin_date_str+''''+
' if not exists(select 1 from #temp_date)'+
' return '+
' declare @exe_sql varchar(2000)'+
' declare @filed varchar(100)'+
' set @exe_sql='''''+
' if exists(select b.name'+
' from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
' join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''+@table_name+''''+' and '+
' a.xtype = ''U'''+
' and b.colstat= 1)'+
' begin '+
' select @filed=b.name'+
' from '+@target_server+'.'+@target_db+'.dbo.sysobjects a'+
' join '+@target_server+'.'+@target_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''+@table_name+''''+' and '+
' a.xtype = ''U'''+
' and b.colstat= 1'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''1'''''''' RECONFIGURE WITH OVERRIDE '''''''+
' execute(@exe_sql)'+
' set @exe_sql=''update '+@target_server+'.'+@target_db+'.dbo.'+'syscolumns set colstat=0 where colstat=1 and id in (select id from '+@target_server+'.'+@target_db+'.dbo.'+'sysobjects where name='''''+@table_name+''''') and name=''''''+@filed+'''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''0'''''''' RECONFIGURE WITH OVERRIDE '''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' select * into '+@table_name+'_jaime from '+@target_db+'.dbo.'+@table_name+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' drop table '+@target_db+'.dbo.'+@table_name+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' exec sp_rename '''''''''+@target_db+'.dbo.'+@table_name+'_jaime '''''''','''''''''+@table_name+''''''''''+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '+@target_server+'.'+@target_db+'.dbo.'+'sp_executesql N'''' ALTER TABLE '+@table_name+' WITH NOCHECK ADD CONSTRAINT pk_'+@table_name+'_pk PRIMARY KEY CLUSTERED (''+@mkey+'')'+''''''''+
' execute(@exe_sql)'+
' end '+
' set @exe_sql='''+
' delete '+@target_server+'.'+@target_db+'.dbo.'+@table_name+
' from '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' d ,#temp_date s '+
' where ''+@mkey_sql'+
' exec (@exe_sql'+')'+
' insert into '+@target_server+'.'+@target_db+'.dbo.'+@table_name+' select * from #temp_date '+
' drop table #temp_date '
execute(@exec_sql)
if @@error<>0
begin
goto tran_error
end
end
update sys_tran_info set is_complete=1,complete_date=getdate() where id=@id
FETCH NEXT FROM table_cursor INTO @id,@source_server,@source_db,@target_server,@target_db,@table_name,@is_tranall,@tag_column
end
CLOSE table_cursor
DEALLOCATE table_cursor
return

tran_error:
CLOSE table_cursor
DEALLOCATE table_cursor
set @error_txt='表: '+@table_name+' 传输失败'
RAISERROR (@error_txt,16, 1)
return

Sql Server 数据库字典

--1.SqlServer2000 数据库字典--表结构.sql

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, a.colorder

--SqlServer2005 数据库字典--表结构.sql

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND
g.name = 'MS_Description' LEFT OUTER JOIN
sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, 字段序号

经在sql server 2005上测试，

只要将一个SQL的最后两行替换如下，即可正确运行．
dbo.dtproperties g ON a.id = g.id AND a.colid = g.objectid LEFT OUTER JOIN

dbo.dtproperties f ON d.id = f.id AND f.objectid = 0
2. SqlServer数据库字典--索引.sql

SELECT TOP 100 PERCENT --a.id,
CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名,
CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名,
b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')
WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id,
a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一,
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束,
a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间
FROM dbo.sysindexes a INNER JOIN
dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id,
N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno

3. SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则.sql

SELECT DISTINCT
TOP 100 PERCENT isnull(p.name,'') AS 父对象, o.xtype,
CASE o.xtype WHEN 'C' THEN 'CHECK 约束' WHEN 'D' THEN '默认值或DEFAULT约束'
WHEN 'F' THEN 'FOREIGNKEY约束' WHEN 'L' THEN '日志' WHEN 'FN' THEN '标量函数'
WHEN 'IF' THEN '内嵌表函数' WHEN 'P' T