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=目录名称,链接到远程数据源,链接到远程数据源（续）,链接到远程 SQL Server若链接到运行 SQL Server 的服务器，只需要提供 srvproduct 和 server 两个参数，不必指定其他参数。SQL Server 自动使用 SQL Server OLE DB 程序提供者（NSQLOLEDB）链接到 OLE DB 数据源若链接到 SQL Server 之外的其他数据源，则当创建链接服务器时，需要指定所有参数,链接到远程数据源,建立链接服务器安全性,在本地服务器和远程服务器间建立安全性当用户登录到本地 SQL Server 并执行分布式查询时，本地 SQL Server 代表用户登录到远程 SQL Server若用户的账户和密码在本地和远程的 SQL Server 中都存在，则本地 SQL Server 可以使用用户凭据登录到远程 SQL Server 中可以使用系统存储过程 sp_addlinkedsrvlogin 在本地和远程 SQL Server 之间映射用户账户和密码可以链接到多个服务器，以及随着每个服务器更改，保留原始客户端的身份验证凭据，这称为安全账户委托若链接服务器不支持安全账户委托，则必须设置从 Windows 身份验证登录账户到链接服务器登录账户的本地登录映射,建立链接服务器安全性,建立链接服务器安全性（续）,系统存储过程 sp_addlinkedsrvlogin语法：sp_addlinkedsrvlogin rmtsrvname=远程服务器名称,useself=是否使用自己的凭据,locallogin=本地登录账户,rmtuser=远程用户名,rmtpassword=远程密码,建立链接服务器安全性,配置链接服务器选项,通过使用 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：确定是使用远程列的排序规则还是本地服务器的排序规则,配置链接服务器选项,配置链接服务器选项（续）,Sp_serveroption 支持的选项（续）Data Access 启用和禁用分布式查询访问的链接服务器 仅用于通过 sp_addlinkedserver 添加的 sysserver 项RPC 和 RPC out RPC 选项从给定的服务器启用 RPC RPC out 选项对给定的服务器启用 RPCLazy Schema Validation 确定是否检查远程表的架构。如果为 true，则在查询开始时跳过远程表的架构检查 推迟架构检查可以提高性能,配置链接服务器选项,获得关于链接服务器的信息,获得关于链接服务器的信息,第19章 多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,在链接服务器上执行查询,使用链接服务器执行链接服务器查询执行直接传递查询,19.4 在链接服务器上执行查询,使用链接服务器,SQL Server 如何优化远程查询SQL Server 从原始分布式查询中析取只访问提供程序数据源中的远程表的语法元素，然后在提供程序上执行简化后的查询引用链接服务器上的对象当进行分布式查询的时候，必须以四部分名称引用链接对象：链接服务器名称.目录名称.架构名称.对象名称,使用链接服务器,使用链接服务器（续）,允许的 Transact-SQL 语句：当使用链接 SQL Server 时，可在链接数据上执行带 WHERE 子句或 JOIN 子句的 SELECT 语句INSERT、UPDATE 和 DELETE 语句不允许的 Transact-SQL 语句在链接服务器上使用 CREATE、ALTER 或 DROP 语句链接表中某个大对象列在选择列表中且包括 ORDER BY 子句的 SELECT 语句READTEXT、WRITETEXT 和 UPDATETEXT 语句,使用链接服务器,执行链接服务器查询,当查询链接服务器上对象的时候，应该使用完全合法的四部分名称来引用对象,SELECT CompanyName,PhoneINTO PhoneList FROM AccountingServer.NorthwindRemote.dbo.Suppliers,示例 1,示例 2,执行链接服务器查询,执行直接传递查询,当查询链接服务器的时候，可以使用 OPENQUERY 函数在链接服务器上执行直接传递查询语法：OPENQUERY(链接服务器名称,查询字符串)使用 OPENQUERY 函数进行直接传递查询可以在 SELECT 语句中用 OPENQUERY 函数的结果代替表名的位置若 OLE DB 提供程序支持，还可以用 OPENQUERY 函数的结果作为 INSERT、UPDATE 或 DELETE 语句的目标表直接传递查询示例,执行直接传递查询,示例,第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 实例间分区数据分区视图的优点不同表的结果可以被组合成为一个结果集，对用户来说好像是一个表，称为分区视图数据的位置对应用程序来说是透明的数据库作为单个实体被编程,分区视图的要求,分区视图的工作原理,分区视图的工作原理,分区视图分区视图允许将大型表中的数据水平拆分成较小的成员表。每个成员表与原表的格式相同，但只有部分行包含成员表的服务器称为成员服务器。每个成员服务器包含一个成员表和一个分布式分区视图访问任一成员服务器上的视图都可得到完整的数据，就像原表在每个服务器上都有一个完整副本一样本地和分布式分区视图如果分区视图所引用的所有成员表都在同一服务器上，则该视图是本地分区视图如果成员表在多台服务器上，则该视图是分布式分区视图,分区视图的工作原理（续）,分区视图的工作原理,实现分区视图所需要的 SQL Server 特性,实现分布式分区视图,设置分布式分区视图的步骤创建多个数据库，每个数据库在不同的运行 SQL Server 实例的成员服务器上通过在各个成员服务器上创建表来水平分割表在每个成员服务器上创建链接服务器定义，这用来向每个成员服务器发送分布式查询在每个成员服务器上，使用 UNION ALL 集合运算符创建分区视图，以组合每个成员表的所有行。每个视图应使用相同的名称，以便引用分布式分区视图名称的查询可以在任一成员服务器上运行,实现分布式分区视图,分区数据的考虑事项,设计时的考虑事项单独分区原始数据库中的表，以使最相关的数据放置在同一个成员服务器上最小化对其他成员服务器上数据的需求。分布式查询应该只占百分之二十，或更少在相同成员服务器上放置完整记录。应将数据行和它们引用的外键行放在相同成员服务器上为了平均分布工作负荷，应该在最能平均分布数据到各个分区表的列上定义分区,分区数据的考虑事项,分区数据的考虑事项（续）,可以使用多种方式将数据分布在所有成员数据库的不同表中。应该考虑分区若干表在每个成员数据库中完整复制其他表在原始服务器中剩下一些表不作变动分区的规则分区表与原始表的格式必须相同。表必须包括相同数量的列，并且列的属性也要相同分区范围不能重叠在每个成员表中必须使用 CHECK 约束来强制值的分区范围,分区数据的考虑事项,第19章 多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,推荐操作,19.9 推荐操作,实验 使用分布式数据,目标设置链接服务器并建立安全性查询链接服务器上的数据从链接服务器导入数据练习 1 设置链接服务器练习 2 查询远程数据可选 管理分布式事务,回顾,学习完本章后，将能够：描述分布式查询编写访问存储在远程 SQL Server 2000 或 OLE DB 数据源上数据的特殊查询设置链接服务器环境以访问存储在远程 SQL Server 2000 或 OLE DB 数据源上的数据编写访问链接服务器上数据的查询执行远程服务器或链接服务器上的存储过程管理分布式事务使用分布式事务修改分布式数据使用分区视图来提升性能,
