Lesson 1: What Is SQL Server 2000?

3 4

SQL Server 2000 is a family of products designed to meet the data storage requirements of large data processing systems and commercial Web sites, as well as meet the ease-of-use requirements of individuals and small businesses. At its core, SQL Server 2000 provides two fundamental services to the emerging Microsoft .NET platform, as well as in the traditional two-tier client/server environment. The first service is the SQL Server service, which is a high-performance, highly scalable relational database engine. The second service is SQL Server 2000 Analysis Services, which provides tools for analyzing the data stored in data warehouses and data marts for decision support.

---

After this lesson, you will be able to

• Describe the SQL Server 2000 environment
• Describe the SQL Server 2000 relational database engine
• Describe SQL Server 2000 Analysis Services
• Describe SQL Server 2000 application support
• Describe the various editions of SQL Server 2000 and understand their differences
• Describe how SQL Server 2000 integrates with Windows 2000 and Windows NT 4.0
• Describe the database and security architecture of SQL Server 2000

Estimated lesson time: 45 minutes

---

The SQL Server 2000 Environment

The traditional client/server database environment consists of client applications and a relational database management system (RDBMS) that manages and stores the data. In this traditional environment, the client applications that provide the interface for users to access SQL Server 2000 are intelligent (or thick) clients, such as custom-written Microsoft Visual Basic programs that access the data on SQL Server 2000 directly using a local area network.

The emerging Microsoft .NET platform consists of highly distributed, loosely connected, programmable Web services executing on multiple servers. In this distributed, decentralized environment, the client applications are thin clients, such as Internet browsers, which access the data on SQL Server 2000 through Web services such as Microsoft Internet Information Services (IIS).

Figure 1.1 illustrates each of these types of clients accessing SQL Server 2000.

SQL Server 2000 Components

SQL Server 2000 consists of numerous components. An administrator of SQL Server 2000 servers and databases must understand each of the components that comprise SQL Server 2000. Figure 1.2 illustrates the major components of SQL Server 2000 and their relationships.

Figure 1.1

The SQL Server 2000 environment.

Figure 1.2

SQL Server 2000 components.

SQL Server 2000 Relational Database Engine

The SQL Server 2000 relational database engine is an RDBMS that manages and stores data in relational tables. Each table represents some object of interest, such as customers, employees, or products. Each table has columns that represent an attribute of the object modeled by the table (such as customer number, customer name, address, and phone number), and rows that represent a single occurrence of the type of object modeled by the table (such as customer number 1374281). The relational database engine relates tables to each other when requested by an application (such as a request for a list of all customers who purchased blue trucks in 1999).

The relational database engine is designed to store detailed records of transactions generated by online transaction processing (OLTP) systems, as well as handle the online analytical processing (OLAP) requirements of data warehouses. The relational database engine is responsible for maintaining data security, providing fault tolerance, dynamically optimizing performance, using locking to provide concurrency, and ensuring data reliability.

SQL Server 2000 Analysis Services

SQL Server 2000 Analysis Services provides tools for analyzing the data stored in data warehouses and data marts on SQL Server 2000. Certain analytical processes, such as getting a summary of the monthly sales by product of all the stores in a region, take a long time if run against all the detail records of an OLTP system. To speed up these types of analytical processes, you can use SQL Server 2000 to periodically summarize and store data from an OLTP system in fact and dimension tables. This storage of summarized data for analysis is called a data warehouse. A subset of this data (such as for a region or a division of a company) is called a data mart. SQL Server 2000 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 making informed business decisions. Processing analytical queries on SQL Server 2000 Analysis Services multidimensional cubes is substantially faster than attempting the same queries on the detail data recorded in OLTP databases.

Application Support

Application developers write client applications that access SQL Server 2000 in a number of different ways.

A client application can submit Structured Query Language (SQL) statements to the relational database engine, which returns the results to the client application in the form of a tabular result set. The specific dialect of SQL supported by SQL Server 2000 is called Transact-SQL. Transact-SQL supports the Entry Level of the SQL-92 ANSI standard. Transact-SQL also supports many custom extensions, as well as some features from the Intermediate and Full Levels of SQL-92.

A client application can also submit either Transact-SQL statements or XPath queries and request that the database engine return the results in the form of an Extensible Markup Language (XML) document. XML is an emerging Internet protocol for exchanging information between systems by using self-describing data sets.

A client application can use any of the common Windows data access interfaces, such as Microsoft ActiveX Data Objects (ADO), OLE DB, or Open Database Connectivity (ODBC) to send Transact-SQL statements or XPath queries to the relational database engine using a native OLE DB provider or ODBC driver. A client application can also use Hypertext Transfer Protocol (HTTP) to send Transact-SQL statements or XPath queries to the relational database engine. A client application can also use the multidimensional extensions of either ADO or OLE DB to send Multidimensional Expressions (MDX) queries to SQL Server 2000 Analysis Services multidimensional cubes for decision support queries.

An application developer can also use any of the following administrative Application Programming Interfaces (APIs) to incorporate SQL Server 2000 administration functionality directly into a custom application to manage SQL Server 2000 and create, configure, and maintain databases, data warehouses, and data marts.

• SQL Distributed Management Objects (SQL-DMO)—This API is a set of Component Object Model (COM) objects that encapsulates the administration functions for all of the entities in the relational database engine and databases.
• Decision Support Objects (DSO)—This API is a set of COM objects that encapsulates the administration functions for all of the entities in SQL Server 2000 Analysis Services engine and multidimensional cubes.
• Windows Management Instrumentation (WMI)—This API is an object-oriented API that enables management applications and scripts to monitor, configure, and control the Windows operating system and devices, services, and applications in a Windows network. SQL Server 2000 provides a SQL Server 2000 WMI provider that enables WMI applications to retrieve information on SQL Server 2000 databases and instances.

Additional Components

SQL Server 2000 provides additional components to support the needs of large enterprises. As a database administrator, you will make use of the first two of these components. Application developers primarily use the last two components.

SQL Server 2000 Data Transformation Services (DTS)

You can use SQL Server 2000 Data Transformation Services (DTS) to retrieve data from one data source, perform simple or complex transformations on the data (such as summarizing data), and then store it in another data source, such as a SQL Server database or an Analysis Services multidimensional cube. DTS can work with any data source that can be accessed using OLE DB, including SQL Server, Oracle, Informix, DB2 and Microsoft Access databases, Microsoft Excel spreadsheets, and SQL Server multidimensional cubes. Using DTS, you can simplify and automate the process of building and maintaining data warehouses. You can also use DTS for the initial population of an OLTP database.

SQL Server 2000 Replication

You can use SQL Server 2000 replication to keep data close to individuals or workgroups in order to optimize performance or autonomy, while at the same time making sure that all copies of the data stored on separate computers are kept synchronized with one another. For example, a regional sales office can maintain the sales data on a local server, and replicate the sales data to a SQL Server 2000 database in the national headquarters. Mobile users using laptop computers or Windows CE devices 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. You can also use SQL Server 2000 replication to replicate data to a data warehouse, and to replicate data to or from any data source that supports OLE DB access.

SQL Server 2000 English Query

SQL Server 2000 English Query provides a system for developing client applications that enable end users to pose questions in English instead of forming a query with a Transact-SQL statement or an XPath query. English Query can be used to access data in OLTP databases or in SQL Server 2000 Analysis Services multidimensional cubes. For example, given a car sales database, an application can send English Query a string containing the question, "How many blue trucks were sold in 1999?"

The application developer specifies database information so that English Query can process English questions about the database's particular tables, fields, objects, and data. For example, English Query must know that a question about customers is related to data in a Customers table. English Query translates a question into a Transact-SQL SELECT statement that is then executed against the SQL Server 2000 database to get the answer.

Meta Data Services

SQL Server 2000 Meta Data Services is a set of services that allows meta data about databases and client applications to be stored and managed. Meta data is information about the properties of data, such as the type of data in a column (numeric, text, and so on) or the length of a column. In a data warehouse environment, meta data can be information about the design of objects such as multidimensional cubes or dimensions, the quality and lineage of the data in the warehouse, the source and target databases, data transformations, data cleansing, data marts, and OLAP tools.

SQL Server 2000, SQL Server 2000 Analysis Services, SQL Server 2000 English Query, and Microsoft Visual Studio use Meta Data Services to store meta data, to interchange meta data with other tools, and to add versioning capability to tools that support meta data creation. Meta Data Services supports three open standards: the Meta Data Coalition Open Information Model (MDC OIM), COM interfaces, and XML encoding.

SQL Server 2000 Editions

SQL Server 2000 is available in six different editions. Four of these editions are for production systems, one edition is for application development, and one edition is for evaluation only. In addition, the SQL Server 2000 Desktop Engine component is available for distribution with applications.

SQL Server 2000 Enterprise Edition

The SQL Server 2000 Enterprise Edition supports all SQL Server 2000 features. This edition is for medium and large production database servers and scales up and out to support the performance levels required for the large Web sites, enterprise OLTP, and large data warehousing systems (OLAP).

SQL Server 2000 Standard Edition

The SQL Server 2000 Standard Edition supports many SQL Server 2000 features, but lacks the features required to scale up and out to support very large databases, data warehouses, and Web sites. This edition is for small workgroups or departments. Relational database engine features not supported on this edition are

