1. 背景
当系统的微服务化做的不是很高的时候，部分功能要通过DB LinkServer 来实现跨 Server 查询，当然，有时候BI抽数据、DBA数据库维护可能也会创建LinkServer。
特别是当 DB迁移的时候，我们需要检查、创建 DB LinkServer。
2.脚本实现
下面是我们创建的一个存储过程，通过这个存储过程来实现简单、快速的添加链接服务器。
- USE [DBA_Manager]
- GO
-
- /****** Object: StoredProcedure [dbo].[USP_Create_DBLink] Script Date: 2019/7/5 13:52:50 ******/
- SET ANSI_NULLS ON
- GO
-
- SET QUOTED_IDENTIFIER ON
- GO
-
-
- -- =============================================
- -- Author: <Author,,Carson>
- -- Create date: <Create Date,2018-06-18,>
- -- Description: <Description,实现创建DB LinkServer的脚本化,>
- -- =============================================
- CREATE PROCEDURE [dbo].[USP_Create_DBLink]
- -- Add the parameters for the stored procedure here
- @ServerIP varchar(20),@SQLUserName varchar(20)='',@PassW varchar(20)='',@DelCurLinks varchar(10)='N', @Result nvarchar(3000)='' output
- AS
- BEGIN
-
- SET NOCOUNT ON;
- declare @OriginalSQL nvarchar(3000)
- declare @sSQL nvarchar(3000)
-
- ----------------------------------------------
-
- ---判断指定的ServerIP是否已存在DBLinkServer，结合@DelCurLinks输入参数判断是否删除重建
-
- if @DelCurLinks='Y' and exists(select srvname from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername') and srvname=@ServerIP)
- begin
- set @sSQL=' EXEC master.dbo.sp_dropserver @server=N'''+@ServerIP+''', @droplogins=''droplogins'''
- Print @sSQL
- exec sp_executesql @sSQL
- end
-
- if not exists(select srvname from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername') and srvname=@ServerIP) or @DelCurLinks='Y'
- begin
- --create script
- set @OriginalSQL='/****** Object: LinkedServer [<ServerIP>] ******/
- EXEC master.dbo.sp_addlinkedserver @server = N''<ServerIP>'', @srvproduct=N''SQL Server''
- /* For security reasons the linked server remote logins password is changed with ######## */
- EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N''<ServerIP>'',@useself=N''False'',@locallogin=NULL,@rmtuser=N''<UserName>'',@rmtpassword=''<PWD>''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''collation compatible'', @optvalue=N''true''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''data access'', @optvalue=N''true''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''rpc'', @optvalue=N''true''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''rpc out'', @optvalue=N''true''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''use remote collation'', @optvalue=N''true''
- --GO
- EXEC master.dbo.sp_serveroption @server=N''<ServerIP>'', @optname=N''remote proc transaction promotion'', @optvalue=N''true''
- --GO
- '
-
- set @sSQL=REPLACE( @OriginalSQL, '<ServerIP>',@ServerIP )
- set @sSQL=REPLACE( @sSQL, '<UserName>',@SQLUserName )
- set @sSQL=REPLACE( @sSQL, '<PWD>',@PassW )
- begin try
- exec sp_executesql @sSQL
- print @sSQL
- print 'Create Link Server['+ @ServerIP +'] successfully!'
- end try
- begin catch
- print 'Create Link Server ['+ @ServerIP +'] fail! ErrMsg: '+ERROR_MESSAGE()
- end catch
- end
-
- if @DelCurLinks='N' and exists(select srvname from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername') and srvname=@ServerIP)
- begin
- print 'Create Link Server ['+ @ServerIP +'] fail! ErrMsg: Find DBLinkserver of the Same Name ,Please Check it.'
- end
-
- END
-
-
- GO
3.方法使用
方法 1: 只输入IP、UID、PWD三个参数，@DelCurLinks不显示输入【此时，@DelCurLinks默认为 N，指明当存在相同的LinkServer时，不删除直接退出。】
- Exec USP_Create_DBLink '172.XXX.XXX.XXX','UID','PWD'
方法 2：输入IP、UID、PWD、DelCurLinks 四个参数，显示指明当存在相同的LinkServer时，不删除直接退出。
- Exec USP_Create_DBLink '172.XXX.XXX.XXX','UID','PWD','N'
方法 3：输入IP、UID、PWD、DelCurLinks 四个参数，显示指明当存在相同的LinkServer时，删除重新创建
- Exec USP_Create_DBLink '172.XXX.XXX.XXX','UID','PWD','Y'
4. 其它知识
（1） 查询本SQL Server 已创建的所有实例
- select srvname as '链接服务器' from master.dbo.sysservers where srvname <> SERVERPROPERTY('servername')
（2）删除已建立的链接服务器（LinkServer）
- EXEC master.dbo.sp_dropserver @server=N'172.XXX.XXX.XXX', @droplogins='droplogins'
经验首页