SQL Server中怎么實(shí)時(shí)同步更新遠(yuǎn)程數(shù)據(jù)庫(kù)

SQL Server中怎么實(shí)時(shí)同步更新遠(yuǎn)程數(shù)據(jù)庫(kù)，很多新手對(duì)此不是很清楚，為了幫助大家解決這個(gè)難題，下面小編將為大家詳細(xì)講解，有這方面需求的人可以來(lái)學(xué)習(xí)下，希望你能有所收獲。

1. 訪(fǎng)問(wèn)異地?cái)?shù)據(jù)庫(kù)

在ServerA 中創(chuàng)建指向ServerB的鏈接服務(wù)器，并做好賬號(hào)映射。addlinkedserver存儲(chǔ)過(guò)程創(chuàng)建一個(gè)鏈接服務(wù)器，參數(shù)詳情參見(jiàn)官方文檔。第1個(gè)參數(shù)LNK_ServerA是自定義的名稱(chēng)；第2參數(shù)產(chǎn)品名稱(chēng)，如果是SQL Server不用提供；第3個(gè)參數(shù)是驅(qū)動(dòng)類(lèi)型；第4個(gè)參數(shù)是數(shù)據(jù)源，這里寫(xiě)SQL Server服務(wù)器地址

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

配置鏈接服務(wù)器后，默認(rèn)使用同一本地賬號(hào)登陸遠(yuǎn)程數(shù)據(jù)庫(kù)，如果賬號(hào)有不同，還需要進(jìn)行賬號(hào)映射。sp_addlinkedsrvlogin參數(shù)詳情參見(jiàn)官方文檔。第1個(gè)參數(shù)同上；第2個(gè)參數(shù)false即使用后面參數(shù)提供的用戶(hù)密碼登陸；第3個(gè)參數(shù)null使所有本地賬號(hào)都可以使用后面的用戶(hù)密碼來(lái)登陸鏈接服務(wù)器，如果第3個(gè)參數(shù)設(shè)置為一個(gè)本地SQL Server登陸用戶(hù)名，那么只有這個(gè)用戶(hù)才可以使用遠(yuǎn)程賬號(hào)登陸鏈接服務(wù)器；最后兩個(gè)是登錄遠(yuǎn)程服務(wù)器的用戶(hù)和密碼。

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

如果要?jiǎng)h除以上配置可以如下

exec sp_droplinkedsrvlogin 'LNK_ServerB_DatabaseB',nullexec sp_dropserver 'LNK_ServerB_DatabaseB','droplogins'

上面的配置在SQL Server Management Studio管理器里Server Objects下LinkedServers可以查詢(xún)到，如果一切鏈接正常，可以直接打開(kāi)鏈接服務(wù)器上的庫(kù)表

值得注意的是以上兩個(gè)存儲(chǔ)過(guò)程不能出現(xiàn)在觸發(fā)器代碼中，而是事先在服務(wù)器ServerA中運(yùn)行完成配置，否則觸發(fā)器隱式事務(wù)的要求會(huì)報(bào)錯(cuò)“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ù)器之間開(kāi)啟分布式事務(wù)處理，否則會(huì)報(bào)“The partner transaction manager has disabled its support for remote/network transactions”的錯(cuò)誤。我在ServerA和ServerB中都開(kāi)啟分布式事務(wù)協(xié)調(diào)器，并進(jìn)行適當(dāng)配置，以支持分布式事務(wù)。ServerA和ServerB都是Windows Server 2012 R2，其他版本服務(wù)器類(lèi)似。

（1）首先在Services.msc中確認(rèn)Distributed Transaction Coordinator已經(jīng)開(kāi)啟，其他版本的服務(wù)器不一定默認(rèn)安裝，需要安裝windows features的方式先進(jìn)行該特性的安裝。

（2）在服務(wù)器管理工具Administrative Tools中找到Component Services，在Local DTC中屬性Security選項(xiàng)卡中配置如下，打開(kāi)相關(guān)安全設(shè)置，完成后會(huì)重啟服務(wù)，也有文檔稱(chēng)需要重啟服務(wù)器，但是至少2012 R2不用。

（3）配置防火墻，Inbound和Outbound都打開(kāi)

3. 數(shù)據(jù)庫(kù)字段text, ntext的處理

業(yè)務(wù)中表TableA中有一個(gè)Content字段是text類(lèi)型，同步到TableB時(shí)需要對(duì)內(nèi)容做一些替換處理。對(duì)于text類(lèi)型是一個(gè)過(guò)時(shí)的類(lèi)型，微軟官方建議用(N)VARCHAR(MAX)替換，可查閱這里。今后設(shè)計(jì)時(shí)可以考慮，這里我們考慮對(duì)text進(jìn)行處理。

但是在觸發(fā)器中，inserted和deleted表都是不允許對(duì)text/ntext/image類(lèi)型進(jìn)行處理的，這里我們采用一個(gè)曲線(xiàn)救國(guó)的辦法，從數(shù)據(jù)庫(kù)中把記錄讀取到臨時(shí)表中，然后通過(guò)textptr和patindex函數(shù)和updatetext命令完成字符串替換處理

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

4. 執(zhí)行遠(yuǎn)程數(shù)據(jù)庫(kù)操作

當(dāng)配置鏈接服務(wù)器時(shí)，我們可以直接訪(fǎng)問(wèn)遠(yuǎn)程數(shù)據(jù)庫(kù)表了，如下

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

但簡(jiǎn)陋的SQL編輯器往往會(huì)對(duì)語(yǔ)法報(bào)錯(cuò)，另外為方便編程，我們希望通過(guò)exec sp_executesql的方式獲得更多的靈活性。其實(shí)exec就可以直接執(zhí)行sql語(yǔ)句，但如果有返回值就比較困難了。如下，從遠(yuǎn)程服務(wù)器上通過(guò)ID查詢(xún)表TableB后返回Name，sp_executesql存儲(chǔ)過(guò)程可以使用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語(yǔ)句，本質(zhì)上是執(zhí)行拼接后的sql字符串，有時(shí)將變量拼接進(jìn)字符串會(huì)困難的多（到底需要幾個(gè)單引號(hào)），而sp_executesql則清晰多了

declare @SQL nvarchar(500),@Name nvarchar(50),@Count int,@ID nvarchar(40)set @Name=N'Cat'set @Count=0set @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

看完上述內(nèi)容是否對(duì)您有幫助呢？如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝您對(duì)億速云的支持。