Migrating to SQL 2005: failover

Showing posts with label failover. Show all posts

Friday, March 30, 2012

Mirroring Failover/Failback

I have been using Mirroring now for a while and put the system into
production now after applying SQL sp 1.
We have a setup with a witness, principal and mirror with certificates (the
witness is on a webserver in a different domain, so we can't use integrated
security).
Automatic failover works fine if we take down the principal. But when the
principal is up and running again, no automatic failback occur. If this by
design, or is there something I've missed out in the configuration?
Per SchjetneThe roles for mirroring are soft roles. So, you cannot define a preferred node (like you can for a
cluster). If you want to fail back, you have to ask for it (ALTER DATABASE).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into production now after applying
>SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates (the witness is on a
> webserver in a different domain, so we can't use integrated security).
> Automatic failover works fine if we take down the principal. But when the principal is up and
> running again, no automatic failback occur. If this by design, or is there something I've missed
> out in the configuration?
> Per Schjetne
>|||"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
Sorry, there isn't an option for automatic failback. You will have to do
that manually.
--
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp|||If you think about it, failing back will cause all client connections to be
dropped again so you probably don't want SQL Server to decide when is a good
time to fail back. It's probably much better if you pick a time when the
change won't affect too many users.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>I have been using Mirroring now for a while and put the system into
>production now after applying SQL sp 1.
> We have a setup with a witness, principal and mirror with certificates
> (the witness is on a webserver in a different domain, so we can't use
> integrated security).
> Automatic failover works fine if we take down the principal. But when the
> principal is up and running again, no automatic failback occur. If this by
> design, or is there something I've missed out in the configuration?
> Per Schjetne
>|||I agree on that. Is there a way to query any system tables telling me which
database is acting as principal vs mirror? Is the witness aware of this, or
is it just monitoring which server is replying, being able to do a failover?
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
> If you think about it, failing back will cause all client connections to
> be dropped again so you probably don't want SQL Server to decide when is a
> good time to fail back. It's probably much better if you pick a time when
> the change won't affect too many users.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||There's an event that you can monitor to be notified when the state changes.
The sys.dm_database_mirroring view can be used to determine the mirroring
state of all the databases of an instance. The
sys.dm_database_mirroring_witnesses can be used from the witness to see
which server is the primary.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>I agree on that. Is there a way to query any system tables telling me which
>database is acting as principal vs mirror? Is the witness aware of this, or
>is it just monitoring which server is replying, being able to do a
>failover?
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>|||That's not exactly correct.
The reason it did not failback, is because that database is no longer the
principal. Failover causes a role reversal. When it fails over to the
mirror, that database is promoted to a principal and begins serving the
database. When the other database comes online, its role is automatically
demoted to mirror and the transactions start flowing back to it.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eDV9lIeaGHA.4936@.TK2MSFTNGP05.phx.gbl...
> The roles for mirroring are soft roles. So, you cannot define a preferred
> node (like you can for a cluster). If you want to fail back, you have to
> ask for it (ALTER DATABASE).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when the
>> principal is up and running again, no automatic failback occur. If this
>> by design, or is there something I've missed out in the configuration?
>> Per Schjetne
>|||Thanks! Just what I needed. By the way, the correct tablename is:
sys.database_mirroring_witnesses
Per :-)
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:%23bsLDGkaGHA.3720@.TK2MSFTNGP03.phx.gbl...
> There's an event that you can monitor to be notified when the state
> changes.
> The sys.dm_database_mirroring view can be used to determine the mirroring
> state of all the databases of an instance. The
> sys.dm_database_mirroring_witnesses can be used from the witness to see
> which server is the primary.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
> news:OLn03SiaGHA.2368@.TK2MSFTNGP03.phx.gbl...
>>I agree on that. Is there a way to query any system tables telling me
>>which database is acting as principal vs mirror? Is the witness aware of
>>this, or is it just monitoring which server is replying, being able to do
>>a failover?
>>
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:%23J5%23JJhaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>> If you think about it, failing back will cause all client connections to
>> be dropped again so you probably don't want SQL Server to decide when is
>> a good time to fail back. It's probably much better if you pick a time
>> when the change won't affect too many users.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Per Schjetne" <newsuser1@.gdconsult.no> wrote in message
>> news:uo3Ac4caGHA.3736@.TK2MSFTNGP04.phx.gbl...
>>I have been using Mirroring now for a while and put the system into
>>production now after applying SQL sp 1.
>> We have a setup with a witness, principal and mirror with certificates
>> (the witness is on a webserver in a different domain, so we can't use
>> integrated security).
>> Automatic failover works fine if we take down the principal. But when
>> the principal is up and running again, no automatic failback occur. If
>> this by design, or is there something I've missed out in the
>> configuration?
>> Per Schjetne
>>
>>
>

Mirroring Failover leaves 2 databases as Principal

We have seven databases mirrrored on our production server. Two of
these failed over, one correctly. The other database is marked as
"Principal/Disconnected" on BOTH the primary and secondary servers. I
can browse the tables in each database. Obviously this is not meant to
happen - one of the databases should be in Restoring mode.Transactions
are only going into the one database - on the Primary server. Has
anyone come across this before? Any ideas on how to proceed?
If I attempt to pause mirroring on the primary server it fails with
the message "The database mirror is busy, re-issue the command later".
I can see this getting really bad if I am unable to (at worst) remove
mirroring from either database due to them thinking that they are the
principal and cannot connect to the other mirrored database.
(both servers on SP2.)On Sep 19, 7:03 pm, Calculated <sarahjco...@.gmail.com> wrote:
> We have seven databases mirrrored on our production server. Two of
> these failed over, one correctly. The other database is marked as
> "Principal/Disconnected" on BOTH the primary and secondary servers. I
> can browse the tables in each database. Obviously this is not meant to
> happen - one of the databases should be in Restoring mode.Transactions
> are only going into the one database - on the Primary server. Has
> anyone come across this before? Any ideas on how to proceed?
> If I attempt to pause mirroring on the primary server it fails with
> the message "The database mirror is busy, re-issue the command later".
> I can see this getting really bad if I am unable to (at worst) remove
> mirroring from either database due to them thinking that they are the
> principal and cannot connect to the other mirrored database.
> (both servers on SP2.)
My plan was to come in and backup both databases and then restart the
SQL Service on the secondary, hoping that the state would change.
However when I got in to check this (maybe 6 hours later), the
database on the Secondary had elevated itself to be the Principal
(connected) and the Primay server's database was Synchronised/
Restoring. Good. I have since manually failed the databases back over
to primary. All seems ok so far but I will need to check this
throughout the day. (Hope no-one else runs into this!)

Mirroring Failover - Have to run sp_change_users_login

I have mirroring configured (with a witness server) on a database that is
being used by an ASP.NET application. The app uses SQL Authentication, and
the conneciton string includes the Failover Partner statement.
Whenever the database fails over, the app throws the following error: Cannot
open database 'mydb' requested by the login. The login failed. Login failed
for user 'myuser'.
Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
the problem, but only until I fail back over the the primary database, then I
have to run the script again. Every time I fail over (in either direction),
the sp_change_users_login needs to be run again before apps connection using
SQL Authentication will work.
Auto failover with mirroring, isn't really auto failover if I have to run
this script every time.
1) Is there a way to insert a custom script into the autofailover process?
2) Do I need to convert all applications using this db to use Windows Auth
only?
3) Are there any other solutions to this problem?... the first one gave me an error. I didn't think it had gone through.|||... the first one gave me an error. I didn't think it had gone through.|||Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error:
> Cannot
> open database 'mydb' requested by the login. The login failed. Login
> failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database,
> then I
> have to run the script again. Every time I fail over (in either
> direction),
> the sp_change_users_login needs to be run again before apps connection
> using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>|||How do I get the SIDs for logins on the primary?
"Keith Kratochvil" wrote:
> Drop the [SQL] logins on your mirror.
> When you run the CREATE LOGIN script on that server to create the logins,
> make sure that you specify the SID of that particular user that is within
> the primary server.
>
> --
> Keith Kratochvil
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
> >I have mirroring configured (with a witness server) on a database that is
> > being used by an ASP.NET application. The app uses SQL Authentication, and
> > the conneciton string includes the Failover Partner statement.
> >
> > Whenever the database fails over, the app throws the following error:
> > Cannot
> > open database 'mydb' requested by the login. The login failed. Login
> > failed
> > for user 'myuser'.
> >
> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> > the problem, but only until I fail back over the the primary database,
> > then I
> > have to run the script again. Every time I fail over (in either
> > direction),
> > the sp_change_users_login needs to be run again before apps connection
> > using
> > SQL Authentication will work.
> >
> > Auto failover with mirroring, isn't really auto failover if I have to run
> > this script every time.
> >
> > 1) Is there a way to insert a custom script into the autofailover process?
> >
> > 2) Do I need to convert all applications using this db to use Windows Auth
> > only?
> >
> > 3) Are there any other solutions to this problem?
> >
>
>|||select name, sid from master..syslogins
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82944D2B-DD3F-44F7-A7D7-495C446474B6@.microsoft.com...
> How do I get the SIDs for logins on the primary?
> "Keith Kratochvil" wrote:
>> Drop the [SQL] logins on your mirror.
>> When you run the CREATE LOGIN script on that server to create the logins,
>> make sure that you specify the SID of that particular user that is within
>> the primary server.
>>
>> --
>> Keith Kratochvil
>>
>> "Dan" <Dan@.discussions.microsoft.com> wrote in message
>> news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>> >I have mirroring configured (with a witness server) on a database that
>> >is
>> > being used by an ASP.NET application. The app uses SQL Authentication,
>> > and
>> > the conneciton string includes the Failover Partner statement.
>> >
>> > Whenever the database fails over, the app throws the following error:
>> > Cannot
>> > open database 'mydb' requested by the login. The login failed. Login
>> > failed
>> > for user 'myuser'.
>> >
>> > Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser'
>> > fixes
>> > the problem, but only until I fail back over the the primary database,
>> > then I
>> > have to run the script again. Every time I fail over (in either
>> > direction),
>> > the sp_change_users_login needs to be run again before apps connection
>> > using
>> > SQL Authentication will work.
>> >
>> > Auto failover with mirroring, isn't really auto failover if I have to
>> > run
>> > this script every time.
>> >
>> > 1) Is there a way to insert a custom script into the autofailover
>> > process?
>> >
>> > 2) Do I need to convert all applications using this db to use Windows
>> > Auth
>> > only?
>> >
>> > 3) Are there any other solutions to this problem?
>> >
>>|||Google for sp_help_revlogin. Run this periodically on the source server so you have a script
containing the proper SID for each login.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:0BD0F8B7-A517-4707-B321-3D48F2B77EFF@.microsoft.com...
>I have mirroring configured (with a witness server) on a database that is
> being used by an ASP.NET application. The app uses SQL Authentication, and
> the conneciton string includes the Failover Partner statement.
> Whenever the database fails over, the app throws the following error: Cannot
> open database 'mydb' requested by the login. The login failed. Login failed
> for user 'myuser'.
> Running EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser' fixes
> the problem, but only until I fail back over the the primary database, then I
> have to run the script again. Every time I fail over (in either direction),
> the sp_change_users_login needs to be run again before apps connection using
> SQL Authentication will work.
> Auto failover with mirroring, isn't really auto failover if I have to run
> this script every time.
> 1) Is there a way to insert a custom script into the autofailover process?
> 2) Do I need to convert all applications using this db to use Windows Auth
> only?
> 3) Are there any other solutions to this problem?
>

Mirroring confusion.

SQL2K5
SP1
I am testing out Synchronous Mirroring and have a question about failover.
It seems the only way I can failover to the Mirror box is to log onto the
Principal box and hit the Failover button? Isn't the whole point of
mirroring to be able to handle the Principal not being available? Is there
another way? To clarify, I have no witness and am attempting a manual
failover.
TIA, ChrisRHi Chris,
I believe the option you are looking for is as follows from bol:
1.. Connect to the mirror server.
2.. Issue the following statement:
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
where <database_name> is the mirrored database.
The mirror server immediately transitions to principal server, and
mirroring is suspended.
Regards
Jamie
"ChrisR" <NotAChance@.ms.com> wrote in message
news:O9tTWmHyGHA.4232@.TK2MSFTNGP05.phx.gbl...
> SQL2K5
> SP1
> I am testing out Synchronous Mirroring and have a question about failover.
> It seems the only way I can failover to the Mirror box is to log onto the
> Principal box and hit the Failover button? Isn't the whole point of
> mirroring to be able to handle the Principal not being available? Is there
> another way? To clarify, I have no witness and am attempting a manual
> failover.
> TIA, ChrisR
>sql

Mirroring confusion.

I am testing out Synchronous Mirroring and have a question about failover. It seems the only way I can failover to the Mirror box is to log onto the Principal box and hit the Failover button? Is the whole point of mirroring to be able to handle the Principal not being available? Is there another way?

TIA, ChrisRIn the event of a failure, the "witness" does that automagically.

-PatP|||Yes, but I am using "no witness" mirroring.|||You have to have a witness server to automatic failover.
The witness can be a stripped down server running SQL Server Express. It does not need to be a powerful platform or have a licensed install.|||Yes, but I am attempting a manual failover.

Mirroring confusion.

Mirroring Client

Hello,
When a failover occurs can clients be automatically redirected to the active
instance of a db or does this have to be done with code?
Thanks in advance!
Mark,
When the database failover occurs your sessions will be disconnected. You
need to update your application to automatically fail over when this database
failover occurs.
Here is how you can do it.
Implementing Application Failover with Database Mirroring
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
Hope this helps,
Ben Nevarez
"Mark" wrote:

> Hello,
> When a failover occurs can clients be automatically redirected to the active
> instance of a db or does this have to be done with code?
> Thanks in advance!
>
|||When you have a session connected to a database (principal) and the database
fails over to the mirror, your session will be disconnected. You can open a
new session and it will now be connected to the new principal (that was the
mirror before). This requires no application changes and perhaps only will
need the failover partner clause on the connection string.
But if you need application automatic failover you can implement the retry
logic described in the best practices article I mentioned here.
Hope this helps,
Ben Nevarez
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Mark,
> When the database failover occurs your sessions will be disconnected. You
> need to update your application to automatically fail over when this database
> failover occurs.
> Here is how you can do it.
> Implementing Application Failover with Database Mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
> Hope this helps,
> Ben Nevarez
>
>
> "Mark" wrote:

Mirroring Client

Hello,
When a failover occurs can clients be automatically redirected to the active
instance of a db or does this have to be done with code?
Thanks in advance!Mark,
When the database failover occurs your sessions will be disconnected. You
need to update your application to automatically fail over when this database
failover occurs.
Here is how you can do it.
Implementing Application Failover with Database Mirroring
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
Hope this helps,
Ben Nevarez
"Mark" wrote:
> Hello,
> When a failover occurs can clients be automatically redirected to the active
> instance of a db or does this have to be done with code?
> Thanks in advance!
>|||When you have a session connected to a database (principal) and the database
fails over to the mirror, your session will be disconnected. You can open a
new session and it will now be connected to the new principal (that was the
mirror before). This requires no application changes and perhaps only will
need the failover partner clause on the connection string.
But if you need application automatic failover you can implement the retry
logic described in the best practices article I mentioned here.
Hope this helps,
Ben Nevarez
"Ben Nevarez" wrote:
> Mark,
> When the database failover occurs your sessions will be disconnected. You
> need to update your application to automatically fail over when this database
> failover occurs.
> Here is how you can do it.
> Implementing Application Failover with Database Mirroring
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/implappfailover.mspx
> Hope this helps,
> Ben Nevarez
>
>
> "Mark" wrote:
> > Hello,
> >
> > When a failover occurs can clients be automatically redirected to the active
> > instance of a db or does this have to be done with code?
> >
> > Thanks in advance!
> >

