SQL Server實時同步更新遠程數據庫遇到的問題小結

投稿：mrr 字體：[增加 減小] 類型：轉載 時間：2017-04-19 我要評論

這篇文章主要介紹了SQL Server實時同步更新遠程數據庫遇到的問題小結,需要的朋友可以參考下

工作中遇到這樣的情況，需要在更新表TableA（位於服務器ServerA 172.16.8.100中的庫DatabaseA）同時更新TableB（位於服務器ServerB 172.16.8.101中的庫DatabaseB）。

TableA與TableB結構相同，但數據數量不一定相同，應為有可能TableC也在更新TableB。由於數據更新不頻繁，為簡單起見想到使用了觸發器Tirgger。記錄一下遇到的一些問題：

1. 訪問異地數據庫

在ServerA 中創建指向ServerB的鏈接服務器，並做好賬號映射。addlinkedserver存儲過程創建一個鏈接服務器，參數詳情參見官方文檔。第1個參數LNK_ServerA是自定義的名稱；第2參數產品名稱，如果是SQL Server不用提供；第3個參數是驅動類型；第4個參數是數據源，這裡寫SQL Server服務器地址

exec sp_addlinkedserver 'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'

配置鏈接服務器後，默認使用同一本地賬號登陸遠程數據庫，如果賬號有不同，還需要進行賬號映射。sp_addlinkedsrvlogin參數詳情參見官方文檔。第1個參數同上；第2個參數false即使用後面參數提供的用戶密碼登陸；第3個參數null使所有本地賬號都可以使用後面的用戶密碼來登陸鏈接服務器，如果第3個參數設置為一個本地SQL Server登陸用戶名，那麼只有這個用戶才可以使用遠程賬號登陸鏈接服務器；最後兩個是登錄遠程服務器的用戶和密碼。

exec sp_addlinkedsrvlogin 'LNK_ServerB_DatabaseB','false',null,'user','password'

如果要刪除以上配置可以如下

exec sp_droplinkedsrvlogin 'LNK_ServerB_DatabaseB',null
exec sp_dropserver 'LNK_ServerB_DatabaseB','droplogins'

上面的配置在SQL Server Management Studio管理器裡Server Objects下LinkedServers可以查詢到，如果一切鏈接正常，可以直接打開鏈接服務器上的庫表

值得注意的是以上兩個存儲過程不能出現在觸發器代碼中，而是事先在服務器ServerA中運行完成配置，否則觸發器隱式事務的要求會報錯“The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.”

2. 配置分布式事務

SQL Server的觸發器是隱式使用事務的，鏈接服務器是遠程服務器，需要在本地服務器和遠程服務器之間開啟分布式事務處理，否則會報“The partner transaction manager has disabled its support for remote/network transactions”的錯誤。我在ServerA和ServerB中都開啟分布式事務協調器，並進行適當配置，以支持分布式事務。ServerA和ServerB都是Windows Server 2012 R2，其他版本服務器類似。

（1）首先在Services.msc中確認Distributed Transaction Coordinator已經開啟，其他版本的服務器不一定默認安裝，需要安裝windows features的方式先進行該特性的安裝。

（2）在服務器管理工具Administrative Tools中找到Component Services，在Local DTC中屬性Security選項卡中配置如下，打開相關安全設置，完成後會重啟服務，也有文檔稱需要重啟服務器，但是至少2012 R2不用。

（3）配置防火牆，Inbound和Outbound都打開

3. 數據庫字段text, ntext的處理

業務中表TableA中有一個Content字段是text類型，同步到TableB時需要對內容做一些替換處理。對於text類型是一個過時的類型，微軟官方建議用(N)VARCHAR(MAX)替換，可查閱這裡。今後設計時可以考慮，這裡我們考慮對text進行處理。

但是在觸發器中，inserted和deleted表都是不允許對text/ntext/image類型進行處理的，這裡我們采用一個曲線救國的辦法，從數據庫中把記錄讀取到臨時表中，然後通過textptr和patindex函數和updatetext命令完成字符串替換處理

if exists(select * from tempdb..sysobjects where id=object_id('tempdb..#temp_tablea')) 
 drop table #temp_tablea
select * into #temp_tablea from TableA where ID = @ID
declare @s varchar(200),@d varchar(200)
select @s='="/_target/',@d='="/_replacement/'
declare @p varbinary(16),@postion int,@l int
select @p=textptr(Content),@l=len(@s),@postion=patindex('%'+@s+'%',Content)+1 from #temp_tablea
while @postion>1
begin
 updatetext #temp_tablea.Content @p @postion @l @d
 select @postion=patindex('%'+@s+'%',Content)+1 from #temp_tablea
end

4. 執行遠程數據庫操作

當配置鏈接服務器時，我們可以直接訪問遠程數據庫表了，如下

insert into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB ...
update LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB set ...

但簡陋的SQL編輯器往往會對語法報錯，另外為方便編程，我們希望通過exec sp_executesql的方式獲得更多的靈活性。其實exec就可以直接執行sql語句，但如果有返回值就比較困難了。如下，從遠程服務器上通過ID查詢表TableB後返回Name，sp_executesql存儲過程可以使用output關鍵字定義變量為返回變量，其中@Name output為返回變量，@ID則是傳入變量。

declare @sql nvarchar(500), @Name nvarchar(50),@ID nvarchar(40)
set @SQL=N'select @Name=Name from LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB where ID=@ID'
exec sp_executesql @SQL,N'@Name nvarchar(50) output,@ID nvarchar(40)',@Name output,@ID

另外exec直接執行sql語句，本質上是執行拼接後的sql字符串，有時將變量拼接進字符串會困難的多（到底需要幾個單引號），而sp_executesql則清晰多了

declare @SQL nvarchar(500),@Name nvarchar(50),@Count int,@ID nvarchar(40)
set @Name=N'Cat'
set @Count=0
set @ID=N'{00000000-0000-0000-0000-000000000000}'
set @SQL=N'update TableA set Name='''+@Name+''', Count='+@Count+' where ID='''+@ID+''''
exec(@SQL)
set @SQL=N'update TableA set Name=@Name,Count=@Count where ID=@ID'
exec sp_executesql @SQL, N'@Name nvarchar(50),@Count int,@ID nvarchar(40)',@Name,@Count,@ID

以上所述是小編給大家介紹的SQL Server實時同步更新遠程數據庫遇到的問題小結，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對 網站的支持！