locked
Db Mirroring w Snapshots RRS feed

  • Question

  • We are interested in querying a copy of the principal for reporting purposes (not dr or availablility purposes).  The partners are on opposite coasts and are connected via a vpn.  Transactional replication experiences unacceptable latencies (several hours) when certain processes run on the primary.  Bandwidth does not appear to be the bottleneck, but rather processing time on the subscriber.  Our hope is that db mirroring w snapshots (high performance/async mode) will overcome these latencies but I need to ask: 1) should we expect less latency ? 2) Does the snapshot continue to reflect new transactions after the snapshot is made, or does the snapshot only reflect the state of transactions at the time the snapshot gets created ? 3) Is db mirroring suitable over a vpn ? 4) please describe the process of creating a new snapshot - I would imagine we will periodically need new ones periodically to keep the size down.

     Thanks!

    Friday, December 10, 2010 4:29 PM

Answers

  • I will try to summarize my answers in points

    1. It depends on your network speed and bandwidth it can handle and one of the improvement in SQL server mirroring starting from 2008 version is the log stream compression which should help you

    2.Snapshot is just the consistent view of your mirrored database at the instant when you have run a CREATE DATABASE as SNAPSHOT of on the mirrored database

    3. The answer is still the same as point 1.Yes, database mirroring can be built over a VPN as the mirroring endpoints are reachable between partners.

    4. A database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot's creation. I perform snapshot creation at scheduled frequency by dropping the old one and recreating a new snapshot through SQL agent job on the mirrored instance.More info here http://technet.microsoft.com/en-us/library/ms175511.aspx


    Thanks, Leks
    • Proposed as answer by PrinceLucifer Monday, December 13, 2010 6:04 AM
    • Marked as answer by Alex Feng (SQL) Sunday, December 19, 2010 5:32 AM
    Friday, December 10, 2010 10:06 PM
    Answerer
  • Hi,

    Addition to Lekss:

    About latency that mirror server can catch up the pricipal, there are some system performance counter can help you estimate. You can may reply of a previous thread, see http://social.technet.microsoft.com/Forums/en/sqldatabasemirroring/thread/7a4f6c24-8341-478a-b0e6-4948c46295ea.

    For more information, you can refer to Monitoring Database Mirroring section in the Database Mirroring in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc917680.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    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.
    Monday, December 13, 2010 3:43 AM

All replies

  • I will try to summarize my answers in points

    1. It depends on your network speed and bandwidth it can handle and one of the improvement in SQL server mirroring starting from 2008 version is the log stream compression which should help you

    2.Snapshot is just the consistent view of your mirrored database at the instant when you have run a CREATE DATABASE as SNAPSHOT of on the mirrored database

    3. The answer is still the same as point 1.Yes, database mirroring can be built over a VPN as the mirroring endpoints are reachable between partners.

    4. A database snapshot is a static, read-only, transaction-consistent snapshot of its source database as it existed at the moment of the snapshot's creation. I perform snapshot creation at scheduled frequency by dropping the old one and recreating a new snapshot through SQL agent job on the mirrored instance.More info here http://technet.microsoft.com/en-us/library/ms175511.aspx


    Thanks, Leks
    • Proposed as answer by PrinceLucifer Monday, December 13, 2010 6:04 AM
    • Marked as answer by Alex Feng (SQL) Sunday, December 19, 2010 5:32 AM
    Friday, December 10, 2010 10:06 PM
    Answerer
  • Hi,

    Addition to Lekss:

    About latency that mirror server can catch up the pricipal, there are some system performance counter can help you estimate. You can may reply of a previous thread, see http://social.technet.microsoft.com/Forums/en/sqldatabasemirroring/thread/7a4f6c24-8341-478a-b0e6-4948c46295ea.

    For more information, you can refer to Monitoring Database Mirroring section in the Database Mirroring in SQL Server 2005 (http://technet.microsoft.com/en-us/library/cc917680.aspx).

    Hope this helps.

    Thanks,
    Chunsong


    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.
    Monday, December 13, 2010 3:43 AM