SQL Server 本地连接远程数据库的三种实现方案

结果或目标

实现本地 SQL Server 实例对远程实例的透明访问，支持 SELECT 查询、INSERT 数据导入、以及跨库联合查询与 UPDATE 更新操作。

准备条件

• 权限：执行远程操作的用户需具备本地服务器的管理权限（用于创建链接）及远程服务器的访问权限。
• 网络：本地与服务端之间网络互通，且远程 SQL Server 已启用远程连接并开放 TCP 端口（默认 1433）。
• 驱动：确保环境中安装有 SQLOLEDB 或 MSDASQL 等 OLE DB 提供者。

真实操作顺序

第1步：配置链接服务器（推荐用于频繁访问）

目的：建立持久化的远程服务器别名，简化后续查询语法，支持标准的四部分命名法（Server.Database.Schema.Table）。

入口：SQL Server Management Studio (SSMS) 查询窗口或 T-SQL 脚本。

动作：

1. 添加链接服务器：使用 sp_addlinkedserver 存储过程创建别名（此处以 'ITSV' 为例）。
2. 配置登录映射：使用 sp_addlinkedsrvlogin 设置安全认证方式。

-- 创建链接服务器
exec sp_addlinkedserver 'ITSV', '', 'SQLOLEDB', '远程服务器名或ip地址'

-- 设置登录名和密码（使用固定用户名方式）
exec sp_addlinkedsrvlogin 'ITSV', 'false', null, '用户名', '密码'

完成状态：执行成功后，可在“对象资源管理器”中看到新增的 Linked Servers 节点，或通过系统视图确认存在该链接。

第2步：基于链接服务器进行操作

目的：利用已配置的别名进行增删改查操作。

动作：

1. 查询远程数据：

select * from ITSV.数据库名.dbo.表名

2. 将远程数据导入本地表（创建新表或覆盖现有表）：

select * into 本地表 FROM ITSV.数据库名.dbo.远程表名

3. 更新本地表数据（基于远程数据）：

update b 
set b.列A=a.列A 
from ITSV.数据库名.dbo.远程表名 as a 
inner join 本地表 b on a.column1=b.column1

注意：操作完成后，若不再需要该连接，应清理以避免安全隐患：

exec sp_dropserver 'ITSV', 'droplogins'

第3步：使用 OpenRowSet 进行瞬时连接（无需预配置）

目的：在一次性的查询或导入操作中直接指定连接字符串，无需预先创建链接服务器。

动作：

1. 直接查询远程表：

select * from openrowset('SQLOLEDB', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)

2. 生成本地表并导入数据：

select * into 表 from openrowset('SQLOLEDB', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)

3. 将本地表数据插入远程表：

insert openrowset('SQLOLEDB', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名)
select * from 本地表

4. 更新远程表数据（通过别名关联）：

update b 
set b.列A=a.列A 
from openrowset('SQLOLEDB', 'sql服务器名'; '用户名'; '密码', 数据库名.dbo.表名) as a 
inner join 本地表 b on a.column1=b.column1

第4步：使用 OpenQuery 优化复杂查询

目的：当远程表数据量大或查询条件复杂时，openquery 可将查询谓词下推至远程服务器执行，提高性能。此方法仍需依赖步骤1中创建的链接服务器。

动作：

1. 查询示例：

select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名')

2. 更新本地表数据（基于远程查询结果）：

update b 
set b.列B=a.列B 
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名') as a 
inner join 本地表 b on a.列A=b.列A

第5步：使用 Opendatasource 进行独立连接

目的：类似于 openrowset，但通常用于不需要定义列模式或更底层的OLE DB 访问场景。

动作：

SELECT * 
FROM opendatasource('SQLOLEDB', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码').test.dbo.roy_ta

完成判断

• 连通性测试：执行上述任一 SELECT 语句若能返回预期行集，则说明网络连接、身份验证及权限配置正确。
• 性能观察：对于大量数据传输，建议使用 INSERT ... SELECT 或 Bulk Insert，避免逐行处理带来的延迟。
• 安全性检查：确认生产环境中是否必须明文存储密码。若涉及敏感数据，建议改用 Windows 身份验证或配置加密的连接字符串。

简要总结

SQL Server 提供了灵活的远程数据访问机制。对于高频交互业务，推荐使用 sp_addlinkedserver 建立稳定的链接服务器；对于一次性报告或临时数据同步，openrowset 和 opendatasource 提供了便捷的无配置接入方式。开发者应根据具体的业务场景、数据量级及安全要求选择最合适的方案。

以上就是 SQL Server 本地连接远程数据库的详细操作方法，更多关于数据库远程交互的资料请关注本站其它相关文章！