locked
SQL Server 2005: Monitoring Database Mirroring RRS feed

  • Question

  • Hi All,

    I use SQL Server 2005 with mirror enable.

    Are there anyway to check/monitor/verify whether all data at principal are transit to mirror serer ?

    Regards,

    Pitou

    Wednesday, April 2, 2014 6:35 AM

Answers

  • What is operation mode of the mirroring configuration?

    http://technet.microsoft.com/en-us/library/cc917680.aspx#XSLTsection126121120120

    /*If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).*/


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sofiya Li Monday, April 14, 2014 1:28 AM
    Wednesday, April 9, 2014 5:43 AM

All replies

  • Try to create database snapshots
    Wednesday, April 2, 2014 11:39 AM
  • Hello,

    I agree with Imran that best way to show whether data is being moved from principal to mirror is by creating a snapshot on mirror and then running select query.Make sure that after you take snapshot no body else modifies the row otherwise it wont be of any use.

    Also note mirroring transfers data bot logind and jobs so you might have orphaned users there.Fix that as well

    Below link would be helpful for implementing this scenario

    http://technet.microsoft.com/en-us/library/ms175511.aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 2, 2014 12:18 PM
  • Hello,

    Are there other way ? Because database snapshot allocate disk space equal to source database size and free space of my server is less. 

    Regards,

    Pitou


    • Edited by Pitou H Friday, April 4, 2014 8:38 AM
    Friday, April 4, 2014 8:37 AM
  • What is operation mode of the mirroring configuration?

    http://technet.microsoft.com/en-us/library/cc917680.aspx#XSLTsection126121120120

    /*If transaction safety (or just 'safety') is set to FULL, the principal and mirror servers operate in a synchronous transfer mode. As the principal server hardens its principal database log records to disk, it also sends them to the mirror. The principal then waits for a response from the mirror server. The mirror responds when it has hardened those same log records to the mirror's log disk. When safety is set OFF, the principal does not wait for acknowledgment from the mirror, and so the principal and mirror may not be fully synchronized (that is, the mirror may not quite keep up with the principal).*/


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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Sofiya Li Monday, April 14, 2014 1:28 AM
    Wednesday, April 9, 2014 5:43 AM
  • Hi,

    safety is set OFF

    Regards,

    Pitou

    Wednesday, April 9, 2014 8:55 AM
  • Hi,

    safety is set OFF

    Regards,

    Pitou

    Sorry for late reply.I somehow missed track for this thread .If transaction safter is off your best bet will be Creating a snpshot.Snapshot is enterprise only feature.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Wednesday, April 16, 2014 9:57 AM