Integrating an Alternative MS SQL Server Schema

An alternative database scheme for the Automation Engine allows you to split the classic Automation Engine database into two separate ones.

Overview

When you delete rows in a MS SQL Server database, the records can either be marked as ghost records or can be deleted immediately. This depends on your database configuration:

• When your database is configured with clustered and/or heap tables and versioning is turned on (read_committed_snapshot=ON), the records are not deleted but marked as ghost records.

  The ghost cleanup process, a single-threaded task, then physically deletes these records in the background.

• When your database is configured only with heap tables and versioning is turned off (read_committed_snapshot=OFF), the records are physically deleted immediately.

This concept guarantees a performance boost when deleting many rows. However, on large systems, the ghost cleanup process may fall behind the rest of the system and block it.

The Automation Engine system writes every transaction in the database and deletes it after it has been processed successfully, which means that the AE deletes rows mostly from the MQ tables. Using and alternative database scheme allows you to split your database and have your MQ tables in a separate database, therefore, not using the ghost cleanup process in the second database (MQ) and boosting performance.

The architecture diagram below shows the classic and alternative database schemas.

The classic database scheme is configured with clustered and heap tables and has versioning turned on (read_committed_snapshot=ON). In the alternative database scheme, the first database (AE) is similar to the database in the classic scheme. There are only two differences:

• The first database (AE) in the alternative scheme is configured only with clustered tables
• The MQ tables in the first database (AE) in the alternative scheme are synonyms (links) to the corresponding table in the second database (MQ)

The second database (MQ) is configured with heap tables and versioning turned off (read_committed_snapshot=OFF). It contains the actual MQ tables.

The alternative database: 

• is available for the MS SQL Server only

• can be switched to and from the classic database scheme

• can be handled by DBLOAD, see AE DB Load

• must have versioning turned on

  If not, the DBLOAD utility turns it on.

More information:

• https://technet.microsoft.com/en-us/library/ms189050(v=sql.105).aspx
• https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

You can create a system with an alternative scheme from scratch or you can convert an existing classic scheme to an alternative one.

Creating an AE System with the Alternative Scheme from Scratch

You can create an alternative scheme either with the DBLoad utility or manually.

Creating an Alternative Database Schema with the DBLoad Utility

You need two empty MS SQL Server databases with ODBC Connections and a User who can access both of the databases. For example, AE (with versioning) and MQDB (without versioning). AE contains the initialdata and MQDB contains the MQ tables for acceleration.

The UCYBDBld.ini file has a new section called [MQDB]. You can define the ODBC connection to the MQDB in the new [MQDB] section as you would do in the ODBC section.

If there are valid ODBC connections in the INI file, the DBLoad utility runs in split database mode. That means the operations required are made to the MQDB database.

With two valid empty databases, you can run the UC_UPD.txt with the DBLoad utility and, after a successful run, you get the split database. After that, you can start the work and/or communication processes on the system and use the programs as usual.

Creating an Alternative Database Schema Manually

1. Create two databases with ODBC connection. For example, AE and MQDB.

   AE contains the initialdata and MQDB contains the MQ tables for acceleration.

2. Load the initialdata into AE database.

   • Check the settings with dbcc useroptions

   • Make sure the database is running with versioning. If not, run the following command:

     alter database xxxx set read_committed_snapshot on

3. Prepare the MQDB database.

   • Create the MQ tables with KEY NONCLUSTERED. Create statements in all_MQ_S.txt.

   • Check the settings with dbcc useroptions.

   • Make sure the database is running WITHOUT versioning. To do so, run the following command:

     alter database xxxx set read_committed_snapshot off

4. Rename the original MQ tables on the AE database to *_org with the statements:

   rename_MQ_s_on_DB1.txt

   You can generate the needed statements with:

   select 'exec sp_rename ' + name + ' ,' + name + '_org;' from sys.tables where name like 'mq%'

5. Create synonyms to the MQBD. Run the statements on AE:

   create_synonym_on_DB1.txt

   You can create the needed statements with:

   select 'create synonym [dbo].[' + replace (name,'_org','') + '] for [MQDB].[dbo].[' + replace (name,'_org','') + '];' from sys.tables where name like 'mq%'

Converting a Classic AE Database Scheme to the Alternative One

You must create a second, empty MS SQL Server database.

Important! The new database must not have tables defined. For example, running "SELECT count(*) as DIVDB_Int4 FROM INFORMATION_SCHEMA.TABLES" should return zero.

Define the ODBC connection for this database and set it to the new [MQDB] section in the ucybdbld.ini file.

Load a normal initialdata with the UC_UPD.txt. If the second database is empty, the DBLoad utility splits your database into the new schema.

Checking the Versioning Status

To query the settings for the Versioning on you database you can use the following command:

select is_read_committed_snapshot_on as DIVDB_Int4  from sys.databases where name = 'DB_NAME'

See also:

• Preparing the AE Database - MS SQL
• Recommendations for MS SQL