What is the methodology for using SQL Native Client Transparent Client Redirect and mirroring?

Question

I've never used mirroring, clustering or other failover techniques. But am investigating how easy it is to adapt my DAL so that SQLNativeClient transparent client redirect will work for us if my customers decide to use mirroring with or without a Witness.

Can someone explain the pragmatic process for a client application that might sit on hundreds of desktops that will connect to an instance which is mirrored and may failover?

I'm thinking of a zero maintenance approach here for those 100 desktop machines. My current thoughts are that if the process of discovery isn't automatic, that I would have to have an internet/intranet file/service that described which server was the principal and which was the mirror, which applications could read from.

Background: I have read the multiple articles, relating to using SQL_COPT_SS_FAILOVER_PARTNER connection attribute, and that you have to specify the mirror in the connection string to alllow SQLNCLI Transparent Client Redirect, but this all seems a bit back to front. Why does the programmer or end user have to get involved? The network infrastructure could change.

I would hope that an attempted connect to either the principal OR mirror OR witness would re-route me to the correct principal and just 'know' what the mirror was. I understand a witness can manage multiple Database Mirroring Sessions, so something else might be needed.

So, how do I discover the mirror or principle server to start off with? I don't want the user to enter it, as it could change. Do I have to connect first to the running principal, extract the registered mirror from the principal, and then reconnect using those parameters, or can I set the connection attirbute later?

I look forward to some enlightenment!

Answer 1

First lets take the witness out of the SQL Client equation. The witness is only an affair between the two partners involved in mirroring and does not have to accept client connections. Having the witness accept the client connection and 'redirect' appropriately would require the entire client connection infrastructure to be set up in place on the witness (primarily logins and permissions) and would require the witness to be reachable by the client (at IP level). If you think setting up the logins and permissions is trivial, then think about why MSDN has a dedicated article on this topic: Setting Up Login Accounts for Database Mirroring (hint: 'orphaned users').

Next lets consider how would the client reach the principal OR mirror. Say you only specify one server name in the client connection, the principal, and you connect to it and he'll redirect you to the 'mirror' (actually, to the new principal since the roles swapped and he is now the 'mirror'). This would work, but only in the most uninteresting case: when the principal and mirror are both online and have swapper roles. This is no a disaster recovery scenario, the interesting case is when the principal suffered a catastrophic failure and is unavailable. Ins such a case the former principal is unreachable and the client trying to connect would simply get back cricket chirps, there simply isn't a listener there to redirect him to the former mirror (current principal). This is why the client must know the names of both the principal and mirror.

Now that being said, of course there is nothing preventing you from having a central repository of mirroring pairs and have the client connect to the repository first and retrieve the names of the principal and mirror. You're left with the somehow ambiguous task of discovering the repository and the task of keeping the repository up to date (achievable by leveraging Event Notifications on the Database Mirroring State Change event (EN can deliver remotely to a central repository, see routing). The last task is to make the repository highly available, and again mirroring is the likely solution.