KB194843345: Automation Engine - No connection to Microsoft SQL Server anymore after upgrading to Automation Engine 16.1

Summary

After upgrading to Automation Engine 16.1, connection to the configured SQL Server is not available anymore. It happens for both the Automation Engine Server databases as for the External Database (f.e. used for 'Database' SmartNames). This happens for older databases, with no or very old service packs installed on the SQL Server.

Symptoms

In case the older version of the database server is used for the Automation Engine Server databases, the Pilot doesn't startup properly.

A popup window mentions:

Could not connect to the ToDoServer to retrieve the Red Alerts due to the following exception :
BG_EException .server.elm.ELM_ENotServicingException: ElemServer not servicing

For both cases, you will see lot of SQL related 'exceptions' in the bgmd.log file.

 This is caused by the fact that Automation Engine 16.1 has a newer SQL Driver compared to previous versions, which was needed as the old one is no longer supported by Microsoft and the new one is needed to support higher SQL Server versions.

Solution

For Automation Engine databases, it is highly recommended to upgrade to SQL Server 2014. Please check the following KB article for more info on how to upgrade:

• Page:
  KB193111123: Automation Engine - How to upgrade an older Automation Engine using an SQL Server 2005 to version 16.1

This KB is handling an upgrade from SQL 2005 to SQL 2014, but can be used as well to upgrade other versions to SQL 2014. In case you are still Running SQL Server 2005, you will have to upgrade that first to at least SQL Server 2008 R2 ; but SQL Server 2014 is the recommended one.

For all supported versions, Automation Engine can require a certain service pack as well.  See below overview. So in case you prefer to keep the current version and not (yet) upgrade to SQL Server 2014, then check if your SQL Version is at least the version as in the table below:

• Microsoft SQL Server 2008 --> requires Service Pack 4 (10.0.6000)
• Microsoft SQL Server 2008 R2 --> requires Service Pack 3 (10.50.6000)
• Microsoft SQL Server 2012 --> requires Service Pack 4 (11.0.7100.0)
• Microsoft SQL Server 2014 --> requires Service Pack 1 (12.0.4100.0)
• Microsoft SQL Server 2016 --> requires Service Pack 1 (13.0.4001.0)

Some older service packs might still work but are no longer supported by Microsoft and therefore also no longer by Esko. The number at the end refers to the internal number of SQL Server and can be checked with SQL Server Management Studio or with the AE Server Admin' web page.

Please note as well SQL Server 2008 and SQL Server 2008 R2 are soon outdated, so they will become unsupported in a next version.

Workaround

In case the SQL Server is an External Database (used for Database SmartNames or the task 'Interact with external database'), you can - as a temporary workaround - configure the External Database using the older Automation Engine 16.0 driver. See the below steps.

Note: This workaround is not possible for the Automation Engine database itself. In such cases you must either upgrade to a newer version of SQL Server or apply a higher service pack.

1. Copy the old JDBC driver (sqljdbc.jar) from bg_prog_fastserver_v160\classes or bg_prog_fastserver_v141\classes to %bg_custom_dat_v010%\dat\sql
2. In Tools > Configure, change the type of DBMS from 'Microsoft SQL Server' to 'Other'.
3. Define the other details as:
   • Driver (JDBC): com.microsoft.sqlserver.jdbc.SQLServerDriver
   • Driver File: file://'hostname_of_ae-server'/bg_data_custom_v010/dat/sql/sqljdbc.jar
   • Database URL: jdbc:sqlserver://'hostname_of_database_
     server';databaseName=FastLane;authentication=SqlPassword;trustServerCertificate=true;encrypt=false;user=xxxxx;password=password for xxxx
     

Plan for the upgrade to a higher version later. Once you did that upgrade, you can then, in the configuration, switch back to the type 'Microsoft SQL Server'.