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在线帮助中查询。欢迎大家讨论