• Failover clustering
• Failover Support in SQL Server Enterprise Manager
• Parallel CREATE INDEX
• Parallel DBCC
• Log shipping
• Enhanced read-ahead and scan
• Indexed views
• Federated Database Server
• System Area Network (SAN) Support
• Graphical utilities support for language settings

SQL Server 2000 Analysis Services features not supported on this edition are

• User-defined OLAP partitions
• Linked OLAP cubes
• Real-time OLAP
• Partition Wizard
• Relational OLAP (ROLAP) dimension support
• HTTP Internet support
• Calculated cells
• Writeback to dimensions
• Very large dimension support
• Distributed partitioned cubes

SQL Server 2000 Personal Edition

The SQL Server 2000 Personal Edition supports all of the SQL Server 2000 features supported by the SQL Server 2000 Standard Edition, except for transactional replication, which is subscriber-only supported. In addition, full-text search is not supported when this edition is installed on Windows Millennium Edition (ME) and Windows 98.

This edition is for standalone applications and mobile users requiring local data storage on a client computer. The SQL Server 2000 Personal Edition has a concurrent workload governor that limits the performance of the relational database engine when more than five batches are executed concurrently.

SQL Server 2000 Windows CE Edition

The SQL Server 2000 Windows CE Edition (SQL Server CE) is used as the data store on Windows CE devices. The memory footprint for SQL Server CE is approximately 1 MB. SQL Server CE is implemented as a set of dynamic-link libraries (DLLs) that operate as an OLE DB CE provider. This implementation allows SQL Server CE to support the ActiveX Data Objects for Windows CE (ADOCE) and OLE DB CE APIs in the Windows CE-based versions of Visual Basic and Microsoft Visual C++. Also, it means that multiple applications running at the same time can share a common set of DLLs and save space.

Windows CE devices connected to the network can use the Remote Data Access (RDA) feature of SQL Server CE to

• Connect to instances of SQL Server 2000 on other Windows platforms
• Execute a SQL statement and pull in the result set as a recordset
• Modify a recordset and push the modifications back to an instance of SQL Server 2000 on another Windows platform
• Subscribe to merge replication as an anonymous subscriber to keep Windows CE data synchronized with a primary database

The SQL Server CE connectivity options are well suited for use on wireless networks through networking features such as data compression and messaging to reduce data transmissions, and robust recovery from lost connections.

SQL Server 2000 Developer Edition

The SQL Server 2000 Developer Edition supports all of the SQL Server 2000 features, other than graphical utilities support for language settings. This edition is for programmers developing applications that use SQL Server 2000 as their data store. This edition is licensed for use only as a development and test system, not a production server.

SQL Server 2000 Enterprise Evaluation Edition

The SQL Server 2000 Enterprise Evaluation Edition is a full-featured version available by a free download from the Web. This edition is for use in evaluating the features of SQL Server 2000 and will stop running 120 days after downloading. Suppport for language settings in SQL Server graphical tools is not available in the Evaluation Edition.

SQL Server 2000 Desktop Engine

The SQL Server 2000 Desktop Engine is a redistributable version of the SQL Server 2000 relational database engine. This edition is for applications to use to store data without requiring any database administration from the end user. The Desktop Engine is designed to manage its configuration and resource usage dynamically, minimizing the requirement for administration of the engine after it has been installed. The Desktop Engine does not include any of the SQL Server 2000 utilities or tools that have graphical user interfaces. Standard SQL Server 2000 APIs must be used to create and configure the database, and the application must use the SQL Server 2000 APIs to perform any needed administration.

The Desktop Engine supports the same relational database engine and replication features as the Personal Edition, other than full-text search and the graphical administration and developer tools and wizards. However, the Desktop Engine does not support SQL Server 2000 Analysis Services. The size of Desktop Engine databases cannot exceed 2 GB and the Desktop Engine has a concurrent workload governor that limits the performance of the database engine when more than five batches are executed concurrently.

Installing SQL Server Editions on Various Windows Operating Systems

Table 1.1 sets forth the operating system platforms on which each SQL Server edition may be installed.

Table 1.1 Operating Systems Supported by SQL Server Editions

Operating System	Enterprise	Standard	Personal	Enterprise Evaluation	Desktop Engine	Developer	Windows CE
Windows 2000 Data Center	Supported	Supported	Supported	Supported	Supported	Supported	N/A
Windows 2000 Advanced Server	Supported	Supported	Supported	Supported	Supported	Supported	N/A
Windows 2000 Server	Supported	Supported	Supported	Supported	Supported	Supported	N/A
Windows 2000 Professional	N/A	N/A	Supported	Supported	Supported	Supported	N/A
Windows NT 4.0 Server, Enterprise Edition	Supported	Supported	Supported	Supported	Supported	Supported	N/A
Windows NT 4.0 Server	Supported	Supported	Supported	Supported	Supported	Supported	N/A
Windows NT 4.0 Workstation	N/A	N/A	Supported	Supported	Supported	Supported	N/A
Windows, Millennium Edition	N/A	N/A	Supported	N/A	Supported	N/A	N/A
Windows 98	N/A	N/A	Supported	N/A	Supported	N/A	N/A
Windows CE	N/A	N/A	N/A	N/A	N/A	N/A	Supported

