MCSE Training Kit (Exam 70-229): Microsoft SQL Server(TM) 2000 Database Design and Implementation (Pro Certification)

3 4

As described in Lesson 1, SQL Server 2000 is an RDBMS. An RDBMS, by its very nature, is made up of a number of components, including the database engine, various applications and tools, and any databases, whether they are predefined and bundled with the system or added to the RDBMS after it is installed. This lesson provides you with an overview of the main components that make up the SQL Server 2000 RDBMS.

After this lesson, you will be able to:

• Identify and describe the various components that make up SQL Server 2000.

Estimated lesson time:  25 minutes

Overview of the SQL Server 2000 Components

SQL Server 2000 is made up of many components, such as the relational database engine, Analysis Services, and English Query. All of these components play a role in providing a complete database and analysis solution, working together to create a comprehensive RDBMS (as shown in Figure 1.1).

SQL Server 2000 Relational Database Engine

The SQL Server 2000 relational database engine is a modern, highly scalable engine for storing data. The database engine stores data in tables. Each table represents some object class that is of interest to the organization, such as vehicles, employees, or customers. The table has columns that each represent an attribute of the object modeled by the table (such as weight, name, or cost) and rows that each 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). An application submits a SQL statement to the database engine, which returns the result to the application in the form of a tabular result set. An Internet application submits either a SQL statement or an XPath query to the database engine, which returns the result as an XML document. The relational database engine provides support for the common Microsoft data access interfaces, such as ActiveX Data Objects (ADO), OLE DB, and Open Database Connectivity (ODBC).

The relational database engine is highly scalable. The SQL Server 2000 Enterprise Edition can support groups of database servers that cooperate to form terabyte databases that are accessed by thousands of users at the same time. 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. In other words, the smaller editions of SQL Server can be used for individuals or small workgroups that do not have dedicated database administrators. Even large Enterprise Edition database servers running in production are easy to administer by using the GUI administration utilities that are part of the product.

Figure 1.1 The components of SQL Server 2000.

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 security-level auditing of all users accessing the database and can use Secure Sockets Layer (SSL) encryption to encrypt all data transferred between applications and the database. For details about the database engine's architecture, refer to Lesson 3, "Overview of SQL Server 2000 Architecture."

SQL Server 2000 Replication

SQL Server 2000 replication enables sites to maintain multiple copies of data on different computers, in order to improve overall system performance, while ensuring that all the different copies 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 could 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.

Replication is an important and powerful technology for distributing data and certain types of database objects (stored procedures, views, and user-defined functions) across an enterprise. SQL Server Replication uses a publish-and-subscribe metaphor. The publisher, or owner, of the data to be replicated defines articles (analogous to tables in a database) that will be available to subscribers (or those locations receiving copies of the original publication).

The merge replication model enables many or all subscribers to a publication, as well as the publisher, to make updates to replicated data. In any environment where subscribers are frequently disconnected from their network (a remote sales force, for example), merge replication is an ideal solution. Subscribers can modify or add data while on the road, then return to their office or connect to their Local-Area Network (LAN) via modem to merge their modified data with the original copy (the publisher).

SQL Server 2000 DTS

Many organizations need to centralize data in order to improve corporate decision-making. This data can be stored in a large variety of formats in a number of different places, however. By using DTS, you can build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis.

SQL Server 2000 DTS greatly improves the process of building Online Analytical Processing (OLAP) data warehouses. In addition, large Online Transaction Processing (OLTP) databases are finely tuned to support a large number of concurrent users who are actively adding and modifying data. OLTP databases are also structured to record the details of every transaction. Trying to perform sophisticated analyses in order 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 an OLTP system that is extracted and transformed into a form more suited 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. DTS supports extracting data from one source, performing sometimes complex transformations, 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.

SQL Server 2000 Analysis Services

Analysis Services provides tools for analyzing the data stored in data warehouses and data marts. Data warehouses and data marts store their summarized data in fact tables. A fact table is a central table in a data warehouse schema that contains numerical measures and keys relating facts to dimension tables. Typically, base fact tables contain data that describes specific events within a business, such as bank transactions or product sales. Applications work with Analysis Services data by using multi-dimensional extensions to ADO and OLE DB. Processing OLAP queries on multi-dimensional Analysis Services cubes is substantially faster than attempting the same queries on the detail data recorded in OLTP databases.

