Microsoft SQL Server 2000 Administrator's Pocket Consultant / Edition 1

Read an Excerpt

Chapter 4: Core Database Administration

Core database administration tasks involve creating, manipulating, and supporting databases. In Microsoft SQL Server 2000, 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 over 2 billion objects. These are theoretical limits, of course, but they demonstrate that SQL Server can handle just about any chore. To perform most administration tasks, you'll need to log in to the database using an account that has the Sysadmin role, such as the local sysadmin account (sa). Detailed information on roles and SQL Server security is found in Chapter 5.

Database Files and Logs

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 automatically restore a database 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 inseparate 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 FAT or NTFS partitions but can't be stored on any compressed file system.

Database files are set when you create or modify the database. By allowing for multiple database files, 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 GBs, with all editions of SQL Server except the Personal Edition, databases can range in size from 1 MB to a theoretical limit of 1,048,516 TBs. With the Personal Edition, databases have a maximum size limit of 2 GB.

As you set out to 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 won't run out of space under normal conditions—provided, of course, that there's file space on the configured drives and that you don't set a maximum database size manually. System databases are the most important ones on the server. You should never directly update tables in system databases. Instead, you should use the appropriate management tools or stored procedures to modify the databases if you need to. The only exception is the model database, which you can update with settings for new databases.

Database Administration Basics

You do most of your database administration work through Enterprise Manager. You'll use Enterprise Manager to perform many common database administration tasks, including

Viewing database information

Checking user and system databases

Examining database objects

The sections that follow examine each of these tasks.

Viewing Database Information in Enterprise Manager

SQL Server organizes information using a top-down hierarchy that goes from server groups to servers to databases to objects. Accordingly, you must work your way down to the database level in order to view the databases installed on a particular server instance. If you have registered a server instance and have connected to it previously, you can view its databases by completing the following steps:

Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with. If the SQL Server service is stopped, you'll need to restart it before accessing the server.

Click the plus sign (+) next to the server you want to work with and then select the Databases folder.

NOTE:
If you haven't authenticated the server connection, you may need to provide a SQL logon account and password. You may also need to reestablish a connection with the server. In either case, enter any necessary information and then click OK/Yes to continue.

You should see a list of the databases available on the server. Now select the database you want to work with in the left pane.

With the Taskpad enabled (by choosing Taskpad from the View menu, if necessary), the right pane should provide access to three different views:

General Displays database, maintenance, total size, and other important database information; also provides quick access links to start key administration tasks, such as backup database and restore database. Move the mouse pointer over a yellow category button to display a shortcut menu.

Table Info Displays the available user tables and indexes in the databases. The user tables are listed alphabetically along with their associated indexes and clustered indexes. You'll find the total table size and the number of rows in a particular table as well.

Wizards Provides quick access to the most commonly used database administration wizards. The wizards are organized into task-related categories, such as those used to manage SQL Server and those used to set up replication. Click a wizard title to start the wizard.

To view database information, click any of the view links in the right pane, either General, Table Info, or Wizards. Figure 4-1 shows the General view....

...Figure 4-1. The General view provides a summary of the selected database and also gives quick access to start key administration tasks. Move the mouse pointer over a category button to display a shortcut menu.

Viewing Database Information Using SQL

You can also use Transact-SQL to examine database information. Transact-SQL is an enhanced version of the standard structured query language that SQL Server uses. Start Query Analyzer and then use the following command:

sp_helpdb <dbname>

where dbname is the name of the database you want to examine.

When you view database information in this way, you get an overview of the database as well as a listing of current data and log files. Table 4-1 gives a summary of this information.

Table 4-1. Database Properties Viewable Using T-SQL

Column Name	Description
compatibility_level	The current compatibility level of the database. 80 indicates SQL Server 2000 compatibility.
created	The date the database was created.
db_size	The total size of the database including all data and log files.
dbid	The unique identifier for the database on thecurrent server.
filegroup	The filegroup associated with the database file. Filegroups allow you to group sets of database files together.
fileid	The unique identifier for the file in the current database.
filename	The full filename and path.
growth	The number of megabytes or percent the file grows by.
maxsize	The maximum file size. Unlimited means there is no limit.
name	The name of the database or file (without a file extension).
owner	The database owner.
size	The current size of a file.
status	The database status.
usage	The way the file is used, such as data only or log only.

Checking System and Sample Databases

A new SQL Server installation includes the system and sample databases listed in Table 4-2. System databases are critical to the proper operation of SQL Server, and backing up and maintaining these databases is a key part of administration. Sample databases, on the other hand, are meant only to provide examples and don't need regular maintenance. The sample databases take up only 6 MB of disk space, and rather than deleting them, you may want to keep them around for testing and for use in demonstrations.

Table 4-2. Summary of System and Sample Databases

Database Name	Database Type	Description
Master	System	Maintains information on all databases installed on the server. This database is modified anytime you create databases, manage accounts, or change configuration settings. Back up the master regularly.
model	System	Provides a template for all new databases. If you want new databases to have certain properties or permissions, put these changes in the model database and then all new databases will inherit the changes.
tempdb	System	Provides a temporary workspace for processing queries and handling other tasks. This database is recreated each time SQL Server is started and is based on the model database.
pubs	Sample	Provides a sample database and is often used to demonstrate SQL/Transact-SQL commands.
Northwind	Sample	Provides a sample database with application programming interface (API) examples.
msdb	System	Used by the SQL Server Agent service when performing handling alerts, notifications, and scheduled tasks. You can access all the information in this database using Enterprise Manager options.

Examining Database Objects

The key elements of a SQL Server database are referred to as objects. The objects you can associate with a database are:

Constraints

Defaults

Indexes

Keys

Stored procedures

Extended stored procedures

Tables

Triggers

User-defined data types

User-defined functions

Views

You can also associate users, roles, rules, and full-text catalogs with databases.

To examine objects within a database, complete the following steps:

Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.

Click the plus sign (+) next to the server you want to work with again and then, if necessary, authenticate yourself or establish a connection, or both.

Work your way down to the database level. Expand the Databases folder and then expand the entry for the database you want to work with.

You should see a list of available database objects. In the left pane, click the element you want to view.

MORE INFO:
Each of these objects is covered in detail in the appropriate chapter. For example, you'll find more information on tables in Chapter 6.

Creating Databases

SQL Server uses the model database as the basis of new databases. If you want new databases to have the same setup, you should modify the model database and then create the necessary new databases. Otherwise, you'll need to man- ually modify the settings of each new database. The easiest way to create a new database is to use Enterprise Manager. You can also create databases using Transact-SQL....