SQL Server Magazine

Several steps are necessary before you can create SQL Server tables. I covered selection of data types last month. This month, I look at another building block: creating the databases (and in SQL Server 6.5, the devices) to hold the tables. SQL Server 6.5 and 7.0 differ greatly in the way they store data; consequently, the way you construct databases in each is different. I'll address the two versions separately and point out some differences. Let's examine how to create, expand, and remove databases and devices in SQL Server 6.5. As usual, I concentrate on using the Transact SQL (T-SQL) syntax for creating objects. You can create your devices and databases entirely from within the graphical interface of SQL Server Enterprise Manager, but it helps to be familiar with the basic syntax and to understand what the GUI is doing for you behind the scenes.

Devices
In SQL Server 6.5, the first step in database creation is creating a device. This term goes back to the old days of SQL Server when you had to dedicate an entire hard disk to a database. More recent releases don't require an entire disk, but they require that you allocate disk space for the database or databases to use. This pre-allocated space is a disk file, with the default extension .DAT. A device can contain more than one database, and a database can span multiple devices. Because a device is a disk file, it can't cross from one disk to another. So if you have a very large database, you might have to create several devices to get enough space for your database.

SQL Server builds devices on logical disk drives, not physical drives. Thus, a RAID array appears as one logical disk drive, although it consists of multiple physical drives. In that case, you can spread the device across all the physical drives.

Creating Devices. To create a device, from the ISQL/w window or from the Enterprise Manager query window, use the DISK INIT command. The syntax is

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

The device must have a logical name by which you can refer to it later. You also need to supply a physical name for the file, which includes the drive and directory path. The directory must already exist—the DISK INIT won't create the directory for you. The vdevno, or virtual device number, is a number from 0 to 255, which uniquely identifies this device. Vdevno 0 is already in use for the Master device which contains the system databases (Master, Model, and Tempdb). Numbers 126 and 127 are in use for the devices that hold the msdb database and its transaction log. (See Michael Otey, SQL Seven, "System-Supplied Data-bases," May 1999 for information on SQL Server databases.) You don't have to assign sequential numbers as you add devices, but doing so is easier.

How do you find out which virtual device numbers are in use? The sp_helpdevice stored procedure lists them; just run it from the query window. The output listing isn't well formatted, so you'll have to scroll all the way to the right to see the device numbers.

SQL Server 6.5 requires that you supply the size of the device, expressed in 2KB blocks. A megabyte has 512 2KB blocks, so just multiply the desired size in megabytes by 512. Thus, a 10MB database is 5120 blocks, a 100MB database is 51200 blocks, etc.

The vstart parameter is the starting virtual address, or starting offset, expressed in 2KB blocks. Leave this parameter at the default of zero, or just omit it. It's there only for backward compatibility.

Suppose you want to build a 100MB database to hold budget and planning data, with a transaction log of 20MB. Use the code in Screen 1, page 78, to build the devices.

Expanding Devices. If your device runs out of room and the same disk has enough space, you can expand the device. (If you don't have enough space, add another device on a different disk.) To expand a device, use the DISK RESIZE statement. The syntax is

DISK RESIZE 
   NAME = logical_device_name, 
   SIZE = final_size 

You don't have to give the physical name, just the logical name. Be careful to give the total final size in 2KB blocks. Be aware that you can never shrink a device. You can't reduce the size of the device from the original size; you can't even remove additional space after you add it.

Using Default Devices. If a device is a default device, you can use it for any database for which the database creator doesn't specify a device. You can designate any device as a default device, and you can have multiple default devices. If someone creates a database without specifying a device, SQL Server uses the default devices in alphabetical order. This rule can lead to some odd results. Suppose that you have three default devices, DEVA, DEVB, and DEVC. DEVA and DEVB are each 100MB, and DEVC is 300MB. You create a 250MB database and don't specify where you want it, or you use the ON DEFAULT option. SQL Server uses 100MB on DEVA, then 100MB on DEVB, then 50MB on DEVC. Sure, DEVC has enough space for the whole database, but if that's what you want, you have to say so. SQL Server won't depart from alphabetical order.

   Prev. page   [1] 2 3     next page