Integration with Windows 2000 and Windows NT 4.0

When an instance of SQL Server 2000 is running on any version of Windows 2000 or Windows NT 4.0, the following features and capabilities of these operating systems are leveraged and integrated.

Windows Authentication

An instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 can use Windows authentication and allow the operating system to control access to SQL Server 2000 using only trusted connections. In this environment, a user can connect to an instance of SQL Server 2000 without providing additional authentication credentials to SQL Server 2000 if that user has successfully logged on to the Windows operating system by using a valid Windows 2000 or Windows NT 4.0 user account. Allowing the operating system to handle authentication provides additional security, including the use of Kerberos authentication.

Memory Management

An instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 dynamically uses available physical memory as a data buffer to minimize disk I/O and maximize performance. Each instance of SQL Server 2000 coordinates with Windows 2000 or Windows NT 4.0 to dynamically acquire and release memory as needed by instances of SQL Server 2000 and other server applications running on the same computer.

SQL Server 2000 Enterprise Edition uses the Microsoft Windows 2000 Address Windowing Extensions (AWE) API to support memory up to 64 GB of RAM on Windows 2000 Data Center and up to 8 GB on Windows 2000 Advanced Server. This allows instances of SQL Server 2000 Enterprise Edition to cache large numbers of rows in memory, which reduces overhead and speeds the processing of queries.

Active Directory

When SQL Server 2000 is installed on a Windows 2000 Server computer where Active Directory directory services are available, you can publish information about SQL Server 2000 in Active Directory. This information can include information about names and locations of SQL Server 2000 systems and their database names, locations, sizes, and most recent backup dates. Replication publications can also be published. In addition, with SQL Server 2000 Analysis Services, you can publish information about available data warehouses and data marts. Client applications can be coded to search Active Directory for information that has been published.

Additionally, security account delegation is supported on an instance of SQL Server 2000 installed on a Windows 2000 computer where Active Directory directory services and Kerberos authentication are available. Security account delegation is the ability to connect to multiple servers, and with each server change, to retain the authentication credentials of the original client.

An instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 leverages the security and encryption facilities of these operating systems to implement secure data storage. This includes the option to enable the use of Secure Sockets Layer (SSL) to encrypt all data between client applications and SQL Server 2000.

Failover Clustering

An instance of SQL Server 2000 Enterprise Edition running on Windows 2000 or Windows NT 4.0 supports failover clustering to provide immediate failover to a backup server with no disruption in operation. Windows 2000 Data Center supports up to four failover nodes.

Microsoft Distributed Transaction Coordinator

An instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 integrates with Microsoft Distributed Transaction Coordinator (MS DTC) to provide support for distributed transactions. MS DTC allows applications to extend transactions across two or more instances of SQL Server 2000 either on the same computer or across different computers.

SMP

An instance of SQL Server 2000 running on Microsoft Windows 2000 Data Center can scale effectively on up to 32 processors, and on Windows 2000 Advanced Server can scale effectively on up to 8 processors on symmetric multiprocessor (SMP) computers.

Asynchronous and Scatter-gather I/O

An instance of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 takes advantage of asynchronous I/O and scatter-gather I/O to maximize throughput to support many concurrent users. Scatter-gather I/O allows a read or a write to transfer data into or out of discontiguous areas of memory. Asynchronous I/O allows instances of SQL Server 2000 to maximize the work done by individual threads while processing a batch. The scatter-gather I/O algorithm has been significantly improved on Windows 2000. In addition, SQL Server 2000 on Windows 2000 can benefit from the use of Intelligent Input/Output (I2O) hardware to offload I/O from the system processors to a dedicated processor.

Event Logs

Instances of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 use event logs to record significant system, application, and security events related to SQL Server 2000.

System Monitor Counters

Instances of SQL Server 2000 running on Windows 2000 or Windows NT 4.0 provide objects and counters that can be used by System Monitor (Performance Monitor in Microsoft Windows NT 4.0), SQL Server Enterprise Manager, and SQL Server Agent to monitor SQL Server 2000 activity.

Lesson Summary

SQL Server 2000 is a relational database management system providing services to OLTP and OLAP environments. SQL Server 2000 is available in a number of different editions to meet the needs of a variety of users and environments, and can be installed on a variety of Windows operating system platforms. It is tightly integrated with Windows 2000 and Windows NT 4.0 to fully leverage their capabilities and maximize the performance of SQL Server 2000.