none
HA Setup - SQL Server 2008/2012

    Question

  • Hi,

    we've got an older application with a connection logic in it which cannot be changed. Unfortunatly thats given :-/ It works like, that in case the connection to the SQL server is closed, the application will go in some sort of offline mode and tries to reconnect some minutes later. Now the requirement comes up to mitigate the possiblity of a client connection drop. One idea we came up with was, to utilize VMWare FT (as our servers are all virtualized) - but VMWare's FT Feature is limited to only one vCPU - which is not enough for our workload.

    Next idea, cluster the SQL service - in case of an failover it takes up to 30s to have the service available again. And all current client connections are dropped. Not what we want :-)

    Final idea, put some sort of "proxy" in front of a SQL cluster and make this proxy more available using VMWare FTs. As the proxy is only used to route all SQL statements + data traffic through it - the limit of only 1 vCPU does not matter. In case of a failover happening in the SQL cluster, the proxy should know about the primary being down and route the queries - for the client transparently - to the secondary.

    Is it possible to build a setup like this? I am aware that its more benificial to fix the client, however, thats not possible.

    Any input appriciated :-)

    Cheers, Tom

    Monday, February 20, 2012 10:18 AM

Answers

  • You need developers to code that and look to see how your existing application works and if it can handle this since if the SQL server goes down you will need to have the application that is proxying the connection be able to handle the timeout and re-establish a connection to SQL on the remaining node in the SQL cluster. Neverfails products might be able to help you with this but you might need to develop something yourselves.

    http://www.neverfailgroup.com/


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, February 21, 2012 6:34 AM

All replies

  • http://msdn.microsoft.com/en-us/library/ms190202.aspx

    http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx


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

    Monday, February 20, 2012 11:27 AM
  • Hi,

    simple clustering does not work - and therefor Availability Groups too - as MSDN states "When an availability group failover occurs, existing persistent connections to the availability group are terminated and the client must establish a new connection in order to continue working with the same primary database or read-only secondary database. While a failover is occurring on the server side, connectivity to the availability group may fail, forcing the client application to retry connecting until the primary is brought fully back online. "

    http://msdn.microsoft.com/en-us/library/hh213417%28v=sql.110%29.aspx#CCBehaviorOnFailover

    Please note, that the possibility / chance of a client connection drop is to be mitigated.

    Cheers, Tom

    Monday, February 20, 2012 11:55 AM
  • I don't reaqlly see how having a "proxy" is going to give you any benefit in regards to high availability. The single point of failure is always going to be the SQL server. You need stateful failover which can only be provided in a VM environment such as Vmware FT.

    All the other options are going to require the client to deal with loosing connection to SQL and the client should really be designed to deal with that. If changes to that side are not possible you may be stuck having to go with FT on a single vCPU. Possibly you could offload some of the workload to replicated copies of the database on additional Vmware FT enabled SQL VM's.


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Monday, February 20, 2012 10:23 PM
  • Hi,

    thanks for the answer! "Proxy" seems to be not the right wording - better use "relay". I know e.g. http://sqlrelay.sourceforge.net/ - which is some kind of proxy/relay: takes the client connection and uses an own connection to the SQL server and relay the queries/results through it. However, sqlrelay has no drop in replacement for MSSQL :-/

    VMWare FT is a no go as only one vCPU is not enough for our workload. So the idea of some kind of software which "holds" the client connections and is able to deal with failovers of the backend SQL servers in a way such the clients are not affected.

    Cheers, Tom

    Tuesday, February 21, 2012 6:27 AM
  • You need developers to code that and look to see how your existing application works and if it can handle this since if the SQL server goes down you will need to have the application that is proxying the connection be able to handle the timeout and re-establish a connection to SQL on the remaining node in the SQL cluster. Neverfails products might be able to help you with this but you might need to develop something yourselves.

    http://www.neverfailgroup.com/


    Sean Massey | Consultant, iUNITE

    Feel free to contact me through My Blog, Twitter or Hire Me.
    Please click the Mark as Answer button if a post solves your problem!

    Tuesday, February 21, 2012 6:34 AM