WebService同步两台远程SQLSERVER2000数据库

WebService同步两台远程SQLSERVER2000数据库 徐龙 2007-12-13 Sqlserver2000对于数据库的同步内部提供了多种方法，但是由于两台服务器分别在中国和美国. 如果直接运用SqlServer2000的内建同步机制，势必要将两台服务器暴露在internet之下，出于对网络安全和

 WebService同步两台远程SQLSERVER2000数据库
徐龙 2007-12-13

Sqlserver2000对于数据库的同步内部提供了多种方法，但是由于两台服务器分别在中国和美国.
如果直接运用SqlServer2000的内建同步机制，势必要将两台服务器暴露在internet之下，出于对网络安全和网络带宽的考虑，首先否定此种办法。
我采用的方法是通过在源服务器里加入 trace ，将源服务器中对数据库内容变动的所有sql语句都统一记录下来。然后在WEB服务器上部署webservice,目标服务器端可以通过一个winform程序调用(按同步要求定时执行)webservice取得所有sql语句，然后在目的服务器中将sql回放执行，从而达到两端数据库同步的功能.(如下图)

如此以来不必暴露sqlserver，提高了数据的发全性，采用webservice 可以工作在80端口，对防火墙没有特殊要求,同步设置上更加灵活(当然对于webservice要采取适当保护措施，传输过程中数据要全程加密。)。

OK,现在的问题是如何在源服务器里加入 trace ,得到服务器所有执行过的sql语句，我们都知道SQLSERVER 有个工具 profiler,我们经常都会使用它观察sql执行情况，做性能调校等。通过对它的研究我找到了几个内部函数打破难题，成功实现了这个方案.
sp_trace_create  //建立一个跟踪，并能设置选项，但是它保存在一个文件里
::fn_trace_gettable //通过这个方法可以解析跟踪保存的文件
::fn_trace_getinfo   //获取服务器中的trace资料
sp_procoption     //注册函数能在数据库服务器启动时就运行

下面我贴出完整的跟踪记录的SQL代码,至于webservice和winform的程序我就不详细介绍了，基
本上比较容易实现

以下代码在sql2000运行正常
------------------------------------------------------------------------------------------------------------

 

 
Use master
/*
功能:結束之前已有的追蹤
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
Declare @tmpTraceId int
Declare @i int
set @i=1
while @ibegin
set @tmpTraceId=@i
exec sp_trace_setstatus @tmpTraceId,0   -- stop trace
exec sp_trace_setstatus @tmpTraceId,2   -- remove trace
set @i=@i+1
end

/*
功能:建立保存 trace sql 的資料庫
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'tracedata')
 DROP DATABASE [tracedata]
GO

CREATE DATABASE [tracedata]  ON (NAME = N'tracedata_Data', FILENAME = N'C:/Program Files/Microsoft SQL Server/MSSQL/data/tracedata_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'tracedata_Log', FILENAME = N'C:/Program Files/Microsoft SQL Server/
MSSQL/data/tracedata_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS

GO

exec sp_dboption N'tracedata', N'autoclose', N'false'
GO

exec sp_dboption N'tracedata', N'bulkcopy', N'false'
GO

exec sp_dboption N'tracedata', N'trunc. log', N'false'
GO

exec sp_dboption N'tracedata', N'torn page detection', N'true'
GO

exec sp_dboption N'tracedata', N'read only', N'false'
GO

exec sp_dboption N'tracedata', N'dbo use', N'false'
GO

exec sp_dboption N'tracedata', N'single', N'false'
GO

exec sp_dboption N'tracedata', N'autoshrink', N'false'
GO

exec sp_dboption N'tracedata', N'ANSI null default', N'false'
GO

exec sp_dboption N'tracedata', N'recursive triggers', N'false'
GO

exec sp_dboption N'tracedata', N'ANSI nulls', N'false'
GO

exec sp_dboption N'tracedata', N'concat null yields null', N'false'
GO

exec sp_dboption N'tracedata', N'cursor close on commit', N'false'
GO

exec sp_dboption N'tracedata', N'default to local cursor', N'false'
GO

exec sp_dboption N'tracedata', N'quoted identifier', N'false'
GO

exec sp_dboption N'tracedata', N'ANSI warnings', N'false'
GO

exec sp_dboption N'tracedata', N'auto create statistics', N'true'
GO

exec sp_dboption N'tracedata', N'auto update statistics', N'true'
GO

-------------------------------------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fileExists]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
   drop procedure [dbo].[fileExists]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[createTrace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
   drop procedure [dbo].[createTrace]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[storeTraceData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
   drop procedure [dbo].[storeTraceData]
end
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getTraceData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
   exec master.dbo.sp_procoption @ProcName='getTraceData',@OptionName='startup',
   @OptionValue='false'
   drop procedure [dbo].[getTraceData]
end
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
功能:判斷一個文件或目錄是否已存在 存在為返回 0 否則返回 1
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
Create Proc fileExists
                    @filename nvarchar(128)
 WITH ENCRYPTION
as
declare @rc int 
declare @tempFile nvarchar(128)
set @tempFile= 'dir '+@filename+'  /b'
exec @rc= xp_cmdshell @tempFile, No_Output
return @rc

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
功能: 建立一個 trace
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
CREATE  proc createTrace
                        @traceid int output,
                        @tracefilename nvarchar(128),
                        @maxfilesize bigint
 WITH ENCRYPTION
as
declare @rc int
declare @tmpTraceid int
declare @dbid int
declare @dbname nvarchar(128)

--需要跟踪的数据库
set @dbname='testdb'

 

exec   @rc = sp_trace_create @tmpTraceid output, 0, @tracefilename,@maxfilesize, NULL
if (@rc != 0) goto error

set @traceid=@tmpTraceid

--取得要 trace 的資料庫的 ID
select @dbid=dbid from master..sysdatabases where [name]=@dbname

-- 設置要 trace 的事件,可以參考 profiler 程序
declare @on bit
set @on = 1

exec sp_trace_setevent @tmpTraceid, 10, 1, @on
exec sp_trace_setevent @tmpTraceid, 10, 3, @on
exec sp_trace_setevent @tmpTraceid, 10, 10, @on
exec sp_trace_setevent @tmpTraceid, 10, 12, @on
exec sp_trace_setevent @tmpTraceid, 10, 14, @on

exec sp_trace_setevent @tmpTraceid, 12, 1, @on
exec sp_trace_setevent @tmpTraceid, 12, 3, @on
exec sp_trace_setevent @tmpTraceid, 12,10, @on
exec sp_trace_setevent @tmpTraceid, 12, 12, @on
exec sp_trace_setevent @tmpTraceid, 12, 14, @on

declare @intfilter int

exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%Select%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'%sp_reset_connection%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'set%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'use%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec% sp_[^e][^x][^e][^c]%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'IF%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'exec dbo.dt_verstamp%'
exec sp_trace_setfilter @TraceID, 1, 0, 7, N'sp_MS%'

 

exec sp_trace_setfilter @TraceID, 1, 1, 6, N'update%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'delete%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'insert%'
exec sp_trace_setfilter @TraceID, 1, 1, 6, N'exec sp_executesql'
set @intfilter = @dbid
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

set @intfilter = 100
exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilter

--filter on not system's action
set @intfilter = 100
exec sp_trace_setfilter @tmpTraceid, 22, 0, 4, @intfilter

--filter on successfull action
set @intfilter = 1
exec sp_trace_setfilter @tmpTraceid, 23, 1, 0, @intfilter

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

error:
return @rc

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
功能: 將 trace 到的資料 存到 tracedata.dbo.tracesqlStoretb 表中
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
CREATE proc storeTraceData
 @traceFile nvarchar(128)
 WITH ENCRYPTION
as
 declare @cmd varchar(200)
 declare @tmpFile nvarchar(128)
 declare @rc int

 if not exists (select * from TraceData.dbo.sysobjects where id = object_id(N'TraceData.dbo.tracesqlStoretb'))
  begin
     Create Table  TraceData.dbo.tracesqlStoretb (
 [sid] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
 [TextData] [text] COLLATE Chinese_PRC_CI_AS NULL ,
 [DataBaseId] [int] NULL ,
 [ApplicationName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
 [StartTime] [datetime] NULL
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

     Alter Table   TraceData.dbo.tracesqlStoretb WITH NOCHECK ADD
 CONSTRAINT [PK_aa] PRIMARY KEY  CLUSTERED
 (
  [sid]
 )  ON [PRIMARY]
  end
 
  declare @Max_Time datetime
  declare @Min_Time datetime
  declare @tmpTable table
  (
 [TextData] [text] ,
 [DataBaseId] [int] NULL ,
 [ApplicationName] [nvarchar] (100)  ,
 [StartTime] [datetime] NULL
  )
 

  set @tmpFile=@traceFile+'.trc'

  Insert into @tmpTable (TextData,DataBaseId,ApplicationName,StartTime)
      select  TextData,
                 DataBaseID,
                 ApplicationName,
                 StartTime
       from ::fn_trace_gettable(@tmpFile , default)
 where TextData not like N'insert%into%AccessData%'  and
            TextData not like N'update%AccessData%'  and
            TextData not like N'insert%into%AccessingData%'  and
            TextData not like N'update%AccessingData%'  and
            TextData not like N'insert%into%OnLineNumber%'  and
            TextData not like N'update%OnLineNumber%' and
            TextData not like N'%VendorData%'

 

 

  select @Max_Time = max(StartTime) from TraceData.dbo.tracesqlStoretb
  if (@Max_Time is null )
  begin
      Insert into TraceData.dbo.tracesqlStoretb (TextData,DataBaseId,ApplicationName,StartTime)
      select  TextData,
                 DataBaseID,
                 ApplicationName,
                 StartTime
       from @tmpTable
  end
  else
  begin
      select @Min_Time = max(StartTime)   from @tmpTable
     if (not  @Min_Time is null)
      begin
               Insert into TraceData.dbo.tracesqlStoretb (TextData,DataBaseId,ApplicationName,StartTime)
                  select  TextData,
                             DataBaseID,
                             ApplicationName,
                             StartTime
                   from @tmpTable
                   where StartTime>@Max_Time
       end
  end
  set @cmd = 'cmd /q /c del  ' + @tmpFile
  exec @rc=master.dbo.xp_cmdshell @cmd,No_OutPut
  return @rc

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
功能:
 此過程需要在 SQL SERVER 啟動時就執行,且要排程按指定的時間重復執行
會將 tracesqlStoretb 資料庫的變更記錄到 資料表 TraceData..TraceData 中
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
CREATE  proc getTraceData
 WITH ENCRYPTION
as

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @TraceLive int
declare @user nvarchar(128)

declare
@traceFile nvarchar(128),
@traceFilePath nvarchar(128),
@traceFileFullName nvarchar(128),
@tempfilename nvarchar(128),
@oldfilename nvarchar(128),
@traceFileState nvarchar(1),
@traceFileState1 nvarchar(1),
@traceFileState2 nvarchar(1),
--@appendname varchar(128),
@isExist int,
@tmpStr  nvarchar(128),
@cmd varchar(200)

 

set @maxfilesize = 10
set @traceFileState=1
select @user = loginame from master..sysprocesses where spid = @@spid
set @traceFilePath = N'd:/tracefile/'
set @traceFile= N'tracesqlStoretbonlinetrace'
set  @traceFileFullName = @traceFilePath+@traceFile

select @traceFileState1=1
select @traceFileState2=2

--  if trace file is active, must be stopped,removed, and renamed
select @traceID=traceid,
       @traceLive=count(*)   -- if trace file not active, returns 0
from ::fn_trace_getinfo(default)
where ( value = @traceFileFullName+'1' or value = @traceFileFullName+'2') and property = 2
group by traceid

exec @isExist=fileExists @traceFilePath
if @isExist0
begin
  set @cmd = 'cmd /q /c    md  ' + @traceFilePath
  exec @rc=master.dbo.xp_cmdshell @cmd,No_OutPut
end

 -- @traceLive > 0 means trace file is in use. stop and remove trace  
if @traceLive > 0   
begin     
   select @oldfilename=convert(varchar(128),value)
   from ::fn_trace_getinfo(default)
   where traceid=@traceID and property = 2

   if (@oldfilename=@traceFileFullName+@traceFileState1)
       select @traceFileState=2
   else
       select @traceFileState=1
   select @tempfilename=@traceFileFullName+@traceFileState
   set  @tmpStr=(@tempfilename+'.trc')
   exec @isExist=fileExists @tmpStr
   if @isExist=0
   begin
       exec @rc= storeTraceData @tempfilename
       set @cmd = 'cmd /q /c    del  ' + @tempfilename+'.trc'
       exec @rc=master.dbo.xp_cmdshell @cmd,NO_OutPut
   end
  
   declare @tmpTraceId int
   set @tmpTraceId=@TraceID
   exec   @rc = createTrace  @TraceID output, @tempfilename,@maxfilesize
   if (@rc != 0) goto finish

/*
   raiserror ('Created trace  file  %s  by %s',10,1,@tempfilename,@user) with log
*/

   --delay 10s for become easy to join two files
   WAITFOR DELAY '00:00:02'

   exec @rc=sp_trace_setstatus @tmpTraceId,0   -- stop trace

