Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 5 -
Creating Devices, Databases, and Transaction Logs

SQL Server places databases onto devices. Devices must be created on a computer system's storage device, usually a hard drive.
Databases store data and are where you create the tables that you use in SQL server. Transaction logs store transactions that were made against a database, providing for the roll-back and other fault-tolerant capabilities of SQL Server.
SQL Server uses a temporary database as an electronic scratch pad to work with information requests. There are several things that can be done to tempdb to enhance performance.
Many applications are sent to a remote site to be executed or are for temporary applications. Databases stored on removable media might be more efficient and cost-effective for such applications.

The storage of physical data in SQL Server is controlled through the creation of Devices.

Devices are areas of disk that are preallocated for the use of SQL Server, and appear as files on your system. SQL Server can use devices for the storage of data, logs, or database dumps.

Databases are logical areas that SQL Server reserves for the storage of tables and indexes. One or more databases can be created on a given device; the only gating factor is that the overall size of the databases on a device can't exceed the size of the device.

Transaction logs, covered in Chapter 12, "Understanding Transactions and Locking," are the work areas that SQL Server uses to manage a sort of picture of the information on your system in a before-and-after state. This before-and-after information is used to control transaction rollbacks, should the need arise, and they are also used to recover a database, should it need to be restored or restarted unexpectedly.

Defining Devices

Devices are the physical files that SQL Server creates on-disk for storing databases and logs. Devices must be created before databases can be created. A device can be of two types: a database device is used for storing databases, and a dump device is used for storing transaction logs.

Devices can store more than one database or transaction log in them. However, you often get better performance with a single database per device. This performance increase results from the fact that each device is managed by a single I/O thread from the operating system. By allocating only a single database per device, you're, in effect, setting up your system so that it can service each database with a different I/O thread, instead of sharing these threads between databases.

A database can span multiple devices if it needs to grow, and this can be an optimizing method due to striped physical disk access over multiple drives.

The optimization comes from being able to split a database across devices. This gives you two distinct advantages. First, you can place the different devices on different physical drives. By doing so, you allow the different disk drives to service the device, effectively giving you more than one set of read/write heads working on a database request. This can be an especially big benefit in a larger database with large amounts of random requests for information that tend to span the database, or impact it at different points.

The performance increase can be less in a case where your application is largely responsible for inserting new records. This is because the new records are likely to be maintained on the expanded portion of the database and to reside on the same device and physical disk as the database grows.

The second optimization from splitting the database across devices comes from the I/O threads used by the operating system. Because each device is managed by a different I/O thread, when you put different portions of your database on different devices, you allow the system to allocate more than one I/O thread to support your database. Once again, this can be a real benefit to larger databases.

Creating Database Devices

You can create a disk device in SQL Server in two ways. Graphically, you create a disk device by using the SQL Enterprise Manager. Another process is Transact-SQL, T-SQL, using either the ISQL command-line utility or the ISQL/W Windows-based utility.

The Transact-SQL method is performed using the DISK INIT command. Both the Enterprise Manager and T-SQL methods are discussed in the following sections.

Using SQL Enterprise Manager SQL Enterprise Manager is a versatile tool that permits Database Administrators (DBAs) to perform most of the administrative functions of SQL Server without knowledge of the often cryptic Transact-SQL commands required. In some cases, when you create your SQL Server system, you'll want to use an ISQL Script, which enables you to run an essentially unattended installation. One drawback to the Enterprise Manager is that it does not permit this type of unattended installation. Although you can load the installation scripts and run them easily, you cannot run the Enterprise Manager from the command-line. It's meant to be more of an interactive tool for management of the server.

To use SQL Enterprise Manager to create a device, follow these steps:

1. Start SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Figure 5.1 shows SQL Enterprise Manager just after it has been started.

FIG. 5.1
Note that no server is selected, and that most of the main toolbar buttons are disabled.

2. Select the server that is going to be managed. Then, from the Manage menu, choose Database Devices. The Manage Database Devices window appears, as shown in Figure 5.2.

3. Click the New Device toolbar button, located farthest to the left on the Manage Database Devices dialog box, to create a new device. The New Database Device dialog box appears (see Figure 5.3).

4. Enter the details about the device being added, including the name, where the device should be placed, and its size (see Figure 5.4).

FIG. 5.2
Each existing device is shown as a bar on the graph, with the dark area representing unused space in the device.

FIG. 5.3
With the New Database Device dialog box, you can use the slider to specify how big the device should be by dragging it to the right to indicate the size you need.

FIG. 5.4
The information entered creates a 50M device called Demodevice on the C drive using the physical file C:\MSSQL\DATA\ demodevice.DAT.

5. Click Create Now to begin creating the device. The length of time that this process takes depends on the size of the device being created and the speed of the physical drives being used. After the device is successfully created, you see the message box shown in Figure 5.5.

FIG. 5.5
This message indicates that no errors occurred during the allocation of disk space for the database device's creation.


NOTE: This device can be scheduled to be created by using the Schedule button. This option lets the user enter a time--a single or recurring instance--for the operation to be performed. In addition, the Transact-SQL script that SQL Server uses to complete the operation can be edited. Additions or modifications can make the scheduled operation more powerful than simply creating a table.


TIP: If you remove a device, you need to restart SQL Server before you can create a new device with the same physical file name. For example, if you create a test device, remove it; then try to re-create it again. SQL Server fails during the second creation step. You receive an error message indicating that the file could not be opened/accessed (see Figure 5.6).

FIG. 5.6
You may receive an error message if you try to create a new device with the same name as a previously existing device.


TIP: After you restart SQL Server, you can create the new device as needed.

After successful creation of the device, SQL Enterprise Manager will add it to the graph of devices. This graph is shown in Figure 5.7.

FIG. 5.7
The NewDevice device has been added and is now empty, as indicated by the dark graph bar.


NOTE: SQL Server creates a physical file on the drive when the device is created. If you are using NT File System, NTFS, the operating system returns control to SQL Enterprise Manager immediately after executing the create command because of the way NTFS represents files to calling programs. Don't be concerned if device creation that took a long time on FAT or OS/2 HPFS is very quick. This speed is one of the blessings of NT's new file system.

Using DISK INIT DISK INIT is the T-SQL equivalent of creating a device through SQL Enterprise Manager. In fact, SQL Enterprise Manager's graphical front end is actually just creating the right DISK INIT command to be sent to the server. The syntax for the use of DISK INIT is as follows:

DISK INIT
     NAME = `logical_name',
     PHYSNAME = `physical_name',
     VDEVNO = virtual_device_number,
     SIZE = number_of_2K_blocks
     [, VSTART = virtual_address]

The options for the T-SQL command DISK INIT are as shown in Table 5.1.


TIP: Before you run the DISK INIT command, run SP_HELPDEVICE. This stored procedure shows you what device numbers are already in use. Device 0 is reserved for the main, default device on your system, but all other non-used values are available for your new device.

