locked
Can I use failover partner in the connection string for non-mirroring SQL server set up? RRS feed

  • Question

  • 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][1], it seems feasible without underlying mirroring database set up.  


      [1]: https://www.brentozar.com/archive/2011/10/my-favorite-connection-string-tips/
    Tuesday, April 10, 2018 3:17 PM

All replies

  • Jasper

    But if you disable one instance do you mean it will be failover to the second one ? How do the instances communicate each other?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, April 11, 2018 5:07 AM
  • Jasper

    But if you disable one instance do you mean it will be failover to the second one ? How do the instances communicate each other?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Uri

    Currently the two instances belong to the same HA group. I understand that in the connection string I can just specifying the group listener's virtual IP. So on the application level it does not have to know the individual IP of the two instances. But instead in the connection string I purposely specifying the IPs of the two instances hoping that the application will know to talk to the second server in case when first server is down which is achieved by doing a manual failover within the HA group. 

    And reason I am doing this is eventually the two instances will not be in the HA group and they will only be synchronizing each other using Peer to Peer Replication. So instead of having application logic that determines which server to talk to, I hope to achieve the same result by utilizing failover partner in the connection string. 

    Thank you


    Wednesday, April 11, 2018 5:20 PM