locked
Check active sql server in the database mirroring RRS feed

  • Question

  • Dear all,

    How can I check whether the failover happened? As I want to show the information of failover of principle server or mirrored server or witness server to the user in the program status form. Is  it possible??

    Friday, May 27, 2011 8:28 AM

Answers

  • Actually I want the status of sql server shown on the main form.

    Is there any listener of database mirroring for us to check any failover so that I will show the status on the main form??

    Is there any sample code??

    With SMO (SQL Server Management Objects), you could get the status of servers and databases. For instance, to get server's status, there is a Status property of Server class, to get database status, there is a Status property of Database class. For more information, see "Server Class" (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx) and "Database Class" (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx) in Books Online.

    Please let me know if you have any question.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, June 1, 2011 6:29 AM

All replies

  • yes pls check this query

    select DB_Name(database_id) as DBName, 
    mirroring_state_desc as MirrorState, 
    mirroring_role_desc as Role, 
    mirroring_safety_level_desc as SafetyLevel, 
    mirroring_role_sequence as RoleSequence, 
    mirroring_partner_name as Partner, 
    Mirroring_partner_instance as PartnerInstance, 
    mirroring_witness_name as Witness,
    Mirroring_witness_state_desc as WitnessState
     from sys.database_mirroring
    Where database_Id = db_id('<db_Name>')

     

    If you want to setup WMI alerts on failover here is more details http://www.mssqltips.com/tip.asp?tip=1564

     

     


    http://uk.linkedin.com/in/ramjaddu
    Friday, May 27, 2011 8:43 AM
  • What the information of this tell me which server is down???

    Am I using the same connection string in the dot net for getting the ADO.Net result??

    Friday, May 27, 2011 4:09 PM
  • This will give you role of current server (either Principal / mirror) , partner instance name and witness name. This query works on both principal and mirror server.

     

     


    http://uk.linkedin.com/in/ramjaddu
    Friday, May 27, 2011 4:29 PM
  • Actually I want the status of sql server shown on the main form.

    Is there any listener of database mirroring for us to check any failover so that I will show the status on the main form??

    Is there any sample code??

    Saturday, May 28, 2011 12:43 PM
  • What status? Whether sql server is running or not?
    Select
    Connection_id,
    Case state
    When 1 then 'NEW'
    When 2 then 'CONNECTING'
    When 3 then 'CONNECTED'
    When 4 then 'LOGGED_IN'
    When 5 then 'CLOSED'
    End as 'Connection_State'
    from sys.dm_db_mirroring_connections;

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, May 29, 2011 6:45 AM
  • Actually I want the status of sql server shown on the main form.

    Is there any listener of database mirroring for us to check any failover so that I will show the status on the main form??

    Is there any sample code??

    With SMO (SQL Server Management Objects), you could get the status of servers and databases. For instance, to get server's status, there is a Status property of Server class, to get database status, there is a Status property of Database class. For more information, see "Server Class" (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.server.aspx) and "Database Class" (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx) in Books Online.

    Please let me know if you have any question.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, June 1, 2011 6:29 AM