By running SP_HELPDEVICE first, you can avoid getting an error message when you run the DISK INIT indicating that the device number is already in use.


Table 5.1 DISK INIT Parameters

Parameter Description
logical name Any valid SQL Server identifier. A shorter name is helpful as it has to be used each time a database is created.
physical name The full path and file name of the file to be used on the hard disk to store the data.
virtual device number The unique system identifier for the device. It can range from 0 to 255. Zero(0) is reserved for the master database, but it cannot duplicate any existing virtual device number in your system.
number of 2k blocks This is how a device is sized, which is the minimum value of 512, equivalent to 1M.
virtual address This parameter controls the virtual paging of the data device and how SQL Server accesses it. This parameter should be used only when you're working closely with a product support technician at Microsoft and is not typically something you'll need to work with.

Using the DISK INIT command, the following SQL statement creates the same data device that was created earlier by using the SQL Enterprise Manager.

DISK INIT
     Name = `NewDevice',
     PhysName = `C:\SQL60\DATA\NewDevice.DAT',
     VDevNo = 6,
     Size = 2500

Understanding Device Options

There are two different options that you can consider for devices you create. Though not required, these options may be helpful depending on the environment you are setting up. The options control how devices are used after you've created them. These options, mirroring and default devices, let you indicate to SQL Server how the devices will be used, and you provide information about how SQL Server can apply special processing to the devices.

One option, that of creating a mirroring device, is provided to give you a fault tolerant option. The other option, creating a default device, can save time when you create databases on your system.

The specifics of these options are covered in the next two sections.

Mirroring When you set up a device for Mirroring, you indicate to SQL Server that it should write transactions to databases on the device twice. This is done once on the primary device defined for the database and once on the mirror device. Take care in selecting the location of the mirror. You'll be doing yourself little or no good if you place the mirror device on the same physical drive as the source device. If you do, and the drive fails, you will lose both the source device and the mirror.

Because hard drives are the most likely candidate for a system failure that impacts data, you may want to consider this option to protect your databases.


NOTE: Windows NT also supports several fault-tolerant options that are enforced at the operating system level. For more information, please refer to Que's Special Edition Using Microsoft Windows NT Server and see the information topics regarding striped disk storage.

In most cases, the operating system's implementation of mirroring provides better performance and options than SQL Server. These options might include various implementations of Redundant Arrays of Inexpensive Drives, RAID. There are six levels of RAID, zero(0) to five(5), that can be implemented and they provide different methods of distributing physical data across multiple drives.


Overview of Mirroring

Mirroring is done when SQL Server writes changes to a device to two locations at once. The primary location is your standard device. This is the database that you are normally using and have incorporated in your system's applications. Because both devices are identical, if a switch is required from the main device to the secondary device, there is no data loss, and the users of your system will not be interrupted in their use of the system.

Another key aspect of mirroring and the device change that happens when the system recovers from a device failure is that it is completely transparent to your applications. They can still access the system with the same database references and programmatic statements and require no changes in the code to reference the substituted device.


See Appendix B, "Understanding RAID," for more information about levels of RAID.

As SQL Server uses a device, if it can no longer access the main device, it automatically switches to the mirror device, making all future changes to the mirror. When you've recovered the drives containing the problem device, you can move the information back to the original device and restart SQL Server using the corrected devices as the default once again.

SQL Server performs mirroring by installing a mirror-handling user on the server. This user is listed as spid 1 in the system processes, which you can list by running the system procedure sp_who. Mirroring is a continuous operation and provides maximum redundancy in the event of a failure.

You can also set up mirroring to be handled by the underlying Windows NT Operating System and with some hardware-only solutions. For more information on these options, be sure to review the documentation for the product you are considering to ensure that it is compatible with up-time requirements for your system.

Setting Up Mirroring with Enterprise Manager

When you create a device, or if you edit a device, you'll notice an option to establish mirroring. In Figure 5.8, you can see the options that enable you to work with a device.

FIG. 5.8
Right-click a device and select Edit to modify mirroring options.

Click Mirroring to indicate where the mirror file will be located. Remember, the paths you designate are relative to the server. If you're working from a workstation, be careful to keep this in mind. You can select a location that is either on the current server or on a fully qualified network path (see Figure 5.9).

FIG. 5.9
When you select the location of the mirror file, SQL Server mirrors the requested database.


CAUTION: If you're using long file names for your devices (such as NewDevice), be aware that there seems to be a bug in the way that SQL Enterprise Manager assigns the name of the mirror device. SQL Enterprise Manager defaults the name to be limited by the old FAT 8.3 limitations and truncates NewDevice to NewDevic. This isn't a major problem, but it could result in SQL Server attempting to create duplicate files because the difference in the name is not considered until after the eighth byte of the file.

For example, NewDevice and NewDevice2 will have an identical default mirror name created, which will cause an error condition in SQL Enterprise Manager.


After the process is completed, the device is mirrored and SQL Server takes care of the rest. From that point on, any informational changes to the primary device are also reflected in the mirror device.


CAUTION: If you implement mirroring for a device, be sure you also mirror the Master database. This database is responsible for maintaining the system information necessary to continue processing should mirroring become necessary for any device. When you mirror the Master database, you're ensuring that you'll be able to enable SQL Server to recover the mirrored devices that have failed.

If you do not mirror the Master database, you may find that you cannot utilize the mirrors for other devices, as this information (the information about the mirrored devices managed by SQL Server) is maintained in the Master database and could be lost.


The final step is to tell SQL Server about the mirrored device in the case of the Master database. Because the Master database is used to start the server, you need to indicate to the server where the mirror can be found if it is needed. Other devices are managed automatically, so this step is only required for the Master database.

There are two parameters required at SQL Server startup that control the Master database's mirrored state: -r and -d. Figure 5.10 shows the SQL Server Configuration/Options dialog box. Choose Server, S_QL Server, Configure to open this dialog box. Click the Parameters button on the Server Options page and then add the startup parameters.

Add a new parameter,-r<mirror location>, which indicates the location of the mirror for the master device. Note that, even after SQL has switched to the mirror device during normal use, when you restart the system, it must still first try the primary Master device. This means that you must leave the -d specification that is showing the location of the "normal" Master device. When you add the -r option, you're specifying the fallback position that SQL should use when it has determined the master device to have become unusable.

Click the Add button to create a new parameter, and be sure to specify the entire path and file name to the mirror device file when you create the -r parameter.

FIG. 5.10
Startup options that enable mirroring the Master database can be set in the SQL Server configuration subsystem.

Figure 5.11 shows the dialog box that enables you to indicate the startup options.

FIG. 5.11
Be sure to specify both -r and -d options for the Master device. If both are not found, the mirroring will not work correctly.

Setting Up Mirroring Using Transact-SQL

