Preparing a Microsoft SQL Server Database for a Siebel Upgrade

This chapter provides initial preparatory information for a Siebel database upgrade on the Microsoft SQL Server database. This chapter includes the following topics:

Verifying Microsoft SQL Server Sort Order for a Siebel Upgrade

Environments: Development, production test, production.

Databases: MS SQL Server only.

Platforms: Windows only.

Verify that the sort order of the master database and the database instance are the same. This prevents repository merge failure due to sort order mismatch.

Binary sort order is required for the development environment upgrade and strongly recommended for the production environment upgrades.

When you install Microsoft SQL Server, the collation method of the database instance is set by default to dictionary sort order. Every database you create thereafter inherits this setting.

When you create a database, you can accept the inherited sort order or specify the sort order. It is recommended that you set the sort order to binary at the Microsoft SQL Server instance level so that this sort order is inherited by newly created databases.

The sort order of the master database cannot be changed without rebuilding the instance. Consult your Microsoft documentation for instructions on setting database collation.

To verify that your database was created using a binary collation sequence

In the Query Analyzer window, enter the following command:
```
sp_helpsort
```
This command provides a sort order description.
Review the sort order description to verify binary sort order; for example:
```
Latin1_General_BIN 
```
If you find that your Microsoft SQL Server database was not created using a binary collation sequence, then you must rebuild your database and reload your data. Review the Microsoft documentation for detailed instructions.

Setting Microsoft SQL Server Temporary Space Size for a Siebel Upgrade

Environments: Development, production test, production.

Databases: MS SQL Server only.

Platforms: Windows only.

Set the size of the database that Microsoft SQL Server uses for temporary space needed to execute queries.

To setup TEMPDB space

Make TEMPDB as big as the biggest table in the Siebel database, or half the size of the Siebel database.
Make sure that the files used by TEMPDB are configured to allow auto-growth.
This allows Microsoft SQL Server to expand the temporary database as needed to accommodate upgrade activity. Alternatively, you can set MAXSIZE to the size of the biggest table or to 50% of the size of the Siebel database.
Consider putting TEMPDB on a separate drive to improve performance.
Execute dbcc shrinkdatabase against TEMPDB.

Setting Microsoft SQL Server Configuration Parameters for a Siebel Upgrade

Environments: Development, production test, production.

Databases: MS SQL Server only.

Platforms: Windows only.

This topic provides upgrade-specific settings for Microsoft SQL Server. Use the following strategy to set parameters:

Set parameters using the recommendations in Siebel Installation Guide for the operating system you are using. Recommendations are located in the chapter on configuring the RDBMS.
For the upgrade, revise the configuration parameters listed in the following table.
After the upgrade, reset the configuration parameters to the values listed in Siebel Installation Guide for the operating system you are using.

The following table lists upgrade settings for Microsoft SQL Server database parameters. For parameters not listed in this table, it is recommended that you accept the default settings.

Most of the parameter settings in the table are the default settings.

Table Microsoft SQL Server Configuration Parameters

Parameter	Setting
Max. degree of parallelism	`1`
Cost threshold for parallelism	5
Fill factor (%)	`90`
Index create memory (KB)	`0`

For the Siebel database, set the following options to ON (enabled) for the upgrade:

truncate log on chkpt. Set this option to ON (enabled) for upgrade only. Also, for upgrade only, execute the alter command against the Siebel database, specifying set recovery simple.
torn page detection.
auto create statistics.
auto update statistics.
Database size. Increase your database file size by resetting the Autogrowth parameter to between 25% and 50%. Failure to do this could diminish upgrade performance and possibly impact the success of your upgrade.
For a full list of recommended settings for your postupgrade production environment, see the chapter on configuring the RDBMS in Siebel Installation Guide.

Rebuilding Microsoft SQL Server Clustered Indexes for a Siebel Upgrade

Environments: Development, production test, production.

Databases: MS SQL Server only..

Platforms: Windows only.

If you have large tables that you use extensively (such as S_EVT_ACT, S_CONTACT, S_OPTY, S_OPTY_POSTN, S_ORG_EXT), then use the Microsoft SQL Server create index command with drop_existing clause to rebuild large tables with high fill factor (60%-70%).