How to specify port in JDBC connection URL for failoverPartner server in SQL Server

2
votes

I'm trying to add a server as a failover for SQL Server and it is not using the port 1443, I'm using the port 2776. I'm trying to specify it but everything I tried didn't work. How could I do that?

private String url = "jdbc:sqlserver://server1:2776;DatabaseName=db;failoverPartner=server2";

I've tried the following configs, but none of them worked.

  1. ...failoverPartner=server2:2776
  2. ...failoverPartner=server2,2776
  3. ...failoverPartner=server2\\db

But everytime I get exception.

  1. com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host server2, port 1433 has failed. Error: "connect timed out. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

  2. com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host server2:2776, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

  3. com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host server2, 2776, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.".

1
javasql-serverjdbcfailoverdatabase-mirroring
I added tags failover and database-mirroring as I assume that's what you're working with... correct? – TT.
Does this answer help you? Not an identical question, but it might have something to do with the instance name missing from the failoverPartner name. – TT.
Also, did you read through this link: Using Database Mirroring (JDBC)? Maybe there's some pointers there? – TT.
Thanks, @TT. I'm thinking that I need the instanceName to make this work, but I didn't receive it yet to test. I've checked this, but didn't help much. – davis

1 Answers

1
votes

From the documentation Connecting to SQL Server with the JDBC Driver - Setting the Connection Properties documentation, property failoverPartner:

Note: The driver does not support specifying the server instance port number for the failover partner instance as part of the failoverPartner property in the connection string. However, specifying the serverName, instanceName and portNumber properties of the principal server instance and failoverPartner property of the failover partner instance in the same connection string is supported.

Worded very confusingly, but it looks like you can't specify a port number for the failover server.

From KB-2284190 From Microsoft (Applications cannot connect to a mirror partner server when using port number in the failoverPartner attribute of the connection string ) I'm pretty much reading the same:

CAUSE

Both the error messages occur due to the fact that SQL Server JDBC drivers (all versions) do not support parsing of port number for FailoverPartner connection string attribute and rely on DNS and SQL Server Browser service (for named instances only) to resolve the connection information for the partner server. In environments wherein the conditions discussed in the Symptoms sections are met, the JDBC driver is not able to resolve the partner server information and hence you get the error message discussed above.

This behavior of SQL mirroring infrastructure is by design. For this reason, Microsoft JDBC Driver version 3.0 supports failover partner attribute value only in the format <server_name>[\<SQL_Server_instance_name>].

RESOLUTION

To work around this problem, use one of the following methods in environments where database mirroring is involved:

  • For default instances of SQL server that are part of mirroring configuration ensure that they are listening on the default port 1433 for TCP connections.

  • For named instances, ensure that SQL browser service is running and port 1434 is not blocked on the network and server B is not configured as a hidden instance.

In conclusion, do one of the following:

  • Specify the failover server to listen on port 1433 for TCP.
  • Make a named instance and specify the failoverPartner as server_name\instance_name. Make sure SQL Server Browser service is running and port 1434 is not blocked .