The Analysis Services system includes a server that manages multi-dimensional cubes of data for analysis and that provides rapid client access to cube information. Analysis Services organizes data from a data warehouse into cubes with precalculated aggregation data in order to provide rapid answers to complex analytical queries. Analysis Services also facilitates the creation of data mining models from both multi-dimensional and relational data sources. You can apply data mining models to both types of data. Microsoft Excel and applications from other vendors use PivotTable Service, the OLE DB-compliant provider, to retrieve data from the server and present it to the user or to create local data cubes for offline analysis.

SQL Server 2000 English Query

SQL Server 2000 English Query helps 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 among 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 he or 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 that are defined among the tables or cubes. English Query then returns to the application a SQL statement or a Multi-Dimensional Expression (MDX) query that will return the answer to the user's question.

With English Query, developers can turn their relational databases into English Query applications, which make it possible for end users to pose questions in English instead of forming a query with a SQL statement. The English Query model editor appears within the Microsoft Visual Studio version 6.0 development environment. With Visual Studio, database information is specified in such a way that English Query can process English questions about its particular tables, fields, and data.

English Query features include wizards that will automatically create a project (.eqp) and a model (.eqm) for the application. After the project has been tested, it can be compiled into an English Query application (.eqd) and then deployed. The SQL Project wizard speeds the English Query project creation process by automatically creating the entities and relationships for the database that you choose.

Before you deploy an English Query application, you should thoroughly test it to ensure that all of the necessary entities and relationships exist to return accurate results. The Model Test window provides built-in debugging while enabling you to further modify and refine your EQ project.

SQL Server Meta Data Services

SQL Server Meta Data Services provides a way to store and manage metadata about information systems and applications. This technology serves as a hub for data and component definitions, development and deployment models, reusable software components, and data warehousing descriptions. Product components consist of the repository engine, tools, APIs, standard information models, a browser, and a Software Development Kit (SDK).

SQL Server 2000 Meta Data Services is a set of services that helps you to manage metadata. Using Microsoft Meta Data Services requires understanding metadata characteristics. Metadata describes the structure and meaning of data, as well as the structure and meaning of applications and processes. You must remember that metadata is abstract, that it has a context, and that it can be used for multiple purposes in a development environment.

In software design, the application and database structures that represent or store data can be abstracted into metadata classification schemes that make sense to developers and designers. A table or form is derived from an object, which in turn can be derived from a class.

Metadata contains multiple levels of abstraction. You can describe a data instance, then describe that description, and continue to describe subsequent descriptions until you reach some practical limit. Typically, metadata descriptions used in software development extend to two or three levels of abstraction. In real terms, a data instance of "loan table" can be described as a database table name. A database table can be described as a database table object. Finally, an abstract class that formalizes the fixed set of characteristics to which all derived objects must conform can describe a database table object.

The distinction between data and metadata is often called the type/instance distinction. A model designer articulates a type (such as a class or a relationship), and a software developer articulates an instance (such as a table class or a table-has-columns relationship).

The distinction between instance and type is context-sensitive. What is metadata in one scenario becomes data in another scenario. For example, in a typical RDBMS, the system catalog describes the tables and columns that contain your data. You can think of the data in the system catalog as metadata because it describes data definitions. With the right software tool, however, you can manipulate it as you would manipulate any other data. Examples of manipulating metadata include viewing data lineage or table versioning information, or identifying all tables that express financial data by searching for columns that have a currency-based data type. In this scenario, standard metadata (such as the system catalog) becomes data that you can manipulate.

SQL Server Books Online

SQL Server Books Online is the online documentation provided with SQL Server 2000. You can find information in SQL Server Books Online by taking any of the following actions:

• Navigating through the contents
• Typing a keyword in the index
• Typing a word or phrase and performing a search

