SQL Server 2000 Developer's Guide

Chapter 1: An Overview of SQL Server 2000

In this chapter, you get an overview of the essential elements of Microsoft's SQL Server, starting with the big picture of SQL Server. In this first section, you see how SQL Server is used and how it fits into both small business and enterprise-level computing. Next, this chapter covers SQL Server's networked architecture. SQL Server is built using a modular architecture made up of a collection of core components. This section introduces you to these major objects that make up SQL Server and shows you how they are related. After that, an overview of SQL Server's database architecture is provided, followed by its major administrative components. Finally, this chapter concludes with a summary of the development history of SQL Server, along with the new features found in SQL Server 2000.

Microsoft SQL Server 2000 is a relational database system that is scaleable from small departmental networks to enterprise-wide networks. SQL Server maintains the core database files for use by custom database applications using development languages, such as Visual Basic and Visual C++, or by desktop applications, such as Microsoft Word, Excel, and Access.

SQL Server is a native 32-bit Windows database that benefits from its tight integration with the Windows operating system. For small-scale databases, SQL Server Personal Edition runs on simple Windows 9x systems with as little as 32MB of RAM. The SQL Server database engine is also the core of the standalone Microsoft Data Engine (MSDE) product distributed with the Microsoft Office Premium and Microsoft Office Develop editions as an alternative to Jet, the native Access database engine. For high-end databases, the SQL Server Enterprise Edition can take advantage of Windows 2000's support for symmetric multiprocessing (SMP) systems and failover clustering. Running on Windows 2000 Datacenter, the SQL Server 2000 Enterprise Edition can use up to 32 CPUs, up to 64GB of RAM, and be set up in a 4-node failover cluster configuration. For even larger database implementations, the 64-bit version of SQL Server can take advantage of the 16TB of RAM that is accessible by the 64-bit version of Windows 2000 running on the Intel Itanium processor.

SQL Server's Networked Architecture

SQL Server's distributed architecture splits the database access application from the database engine. SQL Server's core database server runs on a Windows-based server, which is typically connected to multiple client systems via an Ethernet local area network (LAN). The client systems are typically PCs running SQL Server client software. These PCs can be standalone desktop systems or they can be the platform for other network services, like an Internet Information Server (IIS) Web server. SQL Server supports the following client systems:

• Windows NT/2000
• Windows 9x
• Remote Access Server (RAS)
• Windows for Workgroups
• Macintosh
• DOS
• OS/2
• UNIX/Linux

Figure 1-1 illustrates the networked database access SQL Server provides. You can see that the client side of SQL Server is separate from the server portion. The SQL Server database engine runs on either a Windows NT or a Windows 9x server, but data is not generally accessed from the server system. Instead, users typically access the SQL Server database from the networked client systems. In other words, the SQL Server client component runs on each of the networked clients, while the database server component only runs on the SQL Server system.

Figure 1: SQL Server's networked architecture

The network is essential for connecting the client systems to the SQL Server system. SQL Server network connections make use of popular network topologies, such as Ethernet and Token Ring. SQL Server also provides protocol independence and is compatible with all the popular network protocols, including TCP/IP, NetBEUI, IPX/SPX, Banyan VINES, DEC Pathworks, and Apple's AppleTalk.

SQL Server's networked foundation gives it many advanced features not shared by traditional mainframe databases. Data access isn't restricted to an existing set of host database application programs. Instead, one of the primary advantages of SQL Server is its tight integration with both leading edge development tools, as well as desktop applications such as Microsoft Word, Access, and Excel. You can access a SQL Server database from custom applications built using Visual Basic, Visual C++, PowerBuilder, Delphi, SQL Windows, Visual FoxPro, and many other PC development environments. SQL Server is compatible with several data access interfaces that can be used by the popular development tools. For example, SQL Server databases can be accessed using the Microsoft JET Engine and Data Access Objects (DAO), Remote Data Objects (RDO), ActiveX Data Objects (ADO), OLE DB, ODBC, SQL Server's built-in DB-Library, and other third-party tools. For seamless desktop database access, SQL Server comes with an OLE DB driver and an ODBC driver that enable SQL Server data access from any ODBC- or OLE DB-compliant desktop application. OLE DB and ODBC database access open up the SQL Server database for ad hoc queries, data analysis, and custom reporting from hundreds of shrink-wrapped desktop applications. Desktop integration reduces the need for custom programming typically required by host environments for every new report or query. Instead, users can access database information using the desktop tool with which they are already familiar. Support for ODBC also enables SQL Server databases to be accessed by other platforms, such as the Macintosh or various UNIX systems.

