工作中遇到這樣的情況,需要在更新表TableA(位于服務(wù)器ServerA 172.16.8.100中的庫DatabaseA)同時更新TableB(位于服務(wù)器ServerB 172.16.8.101中的庫DatabaseB)。
TableA與TableB結(jié)構(gòu)相同,但數(shù)據(jù)數(shù)量不一定相同,應(yīng)為有可能TableC也在更新TableB。由于數(shù)據(jù)更新不頻繁,為簡單起見想到使用了觸發(fā)器Tirgger。記錄一下遇到的一些問題:
1. 訪問異地數(shù)據(jù)庫
在ServerA 中創(chuàng)建指向ServerB的鏈接服務(wù)器,并做好賬號映射。addlinkedserver存儲過程創(chuàng)建一個鏈接服務(wù)器,參數(shù)詳情參見官方文檔。第1個參數(shù)LNK_ServerA是自定義的名稱;第2參數(shù)產(chǎn)品名稱,如果是SQL Server不用提供;第3個參數(shù)是驅(qū)動類型;第4個參數(shù)是數(shù)據(jù)源,這里寫SQL Server服務(wù)器地址
exec sp_addlinkedserver 'LNK_ServerB_DatabaseB','','SQLNCLI','172.16.8.101'
配置鏈接服務(wù)器后,默認(rèn)使用同一本地賬號登陸遠(yuǎn)程數(shù)據(jù)庫,如果賬號有不同,還需要進(jìn)行賬號映射。sp_addlinkedsrvlogin參數(shù)詳情參見官方文檔。第1個參數(shù)同上;第2個參數(shù)false即使用后面參數(shù)提供的用戶密碼登陸;第3個參數(shù)null使所有本地賬號都可以使用后面的用戶密碼來登陸鏈接服務(wù)器,如果第3個參數(shù)設(shè)置為一個本地SQL Server登陸用戶名,那么只有這個用戶才可以使用遠(yuǎn)程賬號登陸鏈接服務(wù)器;最后兩個是登錄遠(yuǎn)程服務(wù)器的用戶和密碼。
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可以查詢到,如果一切鏈接正常,可以直接打開鏈接服務(wù)器上的庫表
值得注意的是以上兩個存儲過程不能出現(xiàn)在觸發(fā)器代碼中,而是事先在服務(wù)器ServerA中運行完成配置,否則觸發(fā)器隱式事務(wù)的要求會報錯“The procedure 'sys.sp_addlinkedserver' cannot be executed within a transaction.”
2. 配置分布式事務(wù)
SQL Server的觸發(fā)器是隱式使用事務(wù)的,鏈接服務(wù)器是遠(yuǎn)程服務(wù)器,需要在本地服務(wù)器和遠(yuǎn)程服務(wù)器之間開啟分布式事務(wù)處理,否則會報“The partner transaction manager has disabled its support for remote/network transactions”的錯誤。我在ServerA和ServerB中都開啟分布式事務(wù)協(xié)調(diào)器,并進(jìn)行適當(dāng)配置,以支持分布式事務(wù)。ServerA和ServerB都是Windows Server 2012 R2,其他版本服務(wù)器類似。
(1)首先在Services.msc中確認(rèn)Distributed Transaction Coordinator已經(jīng)開啟,其他版本的服務(wù)器不一定默認(rèn)安裝,需要安裝windows features的方式先進(jìn)行該特性的安裝。
(2)在服務(wù)器管理工具Administrative Tools中找到Component Services,在Local DTC中屬性Security選項卡中配置如下,打開相關(guān)安全設(shè)置,完成后會重啟服務(wù),也有文檔稱需要重啟服務(wù)器,但是至少2012 R2不用。
(3)配置防火墻,Inbound和Outbound都打開
3. 數(shù)據(jù)庫字段text, ntext的處理
業(yè)務(wù)中表TableA中有一個Content字段是text類型,同步到TableB時需要對內(nèi)容做一些替換處理。對于text類型是一個過時的類型,微軟官方建議用(N)VARCHAR(MAX)替換,可查閱這里。今后設(shè)計時可以考慮,這里我們考慮對text進(jìn)行處理。
但是在觸發(fā)器中,inserted和deleted表都是不允許對text/ntext/image類型進(jìn)行處理的,這里我們采用一個曲線救國的辦法,從數(shù)據(jù)庫中把記錄讀取到臨時表中,然后通過textptr和patindex函數(shù)和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. 執(zhí)行遠(yuǎn)程數(shù)據(jù)庫操作
當(dāng)配置鏈接服務(wù)器時,我們可以直接訪問遠(yuǎn)程數(shù)據(jù)庫表了,如下
insert into LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB ...
update LNK_ServerB_DatabaseB.DatabaseB.dbo.TableB set ...
但簡陋的SQL編輯器往往會對語法報錯,另外為方便編程,我們希望通過exec sp_executesql的方式獲得更多的靈活性。其實exec就可以直接執(zhí)行sql語句,但如果有返回值就比較困難了。如下,從遠(yuǎn)程服務(wù)器上通過ID查詢表TableB后返回Name,sp_executesql存儲過程可以使用output關(guān)鍵字定義變量為返回變量,其中@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直接執(zhí)行sql語句,本質(zhì)上是執(zhí)行拼接后的sql字符串,有時將變量拼接進(jìn)字符串會困難的多(到底需要幾個單引號),而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實時同步更新遠(yuǎn)程數(shù)據(jù)庫遇到的問題小結(jié),希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!
您可能感興趣的文章:- SQL Server 出現(xiàn)Error: 1326錯誤(管理器無法連接遠(yuǎn)程數(shù)據(jù)庫)問題解決方案
- SQL server 2005將遠(yuǎn)程數(shù)據(jù)庫導(dǎo)入到本地的方法
- SQLSERVER 本地查詢更新操作遠(yuǎn)程數(shù)據(jù)庫的代碼