计算机数据库外文翻译外文文献英文文献SQL-SEVER-体系结构

1、武汉科技大学本科毕业设计外文翻译SQL SERVER ARCHITECTURETURLEYPROFESSIONAL SQL SERVER 2005 REPORTING SERVICES ,Chapter 3 , Reporting Sevices Architecture , TURLEY . SecondEdition . New York : John Willey , 2007. Page71Page121SQL SEVER 体系结构特利SQL SEVER 2005专业报告服务，第三章，报告服务体系结构， 特利.第二版.纽约：约翰威利由版社，2007 .第71页第121页武汉科技大学本科

2、毕业设计外文翻译.通信SQL Server使用分层的通信体系结构，把应用程序和底层的网络和协议隔离开来。 这个体系结构允许用户在不同的网络环境中实施同一个应用程序。通信体系结构中的组件包括：应用程序、数据库接、网络库、客户机和服务器可以同时使用多个 Net-Library，但是为了成功通信，它们必须使 用一个公共的网络库。SQL Server支持这样的一些网络协议：TCP/IP,命名管道， NOVELL IPX/SPX , BANYAN VINES/IP 和 APPLETALK ADSP。表数据流（TDS） 这是一个应用程序层的协议，用于客户应用程序与 SQL Server 之间的通信。TDS

3、包封装在为网络库使用的协议栈创建的网络数据包中，例如，如果使 用 Windows sockets Net-Library ,则TDS数据包封装在底层协议的 TCP/IP数据包中。开放数据服务 这是一个SQL Server组件，处理网络连接，把客户的请求传给SQL Server进行处理，并把任何结果和应答返回给 SQL Server客户。开放数据服务自动监 听在服务器上安装的所有服务器网络库。.应用程序开发用户通过使用一个数据对象接口或 API写的应用程序访问SQL Server 0SQL Server支持常用的和正在出现的数据接口，它支持底层的固有的API ,以及容易使用的数据对象接口 。应用

4、程序编程接口数据库API定义如何写一个应用程序连接到数据库，和把命令传递给数据库。SQL Server提供对两大类数据库API的固有支持，而这些API有确定可以使用的数据对象 接口。使用数据库API对应用程序行为和性能有更大的控制。OLE DB 这是一个基于组件对象模型（COM）的数据访问接口，它支持使用OLE DB的数据对象接口写的应用程序。OLE DB可以访问SQL Server,其他关系数据库和2武汉科技大学本科毕业设计外文翻译其他数据源中的数据。ODBC 这是一个调用级的接口，它直接访问 SQL Server TDS协议，并支持使用 ODBC的数据对象接口写的应用程序或组件。 ODBC

5、可以访问SQL Server和其他关系 数据库中的数据，但一般不能用于访问其他数据源中的数据。数据对象接口一般来说，数据对象接口比数据库 API更容易使用，但没有显露 API那样多的功 能。ActiveX数据对象（ADO） ActiveX数据对象（ADO）在一个简化的数据模型中封 装OLE DB API ,降低了应用程序开发和维护的成本。ADO可以从Microsoft VisualBasic, Visual Basic for Applications, Active Server Pages（ASP） 和 Microsoft Internet Explorer脚本对象模型中使用。远程数据对象

6、（RDO） RDO映射和封装 ODBC API ,可以从Microsoft Visual Basic 和 Visual Basic for Applications 中使用。.使用 SQL Server设计SQL Server 应用程序计划一个数据库设计要求想建模的业务对象的知识，和用于表示这些业务功能的数 据库概念及特性。在设计一个SQL Server应用程序之前，花一些时间设计一个数据库对业务对象准 确建模是很重要的。一个很好设计的数据库要求极少的更改通常更有效的执行。选择的 体系结构将影响如何开发、部署和管理软件应用程序。软件体系结构可以使用某个应用程序体系结构实现客户机/服务器应用程序

7、，但是选定一个分层 的应用程序方法会得到灵活性和管理选项的选择。可以把软件应用程序分为3个逻辑层,他们可以位于一个或多个服务器上。武汉科技大学本科毕业设计外文翻译体系结构设计典型的应用程序部署选项包括：智能服务器（两层） 大多数的处理发生在服务器上，而表示服务在客户机上处理。在许多例子中，业务服务逻辑的大部分在数据库中实现。这个设计在客户机没有足够的 资源处理业务逻辑时是很有用的。但是，服务器会变成一个瓶颈，因为数据库和业务服 务竞争相同的硬件资源。以数据库为中心的视点设计的团体应用程序是这种设计的一个例子。智能客户机（两层） 大多数处理发生在客户机上，而数据服务在服务器上处理。这个 设计是广

