This month, I cover a topic from the 70-28 SQL Server 7.0 Administration exam: creating and managing databases. September's T-SQL for Starters column ("Creating SQL Server 6.5 Databases") described how to create databases in SQL Server 6.5. SQL Server 7.0 introduced a new approach to storing data, which takes SQL Server to a new level. You no longer have to define devices before building databases. Now you can jump right in and start creating databases. But you have some new options to consider. Let's look at some of the options and how you can use them to build databases. As always inSQL Server, you can achieve your objective through the GUI or through Transact SQL (T-SQL), but I'll use T-SQL statements in this article.
Before You Begin
As with SQL Server 6.5, in SQL Server 7.0 you define the database size in megabytes. Instead of occupying space in a device, a database is one or more files visible on the hard disk. The transaction log is also a separate file or files and needs to be a separate file set from the database. You can no longer put the data and log on the same device, because they would compete for space. A database can grow automatically as you add data, and so can the transaction log. You can set SQL Server to periodically check whether the database can shrink to recover space after data is deleted.
Creating a Database
Only the sa, a member of the sysadmin role, or someone the sa puts into the dbcreator server role can create a database. In SQL Server 7.0, you create a database with a script such as the one in the Create Database listing, which subscribers can download along with all the code and practice exercises for this article at http://www.sqlmag.com. The Create Database listing creates a budget database. The first line of the script specifies that you want to create the database in the Master database. Technically, shifting the focus to the Master database isn't necessary because the Master database is the only place where you can create a database definition. However, specifying the location reminds you to back up the Master database after you create a database. The database name follows the CREATE DATABASE statement. Then you must specify the logical file name—the name by which SQL Server refers to the database file—and a physical filename, including the path and the directory, for the data file. By convention, use the extension .mdf for the Primary file (which I'll cover later in this article). The example in the create database file puts the budgetdata.mdf file in the C:\mssql7\data directory. SQL Server builds a data directory below the main installation directory. SQL Server uses the data directory as the default data file directory, although you can put your data files anywhere on a local disk (not a network disk). The file starts at 100MB.
Automatic file growth sounds useful, especially if you've ever run out of space on a SQL Server 6.5 database and had to expand the devices and databases. However, you always face the danger of a runaway INSERT statement causing the database to take over the entire disk. (Fortunately, a database file can't expand onto another disk.) If you want to set an upper limit on database growth, use the MAXSIZE option. If you don't set a maximum size, by default the database grows until it fills the disk. To prevent the database from growing automatically, make sure that you set the FILEGROWTH parameter to zero. You can also choose whether the database grows by a fixed number of megabytes or by a percentage of the database size at the time growth occurs. In either case, avoid growing it in tiny increments, which is time-consuming; grab a reasonably sized increment each time. The default, unless you change it, is 10 percent. The database always grows by at least 64KB, in multiples of 64KB, because it allocates space in extents, which are eight 8KB pages. (See Inside SQL Server, "The New Space Management," April 1999, for more information about extents.) If the data needs to grow beyond the limits of one disk, you can spread the data across multiple files on multiple disks—or just use a RAID array.
The transaction log goes in a separate file, with the extension .ldf. The example creates the log in the default C:\mssql7\data directory, but usually it's on another disk. As with the data file, you need to provide both a logical and a physical file name, and the same options for file growth and increment that apply to the data file also apply to the log file. You can spread the transaction log across multiple files or filegroups if it becomes too large for one physical disk. The database creation syntax has a few inconsistencies. For example, the name of the database is a character value, but you must not enclose it in quotes. The logical name of the file is also a character field; you can enclose it in quotes, but you don't need to. And the name of the physical file must be in quotes. However, you can use square brackets instead of quotes if you prefer.
Modifying the Database
Even with automatic file growth, you might need to expand the database to another disk. You use an ALTER DATABASE command for expansion:
ALTER DATABASE Budget
ADD FILE
( NAME = Budget_data2,
FILENAME = 'd:\sqldata\budgetdata2.ndf' ,
SIZE = 200, MAXSIZE = 500, FILEGROWTH = 50 )
By convention, use the .ndf extension for secondary files.
Files and Filegroups
To spread the database across more than one disk, you need both primary and secondary files. A database can have only one primary file. SQL Server 7.0 also introduced the concept of filegroups. A filegroup is an administrative grouping of database files you use for allocating tables and indexes and placing them on specific disks. A filegroup can contain one or more files. Although it's optional in this example, I might have included the keyword PRIMARY when I created the database, thus:
CREATE DATABASE Budget ON PRIMARY( NAME = Budget_data, ...
When you divide a database into filegroups, you can control its distribution across multiple disks, which can help performance. You can achieve similar performance benefits with a stripe set of disks (a RAID array), but adding a file to a filegroup is easy and expanding a stripe set isn't. The Primary filegroup contains the primary file, and always contains the system tables. The Primary filegroup also contains any files that you don't specifically place into a filegroup. So if your database has a primary file and one or two secondary files and you haven't specified any filegroups, all files reside on the Primary filegroup. The transaction log files aren't part of a filegroup.
The Define File Groups listing (online at http://www.sqlmag .com) shows the script for defining filegroups according to Books Online (BOL). We used the
You get another benefit when you perform a backup. The budget projection data, which changes rapidly, is on one filegroup, and the historical data, which doesn't change, is on another filegroup. You can back up the volatile filegroups regularly and back up the static filegroups infrequently. Because the historical data probably comprises a substantial percentage of the total data set, you significantly reduce your backup times. (For more about database backup and recovery, see Wayne Snyder, "Ensuring Up-to-the-Minute Database Recovery," October 1999.)
Other Options
You can specify two other options; the first is for backward compatibility. The FOR LOAD option is necessary in SQL Server 6.5 because during a restore, you first rebuild the database and then restore the backup. FOR LOAD sets the database to dbo use only so that nobody except the sa or DBA can access it while it's being restored. SQL Server 7.0 recreates the database and loads the data from the backup in one operation, so the dbo use only option isn't necessary. However, you can use the FOR LOAD option to rebuild the database as dbo use only, then reload the data in a separate step.
The FOR ATTACH option is new in SQL Server 7.0. You use this option if a set of database files is already present. Just let SQL Server know they exist, so that it can add this database to its list of available databases.
Moving a Database
Also new in SQL Server 7.0 is the ability to detach a database and move it to another server. The command is simple:
EXEC sp_detach_db 'Budget'
The current server doesn't show this database, but the files are still on the disk. Now you can move the files to another server. Then run the sp_attach_db statement to tell SQL Server what this database's files are and where they are. Suppose you moved the Budget database to a new server, copying the data to a new directory on the E drive and the log to a new directory on the F drive. You then run sp_attach_db as follows:
EXEC sp_attach_db 'budget', @filename1 = 'e:\sqldata\Budgetdata.mdf', @filename2 = 'f:\sqllog\Budgetlog.ldf'
The new server places an entry in the sysdatabases table for the database, and the Budget database transfer is complete.
Removing a Database
The syntax for removing a database is still DROP DATABASE plus the name of the database or databases you want to drop. You can't drop a database that's in use, including any published for replication. But in SQL Server 7.0, you don't need to worry about removing the devices—there are none—and SQL Server cleans up the files from the hard disk.
Even if you always use the GUI to create databases, knowing the T-SQL code gives you a feel for what is happening behind the scenes. And generating the code for any database is always a good idea. (In Enterprise Manager, right-click the database, select All Tasks, Generate SQL Scripts, and then generate and save the code.) Remember, always back up the Master database after creating or dropping databases.