Figure 1-2 provides a more detailed look at SQL Server's data access architecture. In the top portion of the figure, you can see how the various client applications use the data access application programming interfaces (APIs) SQL Server provides. SQL Server's four primary data access APIs are OLE DB, ODBC, the DB-Library, and Transact-SQL. For Windows clients, all these APIs are implemented as dynamic link libraries (DLLs) and they communicate to SQL Server via SQL Server's various client Net-Libraries. The client Net-Library uses a networked IPC (interprocess communication) method to communicate across the network to the server's network libraries residing on the SQL Server system.

Figure 2: SQL Server's data access architecture

The server's Net-Libraries receive the data packets sent from the client systems and pass them to SQL Server's Open Data Services (ODS), a server-side API that consists of a set of C++ functions and macros. SQL Server itself is an ODS application that accepts the ODS calls, processes them, and passes the results back to ODS. While it's possible to develop applications using the ODS API, this is typically only used to provide custom gateways to other database systems. The heterogeneous database support provided by the much-easier-to-use ODBC and OLE DB APIs has largely eliminated the need to develop applications using ODS.

SQL Server's Basic Components

Figure 1-3 presents a high-level view of SQL Server's four basic server components. As you saw earlier, SQL Server's Open Data Services component provides an interface between the server's Net-Libraries and the SQL Server engine (MSSQLServer).

The MSSQLServer service manages all the files that comprise the SQL Server database. It is also responsible for processing SQL statements and allocating system resources.

The SQLServerAgent service is responsible for scheduling SQL Server's jobs and alerts. In SQL Server terminology, a job is a predefined object consisting of one or more steps, where each step consists of a predefined data function like a Transact-SQL statement or batch of statements, while an alert is an action taken in response to a specific event. An alert can be set up to perform a variety of tasks, such as running jobs or sending e-mail.

The Microsoft Distributed Transaction Coordinator (MSDTC) is a transaction manager responsible for coordinating database transactions across multiple servers. MSDTC can be invoked either by the SQL Server database engine or directly from a client application.

On Windows NT/2000, these components are implemented as Services. On Windows 9x, they are implemented as standard executable programs.

Figure 3: SQL Server's high-level component hierarchy

Transact-SQL

Structured Query Language (SQL) is a high-level language originally developed by IBM to provide access to the data contained in relational databases. Since its origin, SQL has been widely adopted and now almost all modern databases can be accessed using SQL. With its widespread adoption, the SQL language has been standardized by the American National Standards Institute (ANSI-www.ansi.com). SQL Server uses a dialect of SQL called Transact-SQL. Transact-SQL is compliant with most ANSI SQL standards, but it also provides several extensions and enhancements. For instance, Transact-SQL contains several flow-control keywords that facilitate its use for developing stored procedures and triggers.

Transact-SQL is commonly used for database management tasks, such as creating and dropping tables and columns, as well as for writing triggers and stored procedures. You can also use Transact-SQL to change SQL Server's configuration or you can even use it interactively with SQL Server's Graphical Query Analyzer utility to perform ad hoc queries. Transact-SQL provides three categories of SQL support: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). SQL DDL commands are used for database management tasks, such as creating tables and views. SQL DML commands are used to query and update the data contained in the database. SQL DCL commands are used to control the database operations. Chapters 2 and 3 provide more detailed information on DDL and DC, while Chapters 5 and 6 provide more detailed information about using DML.

SQL Server Database Architecture

Now that you've had a high-level look at SQL Server's basic components, let's look more closely at the database architecture. Figure 1-4 presents SQL Server 2000's database architecture.

Figure 4: SQL Server 2000 database architecture...