When you set up mirroring using T-SQL, you'll use the DISK MIRROR command.

The DISK MIRROR command's syntax is as follows:

DISK MIRROR
     NAME = `logical_name',
     MIRROR = `physical_name'
     [, WRITES = {SERIAL | NOSERIAL }]

The parameters shown in Table 5.2 are available for the DISK MIRROR command:

Table 5.2 DISK MIRROR parameters

Parameter Description
logical name This is the name of the device that will be mirrored.
physical name This is the full path and file name of the mirror device. Use an extension of MIR for compatibility with SQL Enterprise Manager.
WRITES This option is provided for backward compatibility with earlier versions of SQL Server for operating systems other than NT. It is not used with Windows NT.

The following example shows how to mirror the NewDevice created earlier:

DISK MIRROR
     Name = `NewDevice',
     Mirror = `C:\SQL60\DATA\NEWDEVICE.MIR'

What to Do When Mirroring Is in Force

When SQL Server has switched to a mirror, it automatically suspends mirroring with the original device. This frees up the device to be replaced or otherwise corrected. When the mirroring is active, the users of your system will not notice any difference in the functioning of their applications. They'll be able to continue working just as they did before the problem was found with the original device.

After you've replaced the original device, or otherwise corrected the problem that was detected, you can re-mirror the active device back to the primary device. Re-mirroring is identical to the original job of mirroring a device. You simply specify the file name that will contain the mirror, and SQL Server takes care of the rest.


NOTE: Mirroring can have a significant impact on performance on your system. Since SQL Server is forced to write the information to two devices instead of one, you'll quickly find that the overhead involved can be substantial. There are a few things you can consider to help lessen this impact, specifically:


If you want to switch from a current device to the mirror, as may be the case where you've just brought a replacement for a failed device online, the following are the steps you'll follow, from the creation of the original mirror through failure of the device to replacement of it and re-mirroring to return to the original state:

1. Mirror the original device.

2. The device fails; SQL Server switches to the mirror device and continues processing.

3. Replace or correct the original device.

4. Re-mirror the active device back to the original.

5. Unmirror the device, selecting the option to Switch to Mirror Device--Replace option.

6. Mirror the device, now the replacement for the original, back to the backup device.

At this point, you're back where you started with the original device being the active device and the mirror standing by as needed to back it up.

As you can see, mirroring your devices can provide powerful backup capabilities to your key production systems.


See Chapter 19, "SQL Server Administration" in the section, "Backing Up and Restoring Databases and Transaction Logs" for more information about backup techniques and approaches.

Establishing Default Devices Default devices are used by SQL Server when the CREATE DATABASE command isn't accompanied by a specific device on which it should be placed. Many devices can be specified as default. SQL Server uses them alphabetically until each device is filled.


TIP: When SQL Server is installed, the MASTER device is set up as the default device. This means that if you inadvertently create a new database and do not indicate the device on which to place it, it will be created on the MASTER device. This is never a good thing.

The full syntax is explained in the next portion of this chapter, but one of the first things you should do on a new system is create a new device that you can use as a starting point for all databases. Then, issue the command that will make the MASTER device a non-default device:

sp_diskdefault Master, defaultoff

and make the newly created device the new starting default device:

sp_diskdefault newdevice, defaulton

It might be a good idea to make this second, newly defined default device extremely small. This way, if you ever forget to indicate a default device when you create a database, the call will fail because it will run out of space. Think of it as a string around your finger, reminding you that you need to indicate the device you want to use for new databases.


To make a device a default device, select the Default Device check box on the Edit Database Devices dialog box in SQL Enterprise Manager. Alternately, use the sp_diskdefault system-stored procedure. The syntax for sp_diskdefault is as follows:

sp_diskdefault device_name, {defaulton | defaultoff}

Device_name is the logical device name that's being made the default or not. If your device name has special characters you will need to enclose the device name in single quotes so that SQL Server recognizes it.

In the following example, the device NewDevice is made a default device:

sp_diskdefault NewDevice, defaulton

Displaying Device Information

There are two ways to find information about the devices that are now installed/active on a SQL Server. This can be done by using SQL Enterprise Manager or by using the system-stored procedure sp_helpdevice.

The syntax for sp_helpdevice is as follows:

sp_helpdevice [logical_name]

logical_name is the name of the device that's to be inspected. If no device is specified, sp_helpdevice reports information on all the devices on the SQL Server.


NOTE: The sp_helpdevice command must be entered through Transact-SQL. See the command line applications section of Chapter 1, "Introducing Microsoft SQL Server," for help on starting a command line ISQL session. There is also a graphical interface for ISQL/w. Transact-SQL commands can be entered in SQL Enterprise Manager by choosing Tools, SQL Q_uery Tool.


CAUTION: If you use the command line ISQL application, remember to enter a go command to perform the commands entered to that point. The go command is the default command end identifier. This identifier can be changed by using a switch when starting the ISQL session. Again, see the command line applications section in Chapter 1.

Listing 5.1 shows the output and use of sp_helpdevice to view all the devices on the server:

Listing 5.1 Sample sp_helpdevice Results

/*----------------------------
sp_helpdevice
----------------------------*/
device_name    physical_name               description status cntrltype device_number low         high
----------------------------------------------
diskdump       nul                         disk, dump device 16     2         0             0           20000
diskettedumpa  a:sqltable.dat              diskette, 1.2 MB, dump device 16     3         0             0           19
diskettedumpb  b:sqltable.dat              diskette, 1.2 MB, dump device 16     4         0             0           19
master         C:\SQL60\DATA\MASTER.DAT    special, default disk, physical disk, 40 MB 3      0         0             0           20479
MSDBData        C:\SQL60\DATA\MSDB.DAT     special, physical disk, 2 MB2      0         127           2130706432  2130707455
MSDBLog         C:\SQL60\DATA\MSDBLOG.DAT  special, physical disk, 2 MB2      0         126           2113929216  2113930239

(1 row(s) affected)

Creating Dump Devices

Dump devices are special devices that SQL Server uses to perform backups and to dump, or clear out, the transaction logs on databases. By default, SQL Server creates dump devices for the use of backups and log clearing. Several types of dump devices can be created, based on the medium to which the data is being written:


See Chapter 1, "Introducing SQL Server," in the section entitled "Command-Line Applications."

You can add a dump device to the system through SQL Enterprise Manager, or by using the system-stored procedure sp_addumpdevice. The following two sections show you how to use both methods.


TIP: You'll find it easier, and faster, if you first dump a database or transaction log to a disk-based dump device, then use a backup utility to move the resulting dump file to a tape or other backup media. Having SQL Server place database or log dumps directly on a backup device can be substantially slower as it's not optimized for that type of access.

Using SQL Enterprise Manager to Add a Dump Device Using SQL Enterprise Manager to add a dump device removes the burden on the DBA to remember the syntax required for the system-stored procedures that must be executed to perform the task. To add a dump device by using SQL Enterprise Manager, follow these steps:

