locked
Database Mirroring RRS feed

  • Question

  • hi,

    I have three servers to implement Database mirroring, " [with High safety with automatic failover] Mirror type".

     ("A","B","C") servers.

     That is "A" server as Principal, "B" server as Mirror and "c" server as witness.

    And I want to always remain "A" server as Principal Server,after "A" server disconnect and then connect.

    For Example:

                If "A" server go to Failover state, then "B" mirror server comes up as principal.

    And now we want to:  if problem of "A" server was solved, then "A" server automatically return to Principal Mode.

     

    Thank you.

     


    Experience is the best teacher.

    Sunday, August 25, 2013 11:00 AM

Answers

  • There is no build in methology to return to the previous state when the former principal Server has been repaired.

    Therefore you have to manually Failover after your repairing Action as a last step like this (run at current principal Server)

    USE master;
    
    ALTER DATABASE database_name SET PARTNER FAILOVER;

    Of course you could automate this, although it's usually not recommended, by putting this into a Job that checks for connectivity / mirroring state

    exec sp_dbmmonitorresults

    and then initiates this command. Why would I not recommend this? Because it might be switching roles back before you actually found out the root cause and end Up in failing again anyways...


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Sunday, August 25, 2013 12:27 PM

All replies

  • There is no build in methology to return to the previous state when the former principal Server has been repaired.

    Therefore you have to manually Failover after your repairing Action as a last step like this (run at current principal Server)

    USE master;
    
    ALTER DATABASE database_name SET PARTNER FAILOVER;

    Of course you could automate this, although it's usually not recommended, by putting this into a Job that checks for connectivity / mirroring state

    exec sp_dbmmonitorresults

    and then initiates this command. Why would I not recommend this? Because it might be switching roles back before you actually found out the root cause and end Up in failing again anyways...


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Sunday, August 25, 2013 12:27 PM
  • Or if the server B (now it is a principal) goes down so it will be changed back again.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, August 26, 2013 7:44 AM