/*
   if (@rc=0)
   begin
        raiserror ('Stopped  trace  file: %s  ID: %d  ',10,1,@oldfilename,@TraceID) with log
   end
   else
   begin
       raiserror ('Stop  trace  file: %s  ID: %d   failed',10,1,@oldfilename,@TraceID) with log
   end
*/

   exec @rc=sp_trace_setstatus @tmpTraceId,2   -- remove trace

/*
   if (@rc=0)
   begin
         raiserror ('Removed  trace  file: %s  ID: %d  ',10,1,@oldfilename,@TraceID) with log
   end
   else
   begin
       raiserror ('Remove  trace  file: %s  ID: %d   failed',10,1,@oldfilename,@TraceID) with log
   end
*/
   exec @rc= storeTraceData @oldfilename
end
else
begin

   set @tempfilename=@traceFileFullName+@traceFileState2
   set  @tmpStr=(@tempfilename+'.trc')
   exec @isExist=fileExists @tmpStr
   if @isExist=0
   begin
       exec @rc= storeTraceData @tempfilename
       set @cmd = 'cmd /q /c    del  ' + @tempfilename+'.trc'
       exec @rc=master.dbo.xp_cmdshell @cmd,No_OutPut
   end

   set @tempfilename=@traceFileFullName+@traceFileState1
   set  @tmpStr=(@tempfilename+'.trc')
   exec @isExist=fileExists @tmpStr
   if @isExist=0
   begin
       exec @rc= storeTraceData @tempfilename
       set @cmd = 'cmd /q /c    del  ' + @tempfilename+'.trc'
       exec @rc=master.dbo.xp_cmdshell @cmd,No_OutPut
   end

   select @tempfilename=@traceFileFullName+@traceFileState1
   exec   @rc = createTrace  @TraceID output,  @tempfilename,@maxfilesize
   if (@rc != 0) goto finish