SQL Server Books Online also includes Help files, which are the topics found when you click the Help button on a dialog box or when you press F1. These topics are found in the "Using the SQL Server Tools" section.

The complete SQL Server Books Online documentation for SQL Server 2000 cannot be opened from the SQL Server 2000 compact disc. SQL Server Books Online must be installed to your local hard drive before you can open it. The installation documentation in the Microsoft SQL Server 2000 introduction manual does not include some items that arose after the manual was printed. These items are covered in both SQL Server Books Online and the SQL Server Setup Help file.

You can perform a custom setup of SQL Server 2000 and select only the Books Online component to install the SQL Server Books Online documentation on the hard disk of your computer. SQL Server Books Online is a collection of HTML Help documents and requires Microsoft Internet Explorer 5.0 or later. You can download Internet Explorer 5.0 from http://www.microsoft.com/windows/ie/.

SQL Server 2000 Tools

SQL Server 2000 includes many graphical and command-prompt utilities that help users, programmers, and administrators to perform a variety of tasks, including the following:

• Administering and configuring SQL Server
• Determining the catalog information in a copy of SQL Server
• Designing and testing queries for retrieving data
• Copying, importing, exporting, and transforming data
• Providing diagnostic information
• Starting and stopping SQL Server

Command Prompt Tools

The command-prompt utilities are installed automatically when you install the SQL Server 2000 utilities on a computer running Windows 2000, Windows NT, Windows 95, or Windows 98. The following table lists many of the SQL Server command-line utilities and the folders where they are installed by default. Note that the MSSQL subfolder is the folder name for the default instance of SQL Server 2000. For each named instance of SQL Server 2000, the corresponding folder name is MSSQL$<instance_name>.

During installation, the x:\Program Files\Microsoft SQL Server\80\Tools\Binn folder is added to the system path. You can run the utilities in this folder at any command prompt. For a utility that is not in this folder, you must either run the utility from a command prompt in the directory in which it is installed, explicitly specify the path, or add the folder path to the operating system search path.

The following utilities are no longer installed by SQL Server 2000 Setup:

• makepipe
• odbcping
• readpipe

If you need to run these utilities, you can run them from the \x86\Binn folder on the SQL Server 2000 installation CD-ROM, or manually copy the utilities to your computer. For a description of each of the command-line utilities available in SQL Server 2000, refer to SQL Server Books Online.

User Interface Tools

SQL Server 2000 provides an extensive set of user interface tools to work with and to administer SQL Server. This section provides an overview of many of these tools. For information about any of the user interface tools available in SQL Server, refer to the SQL Server Books Online.

SQL Server Enterprise Manager

SQL Server Enterprise Manager is the primary administrative tool for SQL Server 2000 and provides a Microsoft Management Console (MMC)–compliant user interface that helps you to perform a variety of administrative tasks:

• Defining groups of servers running SQL Server
• Registering individual servers in a group
• Configuring all SQL Server options for each registered server
• Creating and administering all SQL Server databases, objects, logins, users, and permissions in each registered server
• Defining and executing all SQL Server administrative tasks on each registered server
• Designing and testing SQL statements, batches, and scripts interactively by invoking SQL Query Analyzer
• Invoking the various wizards defined for SQL Server

MMC is a tool that presents a common interface for managing different server applications in a Microsoft Windows network. Server applications include a component called a snap-in that presents MMC users with a user interface for managing the server application. SQL Server Enterprise Manager is the Microsoft SQL Server 2000 MMC snap-in.

SQL Server Agent

SQL Server Agent runs on the server that is running instances of SQL Server 2000 or earlier versions of SQL Server. SQL Server Agent is responsible for the following tasks:

• Running SQL Server tasks that are scheduled to occur at specific times or intervals
• Detecting specific conditions for which administrators have defined an action, such as alerting someone through pages or e-mail, or issuing a task that will address the conditions
• Running replication tasks defined by administrators

SQL Profiler

SQL Profiler is a tool that captures SQL Server 2000 events from a server. The events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem. SQL Profiler is used for a variety of activities, including the following:

• Stepping through problem queries to find the cause of the problem
• Finding and diagnosing slow-running queries
• Capturing the series of SQL statements that lead to a problem
• Monitoring the performance of SQL Server to tune workloads