8、泛使用的传统客户机/服务器环境，但是网络交通会很繁忙，而且事务更长， 这又会影响性能。使用Microsoft Access这样的产品为小单位开发的应用程序是这种设计的一个例子。N层 处理在数据库服务器、应用程序服务器和客户机之间划分。这个方法把逻辑 与数据服务分开，可以根据需要很容易添加更多的应用程序服务器或数据库服务器。但 是，复杂的可能性增加，且这个方法对小型应用程序较慢。多层企业应用程序和使用事务处理监视程序开发的应用程序是这种设计的一个例子。Internet 处理分为3层，业务和表示服务位于 Web服务器上，客户机使用简单的 浏览器。有浏览器的任何客户机都可以被支持，且软件不需要在客户

9、机上维护。使用几个Web服务器管理客户机的连接，和使用一个SQL Server数据库服务数据 请求的Web站点是这种设计的一个例子。.实现SQL Server 数据库实现一个SQL Server数据库意味着计划，创建和维护一些相互关联的组件。数据库应用程序的性质和复杂性，以及计划数据库的过程，它们之间的差异是非常 大的。例如，一个数据可以很简单，有一个人设计即可；它也可以很大，很复杂，处理 成千上万客户的银行事务。不管数据库的规模和复杂性符合，实现一个数据库通常都包括以下任务：设计数据库。使得应用程序最优化的使用硬件， 并允许未来的扩展；标识和建模数据库4武汉科技大学本科毕业设计外文翻译对象和

10、应用程序逻辑，规定每个对象的信息类型和关系的类型。创建数据库和数据库对象。包括表、数据库完整性机制、数据输入和获取对象（常常是 存储过程）、合适的索引、和安全策略。测试和调试应用程序与数据库。设计数据库时，要保证数据库正确迅速的执行重要的功能。正确的数据库设计、索 引的的正确使用、RAID和文件组，它们的组合对于获得好的性能是必需的。设计应用。包括分析工作负载和建议 SQL Server数据库的最优索引配置。应用后的应用程序管理。包括配置服务器和客户机，监视服务器性能，管理作业、报警 和操作员，管理安全策略，和管理数据库备份过程。.存储过程介绍存储过程本节介绍不同类型的存储过程，描述存储过程的

11、处理，包括初始处理和后续执行， 并列出使用存储过程的一些优点。定义存储过程存储过程是SQL语句的命名集合，存放在服务器上。存储过程是包装重复任务有 效执行的一种方式，支持用户声明的变量，条件执行和其他强功能的程序设计特性。Microsoft SQL Server 7.0 版支持5类存储过程：（1）系统存储过程（sp_）系统存储过程（由前缀sp标识）存放master数据库中，提供获取系统表信息的快 捷方式。它们允许系统管理员执行数据库管理任务，更新系统（2）局部存储过程局部存储过程在各个用户数据库中创建。（3）临时存储过程临时存储过程可以是局部的，名字前有一个磅符（#）,也可以是全局的，名字前

12、有两个磅符（#）;局部临时存储过程在单个用户会话中可用，全局临时存储过程在 所有会话中都可用。武汉科技大学本科毕业设计外文翻译（4）远程存储过程远程存储过程是从连接到不同服务器的远程服务器或客户机调用的存储过程。6.ODBC 目的微软开放式数据库连接（ODBC）接口是为了应用从各种各样的数据库管理系统 （DBMS ）使之有可能到访问数据的一种C编程语言接口。 ODBC接口许可最大限度连机 操作一任一应用程序都能在不同的的DBMS （数据库管理系统）中通过一个单一的接口 访问数据。进而，请求将不依赖于它从其中访问数据的任何 DBMS。应用程序的用户 能增加请求驱动程序的软件组成部分，此驱动程序是

