Cannot get search server connected to SQL cluster env with "Always On Group'

Products

CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction

When re-run pdm_es_rebuild_index.bat , initial_load.log throws the following :

[12:41:17,615][ERROR][loader.source ][main] Database connection could not be established: jdbc:sqlserver://hostnameA:1433;databaseName=mdb; The target database, 'mdb', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. ClientConnectionId:2b7f3625-9df2-491c-8809-0c22764316a6

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827) ~[sqljdbc4-4.0.jar:?]

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012) ~[sqljdbc4-4.0.jar:?]

at java.sql.DriverManager.getConnection(Unknown Source) ~[?:1.8.0_74]

at com.ca.casm.jdbc.DatabasePersister.connect(DatabasePersister.java:148) [search-17.2.0.42.jar:?]

at com.ca.casm.esearch.AbstractLoader.readConfiguration(AbstractLoader.java:348) [search-17.2.0.42.jar:?]

at com.ca.casm.esearch.AbstractLoader.prepare(AbstractLoader.java:144) [search-17.2.0.42.jar:?]

at com.ca.casm.esearch.AbstractLoader.reader(AbstractLoader.java:107) [search-17.2.0.42.jar:?]

at com.ca.casm.esearch.InitialLoader.reader(InitialLoader.java:56) [search-17.2.0.42.jar:?]

at com.ca.casm.esearch.Runner.main(Runner.java:75) [search-17.2.0.42.jar:?]

hostnameA is one of the servers in the cluster we used prior to having "Always On Group" . Once the cluster with "Always on Group" is complete , we need to configure the search server to point to the listener, for example, hostnameABC .

How to get the search server pointing to the listener name here so that pdm_es_rebuild_index.bat can connect to the new SQL server listener ?

Environment

Release : 17.2

Component : SERVICE DESK MANAGER

Resolution

config.yml file under <search server installation home folder>\search\config\ has the information about your SQL server connection information for search server . You need to modify the database host name there accordingly to use/reference the current correct SQL server listener after your SQL server with "Always On group" cluster env is configured :

for example :

database:

# directive: type; mandatory

# which database system to use "oracle"|"mssql"

type: mssql

# directive: host

# database host name or IP address

# example: localhost or 127.0.0.1

host: hostnameABC\MSSQLSERVER

# directive: db; mandatory

# database name

# example: mdb

db: mdb

# directive: user

# database user

user: sa

# directive: password

# password for the database user

password: lM1kG1FJ7SVrYl9wlEOMONvnNNdfNcFeU+Z9r+J5xYaIcCbnrWt/KcIf0OSyYpUG85/Z2nyzi+s=

# directive: port

# port of the database server to use

port: 1433

# directive: query_timeout

# a value for how long an SQL statement is allowed to be executed before it is considered as lost.

# Default is 1800

query_timeout: 1800

modify config.ym file for search server to use virtual DB server name hostnameABC here , for example , instead of the actual DB server name . It will be able to connect to DB then .

Normally you can use microsoft's sqlcmd command to test & verify . Regardless how your SQL server is setup , as soon as sqlcmd can connect to it ( no matter it is actual server or virtual name (listener ) ) , with the same DB connection information in config.yml in search server should also be able to connect also then .