Chapter 2: Database Architecture

Overview

At the conceptual level, the facility for data storage in SQL Server 2005 is the database. SQL Server’s databases are fully compliant with the ANSI SQL-2003 definition of a database…with some remarkable extensions you will learn about in this chapter and throughout this book.

The databases do not themselves store data. Rather, the data is stored in tabulated structures (hereafter called tables), and these tables are stored within the confines of the database file, in a number of sophisticated file structures we will investigate later in this chapter.

Contrary to the terminology used in many circles, the term database will not be used to refer to the system as a whole. A SQL Server database is just the barn where the grain stores or silos are kept. It encompasses the mechanisms of management and functionality, Instead I will refer to the “system” as the database management system or DBMS. The client/server architecture and the DBMS will be the central subject of Chapter 3.

This chapter will thus explore the database architecture of SQL Server on various levels, often in very abstract terms. We will dig deeper in other parts of the book where we need to clarify both development and administration techniques.

From a conceptual level, the database is the container for all tables, indexes, constraints; security, programmatic, and other objects and properties used by the DBMS to manage the data. SQL Server databases are viewed from both the logical perspective-by developers, clients and users, and server components-and the physical perspective-by database administrators, system administrators, and server “machinery.”

The logical perspective is essentially the external view of the database, seen by users and software developers alike. The physical perspective encompasses the internal view of the database. The internal view is primarily the view of the database administrator or DBA. Figure 2–1 illustrates the three abstract SQL Server database levels and the “entities” that “appreciate” these perspectives.

Figure 2–1: The three levels or views of a SQL Server database management system. The internal level contains the physical files of the database, the conceptual level is an abstraction of the physical files, and the external level is what your users interact with

Besides the physical parts of the database, such as its file groups, this chapter kicks off with an overview of the logical components of a SQL Server database, such as tables and indexes.