1. Launch SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Select the server that is going to be managed. From the Tools menu, choose Database Backup/Restore. The Database Backup/Restore dialog box appears as shown in Figure 5.12.

FIG. 5.12
The Backup tab is chosen by default when entering this dialog box.

2. Click the New button to create a new dump device. The New Backup Device dialog box is displayed (see Figure 5.13).

FIG. 5.13
SQL Server places all disk-based dump devices in the Backup subdirectory by default.

3. Enter a Name and specify a Location for the device.


TIP: Click the ... button to find a particular directory on the server or network.
4. Specify whether the device is Tape or Disk, and then click the Create button. The device is added to SQL Server and to the list of available devices.


NOTE: SQL Server allocates resources for a dump device in the list and in the sysdevices system catalog table. It doesn't, however, actually create a file/output item until a backup is actually performed to the device. If you click the dump device to inspect it before you perform a backup, you'll receive error #3201, and SQL Server will say that the device is offline. Don't worry about this error unless you're sure that a backup has been performed to the device. If that's the case, you'll want to rerun your backup to ensure that it completes successfully.

Using sp_addumpdevice SQL Server's system-stored procedure sp_addumpdevice is used to add dump devices to the system. sp_addumpdevice is the only way that you can add a diskette-based device for dumping to the SQL Server. The syntax for sp_addumpdevice is as follows:

sp_addumpdevice {`disk' | `diskette' | `tape'},
      `logical_name',
      `physical_name'


NOTE: Previous versions of SQL Server had some other parameters for sp_addumpdevice that were used to define the characteristics of the media being added. This is no longer necessary because SQL Server now inspects the device to determine its characteristics automatically.

The options for the system-stored procedure sp_addumpdevice are as follows:

The following example adds a disk-based dump device to SQL Server:

sp_addumpdevice 
      `DiskBackup',
      `C:\SQL60\Data\DISKBACKUP.DAT'

The following example adds a remote disk-based dump device on the network workstation/server MainFileServer:

sp_addumpdevice
      `NetworkBackup',
      `\\MainFileServer\Data\NETBACKUP.DAT'

The following example adds a tape dump device to SQL Server:

sp_addumpdevice
      `TapeBackup', 
      `\\.\Tape0'

Dropping Devices

There are two ways to drop a device. These are by using SQL Enterprise Manager or by using the system-stored procedure sp_dropdevice. Dropping a device frees the disk space associated with that device for other uses by the operating system or server.

If a device is not in use or is not correctly sized, it will be necessary to drop it so that it can be appropriately resized or the disk space given back to the operating system for other uses.

Using SQL Enterprise Manager to Remove Devices SQL Enterprise Manager provides a simple interface to the removal of database devices and is a convenient tool for managing large enterprises where lots of servers are involved. The DBA no longer has to know all the physical layouts of the server's devices because they are represented graphically by SQL Enterprise Manager.


CAUTION: The drop device feature of SQL Enterprise Manager defaults to not removing the device file physically from the hard drive, nor does it give you an option to tell it to do so. Consequently, using SQL Enterprise Manager to remove a disk device won't actually make any disk space available for use on the server. You must go to a command prompt or to an Explorer Window and manually delete the file.

If you're deleting a device so that the server frees up some allocated disk space, you're probably better off using sp_dropdevice and specifying DELFILE.


To use SQL Enterprise Manager to remove a database device, follow these steps:

1. In the SQL Enterprise Manager screen, select the server from which you want to remove the device. From the Manage menu, choose Devices. The Manage Database Devices window appears (see Figure 5.14).

FIG. 5.14
SQL Enterprise Manager's Manage Database Devices window lists all the devices that are available on the server that is being managed.

2. Click the device that you want to remove. From the Manage Database Devices window's toolbar, click the Delete Device button. A message box appears that confirms that you want to delete the device, as shown in Figure 5.15.

FIG. 5.15
This is the last chance to abort a device deletion.

If the database device has one or many databases or logs on it, a second warning dialog box appears, asking permission to drop all the databases/logs that reside on it (see Figure 5.16).

FIG. 5.16
This dialog box lists databases that are using the device about to be deleted. You must drop these databases before the device can be deleted.

Using sp_dropdevice to Remove Devices The system-stored procedure sp_dropdevice is provided for dropping devices from SQL Server. The syntax for sp_dropdevice is as follows:

sp_dropdevice logical_name[, DELFILE]

The options for the system-stored procedure sp_dropdevice are as follows:

An error occurs if this procedure is run against a device that has databases in it. The databases must be dropped before the device can be deleted.

Defining Databases and Transaction Logs

Databases are logical entities in which SQL Server places tables and indexes. A database exists on one or many database devices. Correspondingly, a database device can have one or many databases on it.

Every database has a transaction log associated with it. The transaction log is a place where SQL Server writes all the database transactions before writing them to the database.

There are two types of operations written to the transaction log. The first, as you might guess, is the process of working with transactions in the programming sense. These "open" transactions let your application ensure database consistency based on the process that is going on in your application. In other words, it's a form of checkpoint in your application that lets you make sure you get everything done that you expect for a specific operation.

The other type of information written to the transaction log is the before-and-after picture of information in your database when you perform operations on it. That is, when you update a row, the before-and-after values of the row are stored in the transaction log.

In either event, the transaction log is used to recover the database should an error occur that requires a roll-back or roll-forward of information. It is what SQL Server uses to ensure database consistency relative to both the rules managed by the server, and the processing completed by your applications.

By default, the transaction log is placed on the same database device as the database. You can improve performance, however, by creating two devices, one for the log and one for the database itself. This helps performance because you'll be able to minimize the physical movement of the read-write heads on the disk drive. If you have only one physical drive for both the database and the log, the disk drive read-write operations will need to race back and forth between the database and the log on each operation.

Logs are a sequential type of operation, typically just logging new information to the end of the log. Because this is true, by placing the log on a different drive, you allow it to be fairly optimized, always ready for the next item to log. It won't have to re-seek the end of the log prior to writing the information to disk.

SQL Server can logically maintain up to 32,767 databases on a single server. However, it's more likely that the server will run out of disk, memory, and CPU resources before this limit is ever reached. A database can be up to 1T (Terabyte) in size and can have as many as 32 device fragments, which are portions of the database on different devices.

Given that physical disk sizes don't typically support this size of a hard drive, it would seem to be impossible to get a database much bigger than 320G, 10G/drive approximate current maximum disk size, by using SQL Server. However, a database device actually can be mapped to multiple physical devices, provided some form of software- or hardware-based striping is in use.

Striping is highly recommended because it provides substantial performance gains due to multiple physical disk drives being used for a single database device. When used with RAID, striping also provides an extra level of data integrity in case of a media failure.


