Core database administration tasks involve creating, manipulating, and supporting databases. In Microsoft SQL Server 2005, a database is a collection of data and the objects that represent and interact with that data. Tables, views, stored procedures, triggers, and constraints are typical database objects.
A single database server instance can have up to 32,767 databases, and each database can have more than 2 billion objects. These are theoretical limits, of course, but they demonstrate that SQL Server can handle just about any job. To perform most administration tasks, you must log on to the database using an account that has the Sysadmin fixed server role, such as the local sysadmin account (sa). Detailed information on roles and SQL Server security is found in Chapter 8.
Each SQL Server database has a transaction log associated with it. A transaction log is a history of modifications to the database, and SQL Server uses it to ensure database integrity. All changes to the database are first written to the transaction log and then applied to the database. If the database update is successful, the transaction is completed and recorded as successful. If the database update fails, SQL Server uses the transaction log to restore the database to its original state (which is called rolling back the transaction). This two-phase commit process makes it possible for SQL Server to restore a database automatically in case of power failure, server outage, or other problems that occur when you enter a transaction.
SQL Server databases and transaction logs are contained in separate database files. This means that each database always has at least two files associated with it—a data file and a log file. Databases also can have secondary data files. SQL Server uses three types of database files:
Primary data files. Every database has one primary data file. These files store data and maintain records of other files used in a database. By default, these files end with the .mdf extension.
Secondary data files. These files store additional data for a database. By default, these files end with the .ndf extension.
Transaction log files. Every database has at least one transaction log file. This file contains information necessary to restore the database. By default, log files end with the .ldf extension.
Note
SQL Server also uses backup devices. Backup devices can be physical devices, such as tape drives, or files that are stored on a local drive or a network share. SQL Server data and log files can be stored on either File Allocation Table (FAT) or NT File System (NTFS) partitions, but they cannot be stored on any compressed file system.
Tip
In SQL Server 2005, full-text catalogs are treated as files and are included in the database file set for the purposes of backup and restore. See the section titled "Working with Full-Text Search" in Chapter 5, for more information.
Database files are set when you create or modify the database. Because multiple database files are allowed, SQL Server can create databases that span multiple disk drives and that can grow in size as needed. Although the size of a SQL Server database is often measured in gigabytes, with all editions of SQL Server except the Express Edition, databases can range in size from 1 MB to a theoretical limit of 1,048,516 terabytes. With the Express Edition, databases have a maximum size limit of 4 GB.
As you work with databases, keep in mind that SQL Server is designed to expand databases automatically as necessary. This means that master, tempdb, msdb, and other critical databases will not run out of space under normal conditions—provided, of course, that there is file space on the configured drives and that you have not set a maximum database size manually.
System databases are the most important databases on the server. You should never directly update tables in system databases. Instead, use the appropriate management tools or stored procedures to modify the system databases if necessary. The only exception is the model database, which you can update with settings for new databases.