SQL Server 2000 Component Overview

This diagram is an illustration of the relationships between the major components of Microsoft® SQL Server™ 2000.

SQL Server 2000 provides two fundamental services to applications in a Windows® DNA environment:

• The SQL Server 2000 relational database engine is a modern, highly scalable, highly reliable engine for storing data. The database engine stores data in tables. Each table represents some object of interest to the organization, such as vehicles, employees, or customers. Each table has columns that represent an attribute of the object modeled by the table (such as weight, name, or cost), and rows that represent a single occurrence of the type of object modeled by the table (such as the car with license plate number ABC-123, or the employee with ID 123456). Applications can submit Structured Query Language (SQL) statements to the database engine, which returns the results to the application in the form of a tabular result set. The specific dialect of SQL supported by SQL Server is called Transact-SQL. Applications can also submit either SQL statements or XPath queries and request that the database engine return the results in the form of an XML document.

  The relational database engine is highly scalable. The SQL Server 2000, Enterprise Edition can support groups of database servers that cooperate to form terabyte-sized databases accessed by thousands of users at the same time. The engine is capable of handling the traffic of any Web site in the world. The database engine also tunes itself, dynamically acquiring resources as more users connect to the database, and then freeing the resources as the users log off. This means that the smaller editions of SQL Server can be used for individuals or small workgroups that do not have dedicated database administrators. SQL Server for Windows CE even extends the SQL Server programming model to Windows CE devices used by mobile, disconnected users. Even large Enterprise Edition database servers running in production are easy to administer using the graphical user interface (GUI) administration utilities that are a part of the product.

  The relational database engine is highly reliable and capable of running for long periods without down time. Administrative actions that required stopping and starting in earlier versions of the database engine can now be performed while the engine is running, increasing availability. The integration of the database engine with Windows 2000 and Windows NT® failover clustering allows you to define virtual servers that keep running even if one of the physical servers in the node fails. Where appropriate, log shipping can be used to maintain a warm standby server that can replace a production server within minutes of a failure.

  The relational database engine is also highly secure. Login authentication can be integrated with Windows Authentication, so that no passwords are stored in SQL Server or sent across the network where they could be read by network sniffers. Sites can set up C2-level auditing of all users accessing a database, and can use Secure Sockets Layer (SSL) encryption to encrypt all data transferred between applications and the database.

  The distributed query feature of the database engine allows you to access data from any source of data that can be accessed using OLE DB. The tables of the remote OLE DB data source can be referenced in Transact-SQL statements just like tables that actually reside in a SQL Server database. In addition, the full-text search feature allows you to perform sophisticated pattern matches against textual data stored in SQL Server databases or Windows files.

  The relational database engine is capable of storing detailed records of all the transactions generated by the top online transaction processing (OLTP) systems. The database engine can also support the demanding processing requirements for fact tables and dimension tables in the largest online analytical (OLAP) data warehouses.

  For more information about the SQL Server 2000 relational database component, see Relational Database Components

• Microsoft SQL Server 2000 Analysis Services provides tools for analyzing the data stored in data warehouses and data marts. Certain analytical processes, such as getting a summary of the monthly sales by product of all the stores in a district, take a long time if run against all the detail records of an OLTP system. To speed up these types of analytical processes, data from an OLTP system is periodically summarized and stored in fact and dimension tables in a data warehouse or data mart. Analysis Services presents the data from these fact and dimension tables as multidimensional cubes that can be analyzed for trends and other information that is important for planning future work. Processing OLAP queries on multidimensional Analysis Services cubes is substantially faster than attempting the same queries on the detail data recorded in OLTP databases. For more information about Analysis Services, see Data Warehousing and Online Analytical Processing.

Application Support

Both the relational database engine and Analysis Services provide native support for the common Windows DNA or Win32 data access interfaces, such as ActiveX® Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC). Applications can use any of these application programming interfaces (APIs) to send SQL or XML statements to the relational database engine using a native OLE DB provider or ODBC driver. SQL Server 2000 also introduces the ability to use HTTP to send SQL or XML statements to the relational database engine. Applications can use the multidimensional extensions of either ADO or OLE DB to send Multidimensional Expressions (MDX) queries to Analysis Services. Because SQL Server uses the standard Windows DNA data access APIs, the development of SQL Server applications is well supported by the Microsoft application development environments. In addition, interactive query tools, such as Query Analyzer, provide templates, interactive debuggers, and interactive test environments that speed the ability of your programmers to deliver SQL Server applications.

In addition to supporting the data storage and OLAP processing needs of applications, SQL Server 2000 provides a full set of easy to use, graphical administration tools and wizards for creating, configuring, and maintaining databases, data warehouses, and data marts. SQL Server also documents the administration APIs used by the SQL Server tools, giving you the ability to incorporate SQL Server administration functionality directly into your own applications. The SQL Server administration APIs include:

• SQL Distributed Management Objects (SQL-DMO), a set of COM objects that encapsulates the administration functions for all of the entities in the relational database engine and databases.
  
  
• Decision Support Objects (DSO), a set of COM objects that encapsulates the administration functions for all of the entities in Analysis Services engine and multidimensional cubes.
  
  
• Windows Management Instrumentation (WMI), SQL Server 2000 provides a SQL Server WMI provider that lets WMI applications get information on SQL Server databases and instances.

