Chapter 04 - Creating Devices, Databases, and Transaction Logs
The storage of physical data in SQL Server is controlled through the creation of data devices. Data devices are areas of disk that are preallocated for the use of SQL Server. SQL Server can use devices for the storage of either data, logs, or dumps. Databases are logical areas that SQL Server reserves for the storage of tables and indexes. One or many databases may be created on a data device provided the device is an appropriate size. Transaction logs, covered in Chapter 12 "Understanding Transactions and Locking" are the work areas that SQL Server uses to manage the transactions performed by client processes. The transaction logs are used to store the information required by SQL Server to roll back a transaction if a client process issues a ROLLBACK TRANSACTION.
Defining DevicesDevices 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: database (used for storing databases) and dump (used for storing transaction logs).Devices can store more than one database or transaction log in them; however, you often can get better performance with a single database per device, because each device is managed by a single I/O thread from the operating system. 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.
Creating Database DevicesYou can create a disk device in SQL Server in two ways: graphically or through Transact-SQL. The graphical method is performed by using SQL Enterprise Manager. The Transact-SQL method is performed using the DISK INIT command. Both methods are discussed in the following sections.
Using SQL Enterprise ManagerSQL Enterprise Manager is a versatile tool that permits DBAs to perform most of the administrative functions of SQL Server without knowledge of the often cryptic Transact-SQL commands required. However, SQL Enterprise Manager does not permit an automated installation or "scripted" installation procedure. For this you will need to use Transact-SQL.To use SQL Enterprise Manager to create a device, follow these steps:
After successful creation of the device, SQL Enterprise Manager will add it to the graph of devices (see fig. 4.6). Fig. 4.6 - The NewDevice device has been added and is now empty (all dark).
Using DISK INITDISK INIT is the 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:
The options for the Transact-SQL command DISK INIT are as follows: logical_nameAny valid SQL Server identifier. A shorter name is probably preferable because it has to be used each time a database is created. physical_nameThe full path and file name of the file to be used on the operating system to store the data. virtual_device_numberThis is the unique system identifier for the device. It can range from 0 to 255, where 0 is reserved for the master database. number_of_2K_blocksThis is how a device is sized. The minimum value is 512 (meaning 1M). virtual_addressThis parameter controls the virtual paging of the data device and how SQL Server accesses it. This parameter should be used only when you're told to by a Microsoft Service Provider. The following SQL statement creates the same data device that was created earlier using the SQL Enterprise Manager:
Using Device OptionsYou should consider two important device options when creating devices in SQL Server: mirroring and default devices. Both options are important to mission-critical environments where downtime needs to be minimized.
MirroringMirroring is used to provide an absolute copy of the database (usually) on a different physical device so that if a hardware failure occurs on the primary device, the database can be switched over to run on the mirror device. 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.Mirroring can be performed at two levels with SQL Server: either by the underlying operating system (Windows NT Advanced Server) or by SQL Server. More information on mirroring the operating system can be found in the Microsoft documentation that accompanies NTAS 3.51. In most cases, the operating system's implementation of mirroring will provide better performance and optionssuch as various different implementations of Redundant Arrays of Inexpensive Drives (RAID)than SQL Server. There are six levels of RAID (levels 0 to 5) that can be implemented and they provide different methods of distributing physical data (and its copies) across multiple drives. See Appendix B, Redundant Arrays of Inexpensive Drives, for more information about levels of RAID. Furthermore, with NTAS it's possible to mirror whole drives, thereby protecting all devices that reside on the drive. SQL Server's mirroring works by creating an additional device on the specified disk and then using the mirror handler to copy every transaction that the device receives to the mirror device. To enable mirroring on a device by using SQL Enterprise Manager, follow these steps:
Mirroring can also be done by using Transact-SQL. The DISK MIRROR command's syntax is as follows:
physical_nameThis is the full file name of the mirror device. Use an extension of .MIR for compatibility with SQL Enterprise Manager. WRITESThis option isn't used on NT. It's provided only for backward compatibility with earlier versions of SQL Server that ran on OS/2. The following example shows how to mirror the NewDevice created earlier:
Default DevicesDefault devices are used by SQL Server when the CREATE DATABASE command isn't accompanied by a specific device that it should be placed on. Many devices can be specified as default. SQL Server will use them alphabetically until each device is filled.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:
In the following example, the device NewDevice is made a default device:
Displaying Device Informationhere are two ways to find information about the devices that are now installed/active on a SQL Server: by using SQL Enterprise Manager or by using the system-stored procedure sp_helpdevice. Viewing device information by using SQL Enterprise Manager's Manage Database Devices Window has been demonstrated several times earlier in this chapter; refer to those examples for help.The syntax for sp_helpdevice is as follows:
The following example shows the output and use of sp_helpdevice to view all the devices on the server:
Creating Dump DevicesDump devices are special devices that SQL Server uses to perform backups and to dump (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 that's being used to write the data to:
See the Chapter 1 section entitled Command-Line Applications
There are two ways to 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.
Using SQL Enterprise Manager to Add a Dump DeviceUsing 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:
Using sp_addumpdeviceSQL 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:
The options for the system-stored procedure sp_addumpdevice are as follows: logical_nameThis is the logical name of the device that's going to be used for backups/dumps. physical_nameThis is the physical name of the device that's going to be used for the dump. For a 'disk' or 'diskette' dump device, specify the full path of the output file that should be created. For a 'tape' device, reference the locally attached tape device by using Windows NT's Universal Naming Convention (UNC)--for example, "\\.\tape0". The following example adds a disk-based dump device to SQL Server:
Dropping DevicesMicrosoft provides two ways to drop a device: by using SQL Enterprise Manager or by using the system-stored procedure sp_dropdevice. Dropping a device completely deallocates the disk space associated with the device and frees the space for other uses by the operating system or server.If a device is not in use any more or is not correctly sized, it may 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 DevicesSQL 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.
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:
Fig. 4.13 - 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 DevicesThe system-stored procedure sp_dropdevice is provided for dropping devices from SQL Server. The syntax for sp_dropdevice is as follows:
logical_nameThis is the name of the device to be removed. DELFILEIf DELFILE is included, the physical file that was created on the server will also be removed. An error will occur 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 LogsDatabases 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 that belongs or is associated with it. The transaction log is a place that SQL Server writes all the database transactions to before writing them to the database. The transaction log is used to hold "open" transactions (transactions started with a BEGIN TRAN statement) until they're "closed" or COMMITted. By default, the transaction log is placed on the same database device as the database; however, better performance can be achieved by creating two devicesone for the log and one for the database itself. 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 (that is, placements on database devices). Given that a physical disk unlikely will be greater than 10G (gigabytes) in size, it would seem to be impossible to get a database much bigger than 320G by using SQL Server (which would suggest that Microsoft should increase the number of device fragments permitted). 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, Redundant Arrays of Inexpensive Drives, for more information
Creating a Database and Transaction LogThe process of creating a database causes SQL Server to copy the model database to the new database name. This process copies all the items in the model database's catalog. If you want to have custom default tables and objects in every database created on a server, add them to the model just like you would add them to any database.You can take two approaches to create a database and transaction log: you can use either SQL Enterprise Manager or the CREATE DATABASE command.
Using SQL Enterprise Manager to Create Database and Transaction LogsTo create a database using SQL Enterprise Manager, follow these steps:
Using the CREATE DATABASE Command to Create Database and Transaction LogsThe CREATE DATABASE command is the Transact-SQL method to creating a database. The syntax for CREATE DATABASE is as follows:
The following example creates the same database that was created in the preceding section by using SQL Enterprise Manager:
Displaying Database InformationThe Manage Databases window in SQL Enterprise Manager provides a graphical display of all the information about a database. To view this information in ISQL, use the system-stored procedure sp_helpdb. The syntax for sp_helpdb is
The following example 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:
Increasing the Size of the Database and Transaction LogSQL Server allows the size of a database to be resized in case 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 ManagerTo increase the size of a database or transaction log by using SQL Enterprise Manager, follow these steps:
Using the ALTER DATABASE Command to Extend a DatabaseTransact-SQL provides the ALTER DATABASE command to allow a database to be extended. Transaction logs are also extended by using the ALTER DATABASE command; however, 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:
In this example, NewDatabase is extended by 5M on the NewDevice database device:
Dropping DatabasesDropping 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 reversible, so be careful; a restore will be required to recover the database.
User accounts that had their default database as the database that's being dropped will have their default database changed to master. Only the System Administrator (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 DatabaseTo use SQL Enterprise Manager to drop a database, follow these steps:
Fig. 4.20 - This message box is the last chance that you have to abort a database being dropped before SQL Enterprise Manager performs the drop.
Using the DROP DATABASE Command to Drop a DatabaseThe syntax for the DROP DATABASE command in Transact-SQL is as follows:
Databases in all "states" (including Active, Damaged, Suspect, Offline, or Not recovered) can be dropped by using the DROP DATABASE command. A database that's still in Recovery status must be dropped by using the system-stored procedure sp_dbremove.
Defining SegmentsSegments 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 10, Managing and Using Indexes and Keys, for more information about creating indexes.
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.
Using Default SegmentsWhen 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 SegmentsYou add segments by using the system-stored procedure sp_addsegment. The syntax for sp_addsegment is as follows:
segment_nameThe name of the segment that's being added. database_deviceThe name of the database device that this segment should be placed on. The following example creates a segment on the database device NewDevice:
Extending SegmentsYou 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:
segment_nameThe name of the segment to be extended. database_deviceThe name of the database device that this segment should be extended on. The following example extends the segment seg_newdevice1 on to the database device NewDevice2:
Using SegmentsAfter 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 allows a table or index to be created on a particular segment.See Chapter 5, Creating Database Tables and Using Data Typesfor more information about creating tables on segments. SQL Server also provides a system-stored procedure, sp_placeobject, that will direct the server to place any new data for a table onto a new segment. You would 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; all it does is cause future allocations to occur on the requested new segment. The syntax for sp_placeobject is as follows:
The following example makes all further data allocations for the table authors on the new segment seg_data2:
Dropping SegmentsDropping segments removes them from the database devices that they reside on. 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 5, "Creating Database Tables and Using Datatypes" For information about dropping indexes, see Chapter 10,Managing and Using Indexes and Keys,. Segments are dropped by executing the system-stored procedure sp_dropsegment. The syntax for sp_dropsegment is as follows:
Using the Tempdb DatabaseTempdb is a special database that's 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 activity done on Tempdb temporary tables are much faster than 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 SizeThe default size of Tempdb when SQL Server is installed is 2M. For most production environments, this size will be insufficient for Tempdb. 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. 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 for resizing any other database.
Placing Tempdb in RAMBecause Tempdb is so critical to server performance (since SQL Server uses it for just about every query/operation that occurs), Tempdb has a unique option of being able to be placed in RAM. Placing Tempdb in RAM dramatically improves the amount of time required for sorting.Before you consider placing Tempdb in RAM, you must consider how Tempdb uses memory now available in the server. All RAM that's allocated to SQL Server is paged based on a least recently used (LRU) algorithm. What this means is that quite often, data in Tempdb will be 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. 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 itwhether or not Tempdb was in RAM or on a disk deviceis lost. To place Tempdb in RAM, you must use the system-stored procedure sp_configure and restart the server. For more information on using sp_configure and other configurable options of SQL Server, refer to Chapter 16, "Understanding Server, Database, and Query Options"
Using Removable Media for DatabasesA new featureintroduced in SQL Server 6.0 and continued with 6.5enables 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 it. Also, a CD-ROM-based database is truly read-only and is a great way of securing data integrity.
Creating a Removable DatabaseA 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 System Administrator (SA) can create removable databases.SQL Server has a special system-stored procedure, sp_create_removable, that will create a database and devices that will be acceptable for use when creating a database for removable media purposes. It's important that you use this stored procedure because it will guarantee that the database created is usable on a removable device. The syntax for sp_create_removable is as follows:
The following example creates a 1M data, log, and system catalog database and appropriate devices called MyRemovable:
Using the Removable DatabaseWhile 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:
As part of its execution, sp_certify_removable also takes offline all the devices that have been created for use in the removable database and makes them available for copying to the actual physical device. The output from sp_certify_removable is very important because it indicates the database characteristics to use when installing the removable database in to a SQL Server. The following example shows the MyRemovable database being certified and brought offline:
Installing the Removable DatabaseAfter 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 that the removable media is placed on.The system-stored procedure sp_dbinstall is used to install a database from removable media. The syntax for sp_dbinstall is as follows:
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:
Uninstalling a Removable Media DatabaseIf 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 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 entitled Using SQL Enterprise Manager to Remove Devices 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:
Table of Contents
03 - Understanding the Underlying Operating System Windows NT |