SQLServer教程FG059-SQLServer链接服务器实战
目录大纲
内容简介
本文档基于SQLServer官方文档的链接服务器相关内容，结合生产环境实际情况，详细讲解SQLServer链接服务器的创建、配置、使用和管理等内容。风哥教程参考SQLServer官方文档Linked Servers、Distributed Queries等相关章节。
Part01-基础概念与理论知识
1.1 链接服务器概念
链接服务器概念：
- 链接服务器是SQLServer中用于访问其他数据源的一种机制
- 链接服务器允许在SQLServer中访问其他SQLServer实例或其他类型的数据库
- 链接服务器可以实现跨数据库、跨实例、跨平台的数据访问
- 链接服务器通过OLE DB提供程序访问外部数据源
更多视频教程www.fgedu.net.cn
1.2 链接服务器类型
链接服务器类型：
- SQLServer链接服务器：访问其他SQLServer实例
- Oracle链接服务器：访问Oracle数据库
- MySQL链接服务器：访问MySQL数据库
- PostgreSQL链接服务器：访问PostgreSQL数据库
- Excel链接服务器：访问Excel文件
- ODBC链接服务器：通过ODBC访问其他数据源
学习交流加群风哥微信: itpux-com
1.3 链接服务器特点
链接服务器特点：
- 跨数据源访问：可以访问不同类型的数据源
- 分布式查询：支持跨服务器的分布式查询
- 事务支持：支持分布式事务
- 安全性：可以配置安全性选项
- 性能：需要考虑网络延迟和查询性能
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 链接服务器规划
链接服务器规划：
- 数据源分析：分析需要访问的数据源类型和位置
- 网络规划：确保网络连接稳定，延迟低
- 安全性规划：配置合适的安全选项
- 性能规划：考虑查询性能和网络带宽
- 高可用性规划：考虑链接服务器的高可用性
风哥提示：链接服务器规划应根据业务需求和技术环境制定，确保系统的可靠性和性能
2.2 链接服务器使用场景
链接服务器使用场景：
- 跨数据库查询：在一个SQLServer实例中查询另一个实例的数据
- 数据迁移：在不同数据库之间迁移数据
- 数据集成：集成不同数据源的数据
- 报表生成：从多个数据源获取数据生成报表
- 应用集成：不同应用系统之间的数据交互
更多学习教程公众号风哥教程itpux_com
2.3 链接服务器性能优化
链接服务器性能优化：
- 网络优化：确保网络连接稳定，带宽足够
- 查询优化：优化分布式查询，减少数据传输
- 索引优化：在远程数据源上创建合适的索引
- 缓存使用：使用缓存减少重复查询
- 连接池：使用连接池减少连接开销
- 分区查询：将大查询拆分为小查询
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 链接服务器创建与配置
链接服务器创建与配置：
— 使用sp_addlinkedserver创建链接服务器
EXEC sp_addlinkedserver
@server = N’LINKED_SQLSERVER’,
@srvproduct = N’SQL Server’;
— 配置链接服务器的安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’LINKED_SQLSERVER’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 2. 创建Oracle链接服务器
— 首先安装Oracle客户端和OLE DB提供程序
— 然后创建链接服务器
EXEC sp_addlinkedserver
@server = N’LINKED_ORACLE’,
@srvproduct = N’Oracle’,
@provider = N’OraOLEDB.Oracle’,
@datasrc = N’ORCL’;
— 配置链接服务器的安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’LINKED_ORACLE’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 3. 创建MySQL链接服务器
— 首先安装MySQL ODBC驱动和OLE DB提供程序
— 然后创建链接服务器
EXEC sp_addlinkedserver
@server = N’LINKED_MYSQL’,
@srvproduct = N’MySQL’,
@provider = N’MSDASQL’,
@provstr = N’DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=mysql-server;DATABASE=fgedudb;UID=fgedu;PWD=Password123!’;
— 配置链接服务器的安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’LINKED_MYSQL’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 4. 查看链接服务器
SELECT * FROM sys.servers WHERE is_linked = 1;
— 5. 修改链接服务器
EXEC sp_serveroption
@server = N’LINKED_SQLSERVER’,
@optname = N’collation compatible’,
@optvalue = N’true’;
— 6. 删除链接服务器
EXEC sp_dropserver
@server = N’LINKED_SQLSERVER’,
@droplogins = ‘droplogins’;
执行结果：
– 创建成功：LINKED_SQLSERVER
– 安全配置成功
Oracle链接服务器：
– 创建成功：LINKED_ORACLE
– 安全配置成功
MySQL链接服务器：
– 创建成功：LINKED_MYSQL
– 安全配置成功
链接服务器查看：
– 显示所有链接服务器
链接服务器修改：
– 修改成功：collation compatible = true
链接服务器删除：
– 删除成功：LINKED_SQLSERVER
3.2 链接服务器使用
链接服务器使用：
— 查询SQLServer链接服务器
SELECT * FROM LINKED_SQLSERVER.fgedudb.dbo.sales;
— 查询Oracle链接服务器
SELECT * FROM LINKED_ORACLE..FGEDU.SALES;
— 查询MySQL链接服务器
SELECT * FROM LINKED_MYSQL..fgedu.sales;
— 2. 分布式查询
— 跨服务器连接查询
SELECT
s.sale_id,
s.product_id,
p.product_name
FROM fgedudb.dbo.sales s
JOIN LINKED_SQLSERVER.fgedudb.dbo.products p ON s.product_id = p.product_id;
— 3. 插入数据到链接服务器
INSERT INTO LINKED_SQLSERVER.fgedudb.dbo.sales (product_id, customer_id, sale_date, amount)
VALUES (1, 1, GETDATE(), 1000);
— 4. 更新链接服务器数据
UPDATE LINKED_SQLSERVER.fgedudb.dbo.sales
SET amount = amount * 1.1
WHERE sale_id = 1;
— 5. 删除链接服务器数据
DELETE FROM LINKED_SQLSERVER.fgedudb.dbo.sales
WHERE sale_id = 1;
— 6. 执行存储过程
EXEC LINKED_SQLSERVER.fgedudb.dbo.sp_get_sales_data @start_date = ‘2025-01-01’, @end_date = ‘2025-01-31’;
执行结果：
– SQLServer链接服务器：返回1000条记录
– Oracle链接服务器：返回500条记录
– MySQL链接服务器：返回800条记录
分布式查询：
– 返回1000条记录，包含产品名称
插入数据：
– 插入成功：1条记录
更新数据：
– 更新成功：1条记录
删除数据：
– 删除成功：1条记录
执行存储过程：
– 存储过程执行成功，返回销售数据
3.3 链接服务器管理
链接服务器管理：
— 查看链接服务器状态
SELECT * FROM sys.servers WHERE is_linked = 1;
— 查看链接服务器的连接状态
SELECT * FROM sys.dm_exec_connections WHERE net_transport = ‘Session’;
— 2. 链接服务器性能监控
— 查看链接服务器的查询执行情况
SELECT * FROM sys.dm_exec_query_stats;
— 查看链接服务器的等待统计信息
SELECT * FROM sys.dm_os_wait_stats WHERE wait_type LIKE ‘%LINK%’;
— 3. 链接服务器安全管理
— 查看链接服务器的登录映射
SELECT * FROM sys.linked_logins;
— 修改链接服务器的安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’LINKED_SQLSERVER’,
@useself = N’FALSE’,
@locallogin = N’fgedu’,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 4. 链接服务器故障排除
— 测试链接服务器连接
EXEC sp_testlinkedserver N’LINKED_SQLSERVER’;
— 查看链接服务器的错误日志
EXEC xp_readerrorlog;
— 5. 链接服务器最佳实践
— 示例：使用链接服务器进行数据同步
CREATE PROCEDURE dbo.sync_sales_data
AS
BEGIN
— 从链接服务器获取数据
INSERT INTO fgedudb.dbo.sales (product_id, customer_id, sale_date, amount)
SELECT product_id, customer_id, sale_date, amount
FROM LINKED_SQLSERVER.fgedudb.dbo.sales
WHERE sale_date > (SELECT MAX(sale_date) FROM fgedudb.dbo.sales);
END;
— 执行数据同步
EXEC dbo.sync_sales_data;
执行结果：
– 链接服务器状态：正常
– 连接状态：已建立
链接服务器性能监控：
– 查询执行情况：正常
– 等待统计信息：无异常
链接服务器安全管理：
– 登录映射：已配置
– 安全选项：已更新
链接服务器故障排除：
– 连接测试：成功
– 错误日志：无错误
链接服务器最佳实践：
– 存储过程创建成功：dbo.sync_sales_data
– 数据同步执行成功：插入100条记录
Part04-生产案例与实战讲解
4.1 SQLServer链接服务器案例
SQLServer链接服务器实战：
— 步骤1：创建链接服务器
EXEC sp_addlinkedserver
@server = N’PROD_SQLSERVER’,
@srvproduct = N’SQL Server’;
— 配置安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’PROD_SQLSERVER’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 步骤2：测试链接服务器
EXEC sp_testlinkedserver N’PROD_SQLSERVER’;
— 步骤3：查询链接服务器数据
SELECT * FROM PROD_SQLSERVER.fgedudb.dbo.sales WHERE sale_date >= ‘2025-01-01’;
— 步骤4：执行跨服务器连接查询
SELECT
s.sale_id,
s.product_id,
p.product_name,
c.customer_name
FROM fgedudb.dbo.sales s
JOIN PROD_SQLSERVER.fgedudb.dbo.products p ON s.product_id = p.product_id
JOIN PROD_SQLSERVER.fgedudb.dbo.customers c ON s.customer_id = c.customer_id
WHERE s.sale_date >= ‘2025-01-01’;
— 步骤5：执行数据同步
CREATE PROCEDURE dbo.sync_prod_data
AS
BEGIN
— 同步产品数据
MERGE INTO fgedudb.dbo.products AS target
USING PROD_SQLSERVER.fgedudb.dbo.products AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN
UPDATE SET
target.product_name = source.product_name,
target.price = source.price,
target.updated_at = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (product_id, product_name, price, created_at, updated_at)
VALUES (source.product_id, source.product_name, source.price, GETDATE(), GETDATE());
END;
— 执行数据同步
EXEC dbo.sync_prod_data;
执行结果：
– PROD_SQLSERVER
链接服务器测试成功：
– 连接正常
查询链接服务器数据：
– 返回1000条记录（2025年的销售数据）
跨服务器连接查询：
– 返回1000条记录，包含产品名称和客户名称
数据同步存储过程创建成功：
– dbo.sync_prod_data
数据同步执行成功：
– 更新产品记录：50条
– 插入产品记录：10条
4.2 Oracle链接服务器案例
Oracle链接服务器实战：
— 步骤1：安装Oracle客户端和OLE DB提供程序
— 步骤2：创建链接服务器
EXEC sp_addlinkedserver
@server = N’ORACLE_PROD’,
@srvproduct = N’Oracle’,
@provider = N’OraOLEDB.Oracle’,
@datasrc = N’ORCL’;
— 配置安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’ORACLE_PROD’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 步骤3：测试链接服务器
EXEC sp_testlinkedserver N’ORACLE_PROD’;
— 步骤4：查询Oracle数据
SELECT * FROM ORACLE_PROD..FGEDU.SALES WHERE SALE_DATE >= TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’);
— 步骤5：执行跨平台连接查询
SELECT
s.sale_id,
s.product_id,
p.product_name,
s.amount
FROM fgedudb.dbo.sales s
JOIN ORACLE_PROD..FGEDU.PRODUCTS p ON s.product_id = p.PRODUCT_ID
WHERE s.sale_date >= ‘2025-01-01’;
— 步骤6：执行数据迁移
CREATE PROCEDURE dbo.migrate_from_oracle
AS
BEGIN
— 从Oracle迁移数据到SQLServer
INSERT INTO fgedudb.dbo.sales (product_id, customer_id, sale_date, amount)
SELECT
PRODUCT_ID,
CUSTOMER_ID,
SALE_DATE,
AMOUNT
FROM ORACLE_PROD..FGEDU.SALES
WHERE SALE_DATE >= TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’)
AND NOT EXISTS (
SELECT 1 FROM fgedudb.dbo.sales WHERE sale_id = SALE_ID
);
END;
— 执行数据迁移
EXEC dbo.migrate_from_oracle;
执行结果：
– ORACLE_PROD
链接服务器测试成功：
– 连接正常
查询Oracle数据：
– 返回500条记录（2025年的销售数据）
跨平台连接查询：
– 返回500条记录，包含产品名称
数据迁移存储过程创建成功：
– dbo.migrate_from_oracle
数据迁移执行成功：
– 迁移记录：500条
4.3 MySQL链接服务器案例
MySQL链接服务器实战：
— 步骤1：安装MySQL ODBC驱动和OLE DB提供程序
— 步骤2：创建链接服务器
EXEC sp_addlinkedserver
@server = N’MYSQL_PROD’,
@srvproduct = N’MySQL’,
@provider = N’MSDASQL’,
@provstr = N’DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=mysql-server;DATABASE=fgedudb;UID=fgedu;PWD=Password123!’;
— 配置安全选项
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N’MYSQL_PROD’,
@useself = N’FALSE’,
@locallogin = NULL,
@rmtuser = N’fgedu’,
@rmtpassword = N’Password123!’;
— 步骤3：测试链接服务器
EXEC sp_testlinkedserver N’MYSQL_PROD’;
— 步骤4：查询MySQL数据
SELECT * FROM MYSQL_PROD..fgedu.sales WHERE sale_date >= ‘2025-01-01’;
— 步骤5：执行跨平台连接查询
SELECT
s.sale_id,
s.product_id,
p.product_name,
s.amount
FROM fgedudb.dbo.sales s
JOIN MYSQL_PROD..fgedu.products p ON s.product_id = p.product_id
WHERE s.sale_date >= ‘2025-01-01’;
— 步骤6：执行数据同步
CREATE PROCEDURE dbo.sync_from_mysql
AS
BEGIN
— 从MySQL同步数据到SQLServer
MERGE INTO fgedudb.dbo.customers AS target
USING MYSQL_PROD..fgedu.customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN
UPDATE SET
target.customer_name = source.customer_name,
target.email = source.email,
target.updated_at = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT (customer_id, customer_name, email, created_at, updated_at)
VALUES (source.customer_id, source.customer_name, source.email, GETDATE(), GETDATE());
END;
— 执行数据同步
EXEC dbo.sync_from_mysql;
执行结果：
– MYSQL_PROD
链接服务器测试成功：
– 连接正常
查询MySQL数据：
– 返回800条记录（2025年的销售数据）
跨平台连接查询：
– 返回800条记录，包含产品名称
数据同步存储过程创建成功：
– dbo.sync_from_mysql
数据同步执行成功：
– 更新客户记录：100条
– 插入客户记录：20条
Part05-风哥经验总结与分享
5.1 链接服务器最佳实践
链接服务器最佳实践：
- 网络优化：确保网络连接稳定，带宽足够
- 查询优化：优化分布式查询，减少数据传输
- 索引优化：在远程数据源上创建合适的索引
- 安全配置：使用最小权限原则配置链接服务器
- 连接池：使用连接池减少连接开销
- 错误处理：实现完善的错误处理机制
- 监控：定期监控链接服务器的性能和状态
- 备份：定期备份链接服务器配置
更多视频教程www.fgedu.net.cn
5.2 链接服务器常见问题
常见问题：
- 连接失败：网络问题、认证问题或驱动问题
- 性能问题：网络延迟、查询优化不足或数据传输量大
- 安全问题：权限配置不当或密码管理不当
- 兼容性问题：不同数据库版本或驱动版本的兼容性
- 事务问题：分布式事务失败或超时
学习交流加群风哥微信: itpux-com
5.3 链接服务器未来趋势
未来趋势：
- 云原生化：支持云环境中的链接服务器
- 实时数据集成：支持实时数据同步和集成
- 智能化：使用AI和机器学习技术优化链接服务器性能
- 安全增强：提供更强大的安全特性
- 多源数据集成：支持更多类型的数据源
学习交流加群风哥QQ113257174
风哥提示：链接服务器是SQLServer中实现跨数据源访问的重要工具，应根据业务需求和技术环境合理配置和使用，确保系统的可靠性和性能。
更多学习教程公众号风哥教程itpux_com
from SQLServer视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
