SQL19多服务器编程.ppt

数据库程序设计SQLServer2000数据库程序设计,第1章SQLServer概述第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章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,分布式查询介绍,分布式查询支持SQLServer用户访问存储在多个SQLServer实例中的分布式数据存储在各种可使用OLEDB提供程序访问或具有ODBC驱动的关系和非关系数据源中的异类数据从SQLServer中访问OLEDB数据源特殊查询：当不需要多次访问数据源的远程数据时，可使用OPENROWSET或OPENDATASOURCE函数编写特殊查询链接服务器查询：若要多次访问远程数据，可使用链接服务器和四部分对象名称,19.1分布式查询介绍,分布式查询介绍（续）,指定在何处处理对OLEDB数据源的查询本地SQLServer：对链接服务器，SQLServer默认在本地服务器处理分布式查询远程OLEDB数据源（直接传递查询）：可以使用OPENQUERY函数和链接服务器指定对查询的处理在远程服务器上进行，这称为直接传递查询检查连接设置在发出分布式查询的对话中，必须打开ANSI_NULLS和ANSI_WARNINGS选项,19.1分布式查询介绍,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,在远程数据源上执行特殊查询,OPENROWSET函数允许用户连接并访问远程数据源的数据，而不用设置链接服务器语法：OPENROWSET(提供程序名数据源;用户名;密码|提供程序连接字符串,目录名称.架构名称.对象名称|查询),19.2在远程数据源上执行特殊查询,在远程数据源上执行特殊查询（续）,特殊查询示例使用OPENROWSET函数执行查询的事实和指导原则如果OLEDB提供程序在指定的数据源中支持多个目录和架构（对SQLServer而言，是数据库和对象拥有者），那么就需要目录及架构名称，否则可以省略传递到OLEDB提供程序的用户名的权限确定相关连接的权限OPENROWSET函数可以使用在SELECT语句FROM子句中的表名的位置,19.2在远程数据源上执行特殊查询,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,设置链接服务器环境,19.3设置链接服务器环境,链接到远程数据源建立链接服务器安全性配置链接服务器选项获得关于链接服务器的信息,设置链接服务器环境（续）,19.3设置链接服务器环境,使用链接服务器的原因链接服务器提供了使用跨SQLServer联接和其他查询的手段通过使用链接服务器，可将Transact-SQL语句直接提交给远程SQLServer，这些语句可作为分布式事务的一部分执行使用链接服务器的事实和指导原则可访问存储在多个SQLServer上的分布式数据，以及存储在各类关系和非关系数据源的异类数据若具有OLEDB提供程序，非SQLServer的其他数据源也可以定义为链接数据库对于需要经常访问的数据源，应定义链接服务器关于链接服务器的信息存储在sysservers系统表中,链接到远程数据源,为了在远程SQLServer或OLEDB数据源上执行Transact-SQL语句，必须建立到远程服务器或数据源的连接企业管理器系统存储过程sp_addlinkedserver语法：sp_addlinkedserverserver=服务器名称,srvproduct=产品名称,provider=提供程序名称,datasrc=数据源名称,location=位置,provstr=提供程序连接字符串,catalog=目录名称,19.3.1链接到远程数据源,链接到远程数据源（续）,链接到远程SQLServer若链接到运行SQLServer的服务器，只需要提供srvproduct和server两个参数，不必指定其他参数。SQLServer自动使用SQLServerOLEDB程序提供者（NSQLOLEDB）链接到OLEDB数据源若链接到SQLServer之外的其他数据源，则当创建链接服务器时，需要指定所有参数,19.3.1链接到远程数据源,建立链接服务器安全性,在本地服务器和远程服务器间建立安全性当用户登录到本地SQLServer并执行分布式查询时，本地SQLServer代表用户登录到远程SQLServer若用户的账户和密码在本地和远程的SQLServer中都存在，则本地SQLServer可以使用用户凭据登录到远程SQLServer中可以使用系统存储过程sp_addlinkedsrvlogin在本地和远程SQLServer之间映射用户账户和密码可以链接到多个服务器，以及随着每个服务器更改，保留原始客户端的身份验证凭据，这称为安全账户委托若链接服务器不支持安全账户委托，则必须设置从Windows身份验证登录账户到链接服务器登录账户的本地登录映射,19.3.2建立链接服务器安全性,建立链接服务器安全性（续）,系统存储过程sp_addlinkedsrvlogin语法：sp_addlinkedsrvloginrmtsrvname=远程服务器名称,useself=是否使用自己的凭据,locallogin=本地登录账户,rmtuser=远程用户名,rmtpassword=远程密码,19.3.2建立链接服务器安全性,配置链接服务器选项,通过使用sp_serveroption系统存储过程为链接服务器设置选项语法：sp_serveroption服务器名称,选项名称,选项值只有sysadmin服务器角色的成员才能设置选项Sp_serveroption支持的选项CollationCompatible如果该选项设置为true，则SQLServer假定链接服务器中的所有字符在字符集和排序规则上与本地服务器兼容CollationName和UseRemoteCollationCollationName：如果useremotecollation是true，而数据源不是SQLServer数据源，则指定远程数据源使用的排序规则名称UseRemoteCollation：确定是使用远程列的排序规则还是本地服务器的排序规则,19.3.3配置链接服务器选项,配置链接服务器选项（续）,Sp_serveroption支持的选项（续）DataAccess启用和禁用分布式查询访问的链接服务器仅用于通过sp_addlinkedserver添加的sysserver项RPC和RPCoutRPC选项从给定的服务器启用RPCRPCout选项对给定的服务器启用RPCLazySchemaValidation确定是否检查远程表的架构。如果为true，则在查询开始时跳过远程表的架构检查推迟架构检查可以提高性能,19.3.3配置链接服务器选项,获得关于链接服务器的信息,19.3.4获得关于链接服务器的信息,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,在链接服务器上执行查询,使用链接服务器执行链接服务器查询执行直接传递查询,19.4在链接服务器上执行查询,使用链接服务器,SQLServer如何优化远程查询SQLServer从原始分布式查询中析取只访问提供程序数据源中的远程表的语法元素，然后在提供程序上执行简化后的查询引用链接服务器上的对象当进行分布式查询的时候，必须以四部分名称引用链接对象：链接服务器名称.目录名称.架构名称.对象名称,19.4.1使用链接服务器,使用链接服务器（续）,允许的Transact-SQL语句：当使用链接SQLServer时，可在链接数据上执行带WHERE子句或JOIN子句的SELECT语句INSERT、UPDATE和DELETE语句不允许的Transact-SQL语句在链接服务器上使用CREATE、ALTER或DROP语句链接表中某个大对象列在选择列表中且包括ORDERBY子句的SELECT语句READTEXT、WRITETEXT和UPDATETEXT语句,19.4.1使用链接服务器,执行链接服务器查询,当查询链接服务器上对象的时候，应该使用完全合法的四部分名称来引用对象,SELECTCompanyName,PhoneINTOPhoneListFROMAccountingServer.NorthwindRemote.dbo.Suppliers,示例1,示例2,19.4.2执行链接服务器查询,执行直接传递查询,当查询链接服务器的时候，可以使用OPENQUERY函数在链接服务器上执行直接传递查询语法：OPENQUERY(链接服务器名称,查询字符串)使用OPENQUERY函数进行直接传递查询可以在SELECT语句中用OPENQUERY函数的结果代替表名的位置若OLEDB提供程序支持，还可以用OPENQUERY函数的结果作为INSERT、UPDATE或DELETE语句的目标表直接传递查询示例,19.4.3执行直接传递查询,示例,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,在链接服务器上执行存储过程,19.5在链接服务器上执行存储过程,在链接服务器上执行存储过程，可允许连接到一个SQLServer的客户端执行另一个SQLServer上的存储过程，而不用建立到那个SQLServer的客户端连接语法：EXECUTE服务器名称.数据库名称.拥有者名称.存储过程名称客户端连接的服务器接受客户端请求，将请求发送到链接服务器。EXECUTE语句必须包含链接服务器的名称链接服务器处理请求，将结果返回给原来的服务器，后者再接着把结果传递给客户端客户端或服务器上的应用程序都可以发起链接存储过程请求,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,管理分布式事务,分布式事务是涉及来自两个或多个源的资源的事务使用MSDTC管理分布式事务Microsoft分布式事务处理协调器(MSDTC)在所有参与事务的服务器间协调分布式事务的提交。服务器可以包括SQLServer以及中间层组件可在SQLServer存储过程中使用MSDTC协调多台运行SQLServer的计算机或SQLServer与链接服务器间的事务可将运行SQLServer的远程计算机添加到分布式事务中使用组件服务管理分布式事务使用组件服务以部署和管理分布式事务。其底层实现机制是MSDTC。在中间层的组件可以参与分布式事务,19.6管理分布式事务,管理分布式事务（续）,分布式数据的处理SQLServer提供了链接服务器、RPC和两阶段提交协议等特性，使用户可以很容易地管理和使用分布式环境里的数据分布式事务的两阶段提交RPC：是可以从远程服务器调用的存储过程，允许服务器到服务器通讯RPC也可以被MSDTC服务协调以确保事务的ACID属性默认在事务的上下文中不执行RPC也可以通过定义和访问链接服务器的方式管理分布式数据,19.6管理分布式事务,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,修改链接服务器上的数据,当要修改链接服务器上的数据的时候，必须进行分布式事务执行BEGINDISTRIBUTEDTRANSACTION语句或者从客户端应用程序调用API函数使用分布式事务BEGINDISTRIBUTEDTRANSACTION语句不能嵌套ROLLBACKTRANSACTION语句回滚整个分布式事务不支持保存点。若SQLServer回滚了一个分布式事务，则整个事务回滚到分布式事务的起始点，无论是否有保存点当执行链接服务器间的分布式事务的时候，必须设置XACT_ABORT会话选项,19.7修改链接服务器上的数据,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,使用分区视图,分区视图的要求分区视图的工作原理实现分布式分区视图分区数据的考虑事项,19.8使用分区视图,分区视图的要求,可伸缩性增加更多硬件到单个服务器在多个独立计算机间划分工作负荷和数据库分区视图：使用视图在多个数据库或SQLServer实例间分区数据分区视图的优点不同表的结果可以被组合成为一个结果集，对用户来说好像是一个表，称为分区视图数据的位置对应用程序来说是透明的数据库作为单个实体被编程,19.8.1分区视图的要求,分区视图的工作原理,19.8.2分区视图的工作原理,分区视图分区视图允许将大型表中的数据水平拆分成较小的成员表。每个成员表与原表的格式相同，但只有部分行包含成员表的服务器称为成员服务器。每个成员服务器包含一个成员表和一个分布式分区视图访问任一成员服务器上的视图都可得到完整的数据，就像原表在每个服务器上都有一个完整副本一样本地和分布式分区视图如果分区视图所引用的所有成员表都在同一服务器上，则该视图是本地分区视图如果成员表在多台服务器上，则该视图是分布式分区视图,分区视图的工作原理（续）,19.8.2分区视图的工作原理,实现分区视图所需要的SQLServer特性,实现分布式分区视图,设置分布式分区视图的步骤创建多个数据库，每个数据库在不同的运行SQLServer实例的成员服务器上通过在各个成员服务器上创建表来水平分割表在每个成员服务器上创建链接服务器定义，这用来向每个成员服务器发送分布式查询在每个成员服务器上，使用UNIONALL集合运算符创建分区视图，以组合每个成员表的所有行。每个视图应使用相同的名称，以便引用分布式分区视图名称的查询可以在任一成员服务器上运行,19.8.3实现分布式分区视图,分区数据的考虑事项,设计时的考虑事项单独分区原始数据库中的表，以使最相关的数据放置在同一个成员服务器上最小化对其他成员服务器上数据的需求。分布式查询应该只占百分之二十，或更少在相同成员服务器上放置完整记录。应将数据行和它们引用的外键行放在相同成员服务器上为了平均分布工作负荷，应该在最能平均分布数据到各个分区表的列上定义分区,19.8.4分区数据的考虑事项,分区数据的考虑事项（续）,可以使用多种方式将数据分布在所有成员数据库的不同表中。应该考虑分区若干表在每个成员数据库中完整复制其他表在原始服务器中剩下一些表不作变动分区的规则分区表与原始表的格式必须相同。表必须包括相同数量的列，并且列的属性也要相同分区范围不能重叠在每个成员表中必须使用CHECK约束来强制值的分区范围,19.8.4分区数据的考虑事项,第19章多服务器编程,分布式查询介绍在远程数据源上执行特殊查询设置链接服务器环境在链接服务器上执行查询在链接服务器上执行存储过程管理分布式事务修改链接服务器上的数据使用分区视图推荐操作,推荐操作,19.9推荐操作,实验使用分布式数据,目标设置链接服务器并建立安全性查询链接服务器上的数据从链接服务器导入数据练习1设置链接服务器练习2查询远程数据可选管理分布式事务,回顾,学习完本章后，将能够：描述分布式查询编写访问存储在远程SQLServer2000或OLEDB数据源上数据的特殊查询设置链接服务器环境以访问存储在远程SQLServer2000或OLEDB数据源上的数据编写访问链接服务器上数据的查询执行远程服务器或链接服务器上的存储过程管理分布式事务使用分布式事务修改分布式数据使用分区视图来提升性能,