SQL19多服务器编程.ppt

数据库程序设计 SQL Server 2000 数据库程序设计,第1章 SQL Server 概述 第2章 创建和管理数据库 第3章 创建数据类型和表 第4章 实现数据完整性 第5章 Transact-SQL介绍 第6章 使用 Transact-SQL查询工具 第7章 检索数据 第8章 数据分组与汇总 第9章 多表联接 第10章 子查询 第11章 修改数据,第12章 全文索引查询 第13章 规划索引 第14章 创建和维护索引 第15章 实现视图 第16章 实现存储过程 第17章 实现用户定义函数 第18章 实现触发器 第19章 多服务器编程 第20章 优化查询性能 第21章 分析查询 第22章 管理事务和锁,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,分布式查询介绍,分布式查询支持 SQL Server 用户访问 存储在多个 SQL Server 实例中的分布式数据 存储在各种可使用 OLE DB 提供程序访问或具有 ODBC 驱动的关系和非关系数据源中的异类数据 从 SQL Server 中访问 OLE DB 数据源 特殊查询：当不需要多次访问数据源的远程数据时，可使用 OPENROWSET 或 OPENDATASOURCE 函数编写特殊查询 链接服务器查询：若要多次访问远程数据，可使用链接服务器和四部分对象名称,19.1 分布式查询介绍,分布式查询介绍（续）,指定在何处处理对 OLE DB 数据源的查询 本地 SQL Server：对链接服务器，SQL Server 默认在本地服务器处理分布式查询 远程 OLE DB 数据源（直接传递查询）：可以使用 OPENQUERY 函数和链接服务器指定对查询的处理在远程服务器上进行，这称为直接传递查询 检查连接设置 在发出分布式查询的对话中，必须打开 ANSI_NULLS 和 ANSI_WARNINGS 选项,19.1 分布式查询介绍,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,在远程数据源上执行特殊查询,OPENROWSET 函数允许用户连接并访问远程数据源的数据，而不用设置链接服务器 语法：OPENROWSET(提供程序名 数据源; 用户名 ; 密码 | 提供程序连接字符串 , 目录名称. 架构名称. 对象名称 | 查询),19.2 在远程数据源上执行特殊查询,在远程数据源上执行特殊查询（续）,特殊查询示例 使用 OPENROWSET 函数执行查询的事实和指导原则 如果 OLE DB 提供程序在指定的数据源中支持多个目录和架构（对 SQL Server 而言，是数据库和对象拥有者），那么就需要目录及架构名称，否则可以省略 传递到 OLE DB 提供程序的用户名的权限确定相关连接的权限 OPENROWSET 函数可以使用在 SELECT 语句 FROM 子句中的表名的位置,19.2 在远程数据源上执行特殊查询,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,设置链接服务器环境,19.3 设置链接服务器环境,链接到远程数据源 建立链接服务器安全性 配置链接服务器选项 获得关于链接服务器的信息,设置链接服务器环境（续）,19.3 设置链接服务器环境,使用链接服务器的原因 链接服务器提供了使用跨 SQL Server 联接和其他查询的手段 通过使用链接服务器，可将 Transact-SQL 语句直接提交给远程 SQL Server，这些语句可作为分布式事务的一部分执行 使用链接服务器的事实和指导原则 可访问存储在多个 SQL Server 上的分布式数据，以及存储在各类关系和非关系数据源的异类数据 若具有 OLE DB 提供程序，非 SQL Server 的其他数据源也可以定义为链接数据库 对于需要经常访问的数据源，应定义链接服务器 关于链接服务器的信息存储在 sysservers 系统表中,链接到远程数据源,为了在远程 SQL Server 或 OLE DB 数据源上执行 Transact-SQL 语句，必须建立到远程服务器或数据源的连接 企业管理器 系统存储过程 sp_addlinkedserver 语法：sp_addlinkedserver server= 服务器名称 ,srvproduct= 产品名称 ,provider= 提供程序名称 ,datasrc= 数据源名称 ,location= 位置 ,provstr= 提供程序连接字符串 ,catalog= 目录名称 ,19.3.1 链接到远程数据源,链接到远程数据源（续）,链接到远程 SQL Server 若链接到运行 SQL Server 的服务器，只需要提供 srvproduct 和 server 两个参数，不必指定其他参数。SQL Server 自动使用 SQL Server OLE DB 程序提供者（NSQLOLEDB） 链接到 OLE DB 数据源 若链接到 SQL Server 之外的其他数据源，则当创建链接服务器时，需要指定所有参数,19.3.1 链接到远程数据源,建立链接服务器安全性,在本地服务器和远程服务器间建立安全性 当用户登录到本地 SQL Server 并执行分布式查询时，本地 SQL Server 代表用户登录到远程 SQL Server 若用户的账户和密码在本地和远程的 SQL Server 中都存在，则本地 SQL Server 可以使用用户凭据登录到远程 SQL Server 中 可以使用系统存储过程 sp_addlinkedsrvlogin 在本地和远程 SQL Server 之间映射用户账户和密码 可以链接到多个服务器，以及随着每个服务器更改，保留原始客户端的身份验证凭据，这称为安全账户委托 若链接服务器不支持安全账户委托，则必须设置从 Windows 身份验证登录账户到链接服务器登录账户的本地登录映射,19.3.2 建立链接服务器安全性,建立链接服务器安全性（续）,系统存储过程 sp_addlinkedsrvlogin 语法：sp_addlinkedsrvlogin rmtsrvname= 远程服务器名称 , useself= 是否使用自己的凭据 , locallogin= 本地登录账户 , rmtuser= 远程用户名 , rmtpassword= 远程密码,19.3.2 建立链接服务器安全性,配置链接服务器选项,通过使用 sp_serveroption 系统存储过程为链接服务器设置选项 语法：sp_serveroption 服务器名称, 选项名称, 选项值 只有 sysadmin 服务器角色的成员才能设置选项 Sp_serveroption 支持的选项 Collation Compatible 如果该选项设置为 true，则 SQL Server 假定链接服务器中的所有字符在字符集和排序规则上与本地服务器兼容 Collation Name 和 Use Remote Collation Collation Name：如果 use remote collation 是 true，而数据源不是 SQL Server 数据源，则指定远程数据源使用的排序规则名称 Use Remote Collation：确定是使用远程列的排序规则还是本地服务器的排序规则,19.3.3 配置链接服务器选项,配置链接服务器选项（续）,Sp_serveroption 支持的选项（续） Data Access 启用和禁用分布式查询访问的链接服务器 仅用于通过 sp_addlinkedserver 添加的 sysserver 项 RPC 和 RPC out RPC 选项从给定的服务器启用 RPC RPC out 选项对给定的服务器启用 RPC Lazy Schema Validation 确定是否检查远程表的架构。 如果为 true，则在查询开始时跳过远程表的架构检查 推迟架构检查可以提高性能,19.3.3 配置链接服务器选项,获得关于链接服务器的信息,19.3.4 获得关于链接服务器的信息,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,在链接服务器上执行查询,使用链接服务器 执行链接服务器查询 执行直接传递查询,19.4 在链接服务器上执行查询,使用链接服务器,SQL Server 如何优化远程查询 SQL Server 从原始分布式查询中析取只访问提供程序数据源中的远程表的语法元素，然后在提供程序上执行简化后的查询 引用链接服务器上的对象 当进行分布式查询的时候，必须以四部分名称引用链接对象：链接服务器名称.目录名称.架构名称.对象名称,19.4.1 使用链接服务器,使用链接服务器（续）,允许的 Transact-SQL 语句：当使用链接 SQL Server 时，可在链接数据上执行 带 WHERE 子句或 JOIN 子句的 SELECT 语句 INSERT、UPDATE 和 DELETE 语句 不允许的 Transact-SQL 语句 在链接服务器上使用 CREATE、ALTER 或 DROP 语句 链接表中某个大对象列在选择列表中且包括 ORDER BY 子句的 SELECT 语句 READTEXT、WRITETEXT 和 UPDATETEXT 语句,19.4.1 使用链接服务器,执行链接服务器查询,当查询链接服务器上对象的时候，应该使用完全合法的四部分名称来引用对象,SELECT CompanyName, Phone INTO PhoneList FROM AccountingServer.NorthwindRemote.dbo.Suppliers,示例 1,示例 2,19.4.2 执行链接服务器查询,执行直接传递查询,当查询链接服务器的时候，可以使用 OPENQUERY 函数在链接服务器上执行直接传递查询 语法：OPENQUERY (链接服务器名称, 查询字符串) 使用 OPENQUERY 函数进行直接传递查询 可以在 SELECT 语句中用 OPENQUERY 函数的结果代替表名的位置 若 OLE DB 提供程序支持，还可以用 OPENQUERY 函数的结果作为 INSERT、UPDATE 或 DELETE 语句的目标表 直接传递查询示例,19.4.3 执行直接传递查询,示例,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,在链接服务器上执行存储过程,19.5 在链接服务器上执行存储过程,在链接服务器上执行存储过程，可允许连接到一个 SQL Server 的客户端执行另一个 SQL Server 上的存储过程，而不用建立到那个 SQL Server 的客户端连接 语法：EXECUTE 服务器名称.数据库名称.拥有者名称.存储过程名称 客户端连接的服务器接受客户端请求，将请求发送到链接服务器。EXECUTE 语句必须包含链接服务器的名称 链接服务器处理请求，将结果返回给原来的服务器，后者再接着把结果传递给客户端 客户端或服务器上的应用程序都可以发起链接存储过程请求,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,管理分布式事务,分布式事务是涉及来自两个或多个源的资源的事务 使用 MS DTC 管理分布式事务 Microsoft 分布式事务处理协调器 (MS DTC) 在所有参与事务的服务器间协调分布式事务的提交。服务器可以包括 SQL Server 以及中间层组件 可在 SQL Server 存储过程中使用 MS DTC 协调多台运行 SQL Server 的计算机或 SQL Server 与 链接服务器间的事务 可将运行 SQL Server 的远程计算机添加到分布式事务中 使用组件服务管理分布式事务 使用组件服务以部署和管理分布式事务。其底层实现机制是 MS DTC。在中间层的组件可以参与分布式事务,19.6 管理分布式事务,管理分布式事务（续）,分布式数据的处理 SQL Server 提供了链接服务器、RPC 和 两阶段提交协议等特性，使用户可以很容易地管理和使用分布式环境里的数据 分布式事务的两阶段提交 RPC：是可以从远程服务器调用的存储过程，允许服务器到服务器通讯 RPC 也可以被 MS DTC 服务协调以确保事务的 ACID 属性 默认在事务的上下文中不执行 RPC 也可以通过定义和访问链接服务器的方式管理分布式数据,19.6 管理分布式事务,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,修改链接服务器上的数据,当要修改链接服务器上的数据的时候，必须进行分布式事务 执行 BEGIN DISTRIBUTED TRANSACTION 语句 或者从客户端应用程序调用 API 函数 使用分布式事务 BEGIN DISTRIBUTED TRANSACTION 语句不能嵌套 ROLLBACK TRANSACTION 语句回滚整个分布式事务 不支持保存点。若 SQL Server 回滚了一个分布式事务，则整个事务回滚到分布式事务的起始点，无论是否有保存点 当执行链接服务器间的分布式事务的时候，必须设置 XACT_ABORT 会话选项,19.7 修改链接服务器上的数据,第19章 多服务器编程,分布式查询介绍 在远程数据源上执行特殊查询 设置链接服务器环境 在链接服务器上执行查询 在链接服务器上执行存储过程 管理分布式事务 修改链接服务器上的数据 使用分区视图 推荐操作,使用分区视图,分区视图的要求 分区视图的工作原理 实现分布式分区视图 分区数据的考虑事项,19.8 使用分区视图,分区视图的要求,可伸缩性 增加更多硬件到单个服务器 在多个独立计算机间划分工作负荷和数据库 分区视图：使用视图在多个数据库或 SQL Server 实例间分区数据 分区视图的优点 不同表的结果可以被组合成为一个结果集，对用户来说好像是一个表，称为分区视图 数据的位置对应用程序来说是透明的 数据库作为单个实体被编程,19.8.1 分区视图的要求,分区视图的工作原理,19.8.2 分区视图的工作原理,分区视图 分区视图允许将大型表中的数据水平拆分成较小的成员表。每个成员表与原表的格式相同，但只有部分行 包含成员表的服务器称为成员服务器。每个成员服务器包含一个成员表和一个分布式分区视图 访问任一成员服务器上的视图都可得到完整的数据，就像原表在每个服务器上都有一个完整副本一样 本地和分布式分区视图 如果分区视图所引用的所有成员表都在同一服务器上，则该视图是本地分区视图 如果成员表在多台服务器上，则该视图是分布式分区视图,分区视图的工作原理（续）,19.8.2 分区视图的工作原理,实现分区视图所需要的 SQL Server 特性,实现分布式分区视图,设置分布式分区视图的步骤 创建多个数据库，每个数据库在不同的运行 SQL Server 实例的成员服务器上 通过在各个成员服务器上创建表来水平分割表 在每个成员服务器上创建链接服务器定义，这用来向每个成员服务器发送分布式查询 在每个成员服务器上，使用 UNION ALL 集合运算符创建分区视图，以组合每个成员表的所有行。每个视图应使用相同的名称，以便引用分布式分区视图名称的查询可以在任一成员服务器上运行,19.8.3 实现分布式分区视图,分区数据的考虑事项