?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',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'trunc. log',N'torn page detection',N'true'
GO
exec sp_dboption N'tracedata',N'read only',N'dbo use',N'single',N'autoshrink',N'ANSI null default',N'recursive triggers',N'ANSI nulls',N'concat null yields null',N'cursor close on commit',N'default to local cursor',N'quoted identifier',N'ANSI warnings',N'auto create statistics',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,@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,3,12,14,@on
exec sp_trace_setevent @tmpTraceid,@on
declare @intfilter int
exec sp_trace_setfilter @TraceID,7,N'%Select%'
exec sp_trace_setfilter @TraceID,N'%sp_reset_connection%'
exec sp_trace_setfilter @TraceID,N'set%'
exec sp_trace_setfilter @TraceID,N'use%'
exec sp_trace_setfilter @TraceID,N'exec% sp_[^e][^x][^e][^c]%'
exec sp_trace_setfilter @TraceID,N'IF%'
exec sp_trace_setfilter @TraceID,N'exec dbo.dt_verstamp%'
exec sp_trace_setfilter @TraceID,N'sp_MS%'
?
exec sp_trace_setfilter @TraceID,6,N'update%'
exec sp_trace_setfilter @TraceID,N'delete%'
exec sp_trace_setfilter @TraceID,N'insert%'
exec sp_trace_setfilter @TraceID,N'exec sp_executesql'
set @intfilter = @dbid
exec sp_trace_setfilter @TraceID,@intfilter
set @intfilter = 100
exec sp_trace_setfilter @TraceID,22,4,@intfilter
--filter on not system's action
set @intfilter = 100
exec sp_trace_setfilter @tmpTraceid,@intfilter
--filter on successfull action
set @intfilter = 1
exec sp_trace_setfilter @tmpTraceid,23,@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],
?[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,
???????????????? 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,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',@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? ',@oldfilename,@TraceID) with log
?? end
?? else
?? begin
?????? raiserror ('Stop? trace? file: %s? ID: %d?? failed',@TraceID) with log
?? end
*/
?? exec @rc=sp_trace_setstatus @tmpTraceId,2?? -- remove trace
/*
?? if (@rc=0)
?? begin
???????? raiserror ('Removed? trace? file: %s? ID: %d? ',@TraceID) with log
?? end
?? else
?? begin
?????? raiserror ('Remove? trace? file: %s? ID: %d?? failed',@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,@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',
@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',@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',@active_start_time = 100000,@freq_subday_type = 1,@freq_subday_interval = 0,@freq_r
elative_interval = 0,@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',@active_start_time = 120000,@name = N'06:00',@active_start_time = 60000,@freq_re
lative_interval = 0,@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在线帮助中查询。欢迎大家讨论?
