September 16, 2009 at 4:22 am
Hi
I have mirroring implemented on 3 servers (1- mirror, 2 - principal, 3 - witness)
The problem is that there is not only one database to be mirrored but 10.
The idea situation would be that all 10 are in mirror stated on one server and in principal state on second, however the automatic failover occurs sometimes for some of them and the there is complete mix. In consequence the maintenance plan with backup is failing and I do not have backup created for any of database on non of the servers. Do you have any solution for me?
September 16, 2009 at 4:27 am
Mirroring is database based not server based so they are all independant of each other. This means that you can have some databases fail over and others not fail over. You need to use clustering to failover whole servers
September 16, 2009 at 4:29 am
Can you give me more details how to set it up?
September 16, 2009 at 4:30 am
September 16, 2009 at 4:36 am
mode: automatic failover, synchronous. The same for all databases
September 16, 2009 at 4:40 am
This may help
September 16, 2009 at 4:41 am
September 16, 2009 at 4:48 am
k.przewozniak (9/16/2009)
HiI have mirroring implemented on 3 servers (1- mirror, 2 - principal, 3 - witness)
The problem is that there is not only one database to be mirrored but 10.
The idea situation would be that all 10 are in mirror stated on one server and in principal state on second, however the automatic failover occurs sometimes for some of them and the there is complete mix. In consequence the maintenance plan with backup is failing and I do not have backup created for any of database on non of the servers. Do you have any solution for me?
To answer your question, which I believe is to allow backups to happen on these mirrored databases, is to create your backup jobs on both servers, principal and mirror, have it enabled on both and query the sys.database_mirroring and see if the mirroring role is set to principal and if so run the job to backup that database.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 16, 2009 at 4:50 am
Silverfox (9/16/2009)
k.przewozniak (9/16/2009)
HiI have mirroring implemented on 3 servers (1- mirror, 2 - principal, 3 - witness)
The problem is that there is not only one database to be mirrored but 10.
The idea situation would be that all 10 are in mirror stated on one server and in principal state on second, however the automatic failover occurs sometimes for some of them and the there is complete mix. In consequence the maintenance plan with backup is failing and I do not have backup created for any of database on non of the servers. Do you have any solution for me?
To answer your question, which I believe is to allow backups to happen on these mirrored databases, is to create your backup jobs on both servers, principal and mirror, have it enabled on both and query the sys.database_mirroring and see if the mirroring role is set to principal and if so run the job to backup that database.
I think there are 2 questions, one the backup and the other failing over all databases and not having some failed over and some not.
September 16, 2009 at 5:10 am
Matthew Bryant-584838 (9/16/2009)
Silverfox (9/16/2009)
k.przewozniak (9/16/2009)
HiI have mirroring implemented on 3 servers (1- mirror, 2 - principal, 3 - witness)
The problem is that there is not only one database to be mirrored but 10.
The idea situation would be that all 10 are in mirror stated on one server and in principal state on second, however the automatic failover occurs sometimes for some of them and the there is complete mix. In consequence the maintenance plan with backup is failing and I do not have backup created for any of database on non of the servers. Do you have any solution for me?
To answer your question, which I believe is to allow backups to happen on these mirrored databases, is to create your backup jobs on both servers, principal and mirror, have it enabled on both and query the sys.database_mirroring and see if the mirroring role is set to principal and if so run the job to backup that database.
I think there are 2 questions, one the backup and the other failing over all databases and not having some failed over and some not.
Very true, but you can check the logs to see why they have failed over. following on from the 'in consequence' quote, having the backup jobs on both servers will eliminate the issue that he has with the databases failing over and maintenance job failing to backup. databases failing over is something to look at, but the databases in theory will still be operational and connectivity to the databases will still occur.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 17, 2009 at 1:20 am
Thank you very much all of you for tips. I am now implimenting the alerts and job with failover.
Can you tell how long should last query?
ALTER DATABASE msdb SET ENABLE_BROKER
I have already cancel it twice as it was running in my opinion to long (the SQL Server Agent again I had stopped)
September 17, 2009 at 5:39 am
ok, found solution:
alter DATABASE MSDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER database msdb set ENABLE_BROKER
go
alter DATABASE MSDB SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply