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

Define Options for a SQL Server Database

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

Follow these steps:

1. Click Model, Target Server, Databases to open the SQL Server Database Editor.

   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 large list of databases to 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 and the cursor is also LOCAL, 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. Select Time, Immediate, or No Wait.

   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. You can select Multiple Users, Restricted User, or Single User.

   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 are freed when the last user exits.

   Auto Create Statistics

   Specifies whether SQL Server automatically creates missing statistics that is needed for a query.

   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 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 the SQL Server database engine can detect torn pages.

   Page Verify

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

4. Click Close.

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