Summary

Additional SQL Server connection properties may be added post installation to adjust the Management Server. This article explains how to add some components.

Applies To

Moka5 Management Server 4.0 and later

Details

Additional SQL Server connection properties may be added post installation to adjust the Management Server. Configuration modifications such as using a named instance running on a dynamic port, adding failover partners, may be achieved through manually modifying the SQL Server Connection URL. When SQL Server is used as the database engine, the SQL Server Connection URL string is constructed at installation time. The installer constructs the default SQL Server Connection URL construction URL by substituting values from this template:

jdbc:sqlserver://${databaseaddress}:${databaseport};databaseName=${databasename};selectMode=cursor

For example, using the following values:

• databaseaddress=mysqlserverhost
• databaseport=1433
• databasename=mydbname

Produces a SQL Server Connection URL that looks like this:

jdbc:sqlserver://mysqlserverhost:1433;databaseName=mydbname;selectMode=cursor

See the Microsoft documentation for more information about Building the Connection URL and available connection properties that may be added or modified.

Procedure

Here’s how to manually modify the SQL Server configuration after installation:

1. Install the Management Server against a SQL Server Instance on a static port.
2. Open the /conf/jpa.properties file.
3. Modify the jpa.url property value.
4. Save your changes.
5. Restart the M5ES service to apply the SQL Server Connection URL (jpa.url) change.

Example

Editing the SQL Server connection URL to change to use a named SQL Server instance (see below) and save your changes.

Change jpa.properties from something like this (using the fixed database port of 1433):

#Fri Jan 24 18:23:51 PST 2014
jpa.driver=sqlserver
jpa.password=M5DESe3c4fffb662cd293e8ff67918502f6da
jpa.username=moka5
jpa.url=jdbc\:sqlserver\://moka5server\:1433;databaseName\=vanguard2;selectMode\=cursor

to something like this (using the named instance MYINSTANCE):

#Fri Jan 24 18:23:51 PST 2014
jpa.driver=sqlserver
jpa.password=M5DESe3c4fffb662cd293e8ff67918502f6da
jpa.username=moka5
jpa.url=jdbc\:sqlserver\://moka5server\\MYINSTANCE;databaseName\=vanguard2;selectMode\=cursor

The instance name after the hostname, in place of the port with an escaped backslash in between.

Note: Confirm that your firewalls do not require adjustment, since when SQL Server runs with dynamic ports, SQL Server will listen on any open port.

Troubleshooting

Troubleshoot network and other connectivity issues using an external SQL client from the same Management Server host machine.

For example: Use SQLCMD to validate your configuration from the Management Server host. In the example below, SQLCMD is used to test connectivity:

C:\Users\moka5user>SQLCMD -S tcp:moka5server\MYINSTANCE -U moka5 -P mokafive –Q "SELECT GETDATE()"

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired. Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server

. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.

In the example above, the port to SQL Server is firewalled. After adjusting the firewalls between the SQL Server instance and the Management Server, the next attempt is successful:

C:\Users\moka5user>SQLCMD -S tcp:moka5server\MYINSTANCE -U moka5 -P mokafive -Q "SELECT GETDATE()"

-----------------------

2014-01-24 19:11:12.147

(1 rows affected)