June 7, 2010 at 1:33 pm
We had a mirroring failure earlier today with this error in the SQL ERRORLOG of the primary server:
Error: 1479, Severity: 16, State: 1.
The mirroring connection to "TCP://myServerName:7024" has timed out for database "XXXXXX" after 10 seconds without a response. Check the service and network connections.
We are using mirroring in async mode (high performance). The job that drops and recreates our snapshot once an hour was also failing with this error:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Being new to mirroring, I ran the following to break the mirroring session:
ALTER DATABASE XXXXX
SET PARTNER OFF;
I now have to go through the entire sequence of steps to recreate the mirror on the secondary server: take a full backup on the primary server, restore on secondary with NO RECOVERY etc.
I'm wondering whether there was an easier way I could have approched this.
Anyone have any ideas or links to provide on this?
Basically, my question is how to recover from this failure in future with minimal disruption to the users.
Thanks for any feedback!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 7, 2010 at 2:14 pm
We've has some connection losses to, but the mirroring (primary-mirror-witness) resumed as soon as the connection could be reactivated between principal and mirror.
It caught up very well (sql2005 std)
I know topology is different than yours, but IMO you shouldn't have to intervene to reactivate the mirroring.
I'm still setting up my SQL2008 R2 test instance(s) to get to some mirroring tests in the comming weeks, so I'm only speaking from my perception.
BOL contains a topic "How to: Resume a Database Mirroring Session (Transact-SQL)" at http://msdn.microsoft.com/en-us/library/ms191125.aspx
My guess, as long as you haven't performed the restore with recovery on the mirror db, you should be able to rebuild the mirroring session without having to go trough the full restore procedure, as long as your dblog is usable. (as shown in BOL)
Bol article http://msdn.microsoft.com/en-us/library/ms180801.aspx titled "Removing Database Mirroring" also describes a "how-to" to still resume mirroring after you've removed it !!
Note
To continue mirroring after stopping a session, you must establish a new database mirroring session. If you create a log backup after stopping mirroring, apply it to the mirror database before restarting mirroring. For more information about starting mirroring, see How to: Configure a Database Mirroring Session (SQL Server Management Studio) or Setting Up Database Mirroring.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 7, 2010 at 3:25 pm
ALZDBA (6/7/2010)
We've has some connection losses to, but the mirroring (primary-mirror-witness) resumed as soon as the connection could be reactivated between principal and mirror.It caught up very well (sql2005 std)
I know topology is different than yours, but IMO you shouldn't have to intervene to reactivate the mirroring.
I'm still setting up my SQL2008 R2 test instance(s) to get to some mirroring tests in the comming weeks, so I'm only speaking from my perception.
BOL contains a topic "How to: Resume a Database Mirroring Session (Transact-SQL)" at http://msdn.microsoft.com/en-us/library/ms191125.aspx
My guess, as long as you haven't performed the restore with recovery on the mirror db, you should be able to rebuild the mirroring session without having to go trough the full restore procedure, as long as your dblog is usable. (as shown in BOL)
Bol article http://msdn.microsoft.com/en-us/library/ms180801.aspx titled "Removing Database Mirroring" also describes a "how-to" to still resume mirroring after you've removed it !!
Note
To continue mirroring after stopping a session, you must establish a new database mirroring session. If you create a log backup after stopping mirroring, apply it to the mirror database before restarting mirroring. For more information about starting mirroring, see How to: Configure a Database Mirroring Session (SQL Server Management Studio) or Setting Up Database Mirroring.
Thank you, I will look up the links you posted.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply