This chapter discusses SQL Server devices. You learn the difference between a data device and a dump device and how to create, delete, use, and maintain SQL Server devices.
A very general definition of a Microsoft SQL Server device is a storage area from which SQL Server can read and write. Devices are used for database storage, transaction log storage, database backups, and database recovery. Figure 8.1 shows several examples of SQL Server devices.
Figure 8.1.
Examples of SQL Server devices.
Not only can a device be represented by different physical objects such as a floppy disk or hard drive, but devices come in two types: database devices and dump (backup) devices.
A database device is a disk file used to store databases and transaction
logs. Creating a database device requires preallocating storage space for later use.
A database device can be larger or smaller than the database or transaction log you
allocate to the device because databases and transaction logs can span multiple devices.
When SQL Server is first installed, three database devices are created in the \MSSQL\DATA
directory: MASTER.DAT, MSDB.DAT, and MSDBLOG.DAT. The
master Device MASTER.DAT is the master database device
and is the most important SQL Server device.
The master device stores the master, model, tempdb,
and pubs databases, described in detail in Chapter 9,
"Managing Databases." It is important to understand that the master
database, stored on the master device, contains all the SQL Server information
required to manage and maintain the server's databases, users, and devices (basically
all the information required to maintain and run SQL Server). If the master
device becomes corrupted, all databases are unusable until the master device
can be restored.
CAUTION: Never use the master device for any database allocation except for the default databases installed on the master device during SQL Server setup. Even though SQL Server allows you to allocate space on the master device for other databases, don't do it! The master device should be used only for its intended purpose: to store the master, model, and tempdb databases! Following this advice can save you many headaches if you have to recover the master device.
The minimum master device size required to install SQL Server 6.x is 25M. The Scheduler Database and Log MSDB.DAT is the database device created at installation time to store the msdb database used by the SQL Executive for scheduling information. The device MSDBLOG is used to store the transaction log for the msdb database. The default device size used during setup is 2M for MSDB.DAT and 2M for MSBDBLOG.DAT.
NOTE: MSDB.DAT and MSDBLOG.DAT were added in version 6.0 and do not exist in previous 4.2x versions. Also, the SQL Server documentation incorrectly states that the size of MSDBLOG.DAT is 1M; installation allocates 2M to MSDBLOG.DAT.
Dump devices are used to back up and restore databases and transaction logs.
NOTE: The SQL Server 6.5 SQL Enterprise Manager now refers to dump devices as backup devices (previous versions of SQL Server always referred to them as dump devices). I welcome the change because a dump device is a backup device--but then again, the command to perform a backup is DUMP...make a mental note that the terms dump device and backup device are equivalent.
When a dump (backup) device is allocated, unlike a database device, no storage space is preallocated. Dump devices can be tapes, floppy disks, disk files, or named pipes.
NOTE: Named-pipe dump devices are new for version 6.x. A named-pipe dump device is not created like other dump devices; it is a parameter used in the DUMP and LOAD commands. For more information, see Chapter 13, "Distributed Transaction Coordinator," and Chapter 14, "Backups."
Three dump devices are created during SQL Server installation: DISKDUMP, DISKETTEDUMPA, and DISKETTEDUMPB. DISKETTEDUMPA and DISKETTEDUMPB are dump devices for floppy drives A and B for the machine on which SQL Server is running. The floppy drive backup devices are added strictly for backward compatibility and require you to use a special command-line utility called console.
NOTE: I received a few e-mail messages after the first edition of this book was published, asking where the backup devices DISKETTEDUMPA and DISKETTEDUMPB were because they do not show up on the SQL Enterprise Manager. You can verify that they really do exist by issuing the command sp_helpdevice from the SQL Query tool. As stated earlier, the two devices were included only for backward compatibility. The Microsoft documentation suggests that if you want to back up a database to floppy, you should back it up to another device (such as a hard drive) and then copy it to the floppy.
The DISKDUMP device is a special backup device referred to as the NULL dump device because the backup device represents no physical media, only thin air.
TIP: DISKDUMP (the NULL dump device) can be very helpful during database application development. Use DISKDUMP to clear out databases and transaction logs when developers are filling the databases and logs with test data that does not need to be backed up.
Now that you understand the different types of devices, you can create a database device.
NOTE: If you are using SQL Server version 6.0 and not version 6.5, the following steps for creating a device are essentially the same except that the Manage Database Devices dialog box differs slightly between versions and that scheduling device creation is not available in ver-sion 6.0.
From the SQL Server Manager, perform the following steps:
Figure 8.2.
The Manage Database Devices dialog box.
Figure 8.3 specifies a 125M device named test_data to be created in the
default directory \MSSQL\Data on drive C. So what happens when the device
test_data is created with the SQL Server Manager? The SQL Server Manager
performs a command called DISK INIT. The DISK INIT command creates
a 125M initialized file, test_dat.DAT, located in C:\MSSQL\DATA,
and adds a new entry in the system table, sysdevices, located in
the master database.
Figure 8.3
A 125M Device named test_data.
NOTE: Device creation in SQL Server version 6.x happens much faster than in earlier 4.2x versions. Version 6.x does not zero-out each page of the device--it takes advantage of the fact that a DOS FAT file or NTFS file is already initialized with zeroes. Because previous 4.2x versions of SQL Server zeroed out each page, creating a large device with those older versions took longer.
The DISK INIT command can also be issued from an ISQL prompt; the command has the following format:
DISK INIT
NAME = logical_name,
PHYSNAME = physical_name,
VDEVNO = virtual_device_number,
SIZE = number_of_2K_blocks
[, VSTART = virtual_address]
NAME is the logical name of the device; it is the same as the name field shown in Figure 8.3.
PHYSNAME is the physical location and filename of the database device (that is, the path and filename).
VDEVNO is a number between 1 and 255, assigned to the new device. The number 0 is reserved for the master device. VDEVNO uniquely identifies a device; once the number is used for a device, it cannot be used again until the device is dropped.
SIZE is the size of the device to create. When using the DISK INIT command from ISQL/w, the size is in 2K pages (2048) bytes; the SQL Server Enterprise Manager uses megabytes. 1M is equal to 512 2K pages.
VSTART is an optional parameter that represents the starting offset (in
2K blocks) in the device file. The value is 0 and should be modified only if you
are instructed to do so.
Figure 8.4
Database Device Location.
To create a backup device, select the server to which you want to add the backup device from the SQL Server Manager and then perform the following steps:
Figure 8.5.
The New Backup Device dialog box.
Adding a backup device with SQL Server Manager is the same as executing the stored procedure sp_addumpdevice, which has the following parameters:
sp_addumpdevice Type, `Logical_Name',
`Physical_Name'
[,@devstatus = {noskip | skip}]
The Type parameter specifies the type of device and can be `disk', `diskette', or `tape'. Logical Name is the logical name of the dump device. Physical Name is the physical path and name of the dump device.
Set @devstatus to skip or noskip. These parameters determine whether SQL Server will try to read ANSI labels before performing a backup.
When a dump device is added, SQL Server makes an entry in the sysdevices table (the same table used for database devices).
NOTE: In older versions of SQL Server (versions before 4.21), sp_addumpdevice included the parameters cntrltype and media_capacity. Because these parameters were never used in NT versions of SQL Server, they are not included in version 6.x.
CAUTION: Always back up the master database after adding any devices. If the master database should become corrupted or damaged, you will have a valid backup of the database with the newly created devices.
Figure 8.6 shows the system table sysdevices after the devices test_data and test_dump have been added.
Figure 8.6.
The system table, sysdevices.
Sybase Beware!
Microsoft has taken care of the most common problem (besides syntax problems) that I have encountered during device creation: running out of configured devices. SQL Server 4.2x had a configuration parameter called devices, which is the maximum number of devices that can be configured. In SQL Server 4.2x, the initial setting for devices was 10, which means that you can have up to 10 devices, device numbers (VDEVNO) 1 through 9. But what about device number 10? Device numbers are zero-based (remember that the device number 0 is reserved for the master device). When the device limit was exceeded, you could not add any more devices until you reconfigured the SQL Server using the stored procedure, sp_configure. The zero-based numbering and the error message(s) were very confusing to beginning administrators, who often called to say that SQL Server would not let them add another device. With version 6.x, this is no longer a problem. In version 6.x, the configuration parameter devices no longer exists. You now have access to all possible devices, 1 through 255.
You can view device information in several ways. To see the current devices installed on a server, use the Server Manager window to select a server and then click the Database Devices folder. Backup devices can be viewed in the same manner by clicking the Backup Devices folder.
Figure 8.7 shows a view of database devices and backup devices using the SQL Enterprise Manager. To see more detailed information, click the Manage Database Devices button on the Enterprise Manager toolbar.
Figure 8.7.
The SQL Enterprise Manager, showing database and backup devices.
Figure 8.8 shows the detailed database window. Each bar graph represents a database
device. The graph shows the amount of space used on each device and the space available.
Figure 8.8.
The Manage Database Devices dialog box.
TIP: In SQL Server 6.5, the graphical display of the devices can be changed using the Graph Properties button and the Bar Graph Options button. The Graph Properties button allows you to change the graph labels, color, legend display, and types of graphs. You can use the Bar Graph Options button to make the graph vertical or horizontal and selectable or stretched.
The stored procedure, sp_helpdevice, can be used to display device information from the ISQL command line and has the following syntax:
sp_helpdevice [device_name]
The device name parameter is the logical name of the device for which you want information. If the parameter is omitted, all the devices in the sysdevices table are listed.
Now that you can add and view devices you have created, how do you get rid of them? Removing a database or a backup device is called dropping a device in SQL Server terms. When a device is dropped, the row containing the dropped device is removed from the system table, sysdevices. The logical name and device number (VDEVNO) can then be reused. Before you can reuse the physical name of the device, however, you must first delete the file. When a database device is dropped, the actual physical file created for the device is not removed. To remove the file and regain the space, you must delete the file using the NT File Manager or the DOS DEL command to delete the file. Once the file is deleted, you can reuse the physical name.
CAUTION: For versions of SQL Server before version 6.x, you must shut down SQL Server after a device is dropped before the physical file can be removed.
Database and dump devices can be dropped with the stored procedure sp_dropdevice. The syntax for sp_dropdevice is as follows:
sp_dropdevice device name
The device name parameter is the logical name of the device you want to drop.
To drop a database device using the SQL Enterprise Manager, access the Manage Database Devices dialog box. Select the device by clicking it (make sure that the graph mode is selectable) and then click the Delete Device button (refer back to Figure 8.2). Before the device is dropped, a confirmation box appears to prevent accidental deletions.
CAUTION: When a device is dropped, any databases or transaction logs using that device are also dropped. SQL Server displays a warning message that lists any database(s) and transaction log(s) currently using the device. If you decide to continue the operation, first the databases or transaction logs on the device are dropped and then the device itself is dropped.
To delete a backup device using the SQL Enterprise Manager, select the server on which the device resides. Select the Backup Devices folder and choose the backup device to delete. Right-click the backup device to display a shortcut menu. Choose Delete from the shortcut menu. The Delete Backup Device dialog box appears (see Figure 8.9). To delete the backup device, click the Delete button. A confirmation dialog box appears to prevent accidental deletions.
Figure 8.9.
The Delete Backup Device dialog box.
Once a database device is created, the size of the device cannot be decreased, but it can be increased. To increase the database device size with the SQL Enterprise Manager, open the Database Device folder and double-click the database device. From the Manage Database Devices dialog box, double-click the database device to expand the bar graph. The Edit Database Device dialog box appears (see Fig- ure 8.10).
Figure 8.10.
The Edit Database Device dialog box.
To increase the size of the device, change the Size parameter to the new device size;
click Change Now to immediately alter the size of the database device. Click Schedule
to change the database device size at a latter time.
NOTE: The drive or drives on which the device resides must have available free space greater than or equal to the amount by which you want to increase the device.
The corresponding command to increase disk space is the DISK RESIZE command, which has the following syntax:
DISK RESIZE
NAME = 'logical device name',
SIZE = device final size
NAME is the logical device name of the device to expand. SIZE is the size of the database device after the device is expanded. The size is specified in 2K pages. Remember that 512 equals 1M.
SQL Server maintains a pool of database devices that are used if a database is created without specifying a device. Devices in the pool are referred to as default devices. The default device pool can consist of one or many database devices. SQL Server allocates the default space one device at a time, going in alphabetical order. When a default device runs out of space, SQL Server uses the next default device available. You can designate a database device as a default device during device creation with the SQL Enterprise Manager: check the Default Device checkbox in the Create Database Device dialog box. You can designate a database device as a default device after the device has been created: check the Default Device checkbox in the Edit Database Device dialog box. The database device can be removed from the default database pool by deselecting the Default Device checkbox in either dialog box.
CAUTION: At installation, the SQL Server master device is placed in the default disk pool. Remove the master device from the pool immediately so that a database or transaction log is not accidentally placed on the master device.
The stored procedure used to add and remove devices from the default device pool is sp_diskdefault. Following is the syntax for sp_diskdefault:
sp_diskdefault device name, defaulton | defaultoff
The device name parameter is the logical device name for the device; defaulton and defaultoff are the flags used to add or remove the device from the default device pool. The defaulton flag adds the device; defaultoff removes the device.
SQL Server disk configurations were briefly covered in an earlier chapter; this section expands on some of the points mentioned earlier and shows how they relate to SQL Server devices and performance. In SQL Server (or any other database server), one of the most likely bottlenecks is the disk I/O from clients reading and writing from different tables or different databases simultaneously.
Suppose that you have a PC configured as a server; it has a fast processor and
a large amount of memory, but you bought a single 2 gigabyte hard drive with a single
disk controller to store all your database information. Because you have only one
disk drive, any devices you create physically reside on the single hard drive. What
happens when users start inserting and retrieving data simultaneously? SQL Server
has more than enough memory and the processor is fast enough to handle the requests,
but what about the single disk drive and disk controller? A bottleneck will quickly
form as I/O requests queue up to the single disk. An old SQL Server trick, dating
back to the days of Sybase, has been to use a smart disk controller card or disk
array; rather than a single 2 gigabyte hard drive, use four 512M hard drives.
Devices can then be created on different physical hard drives, enabling you to spread
databases and transaction logs across different physical devices. Although this arrangement
is a better solution than a single hard drive, it still has some deficiencies. Databases
and transaction logs can be placed on different physical devices, improving transaction
processing. Databases can be spread over multiple SQL Server devices (thus different
physical devices), but the hot data everyone is after may be on a single drive causing
disk I/O bottlenecks similar to those on a single large drive. Smart SQL Server DBAs
take advantage of segments (described in Chapter 9, "Managing
Databases"). The NT operating system and new advanced hardware systems have
created better solutions: hardware or software disk striping.
Figure 8.11 is a conceptual diagram of disk striping for a drive labeled J. Drive J looks like a single physical drive to the SQL Server DBA who is creating devices. Logically, a striped drive is a single drive, but physically, the logical drive spans many different disk drives. A striped disk is made up of a special file system called a striped set. All the disks in the disk array that make up the logical drive are part of the striped set. Data on each of the drives is divided into equal blocks and is spread over all the drives. By spreading the file system over several disk drives, disk I/O performance is improved because the disk I/O is spread over multiple drives. The balancing of the I/O is transparent to the DBA, who no longer has to worry about spreading out file I/O. Disk striping is also referred to as RAID 0 (Redundant Array of Inexpensive Disks). RAID level 0 is the fastest RAID configuration. The level of fault tolerance is measured in levels 0 through 5, with 0 providing no fault tolerance. If a single disk fails in a RAID 0 system, none of the data in the stripe set can be accessed. Windows NT provides software level disk striping. Disk striping can also be handled by special hardware disk arrays.
Figure 8.11.
Disk striping.
NOTE: Hardware-based disk striping (RAID configurations) outperform Windows NT software disk striping. NT's implementation is done through software and requires system processor resources. The disadvantage of hardware striping solutions is cost. RAID systems can be quite expensive, depending on the level of fault tolerance you select.
A RAID 0 system has no fault tolerance; the entire file system can be rendered useless if a single drive fails. RAID 1 is also known as disk mirroring. In a RAID 1 configuration, data written to a primary disk is also written to a mirrored disk. RAID 2 uses disk striping along with error correction. RAID 3 and RAID 4 also use disk striping and error correction and vary in their degrees of effectiveness and disk space requirements. A RAID 5 system has the maximum fault tolerance: a single disk can fail and the system continues to function. A backup drive can be placed in the disk array so that the lost device can be re-created on the new drive by the RAID system. RAID 5 technology can be implemented using Windows NT disk striping with parity or as a hardware-based solution.
SQL Server provides a method of redundancy to protect against a device failure called device mirroring. When a SQL Server device is mirrored, a duplicate copy of the device is maintained. If either device becomes corrupted, the uncorrupted device takes over SQL Server operations to provide uninterrupted processing.
Figure 8.12 shows a mirrored device. NT SQL Server mirrors a drive using serial writes: Information is written to the primary device first. When the I/O operation is complete, the information is then written to the secondary device.
NOTE: Non-Windows NT SQL Servers can perform nonserial writes to mirror devices; that is, they can write to both devices simultaneously or they can perform serial writes. Mirroring with Windows NT SQL Server always results in serial writes.
Figure 8.12.
A mirrored device.
If an error occurs when writing to either of the mirrored devices, the bad device
becomes unmirrored and the uncorrupted device takes over as the primary device, protecting
your server from downtime caused by a lost device.
NOTE: Remember that devices are mirrored--the databases are not mirrored. If you want to mirror a database, you must mirror all the devices that make up the database.
To mirror a device using the SQL Enterprise Manager, select the server and device you want to mirror and perform the following steps:
Figure 8.13.
The Mirror Database Device dialog box.
TIP: Mirroring a device requires SQL Server to create the mirror device, which uses the same name as the device being mirrored but with a MIR file extension. Once the mirror device is created, used pages from the primary device are copied to the new mirror device. Large databases can take a while to mirror.
The SQL Server command to mirror a device is the DISK MIRROR command, which has the following syntax:
DISK MIRROR
NAME='logical device name',
MIRROR = `mirror device physical name',
[,WRITES=SERIAL | NOSERIAL]
NAME is the SQL Server logical device name of the device to be mirrored.
MIRROR is the location and filename of the mirrored device. WRITES SERIAL
and NOSERIAL are not required; they are provided for SQL Server compatibility
on non-Windows NT platforms.
TIP: If the master device is mirrored and fails, the mirror device takes over and operation continues uninterrupted. If the SQL Server is later halted and restarted, the server fails because of a bad master device. To prevent this failure at startup, use the SQL Server Setup utility to add the following startup parameter to the server:
-r physical mirror device path and nameThe -r startup option tells SQL Server at startup to use the mirrored device when the primary device fails.
When a device is mirrored, the sysdevices table is modified. The physical name of the mirrored device is placed in the mirrorname column and the status column is modified to indicate that mirroring has been turned on.
Unmirroring a device prevents SQL Server from performing writes to the mirrored device. Unmirroring is performed automatically by SQL Server when an I/O error occurs on the primary or mirrored device.
TIP: Devices can be mirrored and unmirrored without shutting down SQL Server.
A DBA can also unmirror a device using the SQL Enterprise Manager. To unmirror a drive, select the server and the device you want to unmirror and perform the following steps:
Figure 8.14.
The Unmirror Device dialog box.
NOTE: Removing a mirrored device does not remove the operating system file. Use the Windows NT File Manager or the DOS DEL command to remove the physical mirrored file.
The SQL command to unmirror a disk is the DISK UNMIRROR command, which has the following syntax:
DISK UNMIRROR
NAME = `logical name'
[, SIDE = PRIMARY | SECONDARY]
[, MODE = RETAIN | REMOVE]
NAME is the SQL Server logical device name to unmirror. SIDE specifies the device to disable (the primary or the secondary device). The default value of MODE is to retain the mirror device entry in the sysdevices table. The REMOVE flag clears mirror information in the sysdevices table.
If mirroring has been paused or halted because of device failure, mirroring can be turned on by remirroring the device. To remirror a device with SQL Enterprise Manager, select the server and the device to remirror and perform the following steps.
NOTE: You can only remirror a database if you chose the Retain Mirror Device option when you unmirrored the device.
Figure 8.15.
The Re-Mirror Database Device dialog box.
The command to remirror a device is DISK REMIRROR, which has the following syntax:
DISK REMIRROR
NAME = `logical name'
NAME is the logical name of the SQL Server device to remirror.
To mirror or not to mirror: that is the question. In the old days, mirroring a device was the best protective measure available for ensuring nonstop SQL Server operation. Today, other options such as RAID 5 hardware configurations and NT software-based striping exist; in most cases, these newer options may be a better recovery strategy for your system. Because better options do exist, Microsoft has recently suggested that, instead of using SQL Server mirroring, you can take advantage of Windows NT mirroring. Although SQL Server mirroring is still supported in SQL Server 6.5, future versions of SQL Server may not support SQL Server mirroring (although Windows NT mirroring will be supported). However, if you decide to use SQL Server mirroring, you must develop a strategy that is cost effective for your organization and provides acceptable downtime in case of a recovery. Following are some suggested mirroring strategies:
Device mirroring provides a level of protection against data corruption. The price you pay for device mirroring is that you use more disk space and add overhead by requiring the server to perform more disk I/O.
When creating SQL Server database devices, consider the following:
Many decisions made about a database device depend on the hardware configuration and the database that resides on the device. The following sections examine each of these considerations and present possible strategies to implement.
Before creating a database device, determine the purpose that the device will serve. Will the device be used for transaction logs, database storage, or both?
I like to create a separate device for each transaction log. Doing so allows more efficient disk allocation because databases have different sizes and backup requirements. If you decide to mirror some production transaction logs but not all of them, you can select only the transaction log devices that need to be mirrored. I do not like to create a single device for all transaction logs. SQL Server maintains an I/O queue for each device. Placing all the database transaction logs on a single device also places all requests on a single device I/O queue. By placing each transaction log on its own device, you limit the overall requests to the device I/O queue.
Another advantage of a single device per transaction log is device failure. If a single database device with a transaction log fails, you lose access to only one database. If a device with several transaction logs fails, you lose access to many databases.
Databases can be placed on several devices or can span multiple devices. One of the deciding factors should be the physical disk layout. If the disk drives are striped or have a RAID configuration or a single disk drive, you do not have to concern yourself with balancing the database across different physical devices. Although I do not do so with transaction logs, I do share database devices for small databases (those less than 200M in size).
It is good practice not to mix database and transaction logs on the same device.
Transaction log and database sizing is detailed in Chapter 9, "Managing Databases"; however, this section examines the size issue as it relates to devices.
Recommended transaction log sizes are 10 to 25 percent of the size of the database. These percentages are just a starting point. Take into consideration the frequency of transaction log dumps and the number of transactions. A transaction-intensive database that has the log dumped every 15 minutes requires a smaller transaction size than the same database being dumped daily.
For database devices, I tend not to create devices larger than 1 gigabyte. If the device fails, it is much faster to recover smaller devices than larger devices. The drawback is that several smaller devices are harder to manage than a single large device. It is a good idea to come up with a standard size for database devices. For a large database, create database devices anywhere from 512M to 1G; 256M is a good average for smaller databases.
The correct drive to select for a device typically depends on available space and whether disk striping or a RAID configuration is being used.
Establish consistent naming conventions for devices and logs. Include the word LOG for transaction log devices and the word DATA or DB for database devices. For dump devices, include the word DUMP. If the device belongs to a single database, use the name of the database. For example, a transaction log and database device for the pubs database would be PUBSLOG, PUBSDB, or PUBSDATA.
Take precautions to protect against data loss by using device mirroring, NT disk mirroring, or RAID configurations. When you create a device, it is important to make sure that you have a hardware or software plan that allows you to recover the device if it is lost--in a acceptable amount of time--especially the master device.
Following are some of the important points in this chapter that you should remember when managing SQL Server devices:
You should now have a good understanding of SQL Server devices and how to create
them, modify them, and remove them. It is also very important to realize the need
to protect the devices (in case of device failure) and the different options available
to you, such as mirroring or RAID configurations. In the next chapter, you learn
how to assign databases to the devices you have created and examine in more detail
the placement of databases and transaction logs on devices.
To order books from QUE, call us at 800-716-0044
or 317-361-5400.
For comments or technical support for our books and software, select Talk to Us.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster
Company.