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
>

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(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(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(CommandBehavior behavior,
String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
C:\Source\WebSolution\Web\Utilities\ConnectionStringTest.aspx.vb:line 42
Thanks,
JodyOne 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(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(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(CommandBehavior
> behavior, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
> C:\Source\WebSolution\Web\Utilities\ConnectionStringTest.aspx.vb:line 42
>
> Thanks,
> Jody
>

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;Databas
e=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;Databas
e=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(SqlException exception,
Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bjec
t
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(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(CommandBehavior behavior,
String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
C:\Source\WebSolution\Web\Utilities\Conn
ectionStringTest.aspx.vb:line 42
Thanks,
JodyOne 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...61141&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;Databas
e=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;Databas
e=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(SqlException exception,
> Boolean breakConnection)
> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
> exception, Boolean breakConnection)
> at
> System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bj
ect
> stateObj)
> at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand
> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
> bulkCopyHandler, TdsParserStateObject stateObj)
> at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
> at System.Data.SqlClient.SqlDataReader.get_MetaData()
> at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
> RunBehavior runBehavior, String resetOptionsString)
> at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(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(CommandBehavior
> behavior, String method)
> at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
> behavior)
> at Web.ConnectionStringTest.Execute(Boolean allowRecursion) in
> C:\Source\WebSolution\Web\Utilities\Conn
ectionStringTest.aspx.vb:line 42
>
> Thanks,
> Jody
>

Mirror Setup

Hello,

I'm trying to setup database mirroring between 3 servers and the details are as follows:

1. All 3 machines reside in the same domain.

2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.

3. All the 3 machines are having their individual service domain accounts.

4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.

5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.

6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.

7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.

8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.

9. On Server 2 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'

10. On Server 1 I'm executing this statement

ALTER DATABASE <dbname>

SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'

ALTER DATABASE <dbname>

SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'

On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."

I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.

Thanks

I just have one question, did you restore the tran logs from principal to mirror using with norecovery option?.......coz you need to restore atleast 1tran log in mirror with norecovery option prior to configuring mirroring......|||

Hello Deepak,

I have restored 2 sets of tlog backups from principal to mirror -- first with norecovery mode and the second one with recovery mode. Still I'm getting the same errors.

Query Window

===========

Msg 1416, Level 16, State 31, Line 3

Database " " is not configured for database mirroring.

Error Log

=======

Error: 1443, Severity: 16, State: 2.

Database mirroring has been terminated for database ' '. This is an informational message only. No user action is required.

Do you have any other workaround for this problem?

Thanks

|||

Hello Deepak,

I have even tried with a sample database on the same set of servers and encountering the same set of errors in query window and error log. Is it something to do with any sort of configuration which I need to do before implementing database mirroring?

Thanks

|||

here is the mistake,

1. you should restore the full backup in the mirror using with norecovery clause

2.you need to restore one tran log in mirror using with norecovery clause

the idea behind restoring the full backups and tran log backups using with norecovery is that you can apply further transaction logs

since you have applied one with norecovey and other with recovery option further tran logs cannot be applied in the mirror...that seems to be the error.........perform the 2 steps as above and then start mirroring.....

|||

Hello Deepak,

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

abybody out there who can troubleshoot my problem?

TIA

|||

well as i said earlier you need to restore the full and subsequent tran log backups in mirror using with norecovery option.

this step has to be performed prior to configuring mirroring....further adding to your problem the error says, that the "database is not configured for mirroring".....which is a clear indication that either your mirror is not restored with norecovery or it is not in Full Recovery i believe.....this is what i know.....

|||

Hello Deepak,

It seems you haven't gone through my earlier messages and here it is

I took 1 full backup and 2 tlog backups from the principal server.

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode

These are the steps I have initiated to configure database mirroring. I have even tested with a sample database following the above steps and still encounter the same error messages.

Thanks

|||

refer this,

On the mirror server

===============

Restored the full backup by keeping the database in No Recovery mode

Restored the first tlog backup by keeping the database in No Recovery mode

Restored the second log backup by keeping the database in Recovery mode > this has to be in norecovery mode but u have restored it in recover mode other steps are fine

|||

Hello Deepak,

Thanks for pointing the problem and I could setup mirroring successfully..

Migration to Yukon

Hi,

Anyone kindly let me know more and clear details about the below 2 statements

1.Remove reference to undocumented system tables/stored procs

2. Remove statements modifying system objects

as are not supported in SQL Server 2005. while migrating from sql server 2000 to YUkon (sql server 2005) the above 2 points need to be taken care. but no much clarity on the above.

kindly help me

Regards

Crish

Hi,

not sure above the first one, which procedure are deprecated but the latter one had some major chnages. The system objects are now in the sys schema,e.g. changing the sysobjects to sys.objects. if you used INFORMATION_SCHEMA views before you don′t need to change anything, because the views were also modified to reflect the changes, if you use direct access to the system tables, you have to change your code.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Thanks much for the information Jens.