Confusion about Transparent Client Redirection with Database Mirroring

Yesterday I was preparing an info session for my customer on database mirroring.
I came to the part where I had to explain how the developers can use database mirroring in their application code, which is called Transparent Client Redirection.  As there is a lot of confusion about the use of the “Failover Partner”, I want to clarify this in this blog.

Implicit mode

When you want to use this mode, actually, you don’t need to change anything in your connection string.
I thought, that you also need to specify the “Failover Partner” parameter, but that’s not the case.
Here is how it works.  When you setup a connection to the SQL Server that is hosting a mirrored database, the SQL Server provider will cache the name of the mirror server into the memory.  While your connection is up and your primary server should not be reachable anymore, the SQL Server provider will try to connect to the mirror server.

Explicit mode

With this mode, you need to specify the server name for the mirror in your connection string.
You should add the following to your connection string “;Failover Partner=<servername>” (where <servername> is the name of your mirror server).

.NET Example

Data Source=SQLA;Failover Partner=SQLB;Initial Catalog=AdventureWorks2008R2;Integrated Security=True

That failover partner option will be used for the initial connection towards your principal server.  When you startup your application and it cannot connect to the principal server, the SQL Server provider will check the failover partner and will try to connect to that server.  If you didn’t specify the failover partner option, your application will not start up. 

Remark: Keep in mind that the failover partner will be overwritten by the SQL Server provider, with the name it received from the SQL Server for the mirror server.

Conclusion

Always add the failover partner option in your connection string if you want to fully use the advantage of database mirroring.  If you don’t specify it, and your database is not available, transparent client redirection will not work and your application will not startup.
And you must admit, it’s not a very time consuming job to modify your connection string Knipogende emoticon

More information can be found on http://technet.microsoft.com/en-us/library/cc917713.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *