mirroring and backup issue

  • 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?

  • 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

  • Can you give me more details how to set it up?

  • Just out of my curiosity, What Mirroring mode you are using and are they same for all 10 databases?

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • mode: automatic failover, synchronous. The same for all databases

  • This may help

    http://www.mssqltips.com/tip.asp?tip=1564

  • Can you give me more details how to set it up?

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/25a44245-e28d-41b5-ad65-7778d50c42ea.htm

    This is pretty much thorough.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

  • k.przewozniak (9/16/2009)


    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?

    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]

  • Silverfox (9/16/2009)


    k.przewozniak (9/16/2009)


    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?

    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.

  • Matthew Bryant-584838 (9/16/2009)


    Silverfox (9/16/2009)


    k.przewozniak (9/16/2009)


    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?

    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]

  • 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)

  • 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

    http://www.learnsqlserver.com/Blogs/SqlServerBlog/2007/05/quick-tip-what-to-do-if-this-statement.html

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply