資料庫增量同步(MS SQL Server)
-----------------------------------------------------------------------
--支援原創,轉載時請保留下面,以供大家加我MSN,增強交流,共同學習.
--姜庭華 msn: [email protected]
--部落格:http://blog.csdn.net/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,
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
--支援原創,轉載時請保留下面,以供大家加我MSN,增強交流,共同學習.
--姜庭華 msn: [email protected]
--部落格:http://blog.csdn.net/jaimejth
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 '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'' truncate table '[email protected]_db+'.dbo.'[email protected]_name+''''
execute (@exec_sql)
if @@error<>0
goto tran_error
set @exec_sql= ' insert into '[email protected]_server+'.'[email protected]_db+'.dbo.'[email protected]_name+' select * from '+case when @is_local=1 then '' else @source_server+'.' [email protected]_db+'.dbo.'[email protected]_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 '[email protected]_server+'.'[email protected]_db+'.dbo.sysobjects a'+
' join '[email protected]_server+'.'[email protected]_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''[email protected]_name+''''+' and '+
' a.xtype = ''U'''+
' and exists(SELECT 1'+
' FROM '[email protected]_server+'.'[email protected]_db+'.dbo.sysobjects c'+
' WHERE c.xtype = ''PK'''+
' AND c.name IN (SELECT name '+
' FROM '[email protected]_server+'.'[email protected]_db+'.dbo.sysindexes e'+
' WHERE e.indid IN (SELECT indid'+
' FROM '[email protected]_server+'.'[email protected]_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 @[email protected]_sql+'' s.''[email protected]_key+''=d.''[email protected]_key+'' and'''+
' set @[email protected][email protected]_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+'.' [email protected]_db+'.dbo.'[email protected]_name+' WITH (NOLOCK) where '+isnull(@tag_column,'')+'>='''[email protected]_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 '[email protected]_server+'.'[email protected]_db+'.dbo.sysobjects a'+
' join '[email protected]_server+'.'[email protected]_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''[email protected]_name+''''+' and '+
' a.xtype = ''U'''+
' and b.colstat= 1)'+
' begin '+
' select @filed=b.name'+
' from '[email protected]_server+'.'[email protected]_db+'.dbo.sysobjects a'+
' join '[email protected]_server+'.'[email protected]_db+'.dbo.syscolumns b ON a.id = b.id '+
' where a.name='''[email protected]_name+''''+' and '+
' a.xtype = ''U'''+
' and b.colstat= 1'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''1'''''''' RECONFIGURE WITH OVERRIDE '''''''+
' execute(@exe_sql)'+
' set @exe_sql=''update '[email protected]_server+'.'[email protected]_db+'.dbo.'+'syscolumns set colstat=0 where colstat=1 and id in (select id from '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sysobjects where name='''''[email protected]_name+''''') and name=''''''[email protected]+'''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' sp_configure ''''''''allow update'''''''',''''''''0'''''''' RECONFIGURE WITH OVERRIDE '''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' select * into '[email protected]_name+'_jaime from '[email protected]_db+'.dbo.'[email protected]_name+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' drop table '[email protected]_db+'.dbo.'[email protected]_name+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' exec sp_rename '''''''''[email protected]_db+'.dbo.'[email protected]_name+'_jaime '''''''','''''''''[email protected]_name+''''''''''+''''''''+
' execute(@exe_sql)'+
' set @exe_sql=''execute '[email protected]_server+'.'[email protected]_db+'.dbo.'+'sp_executesql N'''' ALTER TABLE '[email protected]_name+' WITH NOCHECK ADD CONSTRAINT pk_'[email protected]_name+'_pk PRIMARY KEY CLUSTERED (''[email protected]+'')'+''''''''+
' execute(@exe_sql)'+
' end '+
' set @exe_sql='''+
' delete '[email protected]_server+'.'[email protected]_db+'.dbo.'[email protected]_name+
' from '[email protected]_server+'.'[email protected]_db+'.dbo.'[email protected]_name+' d ,#temp_date s '+
' where ''[email protected]_sql'+
' exec (@exe_sql'+')'+
' insert into '[email protected]_server+'.'[email protected]_db+'.dbo.'[email protected]_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 [email protected]
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='表: '[email protected]_name+' 傳輸失敗'
RAISERROR (@error_txt,16, 1)
return
![Apache配置SSLApache配置SSL[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)