locked
Failover Databases and SQL Mirroring RRS feed

  • Question

  • If I set up two SQL servers to mirror each other, do I have to manually go back and assign failover databases to each of my databases, or does SharePoint automatically know that there is a mirror?

    What I mean to say is say I have a database (DB) that's already created. Then I decide I want a mirror setup of my SQL servers. Would I then have to go back (GUI or PowerShell) and manually assign the "Failover Database Server", which is something you can assign at the time you make a database, or does SharePoint just know?

    I hope I'm wording it correctly.

    Thanks in advance!


    • Edited by Catherine Lam Wednesday, July 10, 2013 5:32 PM Forgot a word.
    Wednesday, July 10, 2013 5:31 PM

Answers

  • do I have to manually go back and assign failover databases to each of my databases, or does SharePoint automatically know that there is a mirror?

    You MUST manually assign.

    In PowerShell, get a list of databases via get-spdatabase and then on each database set the FailoverServer.

    If you do not assign this, SharePoint will not be able to failover.

    Note: SQL will still do mirroring.. but since sharepoint is not aware of the failover partner .. it cannot failover to the mirror node.

    The reason why this is required is because mirroring is done and configured on a per database level. and teh failoverpartner must be specified in the connection string in order to failover.


    val it: unit=()


    • Edited by MSDN Student Wednesday, July 10, 2013 5:56 PM foooccccccccccccccccc
    • Marked as answer by Kris Wagner - MVP Wednesday, July 10, 2013 10:19 PM
    Wednesday, July 10, 2013 5:45 PM
  • Its a manual process unless you setup a SQL witness server.

    Paul Stork SharePoint Server MVP
    Principal Architect: Blue Chip Consulting Group
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Wednesday, July 10, 2013 8:13 PM
  • In SQL 2008 r2 Mirroring you cannot have two mirrors. you can have 1 principal and 1 mirror.

    your connecting looks like

    server=principal;database=db;failover parther=mirror;

    There is a new technology called Always on Availability Group which allows multiple... but then its a different discussion all together.

    Failover is a manual process until you setup a witness server.


    val it: unit=()


    Wednesday, July 10, 2013 8:50 PM
  • Here is more information on mirroring, be sure to hit the drop down for your current version of SQL Server.

    http://msdn.microsoft.com/en-us/library/ms189852.asp


    Kris Wagner, MVP, MCITP, MCTS Twitter @sharepointkris Blog: http://www.sharepointkris.com/blog

    Wednesday, July 10, 2013 11:00 PM

All replies

  • do I have to manually go back and assign failover databases to each of my databases, or does SharePoint automatically know that there is a mirror?

    You MUST manually assign.

    In PowerShell, get a list of databases via get-spdatabase and then on each database set the FailoverServer.

    If you do not assign this, SharePoint will not be able to failover.

    Note: SQL will still do mirroring.. but since sharepoint is not aware of the failover partner .. it cannot failover to the mirror node.

    The reason why this is required is because mirroring is done and configured on a per database level. and teh failoverpartner must be specified in the connection string in order to failover.


    val it: unit=()


    • Edited by MSDN Student Wednesday, July 10, 2013 5:56 PM foooccccccccccccccccc
    • Marked as answer by Kris Wagner - MVP Wednesday, July 10, 2013 10:19 PM
    Wednesday, July 10, 2013 5:45 PM
  • So, say I have two mirrors: SQL1 and SQL2. SQL2 is the mirror. If I assign a failover database (located in SQL2) in SharePoint to my database, and (oh no) the database fails, will SharePoint automatically switch to SQL2? Or is it still a manual process?
    Wednesday, July 10, 2013 7:53 PM
  • Its a manual process unless you setup a SQL witness server.

    Paul Stork SharePoint Server MVP
    Principal Architect: Blue Chip Consulting Group
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Wednesday, July 10, 2013 8:13 PM
  • In SQL 2008 r2 Mirroring you cannot have two mirrors. you can have 1 principal and 1 mirror.

    your connecting looks like

    server=principal;database=db;failover parther=mirror;

    There is a new technology called Always on Availability Group which allows multiple... but then its a different discussion all together.

    Failover is a manual process until you setup a witness server.


    val it: unit=()


    Wednesday, July 10, 2013 8:50 PM
  • Here is more information on mirroring, be sure to hit the drop down for your current version of SQL Server.

    http://msdn.microsoft.com/en-us/library/ms189852.asp


    Kris Wagner, MVP, MCITP, MCTS Twitter @sharepointkris Blog: http://www.sharepointkris.com/blog

    Wednesday, July 10, 2013 11:00 PM