MONITOR database considerations for Microsoft SQL Server

There are specific recommendations for databases that are hosted on Microsoft SQL Server.

Important: When you are installing SQL Server, you must select mixed mode (Windows Authentication or SQL Server Authentication) as the authentication mode.

Important: To use SQL Server with IBM® Business Monitor, you must configure SQL Server for XA transactions. SQL Server is not pre-configured for XA transactions. The XA support is delivered as part of the Microsoft JDBC driver distribution and contains a dynamic link library (sqljdbc_xa.dll) and an installation script (xa_install.sql). Because XA transactions are not enabled by default, you must change the configuration in the Microsoft Windows Distributed Transaction Coordinator (MSDTC). For instructions to enable XA support for SQL Server, see "Understanding XA Transactions" in the Microsoft SQL Server online documentation.

When you are using the Profile Management Tool (in the WebSphere® Customization Toolbox) or the manageprofiles command to create the SQL Server database, the database administrative user specified in the profile creation creates the database. The IBM Business Monitor runtime database user (@DB_USER@) that is specified during profile creation should already exist as an SQL Server login and database user. You can use the following command to create the database login and database user:

CREATE LOGIN @DB_USER@ WITH PASSWORD = '@DB_PASSWORD@', DEFAULT_DATABASE=@DB_NAME@
CREATE USER @DB_USER@ FOR LOGIN @DB_USER@

where DB_USER is the IBM Business Monitor runtime database user, DB_PASSWORD is the runtime database password, and DB_NAME is the IBM Business Monitor database name.

By default, the IBM Business Monitor runtime database user is granted db_owner privileges as part of the database creation. This enables the IBM Business Monitor server to automatically manage the monitor model database schema when models are deployed and removed. To secure the database, you can create the database manually and grant the runtime database user only the privileges required for runtime operations. See the topics "Installing the database manually" and "Securing the MONITOR database environment" in the related links.

The SQL Server JDBC drivers for JVM 1.6 provide JDBC support. IBM Business Monitor uses the Microsoft JDBC 2.0 driver sqljdbc4.jar file. By default, the Profile Management Tool points to the sqljdbc4.jar file supplied in app_server_root\jdbcdrivers\SQLServer. Alternatively, you can download another Microsoft sqljdbc4.jar JDBC driver file and point to it when you run the Profile Management Tool or the manageprofiles command. For information about minimum required settings for SQL Server, see the related link.

Globalization considerations

SQL Server manages the locale settings with the COLLATE option when creating the database. The create database statement for the MONITOR and COGNOSCS databases contains the following option:

COLLATE SQL_Latin1_General_CP1_CS_AS

To change the locale settings, change the collation parameter to a supported collation for the language you want. For example, to change the collation to French, you would use:

COLLATE French_100_CS_AS

SQL Server manages the default language based on the login user. To change the default language, in the createDatabase.sql file, add the DEFAULT_LANGUAGE option to the create login with a different default language. For example, to create the login with a default language of French, you would use:

	IF NOT EXISTS (SELECT * FROM syslogins WHERE NAME = '@DB_USER@') CREATE LOGIN @DB_USER@ WITH PASSWORD = '@DB_PASSWORD@', DEFAULT_DATABASE=@DB_NAME@, DEFAULT_LANGUAGE=French;

The DIM_TIME table contains a column for populating dashboard reports containing time dimensions with a translated month name. By default, the locale code for DEFAULT_LANGUAGE setting is used for populating the DIM_TIME table entries. To change the default language, change the DEFAULT_LANGUAGE for the database user before running the createTables.sql script.. There is also an SQL statement in createTables.sql that you can use to override the month name entries and define your own month names.