Mirroring - Timeout on INSERT or UPDATE after failover

I am having some difficulty with getting database mirroring to work properly
under some failover scenarios. Based on the details which I have provided,
are there any futher suggestions that I can try in order to get this working
properly?
I have setup "High safety with automatic failover (synchronous)" mirroring
with 3 servers all running SQL2005 SP1. The database is being
accessed/updated through an ASP.NET website using the following connection
string:
Server=ServerA;Failover
Partner=ServerB;Network=dbmssocn;Database=Test;Uid =TestUser;Pwd=;
ServerM is the Monitor.
The servers, are developer's boxes all running WinXP Pro SP2 with Firewall
Disabled (for the purposes of our testing). If we manage to get the
database mirroring working, the mirroring system will then be setup on
Windows Server 2003 systems. The 3 SQL Services on each server (SQL Server,
SQL Server Browser, and SQL Server Agent) all use the same Domain accout
with administrative priveleges.
The database was originally created in SQL2000 and brought over to SQL2005
using the Copy Database wizard. All Logins were transferred over and tested
to ensure that queries could be executed.
While testing, the queries executed on the website are also being tested
within the SQL Server Management Studio to ensure that the same results are
achieved.
With mirroring setup, here is a list of steps which were taken and the
results:
ServerA (Principal, Synchronized), ServerB (Mirror, Synchronized /
Restoring...)
- Initially, everything works fine without any known problems
(DataSource = ServerA)
- As data is updated, we found that it is replicated properly by
querying a Snapshot on the mirror.
- While testing, we would execute a manual Failover (right-click on
primary database, and select Tasks - Mirror, then click on Failover)
ServerA (Mirror, Synchronized / Restoring...), ServerB (Principal,
Synchronized)
- Further testing indicates that the manual failover was successful
(DataSource = ServerB)
- Data is able to be updated and replicated successfully
- STOP the SQL Service on ServerB (Principal)
ServerA (Principal, Disconnected), ServerB (Not available)
- Able to query data using SELECT statements (DataSource = ServerA)
- INSERT and UPDATE statements timeout (DataSource = ServerA)
- START the SQL Service (and SQL Server Agent) on ServerB
ServerA (Principal, Synchronizing), ServerB (Mirror, Synchronizing /
Restoring...)
- Doesn't matter how long I wait, the state does not change for these
databases which are 500MB in size
- No longer able to query or update data
- STOP SQL Service on ServerA
ServerA (Not available), ServerB (Mirror, Disconnected / In Recovery)
- Cannot do anything to ServerB since it is still the Mirror
- START SQL Service (and SQL Server Agent) on ServerA
ServerA (Principal, Synchronized), ServerB (Mirror, Synchronized /
Restoring...)
- Able to query data using SELECT statements (DataSource = ServerA)
- Able to INSERT/UPDATE data (DataSource = ServerA)
- STOP ServerA
ServerA (Not available), ServerB (Principal, Disconnected)
- Able to query data using SELECT statements (DataSource = ServerB)
- Able to INSERT/UPDATE data (DataSource = ServerB)
- START ServerA
ServerA (Mirror, Synchronizing / Restoring), ServerB (Principal,
Synchronizing)
- Doesn't matter how long I wait, the state does not change for these
databases which are 500MB in size
- Able to query data using SELECT statements (DataSource = ServerB)
- No longer able to update data
- STOP ServerB
ServerA (Mirror, Disconnected / In Recovery), ServerB (Not available)
- START ServerB
ServerA (Mirror, Synchronized / Restoring...), ServerB (Principal,
Synchronized)
- Able to query data using SELECT statements (DataSource = ServerB)
- Able to INSERT/UPDATE data (DataSource = ServerB)
The exception that I have generated when the INSERT/UPDATE fails is as
follows (each line begins with a Tick count):
620852984 Connection String: Server=ServerA;Failover
Partner=ServerB;Network=dbmssocn;Database=Test;Uid =TestUser;Pwd=;
620852984 Command: UPDATE Customer SET LastName = 'Test3' WHERE
UserName='test'
620852984 Data Source: ServerB
620883015 Exception
System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior,
String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior)
at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
C:\Source\WebSolution\Web\Utilities\ConnectionStri ngTest.aspx.vb:line 42
Thanks,
Jody
One additional thing to note is that the database being mirrored contains a
Full Text Index.
I have found another thread which indicates that installing SQL2005 SP2 CTP
will correct the problem. Is there any way around this without installing
SP2?
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=961141&SiteID=17
Jody
"Jody Gelowitz" <jgelowitz@.lalalaleevalley.com> wrote in message
news:%23$Nvb89OHHA.3944@.TK2MSFTNGP06.phx.gbl...
>I am having some difficulty with getting database mirroring to work
>properly under some failover scenarios. Based on the details which I have
>provided, are there any futher suggestions that I can try in order to get
>this working properly?
>
> I have setup "High safety with automatic failover (synchronous)" mirroring
> with 3 servers all running SQL2005 SP1. The database is being
> accessed/updated through an ASP.NET website using the following connection
> string:
> Server=ServerA;Failover
> Partner=ServerB;Network=dbmssocn;Database=Test;Uid =TestUser;Pwd=;
> ServerM is the Monitor.
> The servers, are developer's boxes all running WinXP Pro SP2 with Firewall
> Disabled (for the purposes of our testing). If we manage to get the
> database mirroring working, the mirroring system will then be setup on
> Windows Server 2003 systems. The 3 SQL Services on each server (SQL
> Server, SQL Server Browser, and SQL Server Agent) all use the same Domain
> accout with administrative priveleges.
> The database was originally created in SQL2000 and brought over to SQL2005
> using the Copy Database wizard. All Logins were transferred over and
> tested to ensure that queries could be executed.
> While testing, the queries executed on the website are also being tested
> within the SQL Server Management Studio to ensure that the same results
> are achieved.
>
> With mirroring setup, here is a list of steps which were taken and the
> results:
> ServerA (Principal, Synchronized), ServerB (Mirror, Synchronized /
> Restoring...)
> - Initially, everything works fine without any known problems
> (DataSource = ServerA)
> - As data is updated, we found that it is replicated properly by
> querying a Snapshot on the mirror.
> - While testing, we would execute a manual Failover (right-click on
> primary database, and select Tasks - Mirror, then click on Failover)
> ServerA (Mirror, Synchronized / Restoring...), ServerB (Principal,
> Synchronized)
> - Further testing indicates that the manual failover was successful
> (DataSource = ServerB)
> - Data is able to be updated and replicated successfully
> - STOP the SQL Service on ServerB (Principal)
> ServerA (Principal, Disconnected), ServerB (Not available)
> - Able to query data using SELECT statements (DataSource = ServerA)
> - INSERT and UPDATE statements timeout (DataSource = ServerA)
> - START the SQL Service (and SQL Server Agent) on ServerB
> ServerA (Principal, Synchronizing), ServerB (Mirror, Synchronizing /
> Restoring...)
> - Doesn't matter how long I wait, the state does not change for these
> databases which are 500MB in size
> - No longer able to query or update data
> - STOP SQL Service on ServerA
> ServerA (Not available), ServerB (Mirror, Disconnected / In Recovery)
> - Cannot do anything to ServerB since it is still the Mirror
> - START SQL Service (and SQL Server Agent) on ServerA
> ServerA (Principal, Synchronized), ServerB (Mirror, Synchronized /
> Restoring...)
> - Able to query data using SELECT statements (DataSource = ServerA)
> - Able to INSERT/UPDATE data (DataSource = ServerA)
> - STOP ServerA
> ServerA (Not available), ServerB (Principal, Disconnected)
> - Able to query data using SELECT statements (DataSource = ServerB)
> - Able to INSERT/UPDATE data (DataSource = ServerB)
> - START ServerA
> ServerA (Mirror, Synchronizing / Restoring), ServerB (Principal,
> Synchronizing)
> - Doesn't matter how long I wait, the state does not change for these
> databases which are 500MB in size
> - Able to query data using SELECT statements (DataSource = ServerB)
> - No longer able to update data
> - STOP ServerB
> ServerA (Mirror, Disconnected / In Recovery), ServerB (Not available)
> - START ServerB
> ServerA (Mirror, Synchronized / Restoring...), ServerB (Principal,
> Synchronized)
> - Able to query data using SELECT statements (DataSource = ServerB)
> - Able to INSERT/UPDATE data (DataSource = ServerB)
>
> The exception that I have generated when the INSERT/UPDATE fails is as
> follows (each line begins with a Tick count):
> 620852984 Connection String: Server=ServerA;Failover
> Partner=ServerB;Network=dbmssocn;Database=Test;Uid =TestUser;Pwd=;
> 620852984 Command: UPDATE Customer SET LastName = 'Test3' WHERE
> UserName='test'
> 620852984 Data Source: ServerB
> 620883015 Exception
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding.
> The statement has been terminated.
> at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method,
> DbAsyncResult result)
> at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior
> cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
> behavior)
> at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
> C:\Source\WebSolution\Web\Utilities\ConnectionStri ngTest.aspx.vb:line 42
>
> Thanks,
> Jody
>