BODS 4.2 repository connection issue with SQL Server alias

[tkoitka]

Hi

I'm having an issue with Data Services intermittently disconnecting from it's repository and not being able to connect again.

The issue is due to the database being on SQL Server 2016 and the server has an alias due to the DR configuration. When I use the actual server name it has no issue but I need to use the alias. It's semi related to the DSN connection property MultiSubnetFailover but for BODS, it doesn't use DSNs.

Has anyone worked with the same setup? What do I need to on the BODS setup to have it work with a database alias and be stable?

[daniel_long_jemena]

Hi Tyler,

I know it's been over 4 years since your post, but I'm facing the exact issue myself. Did you ever solve it?

Regards,

Daniel.

[singcheong]

1. What is the configuration you enter in the database configuration?

2. Which of the BODS component that is intermittently disconnecting? E.g. CMS/CMC, JobServer, DS Designer, DSWC

3. Have you tested using SSMS, or ODBC to use the DB "alias" to simulate it? In other word, are you sure that the issue only experienced in BODS itself? Did you wrote a simple Java DB client program to test out the connection string using DB "alias" to confirm there is no other issue?

4. When it failed to connect, what is the error message?

5. What is the MS SQL Server availability group listener port?

As far as I understand, MS SQL Server connect string parameter "MultiSubnetFailover" is only to make the detection of active DB instance faster, which result in faster login process.

According to following MS doc

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-co...

When configuring an availability group listener, you must designate a port. You can configure the default port to 1433 in order to allow for simplicity of the client connection strings. If using 1433, you do not need to designate a port number in a connection string. Also, since each availability group listener will have a separate virtual network name, each availability group listener configured on a single WSFC can be configured to reference the same default port of 1433.

You can also designate a non-standard listener port; however this means that you will also need to explicitly specify a target port in your connection string whenever connecting to the availability group listener. You will also need to open permission on the firewall for the non-standard port.

If you use the default port of 1433 for availability group listener VNNs, you will still need to ensure that no other services on the cluster node are using this port; otherwise this would cause a port conflict.

If one of the instances of SQL Server is already listening on TCP port 1433 via the instance listener and there are no other services (including additional instances of SQL Server) on the computer listening on port 1433, this will not cause a port conflict with the availability group listener. This is because the availability group listener can share the same TCP port inside the same service process. However multiple instances of SQL Server (side-by-side)should not be configured to listen on the same port.