2

I have set up two SQL server instance with IPs say x.x.x.x and y.y.y.y. On the application level when specifying connection to SQL server, can I specify the connection string as below

<connection-url>jdbc:sqlserver://x.x.x.x:port; databaseName=productionDB; failoverPartner=jdbc:sqlserver://y.y.y.y:port</connection-url>

I have tried to play around it and however when I manually disable SQL server 1 database, on the application level it seems like it is indeed trying to connect to second server but instead it fails with

connection to host y.y.y.y, port 1433 has failed. Error:" null. 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 the TCP connections to the port are not blocked by a firewall.

I know the connection properties such as user, password are all the same across two SQL instance because if I restart the application above with two IPs swapped everything still works. Also I know that failover Partner should only be used when database mirroring is set up. However, according to this article, it seems feasible without underlying mirroring database set up.

MidTierDeveloper
  • 49

2 Answers2

3

Mirroring has been deprecated in SQL 2016

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016

You will have use SQL availability groups.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

It is pretty easy to setup and works well.

One of the better and compete walkthroughs, you need to change some things however it is pretty good.

http://blog.fedenko.info/2016/06/sql-server-2016-always-on-availability.html

Your connection string wont change much only need to change connection IP and add the security parameters on the back.

Anthony Fornito
  • 9,596
1

SQL 2016 Standard using AlwaysOn Basic - In a couple of instances now, we have had to use a Failover Partner because Either SQL or the DC is taking too long to respond to the request. So, in our connection strings for our applications we have "Data Connection=SERVERNAME\NAMEDINSTANCE;Failover Partner=SERVERNAME2\NAMEDINSTANCE;". Being basic with only the two nodes, this seems to function correctly.

Chris Lee
  • 11