In many organizations, it’s not uncommon to have at least two or more subnets spanning between different geographical locations (i.e. East and West coast). For high availability, you may have one or more UEM hosts in one site and at least one or more UEM hosts in another site.

Depending on your SQL back-end configuration, you may also have AlwaysOn using a Failover Cluster Instance (FCI) or availability group (AAG) setup which Blackberry UEM supports for its databases. This can, however, introduce a series of issues unless you take additional steps described further below.

What are some of the issues with hosting Blackberry UEM databases on Microsoft SQL Server AlwaysOn? For me, I experienced two issues described in this post.

For the first issue, Blackberry Control Service is shown as Stopped in UEM console for NJ UEM instance when the database is active in NY. This is what we see within the UEM console:

The expected behavior should be:

Also from the UEM instance in NJ, we cannot access anything under Settings -> Blackberry Dynamics when this issue occurs.

Fortunately, accessing user/device info is not affected when this occurs.

This issue occurs because UEM host in NJ fails to resolve to the correct IP when UEM database within SQL AAG is active in NY. As a result, the Blackberry Control Service on UEM host in NJ fails to connect to the UEM database and thus is reported as Stopped.

Upon reviewing the DB.properties file located at D:\Program Files\BlackBerry\UEM\common-settings on each UEM server (or C drive), we found:

# database type. SQL_SERVER or POSTGRESQL
configuration.database.ng.type=SQL_SERVER

# for sqlserver using named instances, use both server= and instance= properties
# for non sqlserver or not using named instances, leave instance blank
configuration.database.ng.server=SQLAAG
configuration.database.ng.instance=

# the port. Can be blank if using named instance
configuration.database.ng.port=1433

# database name for SQLServer. Leave blank if not using SQLServer
configuration.database.ng.name=UEM

# the authentication type. USER or INTEGRATED (ie in SQLServer terms, sql authentication or NTLM)
configuration.database.ng.authenticationtype=INTEGRATED

# user and password. The password is encrypted.
# They are required for authenticationtype = USER and ignored for authenticationtype = INTEGRATED
configuration.database.ng.user=
configuration.database.ng.password=
configuration.database.ng.password.plaintext=

# optional settings to use failover
configuration.database.ng.failover.server=
# for non sqlserver or not using named instances, leave instance blank
configuration.database.ng.failover.instance=
# the port. Can be blank if using named instance
configuration.database.ng.failover.port=
configuration.database.ng.alwayson=true

# connection settings
core.jdbc.connection.pool.minSize=100
core.jdbc.connection.pool.maxSize=300
hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
jdbc.database_type=SQL_SERVER

# db sizing settings
# db.size.install units are MB
db.size.install=1024

# sqlserver encryption settings. If encrypt is not set, the next 3 settings are ignored.
#configuration.database.ng.encrypt=true
#configuration.database.ng.trustServerCertificate=false
#configuration.database.ng.trustStore=
#configuration.database.ng.trustStorePassword=

# postgres encyption settings. If ssl is not set, the next 2 settings are ignored.
configuration.database.ng.ssl=
#configuration.database.ng.sslfactory=false
configuration.database.ng.sslmode=
configuration.database.ng.sslrootcert=

# dataloader transaction timeout. Units are seconds.
#core.dataloader.importexportTimeout=600

# Good Control database settings
configuration.database.gc.type=SQL_SERVER
configuration.database.gc.server=SQLAAG
configuration.database.gc.name=Control
configuration.database.gc.authenticationtype=INTEGRATED
configuration.database.gc.user=
configuration.database.gc.instance=
configuration.database.gc.port=1433
configuration.database.gc.password=
configuration.database.gc.password.plaintext=
configuration.database.gc.failover.server=
configuration.database.gc.failover.instance=
configuration.database.gc.failover.port=
configuration.database.gc.alwayson=false

One workaround to this issue is to create a single-subnet SQL AAG listener for all Blackberry databases. Unlike multi-subnet, however, single-subnet will require the standard 15 minutes replication timeout period between domain controllers (when database fails over from one site to another, it updates the DNS record on DC within the same site.)

I then worked with Blackberry technical support and implemented the solution below.

“Per 0004750613, JI2222962;SQL DB Mirroring/AlwaysOn MultisubnetFailover Support Enhancement is still open and not yet resolved for Good Control. This JIRA Item is in place to eliminate the admin from having to manually add this string post installation.

