Database Mirroring, client connections all ODBC so what methods are recommended?

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

database mirroring using certificate? (two principles and one mirror)

Hi, all.

here is the situation.

we designated one server as a mirrored server. this server will host all mirrored databases from different principles in different server using certificate.

1) Server A (Principle) Server B(Mirrored)

2) Server C (Principle) Server B(Mirrored)

I was able to set up mirroring between A and B. But unable to set up mirroring between C and B.

My question is "Can I grant connect on endpoint to two different host logins?"

For example, on the server B

I created two different logins for Server A and C in order to assign different certificates to two principle servers.

For Server A.

create login server_A_login with password = 'djkkajnxks';

go

create user server_A_user for login server_A_login

go

create certificate server_A_cert

authorization Server_A_user

from file = 'c:\certificate\server_a_cert.cer'

go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_a_login];
GO

For Server C.

create login server_C_login with password = 'djkkajnxks';

go

create user server_C_user for login server_c_login

go

create certificate server_c_cert

authorization Server_c_user

from file = 'c:\certificate\server_c_cert.cer'

go

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_c_login];
GO

when I set up like this and try to start mirroring, I am getting error from server c saying, "the network tcp:\serverb.corp.com can not be reached or does not exist"

But mirroring between server a and b is OK. I set up them first, then between c and b.

any ideas?

thanks,

Try to backup the cert from A and put into C, make sure the thumbprint version of the certificate are same. Try to execute SELECT * from sys.certificates to view the thumbprint information. Hope this will help you.

Best Regards,

Hans1982

|||Review the similar information in SQL 2005 BOL http://msdn2.microsoft.com/en-us/ms178578.aspx and additional information http://msdn2.microsoft.com/en-us/ms191140.aspx fyi.

database log growth too fast

Dear all

My database log growth is too fast. I want to stop this situation. I am a rookie in database administration. Please give me some suggestions. Thanks a lot.

Alex

Perhaps you are able to shrink this from time to time within a maintaince plan, or a manually setup jon in SQL Server ?

http://www.aspfaq.com/show.asp?id=2471

HTH, jens Suessmeyer.|||thx