Integrating an Alternative MS SQL Server Schema

Some users that run the Automation Engine with a MS SQL Server database and generate many transactions that results in having problems with the ghost clean-up process. For these users, Automic provides an alternative scheme for the Automation Engine, which actually splits the classic Automation Engine database into two separate databases.

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

In the alternate database scheme, the first database looks similar o the classic Automation Engine database, while the second database is a simple one with heap tables that writes no transaction logs. Those tables outsourced to the second database are linked with the classic one via synonyms.

The concept of ghost records was introduced by Microsoft to enhance the performance of the database. When rows are deleted, they are simply marked as ghost records instead of physically being deleted. This concept guarantees a performance boost when deleting many rows. However, there is a downside, which is the ghost cleanup task. On big systems, the ghost cleanup process, a single-threaded task, may fall behind the rest of the system and blocks it. The weak point of our database were MQ tables. Since the Automation Engine is transaction-based, it writes every transaction in the database and deletes it after successfully processing it. Therefore we outsourced the MQ tables into a separate database with the feature of ghost records disabled.

Requirements

The alternate database schema: 

• Is available for MS SQL Server only.
• Can be switched from/to the classic database scheme.
• Can be handled by DBLOAD.
• Is mandatory versioned in Automation Engine and will be activated by DBLOAD. See the following:
  • https://technet.microsoft.com/en-us/library/ms189050(v=sql.105).aspx
  • https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx

Creating an Alternate Database Schema Manually

1. Create two databases with odbc connection. For this example they are called MAIN_1 and MAIN_2.
   • MAIN_1 contains the initaldata.
   • MAIN_2 contains the MQ tables for acceleration.
2. Load the initaldata into the MAIN_1.
   • Check the settings with dbcc useroptions.
   • Make sure the database is running with versioning. If not run:

   alter database xxxx set read_committed_snapshot on

3. Prepare the MAIN_2 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 run:

   alter database xxxx set read_committed_snapshot off

4. Rename the original MQs on the MAIN_1 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%'

Replace your database and owner name.

5. Create synonyms to the MAIN_2. Run the statements on MAIN_1:

create_synonym_on_DB1.txt

You can create the needed statements with:

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

Replace your database and owner name.

Row Versioning for Read-committed and Snapshot Transactions (Versioning) Is Mandatory on MSSQL

During the set_vers command we set the Versioning with alter database MAIN_1 set read_committed_snapshot ON for executing this command, we need to set the autocommit to ON and after the alter table back to OFF.

For this we implemented a new OPC in the database layer (ucudb) which is called DB_OPC_AUTOCOMMIT_ON (OFF).

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

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

Create an Automation Engine System with the Alternative Scheme "from scratch".

You need two empty MS SQL Server databases with ODBC Connections and a User who can access both of the databases. For this example they are called MAIN and MQDB.

• MAIN contains all the tables and initaldata, except the MQ Tables
• MQDB contains all MQ tables with PRIMARY KEY NONCLUSTERED setting

In the UCYBDBld.ini is a new Area Called "MQDB". You can define the ODBC connection to the MQDB into the new MQDB section like in the well known ODBC area.

If there are a valid ODBC connection in the INI the DBLoad will run in split database mode. That means the needed operations are made to the MQDB database.

With two valid empty databases, you can run the UC_UPD.txt with the DBLoad and after a successful run you get the split database. After that, you need to load a license and you are able to start WP/CP on the system and use the Automic programs an usual.

Convert a Classic Automation Engine Database Scheme to the Alternative One

Create a second empty database . It is important that the new database has no tables defined! i.e. "SELECT count(*) as DIVDB_Int4 FROM INFORMATION_SCHEMA.TABLES" returns zero.

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

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

Important for customers they have already using the split schema with V10 and want to update to V12 split schema.

• The utility needs an empty second database (MQDB).
• You need to set back all the custom schema changes for the Utility, that it can upgrade to V12 and create the second database.

Description of Additional Modifications Done by the DBLoad if the MQDB is Enabled

The normal DDL is executed into the MAIN database, the utility will load the MQ_DDL into the MQDB.

After the MQ tables are in the MQDB the utility will execute:

• A rename process on the MAIN database to all MQs. After the rename all old MQ tables are called Tablename_ucorig (for example: MQ1CP001_ucorig)
• A synonym creator process on the MAIN database to all MQs in the MQDB.

Technical Description

The rename and synonym processes are made generic. Both are using a statement generator.

Rename statement generator:

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

Synonym statement generator:

select 'create synonym [SchemaMAIN].[' + replace (name,'_ucorig','') + '] for [MQDB_DBName].[SchemaMQDB].[' + replace (name,'_ucorig','') + '];' as DIVDB_String3 from sys.tables where name like 'mq%%'

There are some parameters SchemaMAIN, SchemaMQDB, DBName, which are filled with the data form the current used databases.

These two statements generate all the statements which are executed on the MAINDB.