locked
Monitoring Mirroring SQL Server 2008R2 RRS feed

  • Question

  • Hi All,

    Greetings of the day,

    We are using SQL Server 2008R2, mirroring is already configured between two servers so my responsibility is to monitor the synchronization between principal and mirror server. I came to know synchronization can be seen through "launch database mirroring monitor" but as a DBA what would the verifications should I do proactively, what are the queries used to retrieve required info and how to get more exposure in this.

    Grateful to your suggestions.

    Regards,
    Kalyan


    ----Learners Curiosity Never Ends----

    Monday, March 18, 2013 9:01 AM

Answers

  • http://www.sqlserver-training.com/how-to-monitor-database-mirroring-sql-server/-

    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

    • Proposed as answer by Fanny Liu Friday, March 22, 2013 10:14 AM
    • Marked as answer by Fanny Liu Monday, March 25, 2013 9:12 AM
    Tuesday, March 19, 2013 6:04 AM
  • Hello,

    Database Mirroring Monitor enables us to verify whether and how well data is flowing in the database mirroring session. It is also useful for troubleshooting the cause of reduced data flow between mirroring partners.
    Mirroring status can be monitored using either Database Mirroring Monitor which is a graphical user interface tool or running the sp_dbmmonitorresults system stored procedure.
    For more information, please see: Monitoring Database Mirroring (SQL Server)

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Friday, March 22, 2013 10:14 AM
    • Marked as answer by Fanny Liu Monday, March 25, 2013 9:13 AM
    Tuesday, March 19, 2013 7:25 AM

All replies

  • one of things is that, you can see how far behind the mirroring database in terms of synchronization(depending on which mode your mirrioring mode is set on)...

    Hope it Helps!!

    Monday, March 18, 2013 12:09 PM
  • http://www.sqlserver-training.com/how-to-monitor-database-mirroring-sql-server/-

    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

    • Proposed as answer by Fanny Liu Friday, March 22, 2013 10:14 AM
    • Marked as answer by Fanny Liu Monday, March 25, 2013 9:12 AM
    Tuesday, March 19, 2013 6:04 AM
  • Hi Team,

    Could you please configure some alerts. Once you configure alerts Database status was changed immidiatly we will get mails on your mail box.

    ex: Fatal alerts 

    i have configured these alerts in our env.. once status changed or suspended and db goes to suspect mode then you will get mail

    i think so this is helpful to you.

    Regards

    subu


    subu

    Tuesday, March 19, 2013 6:15 AM
  • Hello,

    Database Mirroring Monitor enables us to verify whether and how well data is flowing in the database mirroring session. It is also useful for troubleshooting the cause of reduced data flow between mirroring partners.
    Mirroring status can be monitored using either Database Mirroring Monitor which is a graphical user interface tool or running the sp_dbmmonitorresults system stored procedure.
    For more information, please see: Monitoring Database Mirroring (SQL Server)

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    • Proposed as answer by Fanny Liu Friday, March 22, 2013 10:14 AM
    • Marked as answer by Fanny Liu Monday, March 25, 2013 9:13 AM
    Tuesday, March 19, 2013 7:25 AM
  • If you can get the funding, then think about investing in RedGate SQL Monitor.  This is extremely helpful with proactive monitoring.  If you cant then you need to start looking into using the built in alerting functions of SQL Server or start hacking away with Powershell.

    For example, you can use the following query to quickly see if the databases are not synchronized:

    SELECT DB_NAME(database_id) AS 'Database Name', mirroring_state_desc AS 'Mirror State'
    FROM sys.database_mirroring
    WHERE mirroring_state_desc IS NOT NULL AND mirroring_state_desc <> 'SYNCHRONIZED'

    You could set this up to check every 5 minutes and email you when something get's returned.  This is also a custom metric that RedGate is publishing very soon.

    See: http://dbassassin.blogspot.com/search/label/Powershell for some sample powershell scripts that could get you started


    • Proposed as answer by Tim Hidalgo_ Friday, March 22, 2013 3:43 PM
    Friday, March 22, 2013 3:43 PM