SQLServer之创建链接服务器

再无痕迹

创建链接服务器注意事项
当我们要跨本地数据库，访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的DBLINK，通过DBLINNK数据库可以像访问本地数据库一样访问远程数据库表中的数据。

链接服务器允许访问针对OLE DB数据源的分布式异构查询。创建链接服务器后，可以针对此服务器运行分布式查询，并且查询可以连接来自多个数据源的表。如果链接服务器被定义为SQL Server的实例，则可以执行远程存储过程。

链接服务器的功能和必需参数可能会有很大差异。


使用SSMS数据库管理工具创建DBLINK
1、连接服务器-》展开服务器-》展开服务器对象-》展开链接服务器-》右键点击链接服务器-》点击新建链接服务器。

2、在新建链接服务器弹出框-》点击常规-》输入链接服务器名称-》选择服务器类型。

3、在新建链接服务器窗口-》点击安全性-》选择链接服务器的登陆类型-》添加或者删除登陆远程服务器的映射。

4、在新建连接服务器弹出框-》点击服务器选项-》选择服务器选项的属性。

5、在新建链接服务器弹窗框-》点击确定-》在对象资源管理器查看结果。



使用SSMS数据库管理工具创建DBLINK
语法

1. --声明数据库引用
   
2. use master;
   
3. go
   
4. 
   
5. --创建DbLink语法
   
6. --第一步：定义DBLINK类型
   
7. exec master.dbo.sp_addlinkedserver @server='链接服务器名称',@srvproduct='SQL Server';
   
8. go
   
9. 
   
10. --第二步：定义DBLINK连接属性
    
11. --第一种安全性：不建立连接（删除下边的登陆）
    
12. --第二种安全性：不使用安全上下文建立连接
    
13. --exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'链接服务器名称', @locallogin = NULL , @useself = N'False'
    
14. --go
    
15. --第三种安全性：使用登录名的当前安全上下文建立连接
    
16. --exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'链接服务器名称', @locallogin = NULL , @useself = N'True'
    
17. --go
    
18. --第四种安全性：使用此安全上下文建立连接
    
19. --exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname='链接服务器名称',@locallogin=NULL,@useself='False',@rmtuser='登录名',@rmtpassword='密码';
    
20. --go
    
21. 
    
22. --排序规则兼容
    
23. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'collation compatible', @optvalue=N'true' | N'false'
    
24. go
    
25. --数据访问
    
26. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'data access', @optvalue=N'true' | N'false'
    
27. go
    
28. --订阅服务器
    
29. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'dist', @optvalue=N'true' | N'false'
    
30. go
    
31. --发布服务器
    
32. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'pub', @optvalue=N'true' | N'false'
    
33. go
    
34. --RPC
    
35. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'rpc', @optvalue=N'true' | N'false'
    
36. go
    
37. --RPC 超时
    
38. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'rpc out', @optvalue=N'true' | N'false'
    
39. go
    
40. --分发服务器
    
41. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'sub', @optvalue=N'true' | N'false'
    
42. go
    
43. --连接超时值
    
44. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'connect timeout', @optvalue=N'0'
    
45. go
    
46. --排序规则名称
    
47. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'collation name', @optvalue=null
    
48. go
    
49. --惰性架构验证
    
50. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'lazy schema validation', @optvalue=N'true' | N'false'
    
51. go
    
52. --查询超时值
    
53. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'query timeout', @optvalue=N'0'
    
54. go
    
55. --使用远程排序规则
    
56. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'use remote collation', @optvalue=N'true' | N'false'
    
57. go
    
58. --为RPC启用针对分布式事务的升级
    
59. exec master.dbo.sp_serveroption @server=N'链接服务器名称', @optname=N'remote proc transaction promotion', @optvalue=N'true' | N'false'
    
60. go
    

复制代码

语法解析
第一步和第二步必须同时执行，后面DBLINK属性可以不写使用系统默认。

示例：以本机为例

1. --声明数据库引用
   
2. use master;
   
3. go
   
4. 
   
5. --创建DbLink语法
   
6. --第一步：定义DBLINK类型
   
7. exec master.dbo.sp_addlinkedserver @server='TANG\SQLEXPRESS',@srvproduct='SQL Server';
   
8. go
   
9. 
   
10. ----排序规则兼容
    
11. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation compatible', @optvalue=N'false'
    
12. --go
    
13. ----数据访问
    
14. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'data access', @optvalue=N'true'
    
15. --go
    
16. ----订阅服务器
    
17. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'dist', @optvalue=N'false'
    
18. --go
    
19. ----发布服务器
    
20. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'pub', @optvalue=N'false'
    
21. --go
    
22. ----RPC
    
23. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc', @optvalue=N'false'
    
24. --go
    
25. ----RPC 超时
    
26. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'rpc out', @optvalue=N'false'
    
27. --go
    
28. ----分发服务器
    
29. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'sub', @optvalue=N'false'
    
30. --go
    
31. ----连接超时值
    
32. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'connect timeout', @optvalue=N'0'
    
33. --go
    
34. ----排序规则名称
    
35. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'collation name', @optvalue=null
    
36. --go
    
37. ----惰性架构验证
    
38. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'lazy schema validation', @optvalue=N'false'
    
39. --go
    
40. ----查询超时值
    
41. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'query timeout', @optvalue=N'0'
    
42. --go
    
43. ----使用远程排序规则
    
44. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'use remote collation', @optvalue=N'true'
    
45. --go
    
46. ----为RPC启用针对分布式事务的升级
    
47. --exec master.dbo.sp_serveroption @server=N'TEST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
    
48. --go
    
49. 
    
50. --第二步：定义DBLINK连接属性
    
51. --第一种安全性：不建立连接（删除下边的登陆）
    
52. --第二种安全性：不使用安全上下文建立连接
    
53. --exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TANG\SQLEXPRESS', @locallogin = NULL , @useself = N'False'
    
54. --go
    
55. --第三种安全性：使用登录名的当前安全上下文建立连接
    
56. exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TANG\SQLEXPRESS', @locallogin = NULL , @useself = N'True'
    
57. go
    
58. --第四种安全性：使用此安全上下文建立连接
    
59. --exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname='TANG\SQLEXPRESS',@locallogin=NULL,@useself='False',@rmtuser='tests',@rmtpassword='1234';
    
60. --go
    

复制代码

示例结果：显示创建结果



DBLINK使用
示例

1. SELECT * FROM   [testss].[dbo].[test1]     AS A
   
2. INNER JOIN  [TANG\SQLEXPRESS].[testss].[dbo].[test3] AS B ON A.classid=B.id
   

复制代码

结果



DBLINK链接优缺点
优点
1、允许跨服务器访问。
2、数据量少的情况下用dblink比较简单，迅速。
3、可以执行远程存储过程等。
缺点
1、远程查询时易受网络等影响。
2、链接稳定性较差。
3、大量消耗数据库资源。
4、可扩展性较差。
5、维护性差、安全性较低。