Microsoft SQL Server 2000 Administrator's Pocket Consultantby William R. Stanek, William Stanek
Microsoft SQL Server 2000 Administrator's Pocket Consultant is the ideal concise, immediate reference you'll want with you at all times as you deal with the details of Microsoft SQL Server 2000 database administration. Whether you handle administration for 50 users or 5000, this hands-on, fast-answers guide focuses on what you need to do to get the job done quickly
Microsoft SQL Server 2000 Administrator's Pocket Consultant is the ideal concise, immediate reference you'll want with you at all times as you deal with the details of Microsoft SQL Server 2000 database administration. Whether you handle administration for 50 users or 5000, this hands-on, fast-answers guide focuses on what you need to do to get the job done quickly. With extensive easy-to-read tables, lists, and step-by-step instructions, it's the portable, readable guide that will consistently save you time and minimize system downtime by giving you the right information right now.
Read an Excerpt
Chapter 4: Core Database AdministrationCore 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 LogsEach 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.
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 conditionsprovided, 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 BasicsYou 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 ManagerSQL 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.
- 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:
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.
- 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.
Click to view graphic
...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 SQLYou 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
|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 DatabasesA 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 ObjectsThe key elements of a SQL Server database are referred to as objects. The objects you can associate with a database are:
- Stored procedures
- Extended stored procedures
- User-defined data types
- User-defined functions
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.
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 DatabasesSQL 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....
Meet the Author
William R. Stanek has 20 years of hands-on experience with advanced programming and development. He is a leading technology expert and an award-winning author. Over the years, his practical advice has helped millions of programmers, developers and network engineers all over the world. He has written more than two dozen computer books. Current or forthcoming books include Microsoft Windows® XP Professional Administrator's Pocket Consultant, Microsoft Windows 2000 Administrator's Pocket Consultant, Second Edition, and Microsoft Windows Server™ 2003 and IIS 6.0 Administrator's Pocket Consultant.
and post it to your social network
Most Helpful Customer Reviews
See all customer reviews >