SQL Server Redirection

Question

We are switching from a SQL cluster to a mirrored solution. The problem is that we have a bunch of programs that would have to switch connection strings to handle the failover. Is there any way the we can set up a redirect or proxy that would take any legacy requests and forward them to the mirrored solution?

Answer 1

You can set-up something like pfSense on the old IP address to do load balancing for you

Look here for details http://doc.pfsense.org/index.php/Multi_WAN_/_Load_Balancing#Setting_up_the_pools

Answer 2

No. The client connection must be aware of mirroring to connect properly to the right host, the current principal, and to redirect the connection in case of DBM fail over. Simply redirecting connections to the current principal, via DNS tricks or other means, will be detected by the SSPI authentication as a man-in-the-middle situation and will likely result in failed authentication.

Answer 3

If you don't want to worry about missing any connection string, then Modify the HOST file in the %windows%\system32\drivers\etc folder and add an entry for your old system name with the IP address of the new system. This will ensure that anything referencing the old system by name will be redirected to the new system. I ahve tested this in my Dev environment on a 2 node SQL cluster. THis method works...

And to address the SSPI comment above... There are no authentication issues such as kerberos 3 hop, etc. as long as Active Directory has your SPNs setup properly which were most likely automatically created for you when you setup your SQL CLuster. If you wish to check your environments SPNs for your systems, you can download the Windows Support Tool kit and you can use setSPN.exe from the command line to check using the -L switch option for a list. Or, if you don't wish to download the toolkit, then you can do this form a Domain Controller if you have access to one. Let me know if you need anything else.

Thanks!

-VM