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

3 4

Microsoft SQL Server 2000 is a complete database and analysis solution for rapidly delivering the next generation of scalable Web applications. SQL Server 2000 is a key component in supporting e-commerce, line-of-business, and data warehousing applications, while offering the scalability necessary to support growing, dynamic environments. SQL Server 2000 includes rich support for Extensible Markup Language (XML) and other Internet language formats; performance and availability features to ensure uptime; and advanced management and tuning functionality to automate routine tasks and lower the total cost of ownership. Additionally, SQL Server 2000 takes full advantage of Windows 2000 by integrating with Active Directory Services and supporting up to 32 processors and 64 gigabytes (GB) of Random Access Memory (RAM).

After this lesson, you will be able to:

Describe the SQL Server 2000 RDBMS, including its essential components.
Describe several of the important features of SQL Server 2000.
Identify the various editions of SQL Server 2000.

Estimated lesson time: 20 minutes

What Is SQL Server 2000?

SQL Server 2000 is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. An RDBMS includes databases, the database engine, and the applications that are necessary to manage the data and the components of the RDBMS. The RDBMS organizes data into related rows and columns within the database. The RDBMS is responsible for enforcing the database structure, including the following tasks:

Maintaining the relationships among data in the database
Ensuring that data is stored correctly and that the rules defining data relationships are not violated
Recovering all data to a point of known consistency in case of system failures

The database component of SQL Server 2000 is a Structured Query Language (SQL)-compatible, scalable, relational database with integrated XML support for Internet applications. SQL Server 2000 builds upon the modern, extensible foundation of SQL Server 7.0. The following sections introduce you to the fundamentals of databases, relational databases, SQL, and XML.

Databases

A database is similar to a data file in that it is a storage place for data. Like most types of data files, a database does not present information directly to a user; rather, the user runs an application that accesses data from the database and presents it to the user in an understandable format.

Database systems are more powerful than data files because the data is more highly organized. In a well-designed database, there are no duplicate pieces of data that the user or application has to update at the same time. Related pieces of data are grouped together in a single structure or record, and you can define relationships among these structures and records.

When working with data files, an application must be coded to work with the specific structure of each data file. In contrast, a database contains a catalog that applications use to determine how data is organized. Generic database applications can use the catalog to present users with data from different databases dynamically, without being tied to a specific data format.

Relational Databases

Although there are different ways to organize data in a database, a relational database is one of the most effective systems. A relational database system uses mathematical set theory to effectively organize data. In a relational database, data is collected into tables (called relations in relational database theory).

A table represents some class of objects that are important to an organization. For example, a company might have a database with a table for employees, a table for customers, and another table for stores. Each table is built from columns and rows (attributes and tuples, respectively, in relational database theory). Each column represents an attribute of the object class represented by the table, such that the employees' table might have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object class represented by the table. For example, one row in the employees' table might represent an employee who has employee ID 12345.

You can usually find many different ways to organize data into tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively. Normalization is discussed in more detail in Chapter 3, "Designing a SQL Server Database."

SQL

To work with data in a database, you must use a set of commands and statements (a language) supported by the database management system (DBMS) software. You can use several different languages with relational databases; the most common is SQL. The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) define software standards, including standards for SQL. SQL Server 2000 supports Entry Level SQL-92, the SQL standard published by ANSI and ISO in 1992. The dialect of SQL supported by SQL Server is called Transact-SQL, and Transact-SQL is the primary language used by SQL Server applications. Transact-SQL is discussed in more detail in Chapter 2, "Using Transact-SQL on a SQL Server 2000 Database."

XML

XML is the emerging standard format for data on the Internet. XML is a set of tags that can be included in a text document in order to define the structure of the document.

Although most SQL statements return their results in a relational (tabular) result set, the SQL Server 2000 database component supports a FOR XML clause that causes the results to be returned as an XML document. SQL Server 2000 also supports XPath queries from Internet and intranet applications. You can add XML documents to SQL Server databases, and you can use the OPENXML clause to display the data from the document as a relational result set. XML is discussed in more detail in Chapter 7, "Managing and Manipulating Data."

SQL Server 2000 Features

