Siebel Business Analytics Applications Installation and Administration Guide > Preinstallation Considerations for Analytics Applications >

SQL Server-Specific Database Guidelines for Siebel Data Warehouse

This section provides guidelines for SQL Server database usage.

NOTE:  SQL Server databases with binary sort order or case-sensitive dictionary sort order are supported. Case-insensitive dictionary sort order is not supported.

This section includes the following topics:

• Setting the ANSI NULL Option
• Modifying the DB Library Options Setting
• Configuring DAC to Support SQL Server 2005
• Connecting from DAC to SQL Server Using Dynamic Port Allocation
• Recommended SQL Server Database Parameters

Setting the ANSI NULL Option

Siebel Business Analytics requires that SQL Server databases be created with the ANSI NULL option selected.

To set the ANSI NULL option

1. In the SQL Server Enterprise Manager, right-click the appropriate database, and choose Properties.
2. Click the Options tab and select the box for ANSI NULL default.

Modifying the DB Library Options Setting

In a SQL Server 2000 environment, when loading Analytics tables with international data, or loading more than one language, you need to modify the DB Library Options setting.

To modify the DB Library Options setting

1. From the Microsoft SQL Server program menu, select Client Network Utility.
2. Select the DB Library Options tab.
3. Clear the option Automatic ANSI to OEM.

NOTE:  SQL Server 2000 automatically tunes many of the server configuration options; therefore, an administrator is required to do little, if any, tuning. Although these configuration options can be modified, the general recommendation is that these options be left at their default values, allowing SQL Server to automatically tune itself based on run-time conditions.

Configuring DAC to Support SQL Server 2005

The standard DAC configuration supports SQL Server 2000. The DAC can also support SQL Server 2005 with the following configuration changes.

To configure the DAC to support SQL Server 2005

1. Modify the connection_template.xml file:
   1. Navigate to the SiebelAnalytics\DAC\conf directory, and open the connection_template.xml file in a text editor.
   2. Comment out the section related to SQL Server 2000.
   3. Uncomment the section related to SQL Server 2005.
2. Download the SQL Server 2005 JDBC driver from the Microsoft Web site, and install it.
3. Copy the sqljdbc.jar file from the SQL Server 2005 JDBC driver installation directory and paste it into the SiebelAnalytics\DAC\lib directory.
4. If the DAC is installed on Windows, modify the config.bat file as follows:
   1. Locate the following line:

   SQLSERVERLIB=%DAC_HOME%\lib\msbase.jar;%DAC_HOME%\lib\mssqlserver.jar;%DAC_HOME%\lib\msutil.jar

   2. Append the following string at the end of the line referred to above if it is not there already:

   ;%DAC_HoMe%\lib\sqljdbc.jar

5. If the DAC is installed on UNIX, modify the config.sh or config.csh file as follows:
   1. Locate the following line:

   SQLSERVERLIB

   2. Append the following string at the end of the line referred to above if it is not there already:

   /lib/sqljdbc.jar

6. Execute the following script on every instance of the SQL Server 2005 that the DAC will access:

   execute sp_configure 'show advanced options', 1

   GO

   RECONFIGURE

   GO

Connecting from DAC to SQL Server Using Dynamic Port Allocation

To connect to a database on Microsoft SQL Server, the DAC requires a database host name, database name, and port number. If you configure an instance of SQL Server to use a static port, you can configure the connection to the DAC using the static port number. However, if you configure an instance of SQL Server to use the dynamic port allocation process, the port number can change every time SQL Server is restarted. In such cases, you must either manually update the new port number or configure the DAC to automatically identify the port number when you enter the database host name and database name. If you choose to update the port number manually, you can find the allocated port number in the Windows registry. To configure the DAC to identify the port number automatically, follow the instructions below.

To configure the DAC to identify the SQL Server port number automatically

1. Modify the connection_template.xml file:
   1. Navigate to the SiebelAnalytics\DAC\conf directory, and open the connection_template.xml file in a text editor.
   2. Find the entry related to the appropriate version of SQL Server.

      There might be two entries, one for SQL Server 2000 and one for SQL Server 2005.

   3. Remove the port reference from the URL entry.

      For example, for SQL Server 2005:

      Before modification, the URL entry appears as follows:

   <URL>jdbc:sqlserver://%host:%port;databaseName=%connect_str</URL>

   After modification, the URL entry should appear as follows:

   <URL>jdbc:sqlserver://%host;databaseName=%connect_str

   For SQL Server 2000:

   Before modification, the URL entry appears as follows:

   <URL>jdbc:microsoft:sqlserver://%host:%port;DatabaseName=%connect_str</URL>

   After modification, the URl entry should appear as follows:

   <URL>jdbc:microsoft:sqlserver://%host;DatabaseName=%connect_str</URL>

2. When entering SQL Server database connection information in the DAC user interface, for example, in the procedures Creating the DAC Metadata Repository and Creating Transactional and Data Warehouse Database Connections, leave the Database Port field empty.
3. When entering SQL Server database connection information in the DAC user interface and using a named instance of SQL Server:
   1. In the Database Host field, include the instance name as part of the host name using the following format:

   <hostname\\\\instancename>

   NOTE:  The four backslashes are required.

   2. Leave the Database Port field empty.

Recommended SQL Server Database Parameters

If necessary, SQL Server components can be configured to optimize performance, as shown in Table 13.

• SQL Server memory: Make sure adequate memory is available.
• Transaction logs and TempDB: Reside on a separate disk from those used by database data.
• Full load: Full Recovery model for the database.
• Incremental (Refresh) load: Change from Full to Bulk-Logged Recovery model.