Share This:

If you have installed SmartIT and DWP on Windows using a MSSQL Server (with a single node) and using the default "Local System" account to run the Services, you may need to update the Windows installed Service to run against dedicated Windows Service Accounts instead of the default "Local System" and switch over to a MSSQL Always On (HA) configuration.

The following explains how to update the JDBC connection strings for these functions.

 

With the move away from Mongo, the applications utilize the JDBC driver for database connections.

 

When configuring ARS to use an always on Cluster (ar.cfg, SQL-Server-Always-On: T), ARS automatically adds the multiSubnetFailover=true parameter to the JDBC URL and the rest becomes transparent to ARS:

[database('data_source','username','password','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://server:port;database=DatabaseName; multiSubnetFailover=true;')]

 

However, this is not the same for SmartIT/DWP which need to be configured manually to apply the required attributes to the connection strings.

 

We add the second parameter "integratedSecurity=true;" for Windows Authentication e.g. Service Accounts

https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-2017

 

Configuration Files:

 

The smartit.xml file "<SmartIT_Tomcat_Install_Dir>\conf\Catalina\localhost" directory contains references to the JDBC connection to the SmartIT_System and SmartIT_Business schemas:

Within this file there will be references to the JDBC database connection - (this is from a system connecting to MSSQLServer db):

 

                url="jdbc::sqlserver://mydbserver-011:1433;DatabaseName=SmartIT"

 

The dwp.xml file "<DWP_Tomcat_Install_Dir>\conf\Catalina\localhost" directory contains references to the JDBC connection to the DWP_System and DWP_Business schemas:

Within this file there will be references to the JDBC database connection - (this is from a system connecting to MSSQLServer db):

 

                 url="jdbc::sqlserver://mydbserver-011:1433;DatabaseName=DWP"

 

An Always On Cluster will have a listener configured for the active nodes, and this is the information we need to update in the URL connection strings.

The listener may also be using a different port than the underlying nodes, so this needs to be observed and updated in the connection string.

To configure for the listener, the updated strings would be:

 

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=SmartIT"

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=DWP"

 

The instanceName is optional in the connection string, and if not provided a connection is made to the default instance.

If wanting to log into the database using a Service Account, you add in the following attributes to the connection string in conjunction with updating the name/password combination in the XML to use the Service Account details (domain\user & password):

 

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=SmartIT;integratedSecurity=true"

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=DWP;integratedSecurity=true"

 

Timeouts can also be added to the connection strings if wanting to avoid queries waiting indefinitely.

 

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=SmartIT;integratedSecurity=true;queryTimeout=180"

               url="jdbc:sqlserver://alwaysonlistener:port;multiSubnetFailover=True;DatabaseName=DWP;integratedSecurity=true;queryTimeout=180"

 

A full list of options are available here:

Setting the Connection Properties - SQL Server | Microsoft Docs

 

Similarly, if wanting to update Smart Reporting the connection string lives in the web.xml file "<SmartReporting_Install_Dir>\appserver\webapps\ROOT\WEB-INF\" directory for a default installation.