SQL Server 2000 includes a number of features that support ease of installation, deployment, and use; scalability; data warehousing; and system integration with other server software.

Ease of Installation, Deployment, and Use

SQL Server 2000 includes many tools and features that simplify the process of installing, deploying, managing, and using databases. SQL Server 2000 provides database administrators with all of the tools that are required to fine-tune SQL Server 2000 installations that run production online systems. SQL Server 2000 is also capable of operating efficiently on a small, single-user system with minimal administrative overhead.

The installation or upgrade of SQL Server 2000 is driven by a Graphical User Interface (GUI) application that guides users in providing the information that SQL Server 2000 Setup needs. The Setup program itself automatically detects whether an earlier version of SQL Server is present, and after SQL Server 2000 is installed, it asks users whether they want to launch the SQL Server 2000 Upgrade Wizard to quickly guide them through the upgrade process. The entire installation or upgrade process is accomplished quickly and with minimal input from the users.

SQL Server 2000 reconfigures itself automatically and dynamically while running. As more users connect to SQL Server 2000, it can dynamically acquire additional resources, such as memory. As the workload falls, SQL Server 2000 frees the resources back to the system. If other applications are started on the server, SQL Server 2000 will detect the additional allocations of virtual memory to those applications and reduce its use of virtual memory in order to reduce paging overhead. SQL Server 2000 can also increase or decrease the size of a database automatically as data is inserted or deleted.

SQL Server 2000 offers database administrators several tools for managing their systems, such as SQL Server Enterprise Manager and SQL Profiler. The administration tools are discussed in more detail in Lesson 2, "Components of SQL Server 2000."

Scalability

The SQL Server 2000 database engine is a robust server that can manage terabyte databases being accessed by thousands of users. At the same time, when running at its default settings, SQL Server 2000 has features such as dynamic self-tuning that enable it to work effectively on laptops and desktops without burdening users with administrative tasks.

SQL Server 2000 includes several features that extend the scalability of the system. For example, SQL Server 2000 dynamically adjusts the granularity of locking to the appropriate level for each table referenced by a query and has high-speed optimizations that support Very Large Database (VLDB) environments. In addition, SQL Server 2000 can build parallel execution plans that split the processing of a SQL statement into several parts. Each part can be run on a different Central Processing Unit (CPU), and the complete result set is built more quickly than if the different parts were executed serially.

Many of the features that support the extended scalability of SQL Server 2000 are discussed in more detail throughout the training kit.

Data Warehousing

A dat warehouse is a database that is specifically structured to enable flexible queries of the data set and decision-making analysis of the result set. A data warehouse typically contains data representing the business history of an organization. A data mart is a subset of the contents of a data warehouse. A data mart tends to contain data that is focused at the department level, or on a specific business area. SQL Server 2000 includes several components that improve the capability to build data warehouses that effectively support decision support processing needs:

Data Warehousing Framework. A set of components and Application Programming Interfaces (APIs) that implement the data warehousing features of SQL Server 2000.
Data Transformation Services (DTS). A set of services that aids in building a data warehouse or data mart.
Meta Data Services. A set of ActiveX interfaces and information models that define the database schema and data transformations implemented by the Data Warehousing Framework. A schema is a method for defining and organizing data, which is also called metadata.
Analysis Services. A set of services that provide OLAP processing capabilities against heterogeneous OLE DB data sources.
English Query. An application development product that enables users to ask questions in English, rather than in a computer language such as SQL.

System Integration

SQL Server 2000 works with other products to form a stable and secure data store for Internet and intranet systems:

SQL Server 2000 works with Windows 2000 Server and Windows NT Server security and encryption facilities to implement secure data storage.
SQL Server 2000 forms a high-performance data storage service for Web applications running under Microsoft Internet Information Services.
SQL Server 2000 can be used with Site Server to build and maintain large, sophisticated e-commerce Web sites.
The SQL Server 2000 TCP/IP Sockets communications support can be integrated with Microsoft Proxy Server to implement secure Internet and intranet communications.