For more information about developing SQL Server applications, see Application Development Architecture, and SQL Server and XML Support.

Additional Components

SQL Server 2000 provides several components that support important requirements of modern data storage systems. The data storage needs of today's large enterprises are very complex, and go beyond having a single OLTP system integrated with a single data warehouse or data mart. Increasing numbers of field personnel need to load sets of data, disconnect from the network, record their work autonomously during the day, then plug back in to the network and merge their records into the central data store at the end of the day. OLTP systems have to support the needs of both internal employees operating through an intranet and hundreds of thousands of customers placing orders through your Web portal. Keeping data close to the workgroups or even individuals who primarily work on the data, and then replicating the data to a primary data store may minimize the overall processing load of your system.

• SQL Server 2000 replication allows sites to maintain multiple copies of data on different computers in order to improve overall system performance while at the same time making sure the different copies of data are kept synchronized. For example, a department could maintain the department sales data on a departmental server, but use replication to update the sales data in the corporate computer. Several mobile disconnected users can disconnect from the network, work throughout the day, and at the end of the day use merge replication to merge their work records back into the main database. These workers can be using SQL Server Personal Edition on notebook or laptop computers, or using SQL Server for Windows CE on Windows CE devices; all are supported by SQL Server replication. SQL Server replication also supports replicating data to data warehouses, and can replicate data to or from any data source that supports OLE DB access. For more information, see Replication Architecture.
  
  
• SQL Server 2000 Data Transformation Services (DTS) greatly improves the process of building OLAP data warehouses. Large OLTP databases are finely tuned to support the entry of thousands of business transactions at the same time. OLTP databases are also structured to record the details of every transaction. Trying to perform sophisticated analysis to discover trends in sales over a number of months and years would require scanning huge numbers of records, and the heavy processing load would drag down the performance of the OLTP databases. Data warehouses and data marts are built from the data in one or more OLTP systems that is extracted and transformed into something more useful for OLAP processing. OLTP detail rows are periodically pulled into a staging database, where they are summarized and the summary data is stored in a data warehouse or data mart. Data Transformation Services supports extracting data from one source of data, performing sometimes complex transformations of the data, and then storing the summarized, transformed data in another data source. The component greatly simplifies the process of extracting data from multiple OLTP systems and building it into an OLAP data warehouse or data mart. For more information, see Transforming OLTP Data to OLAP Data Warehouses.

  DTS is not limited to being used to build data warehouses. It can be used any time you have to retrieve data from one data source, perform complex transformations on the data, and then store it in another data source. DTS is also not limited to working with SQL Server databases or Analysis Services cubes, DTS can work with any data source that can be accessed using OLE DB.

• SQL Server 2000 English Query allows you to build applications that can customize themselves to ad hoc user questions. An English Query administrator defines for the English Query engine all of the logical relationships between the tables and columns of a database or the cubes in a data warehouse or data mart. An application can then present the user with a box where she can enter a character string with a question (written in English) about the data in the database or data warehouse. The application passes the string to the English Query engine, which analyzes the string against the relationships defined between the tables or cubes. English Query then returns to the application a SQL statement or MDX (multidimensional expression) query that will return the answer to the user's question. For more information, see SQL Server and English Query.
  
  
• Meta Data Services provides facilities for storing, viewing, and retrieving descriptions of the objects in your applications and system. Meta Data Services supports the MDC Open Information Model (OIM) specification defining a common format for storing descriptions of entities such as tables, views, cubes, or transformations, as well as the relationships between these entities. Application development tools that support OIM can use these descriptions to facilitate rapid development and interchange with other tools and applications. SQL Server components, such as Data Transformation Services packages and Analysis Services databases, can also be stored in the Meta Data Services repository. For more information, see SQL Server 2000 Data Warehouse and OLAP Components.

Using SQL Server 2000

An organization may use the SQL Server 2000 components to perform various tasks, for example:

• Each department might have a departmental SQL Server database server. Each of these servers periodically replicate their data into a central database server that serves the entire organization.
  
  
• The organization may have another central database computer that services the organization's Web site, sometimes servicing thousands of queries at once. Some of the Web applications use English Query to allow customers to tailor requests for the data in the Web site database.
  
  
• Several employees may be running individual copies of a shrink-wrapped software product that installed a copy of SQL Server Desktop Engine as its data storage component.
  
  
• Several other employees in the service department are operating as mobile disconnected users, where they use replication each morning to load their daily schedules into notebook computers or Microsoft Windows CE devices, work in the field all day, then use merge replication at the end of the day to enter their work items back into the central computer.
  
  
• Periodically, detailed OLTP data is extracted from the central databases by Data Transformation Services packages that scrub the data and build it into summary data that is then loaded into a data warehouse.
  
  
• The senior managers and marketing personnel use Analysis Services to analyze the data warehouse for business trends that indicate possible opportunities that could be exploited or risks that must be minimized.