Please confirm if the availability group listener instead of SQL server name is in the gc-server properties file located in the c:/Good folder along with the multi-subnet string. Change the SQL Server Name: SQLAAG to your availability group listener and append ;multiSubnetFailover\=True after false and restart services.

Example:

db.connection.url=jdbc\:sqlserver\://AvailabilityGroupListener\:1433;databaseName\=dbName;sendStringParametersAsUnicode\=false;multiSubnetFailover\=True

Steps:

1. Backup the gc-server properties file
2. Launch notepad as administrator
3. Browse to and modify the gc-server properties file
   • From: db.connection.url=jdbc\:sqlserver\://SQLAAG\:1433;databaseName\=BlackberryControl;sendStringParametersAsUnicode\=false
   • To: db.connection.url=jdbc\:sqlserver\://SQLAAG\:1433;databaseName\=BlackberryControl;sendStringParametersAsUnicode\=false;multiSubnetFailover\=True
4. Restart UEM instance

For the second issue, I received an error when retrieving user / device info from either NY or NJ UEM instance when UEM database is active in NJ.

2018-01-23 14:54:17,199 ERROR localhost-startStop-1/ com.good.gmc.GmcServer – getInstance() failed: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host SQLAAG, port 1433 has failed. Error: “connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”

com.good.db.dao.DBException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host SQLAAG, has failed. Error: “connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.”

Based on the Affinity Manager log, one of the UEM hosts is not configured for AlwaysOn.

Here’s what the log should show for the DB connection if UEM host is configured for AlwaysOn:

<snip>

<#03>[30000] (01/24 00:00:06.171):{0x111C} [ENV] [DB] Connection String = Server=SQLAAG,1433;MultiSubnetFailover=true;Database=UEM;Network=dbmssocn;Trusted_Connection=Yes

<snip>

So basically, the string MultiSubnetFailover=true is missing from the server.

You can also confirm in the CORE logs if the server is configured for SQL AlwaysOn: Search for – MultiSubnetFailover=true.

The solution to this issue is simple. Run the Blackberry Configuration Tool that we used in part 2 of this series, and select Update the Blackberry UEM database properties.

Go through the same screens for both Blackberry UEM and Control databases.

Check off Enable AlwaysOn Availability Groups or Failover Cluster Instances. Then click Validate. Blackberry services will be restarted if you check off the box Automatically Restart Services.

If the tool is unable to restart the UEM Core service, reboot the server to resolve.

Now, confirm the string is present in Core log on all UEM servers.

2018-01-26T10:31:47.531-0500 – CORE {localhost-startStop-1} none|dal [{}] – INFO Custom c3p0 settings:  jdbcurl -> jdbc:sqlserver://SQLAAG:1433;databaseName=UEM;integratedSecurity=true;MultiSubnetFailover=true;applicationName=BES-CORE;, minPoolSize -> 100, maxPoolSize -> 300, initialPoolSize -> 100, numHelperThreads -> 100, maxStatementsPerConnection -> 20, deferredCloseThreads -> 1, idleConnectionTestPeriod -> 3000, loginTimeout -> 10, checkoutTimeout -> 30000, maxIdleTime -> 3000, maxIdleTimeExcessConnections -> 1800, preferredTestQuery -> SELECT 1, testConnectionOnCheckout -> true, acquireIncrement -> 3, connectionCustomizerClassName -> com.rim.platform.mdm.dal.utils.ConnectionCustomizer, unreturnedConnectionTimeout -> 0, debugUnreturnedConnectionStackTraces -> false, applicationName -> null, dataSourceName -> BES-CORE

jdbc:sqlserver://SQLAAG:1433;authentication=NotSpecified;authenticationScheme=nativeAuthentication;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;TransparentNetworkIPResolution=true;serverNameAsACE=false;sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptive;packetSize=8000;multiSubnetFailover=true;loginTimeout=10;lockTimeout=-1;lastUpdateCount=true;encrypt=false;disableStatementPooling=true;databaseName=UEM;columnEncryptionSetting=Disabled;applicationName=BES-CORE;applicationIntent=readwrite;”,[\r][\n]

You should also review the Affinity Manager log and confirm the present of the string MultiSubnetFailover=true.

<snip>

<#03>[30000] (01/24 00:00:06.171):{0x111C} [ENV] [DB] Connection String = Server=SQLAAG,1433;MultiSubnetFailover=true;Database=UEM;Network=dbmssocn;Trusted_Connection=Yes

<snip>

In part 5 of this series, we will discuss some of the issues and troubleshooting steps you can take to resolve.