See Appendix B, "Understanding RAID," for more information.

Creating a Database and Transaction Log

After you've created the device for a database and transaction log, you'll be able to create the database itself. This is the last step that you need to complete prior to creating the tables and other structures on which your system will be based. When you create a new database, SQL Server uses a template database, the model database, as the starting point for the database. You can think of the model database as having the default objects that are implemented on your systems databases.

The model database consists of the standard SQL Server objects:

If you have certain attributes you want installed in any database you create, you can implement them in the model database. Then, when new databases are created, the custom attributes are inherited by the new database. One example of this is the case where you have a series of database administrators that you want to have access to all databases created on the system. By including them in the user list, you can make sure they're included in each database, with appropriate rights, as the databases are created.

You manage the model database as you do any other, and there are no limitations on the objects you can place within the database. You can use SQL Enterprise Manager to work with the different objects in the database.

There are two ways to create a database and transaction log. You can use either SQL Enterprise Manager or the CREATE DATABASE command. Each of these is described next.


NOTE: You must either be the System Administrator, or SA, for the SQL Server, or you must have had appropriate rights granted to you by the SA in order to create databases. If you do not have rights, SQL Server will not allow you to create the new database.

Using SQL Enterprise Manager to Create Database and Transaction Logs To create a database using SQL Enterprise Manager, follow these steps:

1. Launch SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Select the server that is going to be managed. From the Manage menu, choose Databases. The Manage Databases window appears (see Figure 5.17).

FIG. 5.17
Each existing database is shown as a bar on the graph, with the dark area representing unused space in the database.

2. Click the New Database toolbar button to create a new database.

3. Enter the details about the database being added, including the name, the devices that the data and logs should be placed on, and how much disk space should be used for each. This is shown in Figure 5.18.

FIG. 5.18
In the SQL Enterprise Manager's New Database dialog box, the required information is entered to add a 3M database called NewDatabase on the NewDevice for data and logs.


TIP: SQL Server 6.0 introduced a new database creation option that stops any users from accessing a database until a load operation is performed. This option is very useful for a database administrator who wants to create a database without having any users connect to it.

Click the Create For Load check box in the New Database dialog box to stop any users from accessing the database until after the load operation has been completed.


4. Click Create Now. After the database is successfully created, the Manage Databases window appears, showing the new database in the graph (see Figure 5.19).

FIG. 5.19
The NewDatabase has been added, based on the model database, and is mostly empty.

Using the CREATE DATABASE Command to Create Database and Transaction Logs The CREATE DATABASE command is the Transact-SQL method for creating a database. The syntax for CREATE DATABASE is as follows:

CREATE DATABASE database_name
      [ON {DEFAULT | database_device} [= size]
      [, database_device [= size]]...]
      [LOG ON log_device [= size]
      [,log_device[= size]]...]
      [FOR LOAD]

The options for the Transact-SQL command CREATE DATABASE are as listed in Table 5.3.

Table 5.3 CREATE DATABASE Parameters

Parameter Description
database_name This is the name of the database to be created. The database name must comply with the standard rules for naming objects.
database_device This is the device or list of devices that this database is to be created on and how much disk space, in megabytes, is to be reserved on each. If DEFAULT is specified, SQL Server chooses the next free default database device to use.
log_device The LOG ON parameter is where the log device is specified. Like the database device, it's possible to specify more than one device to be used for the logging of the database being created.
FOR LOAD This parameter denies user access until a LOAD operation has been completed on the database.

The following example creates the same database that was created in the preceding section by using SQL Enterprise Manager:

CREATE DATABASE NewDatabase
      On NewDevice = 3
      Log On NewDevice = 2


TIP: Be sure to read the size of the database in the message that SQL Server returns. If the full size of the requested database is not available, SQL Server creates the database as large as possible to fit the device or devices you indicated.

The actual size of the resulting database is returned in the status message from SQL Server. Because no error message is issued if the device selection was not large enough, this is your only indication that the database size differs from what you requested.


Displaying Database Information

When you're working with an existing database, for example, when you want to increase the size, or alter other attributes of the database, it's helpful to determine exactly how the database is set up. As you might imagine by now, you can do this with both the SQL Enterprise Manager and ISQL.

The Manage Databases window in SQL Enterprise Manager provides a graphical display of all the information about a database. You can also right-click the database name in the tree view, then select Edit... from the resulting menu, which enables you to edit the different facets of the database configuration.

You can perform all of the database-centric administration of your SQL Server from the Edit... option for the database.

To view this information in ISQL, use the system-stored procedure sp_helpdb. The syntax for sp_helpdb is

sp_helpdb database_name

If a database_name is supplied, sp_helpdb reports information about that database. If not, it reports information about all the databases on the server. For example, sp_helpdb model results in Listing 5.2:

Listing 5.2 Sample Output from sp_helpdb

name                db_size       owner               dbid   created     status 
model                     1.00 MB sa                  3      Apr  3 1996 no options set 
 
device_fragments               size          usage                               
master                               1.00 MB data and log                        
 
device                         segment                        
master                         default                        
master                         logsegment                     
master                         system

Listing 5.3 shows the use of sp_helpdb for all the databases on the server. The information provided shows only the total size of the database and any options in effect:

Listing 5.3 Multiple-Database Sample Output from sp_helpdb

/*----------------------------
sp_helpdb
----------------------------*/
name         db_size   owner dbid created     status
----------------------------------------------------
master      17.00 MB   sa    1    Jun  7 1995 trunc. log on chkpt.
model        1.00 MB   sa    3    Jun  7 1995 no options set
msdb         4.00 MB   sa    5    Nov 23 1995 trunc. log on chkpt.
NewDatabase  5.00 MB   sa    6    Jan  7 1996 no options set
pubs         3.00 MB   sa    7    Jun  7 1995 select into/bulkcopy, trunc.
     log on chkpt., dbo use only
tempdb       7.00 MB   sa    2    Jan  7 1996 select into/bulkcopy, single
     user

In Listing 5.4, a database is supplied, and more detailed information, including device fragment information, is returned from SQL Server:

Listing 5.4 Sample Output from sp_helpdb for a Specific Database

/*----------------------------
sp_helpdb NewDatabase
----------------------------*/
name         db_size   owner dbid created     status
----------------------------------------------------
NewDatabase  5.00 MB   sa    6    Jan  7 1996 no options set

device_fragments               size          usage
------------------------------ ------------ --------------------
NewDevice                            2.00 MB log only
NewDevice                            3.00 MB data only

device                         segment
------------------------------ ------------------------------
master                         default
master                         logsegment
master                         system.


NOTE: The segment information is displayed only if you're executing sp_helpdb from the database that you're inspecting.

Increasing the Size of the Database and Transaction Log

SQL Server lets you resize the database if its space is consumed by user data. In the same way, transaction logs can be increased in size if they get full too quickly and require excessive dumping.

