SQLSEVER体系结构.doc

SQL SERVER ARCHITECTURETURLEYPROFESSIONAL SQL SERVER 2005 REPORTING SERVICES，Chapter 3，Reporting Sevices Architecture，TURLEYSecond EditionNew York：John Willey，2007Page71Page121SQL SEVER 体系结构特利SQL SEVER 2005专业报告服务，第三章，报告服务体系结构，特利第二版纽约：约翰威利出版社，2007第71页第121页1通信SQL Server使用分层的通信体系结构，把应用程序和底层的网络和协议隔离开来。这个体系结构允许用户在不同的网络环境中实施同一个应用程序。通信体系结构中的组件包括：应用程序、数据库接、网络库、 客户机和服务器可以同时使用多个Net-Library，但是为了成功通信，它们必须使用一个公共的网络库。SQL Server支持这样的一些网络协议：TCP/IP，命名管道，NOVELL IPX/SPX，BANYAN VINES/IP和APPLETALK ADSP。表数据流（TDS） 这是一个应用程序层的协议，用于客户应用程序与SQL Server之间的通信。TDS包封装在为网络库使用的协议栈创建的网络数据包中，例如，如果使用Windows sockets Net-Library，则TDS数据包封装在底层协议的TCP/IP数据包中。开放数据服务 这是一个SQL Server组件，处理网络连接，把客户的请求传给SQL Server进行处理，并把任何结果和应答返回给SQL Server客户。开放数据服务自动监听在服务器上安装的所有服务器网络库。2应用程序开发用户通过使用一个数据对象接口或API写的应用程序访问SQL Server。SQL Server支持常用的和正在出现的数据接口，它支持底层的固有的API，以及容易使用的数据对象接口。2.1 应用程序编程接口数据库API定义如何写一个应用程序连接到数据库，和把命令传递给数据库。SQL Server提供对两大类数据库API的固有支持，而这些API有确定可以使用的数据对象接口。使用数据库API对应用程序行为和性能有更大的控制。OLE DB 这是一个基于组件对象模型（COM）的数据访问接口，它支持使用OLE DB的数据对象接口写的应用程序。OLE DB可以访问SQL Server，其他关系数据库和其他数据源中的数据。ODBC 这是一个调用级的接口，它直接访问SQL Server TDS协议，并支持使用ODBC的数据对象接口写的应用程序或组件。ODBC可以访问SQL Server和其他关系数据库中的数据，但一般不能用于访问其他数据源中的数据。2.2 数据对象接口一般来说，数据对象接口比数据库API更容易使用，但没有显露API那样多的功能。ActiveX数据对象（ADO） ActiveX数据对象（ADO）在一个简化的数据模型中封装OLE DB API，降低了应用程序开发和维护的成本。ADO可以从Microsoft Visual Basic, Visual Basic for Applications, Active Server Pages(ASP)和Microsoft Internet Explorer脚本对象模型中使用。远程数据对象（RDO） RDO映射和封装ODBC API，可以从Microsoft Visual Basic和Visual Basic for Applications中使用。3.使用 SQL Server3.1设计SQL Server应用程序计划一个数据库设计要求想建模的业务对象的知识，和用于表示这些业务功能的数据库概念及特性。在设计一个SQL Server应用程序之前，花一些时间设计一个数据库对业务对象准确建模是很重要的。一个很好设计的数据库要求极少的更改通常更有效的执行。选择的体系结构将影响如何开发、部署和管理软件应用程序。3.2 软件体系结构可以使用某个应用程序体系结构实现客户机/服务器应用程序，但是选定一个分层的应用程序方法会得到灵活性和管理选项的选择。可以把软件应用程序分为3个逻辑层，他们可以位于一个或多个服务器上。3.3 体系结构设计典型的应用程序部署选项包括：智能服务器（两层） 大多数的处理发生在服务器上，而表示服务在客户机上处理。在许多例子中，业务服务逻辑的大部分在数据库中实现。这个设计在客户机没有足够的资源处理业务逻辑时是很有用的。但是，服务器会变成一个瓶颈，因为数据库和业务服务竞争相同的硬件资源。以数据库为中心的视点设计的团体应用程序是这种设计的一个例子。智能客户机（两层） 大多数处理发生在客户机上，而数据服务在服务器上处理。这个设计是广泛使用的传统客户机/服务器环境，但是网络交通会很繁忙，而且事务更长，这又会影响性能。使用Microsoft Access这样的产品为小单位开发的应用程序是这种设计的一个例子。N层 处理在数据库服务器、应用程序服务器和客户机之间划分。这个方法把逻辑与数据服务分开，可以根据需要很容易添加更多的应用程序服务器或数据库服务器。但是，复杂的可能性增加，且这个方法对小型应用程序较慢。多层企业应用程序和使用事务处理监视程序开发的应用程序是这种设计的一个例子。Internet 处理分为3层，业务和表示服务位于Web服务器上，客户机使用简单的浏览器。有浏览器的任何客户机都可以被支持，且软件不需要在客户机上维护。使用几个Web服务器管理客户机的连接，和使用一个SQL Server数据库服务数据请求的Web站点是这种设计的一个例子。4.实现SQL Server数据库实现一个SQL Server数据库意味着计划，创建和维护一些相互关联的组件。数据库应用程序的性质和复杂性，以及计划数据库的过程，它们之间的差异是非常大的。例如，一个数据可以很简单，有一个人设计即可；它也可以很大，很复杂，处理成千上万客户的银行事务。不管数据库的规模和复杂性符合，实现一个数据库通常都包括以下任务：设计数据库。使得应用程序最优化的使用硬件，并允许未来的扩展；标识和建模数据库对象和应用程序逻辑，规定每个对象的信息类型和关系的类型。创建数据库和数据库对象。包括表、数据库完整性机制、数据输入和获取对象（常常是存储过程）、合适的索引、和安全策略。测试和调试应用程序与数据库。设计数据库时，要保证数据库正确迅速的执行重要的功能。正确的数据库设计、索引的的正确使用、RAID和文件组，它们的组合对于获得好的性能是必需的。设计应用。包括分析工作负载和建议SQL Server数据库的最优索引配置。应用后的应用程序管理。包括配置服务器和客户机，监视服务器性能，管理作业、报警和操作员，管理安全策略，和管理数据库备份过程。5.存储过程5.1介绍存储过程本节介绍不同类型的存储过程，描述存储过程的处理，包括初始处理和后续执行，并列出使用存储过程的一些优点。5.2定义存储过程存储过程是SQL语句的命名集合，存放在服务器上。存储过程是包装重复任务有效执行的一种方式，支持用户声明的变量，条件执行和其他强功能的程序设计特性。Microsoft SQL Server 7.0版支持5类存储过程：（1）系统存储过程（sp_）系统存储过程（由前缀sp标识）存放master数据库中，提供获取系统表信息的快捷方式。它们允许系统管理员执行数据库管理任务，更新系统（2）局部存储过程局部存储过程在各个用户数据库中创建。（3）临时存储过程临时存储过程可以是局部的，名字前有一个磅符（），也可以是全局的，名字前有两个磅符（）；局部临时存储过程在单个用户会话中可用，全局临时存储过程在所有会话中都可用。（）远程存储过程远程存储过程是从连接到不同服务器的远程服务器或客户机调用的存储过程。6.ODBC目的微软开放式数据库连接（ODBC )接口是为了应用从各种各样的数据库管理系统( DBMS )使之有可能到访问数据的一种C编程语言接口。 ODBC接口许可最大限度连机操作任一应用程序都能在不同的的DBMS（数据库管理系统）中通过一个单一的接口访问数据。 进而，请求将不依赖于它从其中访问数据的任何DBMS。 应用程序的用户能增加请求驱动程序的软件组成部分，此驱动程序是请求和具体的DBMS之间的接口。开放式数据库连接( ODBC )是数据库访问的广泛地可接受的应用编写程序接口( API )。 它基于水平接口( CLI )规则以，X/Open和ISO/IEC做为数据库API借口并使用结构化查询语言作为数据库访问语言。ODBC被设计用于能允许使用相同源代码通过单一请求访问不同数据库管理系统的最大限度联机操作。在ODBC接口中的数据库应用请求定义，是在具体的数据库模块访问驱动程序中别实现的。对驱动程序的使用将应用程序与具体数据库隔开，如打印机驱动程序将字处理与打印机具体的命令隔开一样。 因为驱动程序在运行时被装载，一个用户可以仅增加一个新的驱动程序访问新的DBMS，不必要再编辑或者再连接应用程序。7.Command对象Command对象特定地为处理各种类型的命令而设计，特别是那些需要参数的命令。与Connection对象相似， Command对象可以运行返回记录集和不返回记录集两种类型的命令。实际上，如果命令不含有参数，那么它并不关心是使用Connection对象，还是Command对象，还是Recordset对象。8.存储过程存储过程的使用是Command对象得到应用的一个领域。存储过程(有时也称存储查询)是存储在数据库中预先定义的SQL查询语句。为什么应该创建和使用存储过程而不是在代码中直接使用SQL字符串呢？主要有以下几个理由： 存储过程被数据库编译过。这样可以产生一个“执行计划”，因此数据库确切地知道它将做什么，从而加快了过程的执行速度。 存储过程通常被数据库高速缓存，这样使它们运行得更快，因为此时不需从磁盘中读取它们。并非所有的数据库都支持这种缓存机制，比如微软的Access 就不支持，而SQL Server却支持。 通过指定数据库中的表只能被存储过程修改，可以确保数据更安全。这意味着具有潜在危险的SQL操作不会执行。 可以避免将ASP代码和冗长的SQL语句混在一起，从而使ASP代码更易于维护。 可以将所有SQL代码集中存放于服务器。 可以在存储过程中使用输出参数，允许返回记录集或其他的值。一般说来，存储过程几乎总是比相当的SQL语句执行速度快。为了使用存储过程，只要将存储过程的名字作为命令文本，并设置相应的类型。1. COMMUNICATIONSQL Server uses a layered communication architecture to isolate application from the underlying network and protocols. This architecture allows you to deploy the same application in different network environment. The components in the communication architecture include:Application、Database interface、 Network library .Clients and servers can use more than one Ner-Library concurrently, but they must use a common network library in order to communicate successfully. SQL Server support network protocol such as TCP/IP, Named pipes, Novell IPX/SPC, Banyan VINES/IP, and AppleTalk ADSP.Tabular data stream tabular data stream(TDS) is a application level protocol used for communication between the client and SQL Server. TDS packets are encapsulated in the network packets built for the protocol stack used by the Net-libraries. For example, if you use the windows Socket Net-Library, then the TDS packets are encapsulated in the TCP/IP packets of underlying protocol.Open Data Services This is a component of SQL Server that handles network connections, passing client requests to SQL Server for processing and returning any result and replies to SQL Server client. Open Data Services automatically listens on all server Net-Libraries that are installed on the server.2. APPLICATION DEVELOPMENTUsers access SQL Server through an application that is written with a data object interface or an API.SQL Server supports commonly used and emerging database interfaces. It supports low-level native APIs, as well as easy-to-use data object interfaces.2.1 Application Programming InterfacesA database API defines how to write an application to connect to a database and pass commands to the database. SQL Server provides native support for two main classes of database APIs, which in turn determine the data object interface that you can use. Use the database APIs to have more control over application behavior and performance.OLE DB This is a Component Object Model(COM)-based data access interface. It supports application written using OLE DB or data object interface that use OLE DB. OLE DB can access data in SQL Server, other relational databases, and other data sources.Open Database Connectivity Open Database Connectivity(ODBC) is a call-level interface. It directly access the SQL Server TDS protocol and supports applications or components that are written using ODBC or data object interface that use ODBC. ODBC can access data in SQL Server and other relational database, but generally it cannot be used to access other data sources.2.2 Data Object InterfacesIn general, data object interfaces are easier to use than database APIs but may not expose as much functionality as an API.ActiveX Data Objects Microsoft ActiveX Data Object(ADO) encapsulate the OLE DB API in a simplified object model that reduces application development and maintenance costs. ADO can be used from Microsoft Visual Basic, Visual basic for applications, Active Server Pages(ASP), and the Microsoft Internet Explorer scripting object model.Remote Data Object Remote Data Object(ROD) map over and encapsulate the ODBC API. RDO can be used from Visual Basic and Visual Basic for applications.3.WORKING WITH SQL SERVER3.1 Designing An Application For SQL ServerPlanning a database design requires knowledge of the business functions that you want to model and database concepts and features that you use to represent those business functions.Before you design an application for SQL Server, it is important to spend time designing a database to model the business accurately. A well-designed database requires fewer changes and generally performance more efficiently. The architecture that you select affect how you develop, deploy and manage your software application.3.2 Software ArchitectureYou can use one of several application architecture to implement client/server applications. However, selecting a layered application approach affords flexibility and a choice of management options. You can divide software applications into three logical layers, which can physically reside on one or more servers.3.3Architectural DesignTypical application deployment options include:Intelligent Server(2-tier) Most processing occurs on the server, with presentation services handled on the client. In many instance, most of the business services logic is implemented in the database. This design is useful when clients do not have sufficient resources to process the business logic. However, the server can become a bottleneck because database and business services compete for the same hardware resources.Corporate applications designed from a database-centric point of view are an example of this design.Intelligent Client(2-tier) Most processing occurs on the client, with presentation services handled on the server. This design is widely used. However, network traffic can be heavy and transactions longer, which can in turn affect performance.Applications developed for small organizations with product such as Microsoft Access are an example for this design.N-Tier Processing is divided among a database server, an application server, and clients,. This approach separates logic from data services, and you easily can add more application servers or database servers as needed. However, the potential for complexity increases, and this approach may be slower for small applications.Multitiered enterprise application and applications developed with transaction processing monitors are examples for this design.Internet Processing is divided into three layers, with the business and presentation services residing on the Web server and the clients using simple browsers. Any client that has a browser can be supported, and software does not need to be maintained on the client.An example of this design is a Web site thar uses several Web servers to manage connections to clients and a single SQL Server database that services requests for data.4.IMPLEMENTING A SQL SERVER DATABASEImplementing a SQL Server database means planning, creating, and maintaining a number of interrelated components.The nature and complexity of a database application, as well as the process of planning it, can very greatly. For example, a database can be relatively simple, designed for use by a single person, or it can be large and complex, designed to handle all the banking transactions for hundreds of thousands of client.Regardless of the size and complexity of the database, implementing a database usually involves:Designing the database so that your application use hardware optimally and allows for future grows, identifying and modeling database objects and application logic, and specifying the types of information for each object and type of relationship.Creating the database and database objects, including tables, data integrity mechanisms, data entry and retrieval object(often stored procedure), appropriate indexes, and security.Testing and tuning the application and database. When you design a database, you want to ensure that the database performs important functions correctly and quickly. In conjunction with correct database design, correct use of indexes, RAID, and filegroups are essential to achieving good performance.Planning deployment, which includes analyzing the workload and recommending an optimal index configuration for your SQL Server database.Administering the application after deployment, which includes configuring servers and clients, monitoring server performance, managing jobs, alerts, and operators, managing security, and managing database backup procedures.5.Stored Procedures5.1Introduction to Stored ProceduresThis section introduces the different types of stored procedures, describes how stored procedures are processed, both initially and on subsequent execution, and lists some of the advantages of using stored procedures.5.2Defining Stored ProceduresA stored procedure is a named collection of Transact-SQL statements that is stored on the server. Stored procedures are a method of encapsulating repetitive tasks that executes efficiently. Stored procedures support user-declared variables, conditional execution, and other powerful programming features. Microsoft SQL Server version 7.0 supports five types of stored procedures:System Stored Procedures (sp_) Stored in the master database, system stores procedures (identified by type sp_prefix) provide an effective method to retrieve information from system tables. They allow system administration tasks that update system tables even though the administrators do not have permission to update the underlying tables directly. System stored procedures can be executed in any database.Local Stored Procedures Local stored procedures are created in individual user databases.Temporary Stored Procedures Temporary stored procedures can be local, with names that start with a double pound symbol (#) local temporary stored procedures are available within a single user session; global temporary stored procedures are available for all user sessions.Remote Stored Procedures Remote stored procedures are a legacy feature of SQL Server environment. Extended stored procedures are typically identified by the xp_prefix. They are executed in a manner similar to that of stored procedures.6. ODBC PurposeThe Microsoft Open Database Connectivity (ODBC) interface is a C programming language interface that makes it possible for applications to access data from a variety of database management systems (DBMSs). The ODBC interface permits maximum interoperabilityan application can access data in diverse DBMSs through a single interface. Furthermore, that application will be independent of any DBMS from which it accesses data. Users of the application can add software components called drivers, which interface between an application and a specific DBMS.Open Database Connectivity (ODBC) is a widely accepted application programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language.ODBC is designed for maximum interoperabilitythat is, the ability of a single application to access different da