SQL Server 2005 提供了链接服务器（Linked Server）功能，该功能允许用户在单个 SQL Server 实例中访问外部数据源，如其他 SQL Server 实例、Oracle 数据库、Excel 文件、OLE DB 数据源等，通过链接服务器，用户可以像查询本地表一样查询远程数据，简化了跨数据库操作，并支持分布式查询和事务处理，以下是关于 SQL Server 2005 链接服务器的详细说明。
链接服务器的概述
链接服务器是 SQL Server 中的一种对象，它通过 OLE DB 或 ODBC 驱动程序建立与外部数据源的连接，SQL Server 2005 支持多种数据源，包括但不限于 SQL Server、Oracle、Sybase、Access、Excel、文本文件等，通过链接服务器，用户可以使用四部分名称（linked_server_name.catalog.schema.object_name）来访问远程对象，SELECT * FROM OracleServer.MyDB.dbo.Employees。
创建链接服务器的方法
在 SQL Server 2005 中，创建链接服务器可以通过 SQL Server Management Studio（SSMS）界面或 T-SQL 脚本实现，以下是两种方式的详细说明：
使用 SSMS 界面创建
- 打开 SSMS，展开服务器节点，右键单击“服务器对象”，选择“新建链接服务器”。
- 在“常规”选项卡中，输入链接服务器的名称（如
OracleServer），选择数据源类型（如Oracle或其他数据源）。 - 如果选择“其他数据源”，需指定 OLE DB 提供程序（如
Microsoft OLE DB Provider for Oracle）。 - 在“产品名称”和“数据源”字段中输入相应的信息（如 Oracle 服务名或 IP 地址）。
- 在“安全”选项卡中配置身份验证方式，可以是“使用此安全上下文”或“使用此安全上下文的凭据”，输入远程服务器的用户名和密码。
- 点击“确定”完成创建。
使用 T-SQL 脚本创建
通过 sp_addlinkedserver 和 sp_addlinkedsrvlogin 存储过程创建链接服务器，以下示例为创建指向 Oracle 数据库的链接服务器：
-- 创建链接服务器
EXEC sp_addlinkedserver
@server = 'OracleServer',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'OracleDB';
-- 配置登录凭据
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'OracleServer',
@useself = 'false',
@rmtuser = 'scott',
@rmtpassword = 'tiger';
链接服务器的配置选项
链接服务器的配置涉及多个参数,以下是关键选项的说明：
| 参数 | 说明 |
|---|---|
@server |
链接服务器的名称，用于 T-SQL 查询中引用。 |
@srvproduct |
数据源的产品名称（如 Oracle、SQL Server）。 |
@provider |
OLE DB 提供程序的名称（如 SQLOLEDB 用于 SQL Server）。 |
@datasrc |
数据源标识符（如服务器名、服务名或文件路径）。 |
@catalog |
默认数据库或目录（可选）。 |
@useself |
是否使用本地登录凭据（true 或 false）。 |
使用链接服务器查询数据
创建链接服务器后,可通过四部分名称查询远程数据。
-- 查询 Oracle 数据库中的表 SELECT * FROM OracleServer.MyDB.dbo.Employees; -- 查询 SQL Server 链接服务器中的表 SELECT * FROM SQL_LinkedServer.RemoteDB.dbo.Customers;
链接服务器支持 OPENQUERY 和 OPENROWSET 函数，用于执行远程查询或传递命令：
-- 使用 OPENQUERY 执行远程查询
SELECT * FROM OPENQUERY(OracleServer, 'SELECT * FROM Employees WHERE Salary > 5000');
-- 使用 OPENROWSET 查询 Excel 文件
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Data.xlsx', 'SELECT * FROM [Sheet1$]');
链接服务器的安全性和性能优化
安全性
- 避免使用
sa或高权限账户链接远程服务器，建议为链接服务器创建专用账户。 - 通过
sp_droplinkedsrvlogin删除不必要的登录映射。 - 启用加密连接（如 SSL/TLS）以保护数据传输安全。
性能优化
- 为远程表创建本地视图或存储过程,减少直接查询。
- 使用
WITH (NOLOCK)提示减少锁争用（但需注意数据一致性问题）。 - 限制查询结果集大小,避免大数据量传输。
常见问题及解决方案
- 链接服务器连接失败
检查网络连通性、OLE DB 提供程序是否正确安装、远程服务器是否允许远程连接。 - 查询超时
增加query timeout值或优化远程查询，减少数据传输量。
相关问答 FAQs
问题 1：如何删除已创建的链接服务器？
解答：使用 sp_dropserver 存储过程删除链接服务器，
EXEC sp_dropserver @server = 'OracleServer', @droplogins = 'true';
@droplogins 参数表示是否同时删除关联的登录映射。
问题 2：如何修改链接服务器的配置？
解答：通过 sp_serveroption 存储过程修改链接服务器的选项，例如启用 RPC（远程过程调用）：
EXEC sp_serveroption @server = 'OracleServer', @optname = 'rpc', @optvalue = 'true';
如需修改登录凭据,需先删除原有映射（sp_droplinkedsrvlogin），再重新创建（sp_addlinkedsrvlogin）。
