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 @i<10
begin
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 @isExist<>0
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)]') < 1
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在線幫助中查詢。歡迎大家討論

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com