Wednesday, March 28, 2012

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
>

Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NET
web application. The web appliation uses SQL Authentication to connect to the
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?
Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NET
web application. The web appliation uses SQL Authentication to connect to the
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
--
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mirroring - Have to run sp_change_users_login after failover

I have mirroring set up and working on a database that is used for an ASP.NE
T
web application. The web appliation uses SQL Authentication to connect to th
e
database. The mirroring is set up with a witness, and the connection string
includes "Failover Partner=myfailoverserver;".
When I'm running on the primary database, the app works great. When I do a
manual failover, the app crashes with the following error: "Cannot open
database "mydb" requested by the login. The login failed.
Login failed for user 'myuser'".
If I then run "EXEC sp_change_users_login 'Update_One', 'myuser', 'myuser';"
everything works fine again, untill I fail over back tot he primary server.
Then I have to run sp_change_users_login again.
The mirroring auto failover, isn't really auto failover if I have to
manually run this script whenever it fails over.
1) Is there a way to add to the failover script that is run whenever there
is an automatic failover?
2) Do I need to recode my app to just use windows authentication?
3) Are there any other solutions to this problem?Drop the [SQL] logins on your mirror.
When you run the CREATE LOGIN script on that server to create the logins,
make sure that you specify the SID of that particular user that is within
the primary server.
Keith Kratochvil
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:030929C7-7486-4E19-B8E6-32074DD4E26F@.microsoft.com...
>I have mirroring set up and working on a database that is used for an
>ASP.NET
> web application. The web appliation uses SQL Authentication to connect to
> the
> database. The mirroring is set up with a witness, and the connection
> string
> includes "Failover Partner=myfailoverserver;".
> When I'm running on the primary database, the app works great. When I do a
> manual failover, the app crashes with the following error: "Cannot open
> database "mydb" requested by the login. The login failed.
> Login failed for user 'myuser'".
> If I then run "EXEC sp_change_users_login 'Update_One', 'myuser',
> 'myuser';"
> everything works fine again, untill I fail over back tot he primary
> server.
> Then I have to run sp_change_users_login again.
> The mirroring auto failover, isn't really auto failover if I have to
> manually run this script whenever it fails over.
> 1) Is there a way to add to the failover script that is run whenever there
> is an automatic failover?
> 2) Do I need to recode my app to just use windows authentication?
> 3) Are there any other solutions to this problem?

Mirroring - Asynchronous mode not available

Hi,
I've configured mirroring on the database and selected the high safety
without automatic failover. It all seems fine.
Now the problem is in the mirroring screen I can't select the High
performance mode (Asynchronous ). I would like to test this option as well. I
know I can't select the automatic failover option cause I don't have a
witness set-up but why I can't select Asynchronous mode? Any ideas? Thanks.
Panos.
Panos.
Hello Panos,
It is only available in Enterprise edition or Developer edition
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> I've configured mirroring on the database and selected the high safety
> without automatic failover. It all seems fine.
> Now the problem is in the mirroring screen I can't select the High
> performance mode (Asynchronous ). I would like to test this option as
> well. I know I can't select the automatic failover option cause I
> don't have a witness set-up but why I can't select Asynchronous mode?
> Any ideas? Thanks.
> Panos.
> Panos.
>

Friday, March 30, 2012

Mirroring Failover/Failback

Mirroring Failover/Failback

Mirroring Failover leaves 2 databases as Principal

Mirroring Failover - Have to run sp_change_users_login

Mirroring Failover - Have to run sp_change_users_login

Mirroring Failover - Have to run sp_change_users_login

Mirroring confusion.

Mirroring confusion.

Mirroring confusion.

Mirroring Client

Mirroring Client

Wednesday, March 28, 2012

Mirroring - Timeout on INSERT or UPDATE after failover

Mirroring - Timeout on INSERT or UPDATE after failover

Mirroring - Timeout on INSERT or UPDATE after failover

Mirroring - Have to run sp_change_users_login after failover

Mirroring - Have to run sp_change_users_login after failover

Mirroring - Have to run sp_change_users_login after failover

Mirroring - Asynchronous mode not available

Migrating to SQL 2005

Blog Archive

About Me