MSSQL™ Audit Trail Database Setup

This section addresses the setup and installation of a Microsoft SQL Server (MSSQL) audit trail database. It includes setup of MSSQL, creation of the MSSQL audit trail database and schema.

Prerequisites

The following sections assume basic understanding of MSSQL database server installation and configuration.

Before an audit trail database can be created, a properly installed and configured instance of a MSSQL database server must exist. You can retrieve MSSQL from the Microsoft SQL Server site. Refer to the Infinity Process Platform Release Notes to check for the currently supported version.

After downloading MSSQL, install MSSQL on the database server. During the installation of the MSSQL Server, select the Authentication Mode as Mixed Mode which includes both, Windows Authentication and SQL Server Authentication. Provide a password for the SQL Server Authentication, whose default user name is 'sa'.

Once the database is started, invoke the MSSQL client to continue the creation of the Infinity Process Platform audit trail database. Open a Windows Command prompt and enter the following command:

C:/>sqlcmd -Usa -P

Enter the password at the prompt.

If login is successful, the following is displayed:

1>

Use Prepared Statements in Combination with native MSSQL XA Driver

Issues occur when you use an MSSQL XA driver and you do not have enabled the usage of prepared statements. Using prepared statements is required in combination with a native MSSQL XA driver. To enable prepared statements, check that property AuditTrail.UsePreparedStatements is set to true> in your carnot.properties file (this is the default value):

AuditTrail.UsePreparedStatements = true

MSSQL - Restrictions in case sensitive String Comparison

As MSSQL always performs a case insensitive String comparison, the usage of the Boolean parameter caseSensitive in a DataFilter query will have no effect when using MSSQL.

Configuration Settings for MSSQL on Tomcat

The configuration settings required for MSSQLon Tomcat are described in detail in section Configuration Settings for MSSQL of chapter Tomcat.

Enforcing Unicode Usage

In case you use a language with multi-byte character sets, like Chinese or Japanese, you might face deployment issues. To avoid these issues, set the server-side property Audittrail.EnforceUnicode in your carnot.properties file to true.

With this property set to true, all text columns will be created as NVARCHAR / NTEXT instead of VARCHAR / TEXT. Additionally, all DML uses the syntax N<some text> instead of <some text>. Note that the property has an effect on newly created schemas only.

Database Creation

At the 1> prompt, execute the following commands to create the database with name dbName:

1>  create database <dbName>;
2>  go

Create a new login with dbcreation privilege, then create a user for this login and grant db_owner role to it.

1>  CREATE LOGIN <login> WITH PASSWORD = '<password>', DEFAULT_DATABASE = <dbName>, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF;
2>  go
1>  sp_addsrvrolemember  '<login>', 'dbcreator';
2>  go
1> use <dbName>;
2> go
 
Change database context to '<dbName>'.
 
1> CREATE USER <username> FOR LOGIN <login>;
2> go
1> sp_addrole 'db_owner', '<username>';
2> go

Create Schema for the above created database.

1> use <dbName>
2> go
 
Changed database context to '<dbName>'.
 
1> create schema <schemaName> AUTHORIZATION <username>
2> go

Schema Setup

Once the database has been created, the audit trail database schema must be created within MSSQL. This process utilizes the System Console utility provided with Infinity Process Platform. Execute the System Console with the following arguments:

sysconsole -v -r <jdbcDriver> -dbtype MSSQL -dbschema <dbschema> -l jdbc:sqlserver://<host>/databaseName=<yourdbName> -d <user> -s <password> createschema

The following is displayed after the command is invoked:

Infinity Process Platform (TM) Administration Console, Version 4.x
Copyright (C) SunGard CSA LLC, 2000-2011. All rights reserved.
 
Create Infinity Process Platform schema:
 
Database type   : MSSQL
Database URL    : jdbc:sqlserver://<host>/databaseName=<yourdbName>
Database user   : <user>
Database driver : <jdbcDriver>
 
Do you want to proceed? (Y/N):

Answer Y to proceed. The following is displayed after a successful creation of the audit trail database schema.

Schema created.

Alternative Schema Setup

If you do not have the ability to directly create the database schema because of privileges within your database environment or desire to create a file with the appropriate SQL commands, the following procedures can be followed to create a file containing the appropriate DDL and DML statements that can be executed within the database as a SQL script.

sysconsole -v -dbtype MSSQL ddl -file <fileName> -schemaName <schemaName>

Once the DDL/DML file has been created by the previous command, follow the Database setup instructions from the previous sections if not already completed, and then have a user with appropriate privileges execute the created DDL/DML script file as such:

mssql> source <fileName>

Modifying the MSSQL Database Locking Behavior

Execute the following SQL to modify the MSSQL database locking behavior:

ALTER Database carnot 
SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

/*
SELECT is_read_committed_snapshot_on
#FROM sys.databases
#WHERE name = N'carnot'

#Return value should be 1
*/

Enabling XA Transaction Support

To enable the XA transaction support, follow the instructions described in the Understanding XA Transactions article.

Configuring the Timeout for Lock Queries

To avoid SQL Exceptions based on query timeout, you have the option to change the query timeout for lock queries by setting the property Infinity.Engine.Tuning.Query.LockTimeout in your carnot.properties file. This property determines the query timeout for lock queries in seconds. Default value is 1.

Local and Remote Mode Setup

The following sections discuss the data source / database connection pool setup of the audit trail database based on the deployment mode.

Spring Local Mode Setup

If you use Infinity Process Platform in Spring local mode, you have to perform the following steps:

  1. In your carnot.properties file add the following properties:
    AuditTrail.Type = MSSQL
    AuditTrail.DriverClass = com.microsoft.sqlserver.jdbc.SQLServerDriver
    AuditTrail.URL = jdbc:sqlserver://<host>/databaseName=<yourdbName>
    AuditTrail.User = <yourDBUserName>
    AuditTrail.Password = <yourDBUserPassword>
    Set the type of the audit trail database to MSSQL and use the database driver class com.microsoft.sqlserver.jdbc.SQLServerDriver.
  2. In your carnot-spring-ds-jndi-context.xml replace the parameter ${AuditTrail.XaDataSourceClass} in the carnotXaAuditTrailDataSource bean part with class com.microsoft.sqlserver.jdbc.SQLServerXADataSource:
    <bean id="carnotXaAuditTrailDataSource" class="com.microsoft.sqlserver.jdbc.SQLServerXADataSource" lazy-init="true">
        <property name="serverName" value="${AuditTrail.ServerName}" />
        <property name="portNumber" value="${AuditTrail.Port}" />
    </bean>

Remote Mode Setup

The audit trail database data source and connection pool setup is configured based on the application server/container utilized for the Infinity Process Platform deployment. Refer to Application Server Setup for more information regarding data source and connection pool setup for an audit trail database.