Using SQL Enterprise Manager To increase the size of a database or transaction log by using SQL Enterprise Manager, follow these steps:

1. Run SQL Enterprise Manager; select the required server, and from the Manage menu choose Databases. Double-click the database that needs to be adjusted. The Edit Database dialog box appears as shown in Figure 5.20. You can also right-click the database you want to work with and select Edit... from the resulting menu.

FIG. 5.20
SQL Enterprise Manager's Edit Database dialog box enables you to configure options that apply to the currently selected database.

2. Click the Expand button to display the Expand Database dialog box (see Figure 5.21).

FIG. 5.21
At the bottom of the dialog box is a graph showing all the devices on the server and the amount of free space in each.

3. If you're expanding the database, select the device that you want to expand the database into from the Data Device drop-down list box. In the Size (MB) text box next to the drop-down box, enter the number of megabytes that are required.

4. If you're expanding the log, select the device that you want to expand the log into from the Log Device drop-down list box. In the Size (MB) text box next to the drop-down box, enter the number of megabytes that are required.


TIP: To add a new device for the database or log to grow into, select <new> from either drop-down list box, and the New Database Device dialog box is displayed.
5. Click Expand Now to expand the database/log and return to the Edit Database dialog box. The Edit Database dialog box is updated and reflects the addition of the new log or database devices in the Log Space Available or Database Space Available fields.


NOTE: SQL Enterprise Manager also provides an option to shrink a database. This is done by internally calling DBCC SHRINKDB. Note that the SQL Server must be started in single-user mode to perform these operations.
If it's not started in single-user mode, there will be users, system handles such as the CHECKPOINT and MIRROR handlers, that can't be removed from the database and, therefore, prevent the operation from succeeding.

To start SQL Server in single-user mode use the -m keyword and start SQL Server from the command-line.


Using the ALTER DATABASE Command to Extend a Database Transact-SQL provides the ALTER DATABASE command to allow a database to be extended. Transaction logs are also extended by using the ALTER DATABASE command. After the database is extended, the system-stored procedure sp_logdevice is used to specify that the extension to the database is actually for transaction log use. The syntax for ALTER DATABASE is as follows:

ALTER DATABASE database_name
      [ON {DEFAULT | database_device} [= size]
      [, database_device [= size]]...]
      [FOR LOAD]

The options for the Transact-SQL command ALTER DATABASE are as listed in Table 5.4.

Table 5.4 ALTER DATABASE Parameters

Parameter Description
database_name This is the name of the database that is being extended.
database_device This is one or more database devices and the size, in megabytes, to be allocated to the database.
ON DEFAULT If DEFAULT is specified, SQL Server allocates the requested space to the first free database device or devices that have enough space to meet the request.
FOR LOAD If FOR LOAD is specified, SQL Server stops any user processes from connecting to the database until a LOAD has completed. FOR LOAD can be specified only if the database was initially created with the FOR LOAD option.

In this example, NewDatabase is extended by 5M on the NewDevice database device:

ALTER DATABASE NewDatabase
     On NewDevice = 5

In the following example, NewDatabase is extended by a further 5M, and the logs are placed on the extended portion:

ALTER DATABASE NewDatabase
     On NewDevice = 5
Go
sp_logdevice NewDatabase, NewDevice

Dropping Databases

Dropping a database frees up any space that it consumed on any database devices and removes any objects that it contained.

Dropping a database isn't something you can easily undo, so be careful. If you do find you need to recover a database that has been dropped, you will be forced to do one of two things. You'll either need to do a restore of the database and its associated transaction logs, or you can sneak around SQL Server.

If you have yet to remove the physical file on your server, you'll still have the .DAT file associated with the device on your hard drive. You can re-use this file by remounting it using the sp_dbinstall command:

sp_dbinstall database_name, device_name, disk_file_name.dat, size, type [, location]

where the type is either "system" or "data" and the location is where you want the new device to be located.


CAUTION: Except in cases where you're absolutely certain that you don't need the database information any more, you should always first DUMP the database to disk and back up the resulting file prior to dropping the database.

Having the resulting database dump file available will certainly save you headaches in those cases where a user needs "just one more thing before you delete the database."



NOTE: Be sure to remember the difference between dropping a database and dumping one. Dropping a database drops all the tables and indexes and removes the logical area on the database device reserved for the database. Dumping a database creates a backup of the database's data onto a disk, diskette, or other type of media.

User accounts that had their default database as the database that is being dropped will have their default database changed to master. Only the SA or the database owner (dbo) can drop a database. The master, model, and tempdb databases can't be dropped by any user account. Also, any databases that are participating in replication or have active users can't be dropped until the replication is suspended or until the users have disconnected from the database.

Using SQL Enterprise Manager to Drop a Database To use SQL Enterprise Manager to drop a database, follow these steps:

1. Run SQL Enterprise Manager; select the server that the database resides on, and from the Manage, menu choose Databases. The Manage Databases window is displayed (see Figure 5.22). You can also right-click the database and select the Delete option from the menu.

FIG. 5.22
SQL Enterprise Manager's Manage Databases window lists all the active databases on the currently managed server.

2. Click the database that you want to drop.

3. Click the Delete Database toolbar button. A message box is displayed, asking for confirmation to drop the database as shown in Figure 5.23.

FIG. 5.23
This message box is the last chance that you have to abort a database drop.

4. Click Yes, and the database is dropped.

Using the DROP DATABASE Command to Drop a Database The syntax for the DROP DATABASE command in Transact-SQL is as follows:

DROP DATABASE database_name, [database_name...]

database_name is the name of the database to be dropped.

Databases in all normal states, including Active, Damaged, Suspect, Off-line, or Not recovered, can be dropped by using the DROP DATABASE command. A database that is still in Recovery status must be dropped by using the system-stored procedure sp_dbremove. If you have a database in this mode, it is indicated by an error message when you use the Drop command.


TIP: If you have a database that you cannot access in order to drop it, you can use the DBCC command DBREPAIR to remove the database:

dbcc dbrepair(database_name, dropdb)

This will correct the problem with the database and immediately drop the database. You don't need this command as much with SQL 6.x because the DUMP command drops the database in most cases, but if you're using versions of SQL Server prior to 6.x, you need to use this command to drop damaged databases, or databases that otherwise fail with the standard DROP command.


You can also use a stored procedure approach to dropping databases. The sp_dbremove command enables you to indicate which database to remove, and you can also ask that the device be removed.

Sp_dbremove database, [dropdev]

If you specify the DROPDEV option, the database's device is also removed, assuming of course that no other active databases or logs reside on the device. One other benefit of the stored procedure approach is that sp_dbremove removes databases in all states, whether they be in recovery mode, suspect, or "normal" at the time the command is issued.

Defining Segments

