Database Mirror and Failover

Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when I plug out the ethernet cable from SQL1 the whitness is detecting the failure an is switching over to SQL2 and so on. The running application is connecting to SQL2 after the failure. In case of a restart of the application the client tries to connect to SQL1, which is not reachable. As I'm expecting there should be a connect to SQL2 after the sqlclient can't reach SQL1 but with netstat -na I only see connection attempts to SQL1. From a former posting I got the Information about some existing connections to SQL1, but I made a restart of the client so be sure that there is no existing connection to SQL1. When both SQL-Servers are reachable and I do a manual failover everything is working fine, so I think database mirroring only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/
Database mirroring works either way depending on which mode you're in. It's
the automatic failover in high safety mode that may or may not meet your
needs. Specifically, not the failover part but rather the new client
connections part post failover (as per your scenario).
Automatic failover only works if your client's data access layer (SNAC or
ADO/ODBC.NET) has cached both server names. This happens in the first
connection attempt on the server. This is usually the primary server unless
you have specific code in your app (just a few simple lines) to test for
both. Many DB Mirroring implementations are "as-is" using exising apps
without code tweaks so they connect to their "original" primary as per
normal ops but benefit from automatic reconnect in a failover.
However, if the primary server was down before the client connects for the
first time, it would not know there is a mirror server to connect to which
is what you're seeing. Whether it's a new client connection or a re-started
app/web server, it's the same effect; you don't have the mirror server name
cached so the app/web server or client doesn't know it exists.
joe.
"Christian Grbner [MVP]" <newsgroups@.groeby.net> wrote in message
news:uunWT7wuHHA.2360@.TK2MSFTNGP06.phx.gbl...
Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two
SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when
I plug out the ethernet cable from SQL1 the whitness is detecting the
failure an is switching over to SQL2 and so on. The running application is
connecting to SQL2 after the failure. In case of a restart of the
application the client tries to connect to SQL1, which is not reachable. As
I'm expecting there should be a connect to SQL2 after the sqlclient can't
reach SQL1 but with netstat -na I only see connection attempts to SQL1. From
a former posting I got the Information about some existing connections to
SQL1, but I made a restart of the client so be sure that there is no
existing connection to SQL1. When both SQL-Servers are reachable and I do a
manual failover everything is working fine, so I think database mirroring
only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User
ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/