Creating additional Communication Processes (CP) when the Alternative MS SQL Server Schema is in use

Issue/Introduction:

Error Message :
N/A

When adding a new CP (i.e. CP006 and only CP tables up to CP005 exist) and the alternative MS-SQL Schema is in use the communication processes are only created in the Main-DB without any Synonym for the MQ-DB (Message Queue Database).

The added tables are also not renamed and there is no link between the Main-DB and the splitted MQ-DB for the new process.

Cause:

Cause type:
Defect
Root Cause: With Automation Engine Version 12.1.2 there will be a new message "Cannot create a new MQCP (&01) table because you are using an alternative DB schema for MS-SQL. Please create the table manually."

Environment:

Release: AUTWAB99000-12.0-Automic Workload Automation-Base Edition
Component:

Resolution:

Reminder:
A CP/WP has no information about a split schema and therefore cannot create tables in the split DB.

In V12.1 four Communication Processes (CPs) will handle approximately 400000 connections. Therefore, it might not be necessary to add additional CPs. Just add them if necessary - this approach is more resource-conserving.

Steps to create additional CPs in the database manually when the alternative MS SQL Server Schema is in use:

All the following SQL-Queries can be found in our Documentation:
https://docs.automic.com/documentation/webhelp/english/AWA/12.1/DOCU/12.1/AWA%20Guides/help.htm#Installation_Common/install_alternative_MS_SQL_Server_DB_schema.htm?Highlight=alternative

Step 1 (optional: must only be considered if additional CP tables have already been created in the Main-DB):
If there were already tables added for new Communication Processes in the Main-DB (because additional processes were started before) these need to be renamed to e.g.: _ucorig.

With the following script you can select all server processes to rename them afterwards (on the Main-DB)

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

Copy the result to Notepad and remove all tables which already have an alternative name and the MQSrv table (this one must not be located in the MQ-DB (Message Queue Database) but only in the Main-DB) – so only the new added communication processes shall stay over.

e.g. Result:
exec sp_rename MQ1CP00x ,MQ1CP00x_ucorig;
exec sp_rename MQ2CP00x ,MQ2CP00x_ucorig;

Execute the resulting statements on the Main-DB to rename the Processes.

Step2:
Create the additional CP tables directly in the MQ-DB (Message Queue Database) with the correct script (v.12.1 or v.12.0 depending on the Automation Engine Version in use) from the uc_dll.sql file (CREATE TABLE MQ1CP00x; CREATE TABLE MQ2CP00x) available in the folder\db\sql_9\12.1.

Step3:
The following statement is meant for the creation of the synonyms between the two DBs. It is executed on the Main-DB.

Execute the following statement to select which tables need a synonym:

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

Copy the result to Notepad and remove all tables that already have a synonym in the MQ-DB (again only select the communications processes which have not yet a synonym)

e.g. Result:
create synonym [dbo].[MQ1CP00x] for [MAIN_2].[dbo].[MQ1CP00x];
create synonym [dbo].[MQ2CP00x] for [MAIN_2].[dbo].[MQ2CP00x];

Execute the resulting statements on the Main-DB to create the synonym between the Main-DB and the MQ-DB.

Result:
The MQCP00x tables are now available in the MQ-DB and in the Main-DB the synonyms for the tables are available.

Fix Status: In Progress

Fix Version(s):
Automation Engine 12.1.2 - Planned release date: 2018-05-23

Additional Information:

Workaround :
N/A