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
  • 1
  • 5

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,526
  • 1
  • 33
  • 122
  • Thank you for your reply. In fact we have done what you described above. But what I am interested in is if I can still use the failover partner in the connection string such that application can automatically try to talk to the failover partner in case of primary server down, without having database mirroring. Thanks – MidTierDeveloper Apr 11 '18 at 14:51
  • @MidTierDeveloper Yes that is exactly what happens, however you need to change the thought process slightly, Using windows failover clustering you will use the Virtual IP in your connection string, the quorum is what will handle the failover, need to also do a little research into using synchronous vs asynchronous, also be aware that in the event of a failover your secondary will become your new primary, if you need to fail back it is a manual process. – Anthony Fornito Apr 11 '18 at 15:00
  • I know that with high availability group i can use the group listener with a virtual IP and in my connection string i just have to specify the virtual IP and failover will be taken care of by the HA groups. In fact we have it set up and tested and everything is working as expected. But what I am testing is that there are two active sql server/database, not participating in the HA group or database mirroring.Yet on the application level I want to specify failover partner in the connection string hoping that the application can try to talk to second server in the event of first server down. – MidTierDeveloper Apr 11 '18 at 15:18
  • @MidTierDeveloper I am sure how you would do that without mirroring and SQL 2016 does not support mirroring anymore, the only way I can think of to do it would be powershell scripting, but... If you are going to use SQL 2016 Ent why reinvent the wheel – Anthony Fornito Apr 11 '18 at 15:44
  • I am not trying to reinvent the wheels because the current design is that the two instances will be syncing with each other using Peer to Peer Replication. But the on application level, it has to know which instance to talk to. So instead of writing application logic that determines the instance to talk to, I hope to elevate the use of failover partner in the connection string. – MidTierDeveloper Apr 11 '18 at 17:24
  • Ok good luck with that, what I am trying to tell you is the technology does not exist for the version of SQL you are using (2016) – Anthony Fornito Apr 11 '18 at 21:48
0

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
  • 1