Move database logging to SQL server.

Move database logging to SQL server.

Changing / migrating the Logging Database from HSQL to MySQL , MSSQL.

By default, the Stoneware Relay logs all transactions to a Java database called HyperSonic SQL. This database is installed as part of the portal installation and automatically configured through the Stoneware Wizard process. Organizations may wish to change the database the Stoneware Relays are logging their transactions to.

Checklist
  1. Any ODBC/JDBC compliant database (Oracle, SQL, Sybase, Informix, MySQL, DB2, etc...). 
  2. ODBC/JDBC driver (Type 3 or 4)
  3. Driver class and connection information from database administrator.

To change the logging database do the following:
  1. Modify the existing ’ReportLogs’ connection object to point to the new database, or create a new connection object that points to the new database. 
    1. Select the properties of the ’ReportLogs’ connection object in webAdmin. 
    2. Change the current ’Class Name’ to the class name provided from the database administrator or vendor.
      * You can create a sample Database Connection object to see the connection string to the selected database. 
    3. Change the current ’Connection String’ to the connection string provided by the database administrator or vendor.
      * Note * the table name used in the connection string is created in the next step. Make sure these names match (i.e. PortalLog). 
    4. Check ’User Owner/Schema Names’ if the database type is Oracle, SQL, Informix, MySQL, or DB2.
    5. Modify the Security settings
      * If the table created requires an account name and password to add data: 
      1. Select the "Security" tab.
      2. Select the "Specified" under the Consolidated Authentication section. 
      3. Enter the User name and Password. 
    6. Select the SAVE button. 

  2. Create a table structure in the new database to hold logging information. 
    1. Create a table (i.e. LOGTABLE) in your database with the following field names and types:
      ClientHost - Text
      Username - Text
      LogTime - Date/Time
      Request - Text
      Status - Integer
      Bytes - Integer
      Referer - Text
      UserAgent - Text
      RelayDN - Text
      (You can also use the pre-defined SQL script (for your database type) located in /stoneware/hsql/dbscripts) 
    2. Save the table. 

  3. Restart Report Services 
    1. At the Stoneware Loader select the ’Services’ tab. 
    2. Select the "Report Services’ option. 
    3. Stop and Start Report Services. 

  4. Restart the Stoneware Relay. 

If you used the scripts to create your log table and you are using MySQL on Linux you may have to adjust report definitions and change LOGTABLE to LogTable. Linux MySQL is case sensitive for table names. This is an adjustable parameter but it defaults to case sensitive enabled. Changing it on a production system is not advised since you may cause problems for other applications.


It is recommended to configure a scheduled job, on the SQL server, to regularly purge old records in accordance with your data retention policy.  As the size of the Audit database increases, performace in retrieving records decreases.  It can get to the point where there are too many records in the database, causing the connection from UW to timeout before the SQL server can respond with the requested data.  There is also the risk of running the SQL server out of storage space.