Handling Database Properties for the SQL Server 2005 Database Engine

Administering the Database Properties General Page

General, the first page in the Database Properties dialog box, displays information exclusive to backups, database settings, and collation settings. Specific information displayed includes

1. Last Database Backup
   
2. Last Database Log Backup 
    
3. Database Name 
    
4. State of the Database Status 
    
5. Database Owner 
    
6. Date Database Was Created 
    
7. Size of the Database 
    
8. Space Available 
    
9. Number of Users Currently Connected to the Database 
    
10. Collation Settings

You should use this page for obtaining factual information about a database, as displayed in Figure 1.11.

Figure 1.11.   Viewing the General page in the Database Properties dialog box.

Administering the Database Properties Files Page

The second Database Properties page is called Files. Here, you can change the owner of the database, enable full-text indexing, and manage the database files, as shown in Figure 1.12.

Managing Database Files

The Files page is used to configure settings pertaining to database files and transation logs. You will spend time working in the Files page when initially rolling out a database and conducting capacity planning. Following are the settings you’ll see:

• Data and Log File Types—A SQL Server 2005 OLTP database is composed of two types of files: data and log. Each database has at least one data file and one log file. When you’re scaling a database, it is possible to create more than one data and one log file. If multiple data files exist, the first data file in the database has the extension *.mdf and subsequent data files maintain the extension *.ndf. In addition, all log files use the extension *.ldf.

Figure 1.12.   Configuring the database files settings from within the Files page.

Tip

To reduce disk contention, many database enthusiasts recommend creating multiple data files. The database catalog and system tables should be stored in the primary data file, and all other data, objects, and indexes should be stored in secondary files. In addition, the data files should be spread across multiple disk systems or Logical Unit Number (LUN) to increase I/O performance.

• Filegroups—When you’re working with multiple data files, it is possible to create filegroups. A filegroup allows you to logically and physically group database objects and files together. The default filegroup, known as the Primary Filegroup, maintains all the system tables and data files not assigned to other filegroups. Subsequent filegroups need to be created and named explicitly.
  
• Initial Size in MB—This setting indicates the preliminary size of a database or transaction log file. You can increase the size of a file by modifying this value to a higher number in megabytes. 
   
• Autogrowth Feature—This feature enables you to manage the file growth of both the data and transaction log files. When you click the ellipses button, a Change Autogrowth dialog box appears. The configurable settings include whether to enable autogrowth, and if
  autogrowth is selected, whether autogrowth should occur based on a percentage or in a specified number of megabytes. The final setting is whether to choose a maximum file size for each file. The two options available are Restricted File Growth (MB) or Unrestricted File Growth.

Tip

When you’re allocating space for the first time to both data files and transaction log files, it is a best practice to conduct capacity planning, estimate the amount of space required for the operation, and allocate a specific amount of disk space from the beginning. It is not a recommended practice to rely on the autogrowth feature because constantly growing and shrinking the files typically leads to excessive fragmentation, including performance degradation.

• Database Files and RAID Sets—Database files should reside only on RAID sets to provide fault tolerance and availability, while at the same time increasing performance. If cost is not an issue, data files and transaction logs should be placed on RAID 1+0 volumes. RAID 1+0 provides the best availability and performance because it combines mirroring with stripping. However, if this is not a possibility due to budget, data files should be placed on RAID 5 and transaction logs on RAID 1.

Increasing Initial Size of a Database File

Perform the following steps to increase the data file for the AdventureWorks database using SSMS:

1. In Object Explorer, right-click the AdventureWorks database and select Properties.
   
2. Select the File Page in the Database Properties dialog box. 
    
3. Enter the new numerical value for the desired file size in the Initial Size (MB) column for a data or log file and click OK.

Creating Additional Filegroups for a Database

Perform the following steps to create a new filegroup and files using the AdventureWorks database with both SSMS and TSQL:

1. In Object Explorer, right-click the AdventureWorks database and select Properties. 
    
2. Select the Filegroups page in the Database Properties dialog box. 
    