/*
   raiserror ('Created trace  file  %s  by %s',10,1,@tempfilename,@user) with log
*/

end

finish:

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
功能: 將 getTraceData 注冊為 server 啟動就執行的 過程
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
exec master.dbo.sp_procoption @ProcName='getTraceData',@OptionName='startup',
@OptionValue='true'

GO

/*
功能: 對 getTraceData 程序進行排程
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/

BEGIN TRANSACTION           
  DECLARE @JobID BINARY(16) 
  DECLARE @ReturnCode INT   
  SELECT @ReturnCode = 0    
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]')   EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

  -- 刪除相同名稱作業 (如果作業存在)
  SELECT @JobID = job_id    
  FROM   msdb.dbo.sysjobs   
  WHERE (name = N'Recode tracesqlStoretb changing')      
  IF (@JobID IS NOT NULL)   
  BEGIN 
  -- 檢查作業是否是為多重伺服器的作業 
  IF (EXISTS (SELECT  *
              FROM    msdb.dbo.sysjobservers
              WHERE   (job_id = @JobID) AND (server_id 0)))
  BEGIN
    -- 有，所以中斷指令碼
    RAISERROR (N'無法匯入作業 ''Recode tracesqlStoretb changing''，因為多重伺服器已經有這個名稱。', 16, 1)
    GOTO QuitWithRollback 
  END
  ELSE
    -- 刪除 [本機] 作業
    EXECUTE msdb.dbo.sp_delete_job @job_name = N'Recode tracesqlStoretb changing'
    SELECT @JobID = NULL
  END

BEGIN

  -- 新增作業
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Recode tracesqlStoretb changing', @owner_login_name = N'webapp', @description = N'沒有可用的描述。', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email
 = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 0, @delete_level= 0
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

  -- 新增作業步驟
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'GetTrace', @command = N' [master].[dbo].[getTraceData] ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_s
uccess_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

  -- 新增作業排程
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'15:00~20:00', @enabled = 1, @freq_type = 4, @active_start_date = 20050709, @active_start_time = 150000, @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 2, @
freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 190000
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'10:00', @enabled = 1, @freq_type = 4, @active_start_date = 20050709, @active_start_time = 100000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_r
elative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'12:00', @enabled = 1, @freq_type = 4, @active_start_date = 20050709, @active_start_time = 120000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_r
elative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'06:00', @enabled = 1, @freq_type = 4, @active_start_date = 20050709, @active_start_time = 60000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_re
lative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

  -- 新增目標伺服器
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
  IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION         
GOTO   EndSave             
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

/*
功能: 初始執行 getTraceData
author: 徐龍
Date: 2005/07/09
Email:  fangkailove@yeah.net
*/
exec getTraceData
GO

 

-------------------------------------------------------------------

 

 代码中用到很多不常用的方法，请在sql在线帮助中查询。欢迎大家讨论