SQL Server 2000 is scalable to levels of performance capable of handling extremely large Internet sites. In addition, the SQL Server 2000 database engine includes native support for XML, and the Web Assistant wizard helps you to generate Hypertext Markup Language (HTML) pages from SQL Server 2000 data and to post SQL Server 2000 data to Hypertext Transport Protocol (HTTP) and File Transfer Protocol (FTP) locations.

SQL Server supports Windows Authentication, which enables Windows NT and Windows 2000 user and domain accounts to be used as SQL Server 2000 login accounts. Users are validated by Windows 2000 when they connect to the network. When a connection is formed with SQL Server, the SQL Server client software requests a trusted connection, which can be granted only if they have been validated by Windows NT or Windows 2000. SQL Server, then, does not have to validate users separately. Users are not required to have separate logins and passwords for each SQL Server system to which they connect.

SQL Server 2000 can send and receive e-mail and pages from Microsoft Exchange or other Message Application Programming Interface (MAPI)-compliant mail servers. This function enables SQL Server 2000 batches, stored procedures, or triggers to send e-mail. SQL Server 2000 events and alerts can be set to send e-mail or pages automatically to the server administrators in case of severe or pending problems.

Editions of SQL Server 2000

SQL Server 2000 is available in different editions to accommodate the unique performance, run-time, and price requirements of different organizations and individuals.

SQL Server 2000 Enterprise Edition. This edition is the complete SQL Server offering for any organization. The Enterprise Edition offers the advanced scalability and reliability features that are necessary for mission-critical line-of-business and Internet scenarios, including Distributed Partitioned Views, log shipping, and enhanced failover clustering. This edition also takes full advantage of the highest-end hardware, with support for up to 32 CPUs and 64 GB of RAM. In addition, the SQL Server 2000 Enterprise Edition includes advanced analysis features.
SQL Server 2000 Standard Edition. This edition is the affordable option for small- and medium-sized organizations that do not require advanced scalability and availability features or all of the more advanced analysis features of the SQL Server 2000 Enterprise Edition. You can use the Standard Edition on symmetric multi-processing systems with up to four CPUs and 2 GB of RAM.
SQL Server 2000 Personal Edition. This edition includes a full set of management tools and most of the functionality of the Standard Edition, but it is optimized for personal use. In addition to running on Microsoft's server operating systems, the Personal Edition runs on non-server operating systems, including Windows 2000 Professional, Windows NT Workstation 4.0, and Windows 98. Dual-processor systems are also supported. While this edition supports databases of any size, its performance is optimized for single users and small workgroups and degrades with workloads generated by more than five concurrent users.
SQL Server 2000 Developer Edition. This SQL Server offering enables developers to build any type of fapplication on top of SQL Server. This edition includes all of the functionality of the Enterprise Edition but with a special development and test end-user license agreement (EULA) that prohibits production deployment.
SQL Server 2000 Desktop Engine (MSDE). This edition has the basic database engine features of SQL Server 2000. This edition does not include a user interface, management tools, analysis capabilities, merge replication support, client access licenses, developer libraries, or Books Online. This edition also limits the database size and user workload. Desktop Engine has the smallest footprint of any edition of SQL Server 2000 and is thus an ideal embedded or offline data store.
SQL Server 2000 Windows CE Edition. This edition is the version of SQL Server 2000 for devices and appliances running Windows CE. This edition is programmatically compatible with the other editions of SQL Server 2000, so developers can leverage their existing skills and applications to extend the power of a relational data store to solutions running on new classes of devices.

Lesson Summary

Microsoft SQL Server 2000 is a complete database and analysis solution for rapidly delivering the next generation of scalable Web applications. SQL Server is an RDBMS that uses Transact-SQL to send requests between a client computer and a SQL Server 2000 computer. A database is similar to a data file in that it is a storage place for data; however, a database system is more powerful than a data file. The data in a database is more highly organized. A relational database is a type of database that uses mathematical set theory to organize data. In a relational database, data is collected into tables. SQL Server 2000 includes a number of features that support ease of installation, deployment, and use; scalability; data warehousing; and system integration with other server software. In addition, SQL Server 2000 is available in different editions to accommodate the unique performance, run-time, and price requirements of different organizations and individuals.