3. Click the Add button to create a new filegroup. 
    
4. When a new row appears, enter the name of new the filegroup and enable the option Default.

Alternatively, you can use the following TSQL script to create the new filegroup for the AdventureWorks database:

USE [master]
GO
ALTER DATABASE [AdventureWorks] ADD FILEGROUP [SecondFileGroup]
GO

Creating New Data Files for a Database and Placing Them in Different Filegroups

Now that you’ve created a new filegroup, you can create two additional data files for the AdventureWorks database and place them on the newly created filegroup:

1. In Object Explorer, right-click the AdventureWorks database and select Properties. 
    
2. Select the Files page in the Database Properties dialog box. 
    
3. Click the Add button to create new data files. 
    
4. In the Database Files section, enter the following information in the appropriate columns:  

    

   Columns	Value
   Logical Name	AdventureWorks_Data2
   File Type	Data
   FileGroup	SecondFileGroup
   Size	10 MB
   Path	C:\
   File Name	AdventureWorks_Data2.ndf

    

5. Click OK.  

Note

For simplicity, the file page for the new database file is located in the root of the C: drive for this example. In production environments, however, you should place additional database files on separate volumes to maximize performance.

You can now conduct the same steps by executing the following TSQL syntax to create a new data file:

USE [master]
GO
ALTER DATABASE [AdventureWorks] 
  ADD FILE (NAME = N’AdventureWorks_Data2’, 
  FILENAME = N’C:\AdventureWorks_Data2.ndf’,
  SIZE = 10240KB , FILEGROWTH = 1024KB )
  TO FILEGROUP [SecondFileGroup]
GO

Configuring Autogrowth on a Database File

Next, to configure autogrowth on the database file, follow these steps:

1. From within the File page on the Database Properties dialog box, click the ellipses button located in the Autogrowth column on a desired database file to configure it. 
    
2. On the Change Autogrowth dialog box, configure the File Growth and Maximum File Size settings and click OK. 
    
3. Click OK on the Database Properties dialog box to complete the task.

You can use the following TSQL syntax to modify the Autogrowth settings for a database file based on a growth rate at 50% and a maximum file size of 1000MB:

USE [master]
GO
ALTER DATABASE [AdventureWorks]
MODIFY FILE ( NAME = N’AdventureWorks_Data’, MAXSIZE = 1024000KB , FILEGROWTH = 50%)
GO

Administering the Database Properties Filegroups Page

As stated previously, filegroups are a great way to organize data objects, address performance issues, and minimize backup times. The Filegroup page is best used for viewing existing filegroups, creating new ones, marking filegroups as read-only, and configuring which filegroup will be the default.

To improve performance, you can create subsequent filegroups and place data and indexes onto them. In addition, if there isn’t enough physical storage available on a volume, you can create a new filegroup and physically place all files on a different volume or LUN if Storage Area Network (SAN) is being used.

Finally, if a database has static data, it is possible to move this data to a specified filegroup and mark this filegroup as read-only. This minimizes backup times; because the data does not change, SQL Server marks this file group and skips it.

Note

Alternatively, you can create a new filegroup directly in the Files page by adding a new data file and selecting New Filegroup from the Filegroup drop-down list.

Administering the Database Properties Options Page

The Options page, shown in Figure 1.13, includes configuration settings on Collation, Recovery Model, and other options such as Automatic, Cursor, and Miscellaneous. The following sections explain these settings.

Collation

The Collation setting located on the Database Properties Options page specifies the policies for how strings of character data are sorted and compared, for a specific database, based on the industry standards of particular languages and locales. Unlike SQL Server collation, the database collation setting can be changed by selecting the appropriate setting from the Collation drop-down box.

Recovery Model

The second setting within the Options page is Recovery Model. This is an important setting because it dictates how much data can be retained, which ultimately affects the outcome of a restore.

Figure 1.13.   Viewing and configuring the Database Properties Options page settings.

Please check back tomorrow for the continuation of this article.