locked
Mirroring monitoring RRS feed

  • Question

  • Hi everyone Am new to sql server.

    Recently i configured Mirroring How can i monitor mirroring is going on.

    please give me all ways how can i monitor mirroring?

    Wednesday, November 10, 2010 2:10 PM

Answers

  • Monitoring Tools:

    - GUI Monitoring Tool in SSMS

    - View and update Status

    - Configure Warning thresholds

    - View (only if member of db_monitor role)

    System Stored Procedures:

    - sp_dbmonitoraddmonitoring

    - sp_dbmonitorchangemonitoring

    - sp_dbmonitorhelpmonitoring

    - sp_dbmonitorresults

    - sp_dbmonitordropmonitoring

    - sp_dbmonitorupdate - Updates internal undocumented status table in MSDB

    DMVs and Views for Monitoring:

    sys.database_mirroring catalog view

    - mirroring_safety_level

    - mirroring_safety_level_description

    - mirroring_witness_name

    - mirroring_witness_state

    Transaction Rates/Perfromance:

    - Log Send Queue Rate

    - Log Bytes Redone from Cache/sec

    - Log Bytes Sent from Cache/sec

    - Log Compressed Bytes Rcvd/sec (2008)

    - Log Compressed Bytes Sent/sec (2008)

    - Log Harden Time (ms)

    - Log Remaining for Undo (KB)

    - Log Scanned for Undo KB

    - Mirrored Write Transactions/sec

    Communication Delay Indicators:

    - Log Send Flow Control Time (ms)

    - Log Receive Control Time (ms)

    - Send/Receive Ack Time

    Links:

    MSDN Monitor Mirroring Status SQL Server 2008 R2 - http://msdn.microsoft.com/en-us/library/ms365781.aspx

    MSDN sp_dbmonitorupdate (transact-SQL) - http://msdn.microsoft.com/en-us/library/ms403827.aspx

    MSDN sys.database_mirroring (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms178655.aspx


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Wednesday, November 10, 2010 2:39 PM

All replies

  • http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx -----Mirroring(Best Practices)
    http://blogs.msdn.com/sqlcat/archive/2009/02/09/minimize-downtime-with-db-mirroring.aspx ----Mirroring

    ----Script to obtain Database Mirroring connection details
    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',
    Convert(Varchar(24),
    Connect_Time) as 'Connection_Time',
    Convert(Varchar(24),Login_Time) as 'Login_Time',
    Authentication_Method,
    Principal_Name,
    Remote_User_Name,
    Convert(Varchar(24),Last_Activity_Time) as 'Last_Activity_Time',
    Login_state_Desc as 'Login_State',
    Encryption_Algorithm_Desc as 'Encryption_Algorithm',
    Case Is_Send_flow_Controlled
    When 0 then 'Network is not busy'
    When 1 then 'Network is busy'
    end as 'Is_Network_Busy',
    Ltrim(str(Total_bytes_sent)) as 'Total_Bytes_Sent',
    Ltrim(str(Total_bytes_Received)) as 'Total_Bytes_Received',
    Ltrim(str(Total_Fragments_sent)) as 'Total_Message_Fragment_Sent',
    Ltrim(str(Total_Fragments_Received)) as 'Total_Message_Fragment_Received',
    Ltrim(str(Total_sends)) as 'Total_Network_Requests_Sent',
    Ltrim(str(Total_receives)) as 'Total_Network_Requests_Received'
    from sys.dm_db_mirroring_connections;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, November 10, 2010 2:22 PM
  • Monitoring Tools:

    - GUI Monitoring Tool in SSMS

    - View and update Status

    - Configure Warning thresholds

    - View (only if member of db_monitor role)

    System Stored Procedures:

    - sp_dbmonitoraddmonitoring

    - sp_dbmonitorchangemonitoring

    - sp_dbmonitorhelpmonitoring

    - sp_dbmonitorresults

    - sp_dbmonitordropmonitoring

    - sp_dbmonitorupdate - Updates internal undocumented status table in MSDB

    DMVs and Views for Monitoring:

    sys.database_mirroring catalog view

    - mirroring_safety_level

    - mirroring_safety_level_description

    - mirroring_witness_name

    - mirroring_witness_state

    Transaction Rates/Perfromance:

    - Log Send Queue Rate

    - Log Bytes Redone from Cache/sec

    - Log Bytes Sent from Cache/sec

    - Log Compressed Bytes Rcvd/sec (2008)

    - Log Compressed Bytes Sent/sec (2008)

    - Log Harden Time (ms)

    - Log Remaining for Undo (KB)

    - Log Scanned for Undo KB

    - Mirrored Write Transactions/sec

    Communication Delay Indicators:

    - Log Send Flow Control Time (ms)

    - Log Receive Control Time (ms)

    - Send/Receive Ack Time

    Links:

    MSDN Monitor Mirroring Status SQL Server 2008 R2 - http://msdn.microsoft.com/en-us/library/ms365781.aspx

    MSDN sp_dbmonitorupdate (transact-SQL) - http://msdn.microsoft.com/en-us/library/ms403827.aspx

    MSDN sys.database_mirroring (Transact-SQL) - http://msdn.microsoft.com/en-us/library/ms178655.aspx


    MCM: Microsoft Certified Master - SQL Server 2008 | MCT: Microsoft Certified Trainer | MCITP: Database Administrator 2008 | MCITP: Database Developer 2008 | MCITP: Database Administrator | MCITP: Database Developer | MCDBA: Microsoft SQL Server 2000
    Wednesday, November 10, 2010 2:39 PM
  • Hi,

    Since you told you are new to The SQL Server. Monitoring the mirroring through GUI helps you to understand the environment more.

    Please refer to the link to know in detail about the Mirroring Monitor - http://msdn.microsoft.com/en-us/library/ms365786.aspx

    Wednesday, November 10, 2010 3:52 PM
  • Hi,

    You can use below procedure to monitor mirror status:

    Exec msdb..sp_dbmmonitorresults <DatabaseName>

    Here the Mirroring_State indicates State of the database and below are different states.

    0 = Suspended

    1 = Disconnected

    2 = Synchronizing

    3 = Pending Failover

    4 = Synchronized

    unrestored_log indicates the Size of the redo queue on the mirror in kilobytes.

    Please refer below link for more details:

    http://msdn.microsoft.com/en-us/library/ms366320.aspx 

    Wednesday, November 17, 2010 1:20 PM