Creating SQL Server Databases

If you're a network administrator who has recently become a database administrator, too, the task of creating and maintaining database files for your company's developers might seem daunting. Fortunately, this task is much easier with Microsoft SQL Server 2000 and SQL Server 7.0, compared with earlier versions. (If you're using SQL Server 6.5 or earlier, I strongly recommend upgrading to SQL Server 2000 or SQL Server 7.0.) Using SQL Server Enterprise Manager, you can create the necessary database files and set the necessary database properties quickly and easily. Expanding, shrinking, and deleting databases is also easy. However, before I show you how to perform these tasks, you need to know about the types of files a SQL Server database contains and where to place those files.

SQL Server Files
Every SQL Server database has at least one data file and at least one transaction log file. By default, the data file has the extension .mdf and the log file has the extension .ldf. You can add files to permit databases to span physical disks or to split your database into sections for better performance and easier maintenance (more on that later). Additional data files have the extension .ndf; additional log files keep the .ldf extension.

A SQL Server file can't span logical disks. If your database will be large and won't fit on one logical disk, you have several options. You can split the database into several files on different disks. You can put the database on a RAID array, which appears as one logical disk. Or you can use the Windows 2000 and Windows NT tools to create volume sets or, in Win2K, dynamic volumes, which also appear as one logical disk.

The transaction log should be on a separate physical disk. That way, if you lose the disk on which the data is stored, you can at least restore the database from your last backup and the transaction log. In addition, I recommend that you not put the transaction log on the RAID array. The log is written and read sequentially, so it doesn't benefit from the multiple drive heads in the RAID array. Nor is putting the transaction log on the RAID array worth the additional cost of calculating the parity of data that will be read only once or twice before being discarded. Mirroring the drive that contains the transaction log is a better idea and well worth the cost.

Creating a Database with the GUI
Developers who constantly create and delete test databases typically use a script. However, when you need to create only a few production databases, using Enterprise Manager is easier. Open Enterprise Manager and expand the hierarchy so that you can see your server, then Databases. Right-click Databases and select New Database. The first order of business is to provide a name for your database. Select the General tab of the new database's Properties dialog box, and enter a database name in the Name field. Use a name that's easily recognizable and short enough so that it can be typed quickly. You might not be typing it that much, but your developers will. In SQL Server 2000, you have to supply the collation for the database, as Figure 1 shows. Leave the default setting unless you have a good reason to change it (e.g., you're using a third-party database application that requires a specific collation). If you're unfamiliar with collation, see my Web-exclusive article "Installing SQL Server," http://www.winnetmag.com, InstantDoc ID 21742.

In SQL Server 7.0, the General tab doesn't include a collation setting because you set the collation when you installed SQL Server 7.0. After you've set the collation, it's fixed for all databases on that server.

The next order of business is to supply a logical, or internal, name for the primary data file. SQL Server creates and names the physical data file in the default data directory according to the supplied logical filename. Although you can customize the physical filename, letting the logical filename determine the physical filename is easiest. To supply the logical name in SQL Server 2000, select the Data Files tab and enter the logical name for the primary data file in the File Name field, as Figure 2 shows. In SQL Server 7.0, you provide the logical name for the data file on the General tab. For now, don't worry about the filegroup, which Figure 2 shows as PRIMARY.

Notice the Automatically grow file check box at the bottom left of Figure 2. In SQL Server 7.0 and later, the data and log files grow automatically when they've filled their current space. Although this automatic growth feature sounds like it could save you a lot of trouble, a better approach is to allocate as much space as you think the data file will need by specifying that amount in the Initial size (MB) field. By making the file the correct size from the beginning, you avoid the fragmentation that allocating new areas of the disk to the database causes. You also prevent someone else from using that space and leaving your database with no room to grow.

By default, the Automatically grow file check box is selected. You can turn off this feature, or you can use it as a safety valve in case your initial space estimates are off or someone uses the database differently from what you planned. You can set the growth to occur by percent or megabyte. By default, the data file is set to grow in increments of 10 percent. When growth occurs by percent, the data file grows the specified percentage according to its current size. So, you need to be careful with the percent option—the bigger the data file, the more space it grabs with each growth spurt. A better choice is to use the In megabytes option, in which the data file grows the specified amount during each growth spurt.

   Previous  [1]  2  3  4  Next