asked
SQL Server 2008 mirroring problem
I have 3 SQL server 2008 servers in a mirrored setup (principal, mirror & witness), using high availability with automatic failover. I'm using Visual Studio 2008 (vb .net) to connect to the server using the following connection string: > Data Source=TestSvr1; Failover Partner=TestSvr2; Initial > Catalog=TestDB;Uid=TempUser;Pwd=TempPassword In code I'm using the System.Data.SqlClient namespace with the SqlConnection class. If I have everything up and running, with the mirror synchronized, I can connect to the principal server with no problems. If I do a failover, so that the mirror now becomes the principal and the principal now becomes the mirror, I still can connect with no problems. However, if the principal server becomes unavailable, due to shutting down, failed network connection, etc, the mirror does become the principal but I can no longer connect to the principal with the connection string using the failover partner. If I swap the datasource and failover partner in the connection string, I connect to the new principal server immediately. There was a Microsoft Support article regarding almost this same issue for SQL Server 2005 (see below): > Article ID: 912151 - Last Review: > December 3, 2007 - Revision: 2.4 FIX: > The connection may time out before the > first try to connect to the failover > partner server when you try to connect > to a SQL Server 2005 mirrored database > by using an
ADO.NET 2.0-based > application The hotfix mentioned will not install on my client (XP Pro SP3, .NET framework 2.0 SP2, 3.0 SP2 and 3.5 SP1 all installed). The failover is supposed to be automatic in this setup and it IS, unless the principal server becomes unavailable. Has anyone out there come across this problem? How did you resolve the issue? Thanks in advance, Harry
While what I suggest is pretty cheesy, it will work. The **SqlConnection** instances support **ConnectionTimeout** property. This is different from the **CommandTimeout** of the **SqlCommand** instances which determine the number of seconds for queries' timeouts. So if you define 2 connection strings in your config file (or whatever is the place where you store them), you can try something like this: string firstConnection = ConfigurationManager.ConnectionStrings[0].ConnectionString; string secondConnection = ConfigurationManager.ConnectionStrings[1].ConnectionString; using (SqlConnection cn = new SqlConnection(firstConnection)) { try { cn.Open(); } catch { cn.ConnectionString = secondConnection; cn.Open(); } using (SqlCommand cm = new SqlCommand()) { cm.CommandType = CommandType.StoredProcedure; cm.CommandText = "your_proc_name"; cm.Connection = cn; // etc } // cm is disposed :) cn.Close(); } // cn is disposed Second conection string should have the server names flipped of course. **ConnectionTimeout** property is readonly, it has to be present in the connection string itself. If you set it to a rather small value (as default of 15 seconds is too long) you will only end up with the small hickup when the scenario you describe takes place. Oleg
