As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.
Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.
Regards,
Dominic Baines
I have one customer in production that uses the DNS name change method to switch over the clients after a failover, and so that method does have uses.
Alternatively, you could have the application handle the failover by issuing the redirect to another server.
Thanks,
Mark
|||Sorry Mark but just not an option to recode app connection strings.
Several reasons:
1. Most apps are 3rd party and insist on ODBC only connections (I know old method but that is state of affairs).
2. Recoding ODBC connections means we would have to visit physically or by logon/registry script every client to reconfigure connection when failover occurs. Not practical and bound to miss some.
3. When fail back occurs same problem exists in reverse.
We simply have too many applications and over 50 DB's on 6 pairs of servers in the failover/failback situation (canot use clustering) and 100+ app servers and many many direct connect clients involved. And the desktop clients spread over 5 countries including several timezones and as most everyone else these days also India.
It looks like DNS alias is the option unless anyone has a better one? It was originally coded for older NT4 style domain then migrated to AD, which was interesting but that is a different story.
As this appears to be the case can the failover be used to trigger a script in SQL2005 (that will update the DNS (I can handle that script). How could you detect the failover has occured? Still a bit too new to SQL2005 to know the answer to this one.
(and...thanks for the message on the webcast, Kimberley has got in touch).
Regards,
Dom
|||I understood that the SQL 2005 ODBC provider (SQL Native Client ODBC provider) was part of the SNAC stack and therefore would support failover. Of course the SQL 2005 SNAC bits and pieces would need to be installed on the clients/servers and the connection string(s)/DSNs modified to support a mirror, but that would be a one off operation, albeit maybe a large one.
As per BOL : "The SQL Native Client ODBC driver complies with the Microsoft Win32? ODBC 3.51 specification. The driver supports applications written with the ODBC 2.5 or earlier versions of the ODBC functions in the manner defined in the ODBC 3.51 specification."
Am I missing something?
|||I have marked as answer as basically the solution does provide an answer that will probably assist some.
However, I will carry on using the DNS method for the moment.
There is no scope right now to upgrade the native ODBC drivers as I am 'stuck' with desktops with older drivers and reconfiguring the client driver or connections is not possible as it would also mean rolling out new desktop images.
Regards,
Dom
sql