SQL Profiler also supports auditing the actions performed on instances of SQL Server. Security-related actions are stored for later review by a security administrator.

SQL Server Client Network Utility

The Client Network utility is used to manage the client Net-Libraries and to define server alias names. You can also use this utility to set the default options used by DB-Library applications.

Most users will never need to use the Client Network utility. To connect to SQL Server 2000, users can specify the network name of the server where SQL Server is running, and optionally, the name of the instance of SQL Server.

SQL Server Network Utility

The Server Network utility is used to manage the server Net-Libraries and can be used to specify several types of information:

• The network protocol stacks on which an instance of SQL Server 2000 listens for client requests
• The sequence in which server Net-Libraries are considered when establishing connections from applications
• New network addresses on which an instance of SQL Server 2000 listens

Most administrators will never need to use the Server Network utility. They will specify during setup the server Net-Libraries on which SQL Server will listen.

SQL Server Service Manager

SQL Server Service Manager is used to start, stop, and pause the SQL Server 2000 components on the server. These components run as services on Microsoft Windows NT or Windows 2000 and as separate executable programs on Windows 95 and Windows 98:

• SQL Server service.  This service implements the SQL Server database engine. There is one SQL Server service for each instance of SQL Server running on the computer.
• SQL Server Agent service.  This service implements the agent that runs scheduled SQL Server administrative tasks. There is one SQL Server Agent service for each instance of SQL Server running on the computer.
• Microsoft Search service (Windows NT and Windows 2000 only).  This service implements the full-text search engine. There is only one service, regardless of the number of SQL Server instances on the computer.
• MSDTC service (Windows NT and Windows 2000 only).  This service manages distributed transactions. There is only one service, regardless of the number of SQL Server instances on the computer.
• MSSQLServerOLAPService service (Windows NT and Windows 2000 only).  This service implements SQL Server 2000 Analysis Services. There is only one service, regardless of the number of SQL Server instances on the computer.

SQL Server Service Manager is a taskbar application that follows the standard behavior of taskbar applications. When minimized, the SQL Server Service Manager icon appears in the taskbar status area. To get a menu that includes all of the tasks that SQL Server Service Manager supports, right-click the taskbar icon.

SQL Query Analyzer

SQL Server 2000 SQL Query Analyzer is a graphical tool that helps you to perform a variety of tasks:

• Creating queries and other SQL scripts and executing them against SQL Server databases
• Creating commonly used database objects from predefined scripts
• Copying existing database objects
• Executing stored procedures without knowing the parameters
• Debugging stored procedures
• Debugging query performance problems
• Locating objects within databases or viewing and working with objects
• Inserting, updating, or deleting rows in a table
• Creating keyboard shortcuts for frequently used queries
• Adding frequently used commands to the Tools menu

You can run SQL Query Analyzer directly from the Start menu or from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer by executing the isqlw utility from a command prompt.

SQL Server 2000 Built-In Wizards

SQL Server 2000 contains several wizards to walk administrators and programmers through the steps needed to perform complex administrative tasks and to assist all users to view and modify data with SQL Server databases. SQL Server Books Online describes these wizards in detail.

Lesson Summary

SQL Server 2000 is an RDBMS that is made up of a number of components. The database engine is a modern, highly scalable engine that stores data in tables. SQL Server 2000 replication helps sites to maintain multiple copies of data on different computers in order to improve overall system performance while making sure that the different copies of data are kept synchronized. DTS helps you to build data warehouses and data marts in SQL Server by importing and transferring data from multiple heterogeneous sources interactively or automatically on a regularly scheduled basis. Analysis Services provides tools for analyzing the data stored in data warehouses and data marts. SQL Server 2000 English Query helps you to build applications that can customize themselves to ad hoc user questions. SQL Server 2000 Meta Data Services provides a way to store and manage metadata relating to information systems and applications. SQL Server Books Online is the online documentation provided with SQL Server 2000. SQL Server 2000 includes many graphical and command-prompt utilities that help users, programmers, and administrators perform a variety of tasks.