Introduction to SQL Server Databases

The databases are split into two groups, system databases and user databases. The conceptual view of the databases is illustrated in Figure 2–2. The system databases include master, model, tempdb, msdb, and mssqlsystemresource (the new resource database).

Figure 2–2: The conceptual level or view of SQL Server database, as seen from Microsoft SQL Server Management Studio

The master database is used by SQL Server to keep an eye on the state of the system. It also records information about other databases in the system, disk space, usage, DBMS configuration, and so on. Master represents the system schema; and it is also often referred to as the catolog, which is database parlance, and not really a SQL Server term. Database objects, however, are not stored in master as was the case in earlier editions of SQL Server. system objects are stored in the new resource database mentioned earlier. Both master and the resource database are joined at the “hip,” and the one cannot live without the other, as we will discuss shortly.

The model database is a database template. When you create a new database, the new database is a copy of model…actually, it is derived from it. If you need to create databases that are preconfigured with certain settings, you would create the settings in model, and all new databases would inherit the settings. The model database does not need to be installed in turnkey environments.

Note 

You will learn about model and its application in later chapters, especially Chapter 6. It should be used to “bequeath” only, and most positively, the base properties that will form the foundation of all your databases, and not as a means of automatically configuring applications.

Also derived from model is the tempdb database. This database stores all the temporary data that is needed by SQL Server during the lifetime of the SQL Server instance; in other words, until it is stopped and restarted. The data in tempdb is not saved, and the database and its tables are recreated when SQL Server is started.

Then there is the msdb database, which is used by the SQL Server Notification Services and the SQL Server Agent service, discussed in Chapter 4. SQL Server Agent is used for performing scheduled tasks such as database backup and data transformation.

The resource database does not show up in the Object Explorer (Databases node), and you can neither back it up nor restore it using SQL Server’s backup mechanisms. The database is read-only and contains all of the fixed system objects that ship with SQL Server 2005. The objects in the resource database show up in every database you create and will appear in the schema of every database (see the discussions of sys schema in Chapters 4 and 6). The resource database is also discussed as part of a backup/restore strategy in Chapter 7.

The user databases are the databases created for and used by your applications. You can have one database in the system with a certain number of tables, or you can have many databases. One database, serving many needs and users, would be sufficient for most small line-of-business (LOB) applications needed by a small company. For the larger entities, you might have many databases, used by a number of departments and key management entities (KMEs). Only one instance of SQL Server is needed to manage all the databases. Thousands of users can connect to and be serviced on any number of databases, all under the auspices of one instance of the SQL Server engine. (You will learn much more about the role of the server in Chapter 3.)

The physical database files are nothing more than your typical file system files. In that the database can contain objects and properties-and the objects also contain properties-you might prefer to refer to the database file as, itself, an object. And you would be correct. SQL Server regards all the elements of and in its databases as objects; this goes for tables and indexes as well.

A database is split into two or more files as follows:

• The primary data files, which are named with the .mdf extension, keep tabs on the rest of the user files in the database. They are also used for data storage. You can use any extension on your primary data files (and the other files discussed next), but keeping the SQL Server standard extension will let you instantly recognize a data file when you see one.

• The secondary data files are also used for storage and are given the extension .ndf. There can be more than one .ndf file.

• The log files are where all the transaction log entries are stored. These transaction logs account for SQL Server’s recoverability prowess, which I introduced in the preceding chapter. These log files are identified by the .ldf extension.

The files and their location can be viewed, in their physical abstraction, in Windows Explorer, as illustrated in Figure 2–3.

Tip 

The database and log files are shown living together in one folder, which is not a good idea, especially if the disks are not redundant. Better to put the log files on their own hard disk (for better performance, too).

Figure 2–3: Database files as seen from Windows Explorer

Figure 2–4 illustrates the conceptual view of the internal environment from the Object Explorer in Management Studio.

Figure 2–4: The objects inside a SQL Server database

Any number of instances of SQL Server 2005 can attach to a specific collection of databases. You can also detach from a collection of databases and reattach from another instance…even from another server. The only databases you cannot willy-nilly attach is Master and the earlier-discussed system databases. A Master database from one instance is incompatible with another instance of SQL Server. SQL Server can attach version 2000, 7.5, and earlier databases, and you can then perform online upgrades on them. Attaching and detaching are demonstrated in Disaster Recovery in Chapter 7.