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.

    Can't find the KB

    Unable to find the KB to address your issue ?  

      • Recent Articles

      • Lenovo Unified Workspace End-of-Life Questions and Answers

        Will the shutdown of LUW servers and access to downloads affect my server licensing? No, the shutdown of the customer servers and access to the product and licensing downloads will not affect your server licensing. This license is downloaded and ...

      • How do I determine my Unified Workspace license expiration date?

        The best method for determining the licensing information including the expiration date of your Unified Workspace license: Login to your 8090 management console on each server This may take remoting into each LUW server and relay, opening a browser, ...

      • Lenovo Unified Workspace 7.0.2.13 Released

        Highlights of Unified Workspace 7.0.2.13 Before you install: Please view the installation notes here. 7.0.2.13 requires a 7.0 license file. Below is a list of enhancements and fixes released in Unified Workspace 7.0.2.13 Fixed external storage ...

      • LanSchool Documentation Guides

        LanSchool Classic Teacher Console The LanSchool Teacher Console is the interface teachers will use to manage their classroom and students. It contains all the tools necessary for a teacher to effectively interact with students and create a ...

      • Lenovo Unified Workspace 7.0.1.41 Released

        Highlights of Unified Workspace 7.0.1.41 Before you install: Please view the installation notes here. 7.0.1.41 requires a 7.0 license file. Below is a list of enhancements and fixes for Unified Workspace 7.0.1.41 Updated Log4j Updated Java Updated ...

      • Related Articles

      • Move webNetwork to different server

        *** If you are moving to a different OS, please see : https://helpdesk.lenovosoftware.com/portal/kb/articles/migrate-webnetwork-to-windows-linux-22-8-2017 *** Keeping the same OS / version / IP As long as you are keeping the same OS/ version/IP then ...

      • Move SQL Databases to a new SQL Server

        Issue Customer would like to move their UW SQL Databases from older hardware to new hardware. Solution The easiest method is to use the same SQL database type, as this only requires exporting/importing the existing database from the old server to the ...

      • What does webNetwork send to the SQL server to purge logging data each night?

        Each night at midnight the loader sends a SQL command to the SQL server to purge old log information.  That command is as follows : DELETE FROM LogTable WHERE LogTime < 'some date'; 'some date' is formatted like this: yyyy-MM-dd HH:mm:ss If your ...

      • How to setup Analytics in dedicated SQL

        Problem:  Need to move Analytics database to a dedicated SQL server.  It is highly recommended you move this database to a separate dedicated SQL server on any production installations. Cause:  The included Hypersonic SQL (HSQL) is not designed for ...

      • Database Pooling

        Issue The way UW connects to a Database to run queries, it uses a single connection for each DB object created.  Thus if you have multiple queries that are run against that connection, they are processed in order, one at a time.  A very long query ...