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
>

Minimum AD Rights for LAN Based RS

I understand that one must have certain rights in their role as publisher in
reporting services.
Does anyone know the minimum rights needed to allow a developer to publish
reports? Must they have domain admin rights.
Win 2000 Server running SQL server 2000 and RS on a domain.
Thank you in advance.
P.S. I was hoping to not have to give full Administrator rights to publish
reports.
RichExcept for the fact that members of the local administrators group (note
that I say local) has admin rights to RS, there is no correlation between
the rights / groups someone is in as a member of the domain. Because the
domain adminstrator group is usually a member of the local administrators
group this looks the same. Note that you can definitely give rights to any
domain group you want or to individual users. RS operates based on the role
that the user or group has been given rights to. What I do is create a local
group and then to that group I add the domain groups and users that I want
to have rights. I do this just because it is such a pain to create and
manage a domain group but it can work either way. Here are the roles that
you can assign to a user/group:
Browser May view folders, reports and subscribe to
reports.
Content Manager May manage content in the Report Server. This
includes folders, reports and resources.
My Reports May publish reports and linked reports; manage
folders, reports and resources in a users My Reports folder.
Publisher May publish reports and linked reports to the
Report Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Rich" <rwatson@.brick.net> wrote in message
news:uR60h1MfFHA.3912@.tk2msftngp13.phx.gbl...
>I understand that one must have certain rights in their role as publisher
>in reporting services.
> Does anyone know the minimum rights needed to allow a developer to publish
> reports? Must they have domain admin rights.
> Win 2000 Server running SQL server 2000 and RS on a domain.
> Thank you in advance.
> P.S. I was hoping to not have to give full Administrator rights to publish
> reports.
> Rich
>sql

Min(NULL) shows up as 12/30/1899 in cube

Hi!

I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.

I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.

Any help would be greatly appreciated. Thanks!

Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.

In a relational DB 1 + NULL = NULL

In an OLAP DB 1 + NULL = 1

You might have to change your approach, so possible avenues to explore would be

use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||

So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:

iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.

I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.

|||

That would work too.

You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.

|||

I've now created a calculated member as:

IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])

and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).

Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?

Thanks for all your help!

|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.

Min and Max from one dimension based on Grouping from another dimension

Hi all,

I have 2 Dimensions D1 and D2. They both have a common Attribute "ID".

For one id in D1 there are multiple records in D2. This is like a parent child relationship with Product as parent Dimension and Product category as child dimension. For each product there are multiple product categories.

I want to get the Min and Max from child Dimension for each ID in Parent Dimension.

In sql this can be written as

select Distinct D1.ID,Min(D2.AttributeName) from D1,D2 where

D1.ID=D2.ID group by D2.ID

Can anybody write a MDX based on this?

Thanks

Girija.

Girija,

When there is Parent-Child Relationship between D1 and D2 as described Product and Product Category, I feel there should be Hierarchy in Cube for D1 and D2. And hoping you have set Order By Property to Key which stores ID and then simply you could use FirstChild for Min and LastChild for Max ID.

Bhudev

|||

Hi bhudev,

I gave parent - child reationship just as an example..... There is no hierarchy between those two dimensions.... They are connected only through ID.

Regards...

Girija Shankar

|||

Girija

I'm giving you the way how you can do it. Simply make Calculated Members for these ID under Measures Dimension and apply Min and Max function, I hope you will get yourself.

Bhudev

Migration from SQL 2003 to 2005

I have an SQL database that uses standard tables and stored procedures
- no triggers and functions. I have a Microsoft Excel based front-end
which uses ADO to access the DB to (1) run stored procedures (some SPs
return recordsets and some do not), (2) insert data into tables (3)
edit table data using simple linked grid based forms.
This DB was initially created in SQL 2000 and then installed on a SQL
2003 server - absolutely no issues. Now, I have to upgrade to SQL
2005. I am not concerned about DB Admin issues but only my procedures
should not stop working or ADO calls not working (note that I am NOT
using the .NET platform).
It is probably difficult to give any sure answer with this limited
information but I am mainly looking for standard issues that most SQL
2003 databases will face on upgrading to 2005.
Thanks,
Jay
P.S. I am concerned about things like...SQL 2005 has changed the
syntax for cursors; it does not support regular ADO and you must use
ADO.NET; data type Decimal is not available any more and you must use
Numeric; Basically, things like these that will require me to change
my table structures or procedures.
Jay
Download Upgrade Advisor tool from MS site and run it against SS2000
database, see report file.
"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
>

Migration from SQL 2003 to 2005

I have an SQL database that uses standard tables and stored procedures
- no triggers and functions. I have a Microsoft Excel based front-end
which uses ADO to access the DB to (1) run stored procedures (some SPs
return recordsets and some do not), (2) insert data into tables (3)
edit table data using simple linked grid based forms.
This DB was initially created in SQL 2000 and then installed on a SQL
2003 server - absolutely no issues. Now, I have to upgrade to SQL
2005. I am not concerned about DB Admin issues but only my procedures
should not stop working or ADO calls not working (note that I am NOT
using the .NET platform).
It is probably difficult to give any sure answer with this limited
information but I am mainly looking for standard issues that most SQL
2003 databases will face on upgrading to 2005.
Thanks,
Jay
P.S. I am concerned about things like...SQL 2005 has changed the
syntax for cursors; it does not support regular ADO and you must use
ADO.NET; data type Decimal is not available any more and you must use
Numeric; Basically, things like these that will require me to change
my table structures or procedures.Jay
Download Upgrade Advisor tool from MS site and run it against SS2000
database, see report file.
"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
>|||"Jay" <jay6447@.hotmail.com> wrote in message
news:35224406-0ab6-45a3-a92f-410aed08a23f@.s13g2000prd.googlegroups.com...
> I have an SQL database that uses standard tables and stored procedures
> - no triggers and functions. I have a Microsoft Excel based front-end
> which uses ADO to access the DB to (1) run stored procedures (some SPs
> return recordsets and some do not), (2) insert data into tables (3)
> edit table data using simple linked grid based forms.
> This DB was initially created in SQL 2000 and then installed on a SQL
> 2003 server - absolutely no issues. Now, I have to upgrade to SQL
> 2005. I am not concerned about DB Admin issues but only my procedures
> should not stop working or ADO calls not working (note that I am NOT
> using the .NET platform).
> It is probably difficult to give any sure answer with this limited
> information but I am mainly looking for standard issues that most SQL
> 2003 databases will face on upgrading to 2005.
> Thanks,
> Jay
>
> P.S. I am concerned about things like...SQL 2005 has changed the
> syntax for cursors; it does not support regular ADO and you must use
> ADO.NET; data type Decimal is not available any more and you must use
> Numeric; Basically, things like these that will require me to change
> my table structures or procedures.
>
Hi Jay
There was not a version SQL 2003, so I assume this is Windows 2003 and the
SQL Server did not change?
To help you prepare the upgrade to SQL 2005 look at the upgrade advisor to
see what you may need to change when upgrading the database
http://www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e0...
and the upgrade handbook is worth checking out for the additional steps you
will need to do such as rebuilding indexes, statistics and updating usage
http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx.
John