一、背景
我們的數(shù)據(jù)庫(kù)比較多，它們提供了外網(wǎng)的訪問(wèn)，我現(xiàn)在想對(duì)這些數(shù)據(jù)庫(kù)進(jìn)行一些管理，獲取這些數(shù)據(jù)庫(kù)的一些信息，我們可以通過(guò)什么方式實(shí)現(xiàn)呢？
在SQL Server2005版本之后有一個(gè)叫做鏈接服務(wù)器的新功能，基本的操作可以參考：
SQL Server 2005鏈接服務(wù)器，我們就通過(guò)這個(gè)鏈接服務(wù)器來(lái)獲取我們需要的數(shù)據(jù)，但是我們的服務(wù)器比較多，這個(gè)批量創(chuàng)建鏈接服務(wù)器和批量刪除鏈接服務(wù)器就呼之欲出了。
二、設(shè)計(jì)過(guò)程
設(shè)計(jì)簡(jiǎn)述：創(chuàng)建如下圖的表結(jié)構(gòu)，LinkName保存遠(yuǎn)程鏈接的別名，LinkName2是創(chuàng)建鏈接方式2的一個(gè)補(bǔ)充字段，LinkIP代表遠(yuǎn)程服務(wù)器的地址，如果有端口的還需要加上端口。
這里為什么要設(shè)計(jì)成LinkName與LinkName2并存呢？這是因?yàn)槲覀冊(cè)赥-SQL使用遠(yuǎn)程鏈接的時(shí)候是通過(guò)別名的，我在進(jìn)行兩種方式的切換，只要修改調(diào)換下這兩個(gè)字段的名稱，并且去存儲(chǔ)過(guò)程sp_CreateLink注釋方式1的代碼，恢復(fù)方式2的代碼；
（圖1：LinkConfig表，鏈接方式1）
（圖2：LinkConfig表，鏈接方式2）
（圖3：鏈接方式1的屬性）
（圖4：鏈接方式2的屬性）
詳細(xì)代碼：創(chuàng)建表LinkConfig、批量創(chuàng)建鏈接服務(wù)器存儲(chǔ)過(guò)程、批量刪除鏈接服務(wù)器存儲(chǔ)過(guò)程。
--創(chuàng)建表CREATE TABLE [dbo].[LinkConfig]( [Id] [int] IDENTITY(1,1) NOT NULL, [LinkName] [nvarchar](50) NULL, [LinkName2] [nvarchar](50) NULL, [LinkIP] [nvarchar](50) NULL, [LinkSa] [nvarchar](50) NULL, [LinkPassword] [nvarchar](50) NULL, [State] [int] NULL CONSTRAINT [DF_LinkConfig_State] DEFAULT ((0)), CONSTRAINT [PK_LinkConfig] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
--創(chuàng)建遠(yuǎn)程鏈接sp-- =============================================-- Author: <Viajar>-- Create date: <2012.01.05>-- Description: <創(chuàng)建遠(yuǎn)程鏈接>-- =============================================CREATE PROCEDURE [dbo].[sp_CreateLink] ASBEGIN SET NOCOUNT ON; --創(chuàng)建遠(yuǎn)程鏈接 DECLARE @linkname VARCHAR(100) DECLARE @linkip VARCHAR(100) DECLARE @linksa VARCHAR(100) DECLARE @linkpassword VARCHAR(100) DECLARE @isexists VARCHAR(10) DECLARE @sql NVARCHAR(4000) SET @isexists = 'False' DECLARE @itemCur CURSOR SET @itemCur = CURSOR FOR SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1 OPEN @itemCur FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword WHILE @@FETCH_STATUS=0 BEGIN --正在處理 PRINT @linkname --判斷是否存在 set @sql = N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''') begin set @IsExistsOUT = ''True'' end' exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT --不存在 IF(@IsExists = 'False') BEGIN --創(chuàng)建鏈接方式 SET @sql = ' EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N''SQL Server''' EXEC(@sql) --設(shè)置密碼 SET @sql = ' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+'''' EXEC(@sql)-- --創(chuàng)建鏈接方式-- SET @sql = '-- EXEC master.dbo.sp_addlinkedserver @server = N'''+@linkname+''', @srvproduct=N'''+@linkname+''', @provider=N''SQLNCLI'', @datasrc=N'''+@linkip+''''-- EXEC(@sql)---- --設(shè)置密碼-- SET @sql = '-- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'''+@linkname+''', @locallogin = NULL , @useself = N''False'', @rmtuser = N'''+@linksa+''', @rmtpassword = N'''+@linkpassword+''''-- EXEC(@sql)---- --設(shè)置屬性-- SET @sql = '-- EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc'', @optvalue=N''true''-- ;-- EXEC master.dbo.sp_serveroption @server=N'''+@linkname+''', @optname=N''rpc out'', @optvalue=N''true''-- '-- EXEC(@sql) END SET @isexists = 'False' FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword END CLOSE @itemCur DEALLOCATE @itemCurEND
--刪除遠(yuǎn)程鏈接sp-- =============================================-- Author: <Viajar>-- Create date: <2012.01.05>-- Description: <刪除遠(yuǎn)程鏈接>-- =============================================ALTER PROCEDURE [dbo].[sp_DropLink] ASBEGIN SET NOCOUNT ON; --刪除遠(yuǎn)程鏈接 DECLARE @linkname VARCHAR(100) DECLARE @linkip VARCHAR(100) DECLARE @linksa VARCHAR(100) DECLARE @linkpassword VARCHAR(100) DECLARE @isexists VARCHAR(10) DECLARE @sql NVARCHAR(4000) SET @isexists = 'False' DECLARE @itemCur CURSOR SET @itemCur = CURSOR FOR SELECT [LinkName],[LinkIP],[LinkSa],[LinkPassword] FROM dbo.LinkConfig WHERE State =1 OPEN @itemCur FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword WHILE @@FETCH_STATUS=0 BEGIN --正在處理 PRINT @linkname --判斷是否存在 set @sql = N'IF EXISTS (SELECT * FROM sys.servers WHERE name = '''+ @linkname + ''') begin set @IsExistsOUT = ''True'' end' exec sp_executesql @sql,N'@IsExistsOUT varchar(10) OUTPUT',@IsExistsOUT=@isexists OUTPUT --不存在 IF(@IsExists = 'True') BEGIN --刪除鏈接 SET @sql = ' IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'''+@linkname+''') BEGIN EXEC master.dbo.sp_dropserver @server=N'''+@linkname+''', @droplogins=''droplogins'' END' EXEC(@sql) END SET @isexists = 'False' FETCH NEXT FROM @itemCur INTO @linkname,@linkip,@linksa,@linkpassword END CLOSE @itemCur DEALLOCATE @itemCurEND
三、注意事項(xiàng)
1. 進(jìn)行方式1與方式2的切換，需要如圖1、圖2的表字段名稱進(jìn)行修改，并且去存儲(chǔ)過(guò)程sp_CreateLink注釋方式1的代碼，恢復(fù)方式2的代碼；
2. 在需要修改表記錄之前需要先刪除所有鏈接服務(wù)器（執(zhí)行存儲(chǔ)過(guò)程sp_DropLink），再創(chuàng)建鏈接服務(wù)器；（執(zhí)行存儲(chǔ)過(guò)程sp_CreateLink）；
聯(lián)系客服
