1. How to connect client to a Database Mirroring Session with failover aware
A: add FailoverPartner keyword in the connection string, for instance:
"Server=sq01;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
2. why I get timeout error more frequently when I connect to mirrored database with keywords "Failover Partner"?
A: You probably encounter a .Net bug which happens on mirrored database, please refer to the link below
the workaround would be
•Set the time-out connection string to the 150
3. If the "failover partner" parameter is incorrectly setup in the connection string, is the client still aware of the failover, and connect to the mirror(new primary) after failover?
A: The Answer is YES.
when client connect to the primary server first time, it will download the mirror server name, and cache it in client process as failover partner name, it means, even if you set the wrong "Failover Partner" value in the connection string, client still can connect to the correct mirror server when failover happen. for instance,
we have database mirror setup on SQ01 and SQ02, SQ01 is primary, in your connections string, we use SQ03 as failover partner as below, actually sq03 is not existed.
"Server=sq01;Failover Partner=sq03; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
when you failover db from sq01 to sq02, your connection will be failed, but after you reconnect it without changing any connection string, you can still get the data which is from sq02.
so even without specifying the failover partner name in the connection string, client can still access primary or mirror when failover, just like below
"Server=sq01;Failover Partner=; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
Now you know, the words "Failover Partner" is the key in the connection string
4. how about the "Server" keywords in the connection string? if it is wrong, can client still support failover?
A: "Server" is most important in the connection string, here has several situation. let's use the previous sample
primary is sq01, mirror is sq02, sq03 is not existed.
1. "Server=sq03;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
Failed, client can not connect to the server since 02 is mirror and not online.
2. "Server=sq03;Failover Partner=sq01; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
successful, client connected 03 failed, then tried to connect failover partner which is sq01
but it doesn't support failover database from sq01 to sq02. in other words, after client get data from sq01 successfully, if we failover db from sq01 to sq02, client will lost connection, and can not failover to sq02 automatically.
5. last question, just for interesting, if 2 db have same name on sq01 and sq02, both online, does it supports failover
A: No, it will not happen. let's say you have database mydb on sq01 and sq02, both are online without mirroring, you use the connection string below
"Server=sq01;Failover Partner=sq02; Database=mydb; Network=dbmssocn;Trusted_Connection=yes"
then , if the db on sq01 is online, your client can get data from sq01, but if the db on sq01 is not accessible, you will get error below:
Exception calling "Open" with "0" argument(s): "Server sq02, database mydb is not configured for database mirroring."
so it tells us the client provider is aware of mirror 🙂 don't want to cheat it.
database mirror is phasing out from new sql server version, alwayson is new technology of HA now.
