Creating Models › Target Servers for Physical Models › Database Property Editors › SQL Server 2005 Property Editors › Define a Database in SQL Server 2005 › Define Options for a SQL Server 2005 Database

Define Options for a SQL Server 2005 Database

Use the SQL Server Database Editor to define options for a database in a SQL Server 2005 physical model.

To define SQL Server 2005 database options

1. Click Target - SQL Server, Databases on the Model menu..

   The SQL Server Database Editor opens.

2. Select the database in the Navigation Grid for which you want to define options.

   Note: Use the Enter filter text box to filter a very large list of databases to quickly locate the one that you want to define.

3. Click the Options tab and work with the following options:

   Cursor Default

   Specifies whether the cursor scope is LOCAL or GLOBAL. If the cursor scope is LOCAL, unless the cursor is defined as GLOBAL, the cursor scope is local to the batch, stored procedure, or trigger where the cursor was created. If the cursor scope is GLOBAL, the scope of the cursor is global to the connection.

   Cursor Close On Commit

   Specifies whether the cursor is closed or remains open when a transaction is committed or rolled back.

   Termination Option

   Specifies when to roll back incomplete transactions when the database is transitioned to a different state.

   Termination Time

   Defines how long (in seconds) after a state change in the database before it rolls back. Enter a value in this field.

   Note: You must set the termination option to TIME to use this option.

   Restricted Access

   Specifies user access to the database.

   Read Only

   Specifies whether database updates are permitted.

   Is Date Correlation Optimization Active

   Specifies whether SQL Server maintains correlation statistics between any two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.

   Note: When this option is selected, only a single connection to the database is allowed.

   Parameterization

   Determines how queries are parameterized, whether based on the default behavior of the database, or if all queries are parameterized.

   Auto Close

   Specifies whether the database is closed and all resources freed when the last user exits.

   Auto Create Statistics

   Specifies whether SQL Server automatically creates missing statistics needed for a query or if they must be made manually.

   Auto Shrink

   Specifies if the database is a candidate for periodic shrinking.

   Auto Update Statistics

   Specifies if any outdated statistics required by a query are automatically updated during query optimization.

   Auto Update Statistics Async

   Specifies whether the query that initiated an update to the statistics waits for the statistics to update before compiling.

   Recovery

   Specifies the recovery option set for the database. If the recovery option is not specified, the default is set to the recovery model of the model database in SQL Server. Select the recovery type from the drop-down list.

   Torn Page Detection

   Specifies if torn pages can be detected by the SQL Server database engine.

   Page Verify

   Determines if and how a database page was damaged by disk I/O errors.

4. Click Close.

   Database options are defined and the SQL Server Database Editor closes.