Segments are logical groups of disk devices, or portions of devices, on which database objects are placed. Database segments are created in a database, which in turn can be placed on a particular database disk device. The advantage of segments is that individual objects, such as tables and indexes, can then be explicitly placed in a segment, allowing for greater performance.

Typically, two segments would be created for a database that spans two disk devices. These segments would then be used in such a way that all the tables with non-clustered indexes would be created on one segment, and the non-clustered indexes for these tables would be created on the other segment.

Non-clustered indexes are indexes that are binary search trees of the data. This has a tremendous performance advantage because the reading and writing of the data and index pages can execute concurrently on two physical devices rather than run serially.


See Chapter 11, "Managing and Using Indexes and Keys," for more information about creating indexes.


NOTE: SQL Enterprise Manager doesn't have any user interface to work with data segments, and so all the work has to be done with ISQL.

Segments are also good for allowing the TEXT and IMAGE data, associated with a database table, to be stored on a separate physical device, and for splitting large tables across separate physical devices.


TIP: Better performance and far easier management are provided by splitting the database devices themselves across multiple disks at the operating system or hardware level by the use of Raid, instead of the use of segments.

Using Default Segments

When a new database is created, three default segments are created for it. The SYSTEM segment houses all the system tables and their indexes. The LOGSEGMENT stores the transaction log for the database. The DEFAULT segment stores any user-created objects, unless they're explicitly moved or placed on a different segment.

Adding Segments

You add segments by using the system-stored procedure sp_addsegment. The syntax for sp_addsegment is as follows:

sp_addsegment segment_name, database_device

The options for the system-stored procedure sp_addsegment are as follows:

The following example creates a segment on the database device NewDevice:

sp_addsegment seg_newdevice1, NewDevice

Extending Segments

You can extend segments by using the system-stored procedure sp_extendsegment. When a segment is extended, it simply allocates more database disk devices to it for the use of any objects placed in the segment. The syntax for sp_extendsegment is as follows:

sp_extendsegment segment_name, database_device

The options for the system-stored procedure sp_addsegment are as follows:

The following example extends the segment seg_newdevice1 on to the database device NewDevice2:

sp_extendsegment seg_newdevice1, NewDevice2


TIP: If you want to extend the DEFAULT segment, you must enclose default in quotation marks because default is a reserved word. For example,

sp_extendsegment `default', NewDevice


Using Segments

After segments are created on a database, you can place an object on those segments in two ways. Both CREATE TABLE and CREATE INDEX have an ON Segment option that lets a table or index be created on a particular segment.


See Chapter 6, "Creating Database Tables and Using Datatypes," for more information about creating tables on segments.

SQL Server also provides a system-stored procedure, sp_placeobject, which directs the server to place any new data for a table onto a new segment. You need to use this for tables that you want to partially load on one segment and then switch over to another segment. Executing sp_placeobject doesn't move any previously existing data allocations to the new segment. It only causes future allocations to occur on the requested new segment.

The syntax for sp_placeobject is as follows:

sp_placeobject segment_name, object_name

The options for the system-stored procedure sp_placeobject are as follows:

The following example makes all further data allocations for the table authors on the new segment seg_data2:

sp_placeobject seg_data2, authors

The following example moves the logo column from the pub_info table to a new segment for image data:

sp_placeobject seg_ImageData, `pub_info.logo'

Dropping Segments

Dropping segments removes them from the database devices on which they reside. A segment can't be dropped if it contains any database objects. Those objects need to be dropped first. For information about dropping tables see Chapter 6, "Creating Database Tables and Using Datatypes."


See Chapter 11, "Managing and Using Indexes and Keys," for information about dropping indexes.

Segments are dropped by executing the system-stored procedure sp_dropsegment. The syntax for sp_dropsegment is as follows:

sp_dropsegment segment_name[, device_name]

Using the Tempdb Database

Tempdb is a special database that is used by SQL Server to handle any dynamic SQL requests from users. Tempdb is a workspace for SQL Server to use when it needs a temporary place for calculations, aggregations, and sorting operations. The sorts of things that Tempdb is used for include the following:

One key advantage to using Tempdb is that its activity isn't logged. This means that any data manipulation done on Tempdb temporary tables is much faster than on normal disk devices.

This is a double-edged sword, however, because if SQL Server is brought down at any time, all the information in Tempdb is lost. Take care not to rely on Tempdb without having application code that can restart itself in the event of a server shutdown.

Adjusting Tempdb's Size

The default size of Tempdb, when SQL Server is installed, is 2M. For most production environments, this size is insufficient. If the environment is highly active with large queries or lots of requests for queries, it's recommended that Tempdb be extended.

Tempdb is created in the master device by default and can be expanded in that device, or it can be moved so that it spans multiple database devices. Changing the size of Tempdb is accomplished in the same way as resizing any other database. To increase the size of Tempdb, follow the same steps outlined earlier in the section entitled "Increasing the Size of the Database and Transaction Log."

Placing Tempdb in RAM

Tempdb is critical to server performance because SQL Server uses it for just about every query/operation that occurs. You have the unique option of being able to place Tempdb in RAM. Placing Tempdb in RAM dramatically decreases the amount of time required for sorting.

Before placing Tempdb in RAM, you must consider how Tempdb uses memory now available in the server. All RAM, allocated to SQL Server, is paged based on a least recently used, LRU, algorithm. What this means is that quite often, data in Tempdb is in memory anyway as part of a regular page.

You shouldn't place Tempdb in RAM unless sufficient RAM is available to the server to handle its normal operations. It's unlikely that you'll perceive any benefit from Tempdb being in RAM unless the server has more than 64M of memory due to insufficient resources on the server. With 128M or more of RAM, it's quite likely that you'll achieve performance gains by having Tempdb in RAM.

There are no data-integrity considerations with Tempdb in RAM, because none of the operations that occur on Tempdb are logged. What this means is that Tempdb is rebuilt every time the server is restarted, and any data that was in it, whether or not Tempdb was in RAM or on a disk device, is lost.

To place Tempdb in RAM, you must use the system-stored procedure sp_configure and restart the server.


See Chapter 16, "Understanding Server, Database, and Design Query Options," for more information on using sp_configure and other configurable options of SQL Server.

Using Removable Media for Databases

A new feature introduced in SQL Server 6.0 enables databases to be placed on removable media, such as CD-ROMs and magneto-optical, MO, drives. This feature permits the mass distribution of databases in a more friendly form than a backup tape, which the client must restore before using. Also, a CD-ROM-based database is truly read-only and is a great way of securing data integrity.


NOTE: SQL Enterprise Manager doesn't have any user interface to allow the creation of removable media, so all the work has to be done with ISQL. It's possible to create a device and database on a removable drive attached to the Windows NT system. This won't, however, be the same as a removable-media-capable database and shouldn't be done.

Creating a Removable Database

