Configuring the Database

Once you have created the necessary databases, you can add new files to the databases, change some of the existing database file properties, and set the database configuration options appropriately.

Database File Configuration

You can change certain database file properties, as well as add or drop files using either of the following methods:

  • use the Properties dialog box in Management Studio
  • use the ALTER DATABASE command (for details, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads)

Adding Files

Data files can be added to an existing file group in a database, or to a new file group. There are no special restrictions or requirements.

Dropping Files

To drop a file, you must first empty it using the DBCC SHRINKFILE command’s EMPTYFILE option, which transmits the file data to all the other files in the file group. Once you empty the file, you can use the ALTER DATABASE <database name> DROP FILE command to drop it.

Changing File Properties

You can change the size-related properties for all databases, as well as the filename property for the tempdb database (this takes effect after you restart Microsoft SQL Server). The SIZE, MAXSIZE, and FILEGROWTH properties can be changed using the ALTER DATABASE tempdb MODIFY FILE command. Note that the SIZE property can only be enlarged.

To shrink the file, use the DBCC SHRINKFILE command. For details and recommendations concerning file properties, see Creating the Database.

Database Configuration Options

Each database contains a set of configurable options that determine its behavior. You can view or change the database options using any one of the following utilities:

  • the Options tab in the Management Studio’s Properties dialog box
  • the EXEC sp_dboptions stored procedure
  • the ALTER DATABASE <database name> SET command

Note Not all of the database configuration options are available in this dialog box.

The following table lists, in alphabetical order, the default configuration options, as well as the configuration settings required for Universal CMDB certification:

Configuration Option Description Default Universal CMDB Certification in Microsoft SQL Server
ANSI NULL default (see note below) Specifies whether the database columns are defined as NULL or NOT NULL, by default Not set Not set
ANSI PADDING Controls the way the column stores values shorter than the defined size of the column and the way the column stores values that have trailing blanks in char, varchar, binary, and varbinary data. OFF ON
Auto close Specifies whether the database shuts down after its resources are freed and all users exit Not set

Not set

Note If set, it may take a long time for the database to allocate resources every time a user connects, after the database is closed.

Auto create statistics Specifies whether missing statistics required by a query for optimization are built automatically during optimization Set Set
Auto shrink Specifies whether the database is automatically shrunk every hour, leaving 25% of free space Not set

Not set

Note If set, constant growth/ shrinkage may cause file system fragmentation.

Auto update statistics Specifies whether out-of-date statistics required by a query for optimization are built automatically during optimization Set Set
Compatibility level The version of Microsoft SQL Server that the database appears to be (for the application) The same version as the release installed The same version as the release installed
Read only Database is read only Not set (READ_WRITE) READ_WRITE
Recovery The database recovery model determines the recovery capabilities by controlling the amount of bulk operation logging (such as Select into, Bulk, Insert, Create index, LOB manipulation). The higher the recovery model, the higher the recovery capabilities. However, the amount of logging also increases, which may affect performance. Full Full (unless you are certain that the lower recovery capabilities are sufficient for your system)
Recursive triggers Specifies whether recursive triggers are supported Not set Not set
Restrict access Only single users or members of the db_owner, dbcreator, or sysadmin groups can access the database. Not set (MULTI_USER) MULTI_USER
Torn page detection Specifies whether incomplete pages can be detected Set Set
Truncate log on checkpoint Automatically marks inactive portions of log for reuse on checkpoint Not set N/A
Use quoted identifiers Specifies whether the Microsoft SQL Server enforces ANSI rules regarding quotation marks. Select this option to specify that double quotation marks be used only for identifiers, such as column and table names. Note that character strings must be enclosed in single quotation marks. Not set Not set

Note  

Not all ANSI options can be set using Management Studio. The ANSI database configuration options include: ANSI_NULLS, ANSI_NULL_DEFAULT, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and QUOTED_IDENTIFIER.

The options you set may not take effect, since these options can also be set at a higher level.

For example, if the session option QUOTED_IDENTIFIER was turned on, the equivalent database configuration option is irrelevant. Some tools or database interfaces turn certain session options on or off, so that the database configuration options never take effect.

The following table summarizes the characteristics of each recovery model:

Model/ Support Allows Log Backup Allows Point-in-Time/Log Mark Restoration Allows Backup Log when Data Crashes (Saves changes until the crash point) Amount of Bulk Operation Logging (can affect the performance of bulk operations)
Simple No No No Minimal
Bulk Logged Yes No No Minimal
Full Yes Yes Yes Full

To check your database’s properties, run the command:

EXEC sp_helpdb <database name>

For information on SQL databases, see Microsoft SQL Server Books Online at http://www.microsoft.com/downloads.

Composite Indexes vs. Non Composite Indexes

For environments that execute heavy data-in operations, in order to speed up the INSERT statements, it is recommended to transform the database from composite index to non composite index.

You can achieve this by invoking the modifyCompositeIndexes JMX method with composite indexes setting to false. For detailed instructions, see How to Modify Composite Indexes.