13、请求和具体的DBMS之间的接口。开放式数据库连接（ODBC ）是数据库访问的广泛地可接受的应用编写程序接口 （API ） 0它基于水平接口（ CLI ）规则以，X/Open和ISO/IEC做为数据库API借口并使 用结构化查询语言作为数据库访问语言。ODBC被设计用于能允许使用相同源代码通过单一请求访问不同数据库管理系统的 最大限度联机操作。在ODBC接口中的数据库应用请求定义，是在具体的数据库模块访 问驱动程序中别实现的。对驱动程序的使用将应用程序与具体数据库隔开，如打印机驱 动程序将字处理与打印机具体的命令隔开一样。因为驱动程序在运行时被装载，一个用户可以仅增加一个新的驱动程序访问新的 D

14、BMS ,不必要再编辑或者再连接应用程 序。.Command 对象Command对象特定地为处理各种类型的命令而设计，特别是那些需要参数的命令。 与Connection对象相似，Command对象可以运行返回记录集和不返回记录集两种类 型的命令。实际上，如果命令不含有参数，那么它并不关心是使用Connection对象，还是Command对象，还是Recordset对象。.存储过程存储过程的使用是Command对象得到应用的一个领域。存储过程（有时也称存储查武汉科技大学本科毕业设计外文翻译询）是存储在数据库中预先定义的SQL查询语句。为什么应该创建和使用存储过程而不是在代码中直接使用SQL字符串

15、呢？主要有以下几个理由：?存储过程被数据库编译过。这样可以产生一个执行计划”，因此数据库确切地知道它将做什么，从而加快了过程的执行速度。?存储过程通常被数据库高速缓存，这样使它们运行得更快，因为此时不需从磁盘中读取它们。并非所有的数据库都支持这种缓存机制，比如微软的Access就不支持，而SQL Server却支持。?通过指定数据库中的表只能被存储过程修改，可以确保数据更安全。这意味着具有潜 在危险的SQL操作不会执行。?可以避免将ASP代码和冗长的SQL语句混在一起，从而使ASP代码更易于维护。?可以将所有SQL代码集中存放于服务器。?可以在存储过程中使用输出参数，允许返回记录集或其他的值。

16、一般说来，存储过程几乎总是比相当的 SQL语句执行速度快。为了使用存储过程，只要将存储过程的名字作为命令文本，并设置相应的类型。武汉科技大学本科毕业设计外文翻译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 environ

17、ment. 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 s

18、uch 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

19、 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 processin

20、g 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.APPLICATION DEVELOPMENTUsers access SQL Server through an application that is written with a data object interface or an API.SQL Server s

21、upports commonly used and emerging database interfaces. It supports low-level native APIs, as well as easy-to-use data object interfaces.Application Programming InterfacesA database API defines how to write an application to connect to a database and pass commands to the database. SQL Server provide

22、s 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 appli

23、cation 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 sup

24、ports 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.Data Object Interfaces武汉科技大学本科毕业设计外文翻译In general, data object interfaces

25、 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 B

26、asic, 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.WORKING WITH SQL SERVERDesig

27、ning 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 design

28、ing 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.Software ArchitectureYou can use one of several applicatio

29、n 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.Architectural DesignTyp

30、ical 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 sufficie

31、nt武汉科技大学本科毕业设计外文翻译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

32、(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 Microso

33、ft 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 i

34、ncreases, 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 t

35、he 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

36、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

37、 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 database10武汉科技大学本科毕业设计外文翻译usually involve

38、s: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, includin

39、g 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 conj

40、unction 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 d

41、eployment, 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 proc

42、edures, 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 procedu

43、res 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_) Sto

44、red in the master database, system stores11武汉科技大学本科毕业设计外文翻译 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 upda

45、te 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

46、(#)local temporary storedprocedures 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

47、 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 (D

48、BMSs). The ODBC interface permits maximum interoperability an 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

49、 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 L

50、anguage (SQL) as its database access language.ODBC is designed for maximum interoperabilitythat is, the ability of a singleapplication to access different database management systems (DBMSs) with the same source code. Database applications call functions in the ODBC interface, which are implemented

51、in database-specific modules called drivers. The use of drivers isolates applications from database-specific calls in the same way that printer drivers isolate12武汉科技大学本科毕业设计外文翻译word processing programs from printer-specific commands. Because drivers are loaded at run time, a user only has to add a new driver to access a new DBMS; it is not necessary to recompile or relink the application.7.The Command ObjectThe Command object is designed specifically to deal with commands of any sort, but especially those that require parameters. Like the Connection object, the Command object can run bo