A removable database has to be created in such a way that three devices are used: one for the system catalog, one for the user data, and one for the transaction log. Only the SA can create removable databases.

SQL Server has a special system-stored procedure, sp_create_removable, that creates a database and devices that are acceptable for use when creating a database for removable media purposes. It's important that you use this stored procedure because it guarantees that the database created is usable on a removable device. The syntax for sp_create_removable is as follows:

sp_create_removable database_name, sysdevice_name_,
      `sysdevice_physical', sysdevice_size,
      logdevice_name, `logdevice_physical', logdevice_size,
      datadevice1_name, `datadevice1_physical',
      datadevice1_size [... , datadevice16_name,
      `datadevice16_physical', datadevice16_size]

The options for the system-stored procedure sp_create_removable are are listed in Table 5.5.

Table 5.5 Sp_Create Removable Parameters

Parameter Description
database_name The name of the database to be created.
sysdevice_name The logical name to use for the device that will hold the system catalog tables.
sysdevice_physical The physical device path and file name that will be used to store the data for the system catalog device.
Syssize The size, in megabytes, of the device.
logdevice_name The logical name to use for the device that will hold the transaction log.
logdevice_physical The physical device path and file name that will be used to store the data for the log device.
Logsize The size, in megabytes, of the device.
datadeviceN_name The logical name to use for the device that will hold the user data. There can be up to 16 data devices.
datadeviceN_physical The physical device path and file name that will be used to store the data for datadevice.
Datasize The size, in megabytes, of the device.

The following example creates a 1M data, log, and system catalog database and appropriate devices called MyRemovable:

/*----------------------------
sp_create_removable MyRemovable, MySys, `C:\SQL60\DATA\REMOVABLE\MYSYS.DAT', 1,
      MyLog, `C:\SQL60\DATA\REMOVABLE\MYLOG.DAT', 1,
      MyData, `C:\SQL60\DATA\REMOVABLE\MYDATA.DAT', 1
----------------------------*/
CREATE DATABASE: allocating 512 pages on disk `MySys'
Extending database by 512 pages on disk MyData
DBCC execution completed. If DBCC printed error messages, see your System      Administrator.
Extending database by 512 pages on disk MyLog
DBCC execution completed. If DBCC printed error messages, see your System 
    Administrator.
DBCC execution completed. If DBCC printed error messages, see your System
    Administrator.

Using the Removable Database

While the database is in development, the following rules should be observed to ensure that the database is usable on removable media:

After database development is completed and you want to test that the database is acceptable for removable media, you should run the system-stored procedure sp_certify_removable. This procedure checks all the conditions required for a removable database and can automatically fix anything that it finds unacceptable. The syntax for sp_certify_removable is as follows:

sp_certify_removable database_name[, AUTO]

The options for the system-stored procedure sp_certify_removable are as follows:


CAUTION: If sp_certify_removable reports that it corrected anything when the AUTO flag was specified, it's highly recommended that you retest your application program to make sure that it's still compatible with the database. If no testing occurs, SQL Server could have rendered your application useless without you knowing about it.

As part of its execution, sp_certify_removable also takes all the devices that have been created for use in the removable database off-line and makes them available for copying to the actual physical device. The output from sp_certify_removable is very important because it indicates which database characteristics to use when installing the removable database into a SQL Server.

Listing 5.5 shows the MyRemovable database being certified and brought off-line:

Listing 5.5 Sample Output from Setting Up a MyRemovable Device

/*----------------------------
sp_certify_removable MyRemovable, AUTO
----------------------------*/
DBCC execution completed. If DBCC printed error messages, see your System 
    Administrator.
DBCC execution completed. If DBCC printed error messages, see your System 
    Administrator.
DBCC execution completed. If DBCC printed error messages, see your System 
    Administrator.
File: `C:\SQL60\DATA\REMOVABLE\MYLOG.DAT' closed.
Device dropped.
The following devices are ready for removal.  Please note this info. for 
    use when installing on a remote system:

Device name  Device type  Sequence    Device frag. used by database 
Physical file name
--------------------------------------------------------------------
--------------------
MySys        System + Log 1           1 MB                         
C:\SQL60\DATA\REMOVABLE\MYSYS.DAT
MyData       Data         2           1 MB                         
C:\SQL60\DATA\REMOVABLE\MYDATA.DAT

Database is now offline
Closing device `MyData' and marking it `deferred'.
Device option set.
Closing device `MySys' and marking it `deferred'.
Device option set.

Installing the Removable Database

After a distribution media/device is made, you must install it on the target SQL Server. Installation is achieved by using the information supplied in the output from sp_certify_removable. This information should be distributed with each CD-ROM or device on which the removable media is placed.

The system-stored procedure sp_dbinstall is used to install a database from removable media. The syntax for sp_dbinstall is as follows:

sp_dbinstall database_name, device_name, `physical_device',
      size, `device_type'[, `location']

The options for the system-stored procedure sp_dbinstall are listed in Table 5.6.

Table 5.6 sp_dbinstall Parameters

database_name The name of the database to be installed. This can be any name that is valid for a database and doesn't need to be the same as the original device name.
device_name The name of the database device that is to be installed.
physical_device The full path information for the device on the removable media.
Size The size of the device being created.
device_type The type of device being created on the target SQL Server. Valid types are `SYSTEM' and `DATA'. The `SYSTEM' device must be created first. If more than one `DATA' device exists, each should be installed by using sp_dbinstall.
Location The location to use on the local drive for the device being installed. The system device must be installed locally, but the data devices can be left on the removable media, if necessary.

The following example installs the system device created earlier by the system-stored procedure sp_certify_removable in the section "Using the Removable Database" from a CD-ROM in drive E:

sp_dbinstall MyRemovable, MySys, `e:\MySys.dat', 1,'SYSTEM', `c:\sql60\data\invsys.dat'

After the system device is installed, the data device is installed, but left on the CD-ROM:

sp_dbinstall MyRemovable, MyData, `e:\MyData.dat', 1,'DATA'

After all the data devices are installed, you need to place the database on-line so that users can access it. This is achieved by using sp_dboption. The following example shows you how to bring MyRemovable online:

sp_dboption MyRemovable, OFFLINE, FALSE

Uninstalling a Removable Media Database

If a removable media database is no longer required, you can remove it by using the system-stored procedure sp_dbremove. This procedure removes any entries from the system catalog relating to the database that was installed. The syntax for sp_dbremove is as follows:

sp_dbremove database_name[, dropdev]

The options for the system-stored procedure sp_dbremove are as follows:

sp_dbremove doesn't remove the physical data files that were used to store the database devices. This needs to be done manually. See the Caution in the previous section for more information about removing device files manually.

From Here...

In this chapter, you learned all about devices, databases, and segments. This chapter provided you with information on how to create all the fundamentals for your server. From here you should consider looking at the following chapters to further develop your SQL Server and application programming knowledge:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.