Total transfer size for database mirroring over a period of time?

Posted on 2014-08-19
Last Modified: 2014-08-26
We've got mirroring set up on 5 production hosts (4 are SQL2008R2, 1 is SQL2008) all transferring data (asynchronous) to an offsite mirroring server (SQL2008R2).  We have a 50Mb connection for this and maybe hit 20Mb of usage at peak times.  We're looking at moving the mirroring for ~30 databases on one of these production hosts to a different offsite host, but want to know more accurately how much data we're mirroring over a 24hour period (or 12 hour period, or 3 day period, etc).

We're using msdb.sys.sp_dbmmonitorresults to see some data and to monitor the mirroring process, but it isn't telling us what we want to know.  :) Yes, knowing how much data is in the queue and what the recovery rate is is great, but I'm looking for total transfer size over a given time.

Everything I've looked at, even using 'perfmon', seems to want to give averages (bytes/sec sent, bytes/sec rec, etc.), which is too inaccurate for my needs.

Am I going about this the wrong way?  I assumed some DMV somewhere would tell me how many bytes were mirrored in the last hour (and then I could easily set up something to grab this every hour).  Usually I would just look at my transaction log backups (which we do every half hour) and just add those up over 12/24 hour periods as a good estimate, but we're using CommVault for our backups and I don't trust the numbers its giving me (since it does its own de-dupe and maybe its own compression - I don't know).

Any thoughts/ideas?
Question by:nemws1
    LVL 23

    Author Comment

    I found that I can use the numbers from the backup reports in the [msdb] database to find non-compressed backup sizes (from the transaction log backups).  Here's what I'm using and its giving me the numbers I need:

    USE msdb;
    SELECT CONVERT(VARCHAR(10), bs.backup_start_date, 120) AS DATE
        , SUM(bs.backup_size) / (1024 * 1024) AS SizeSumMB
        --, bs.database_name
    FROM dbo.backupmediafamily AS bmf
        JOIN dbo.backupset AS bs
            ON bmf.media_set_id = bs.media_set_id
    WHERE (CONVERT(DATETIME, bs.backup_start_date, 102) >= GETDATE() - 14)
        AND bs.type = 'L'
    GROUP BY CONVERT(VARCHAR(10), bs.backup_start_date, 120)
        --, bs.database_name
    ORDER BY CONVERT(VARCHAR(10), bs.backup_start_date, 120)
        --, bs.database_name

    Open in new window

    LVL 28

    Accepted Solution

    I'd agree that looking at the size of your database logs is going to be the best bet for estimating how much data you're sending for mirroring. You're effectively doing log shipping to the remote locations (not exactly, but it's a good approximation), so if you're generating 100MB of logs/hour (2.4GB/day), and the logs are generated evenly throughout the day, you'd need 250kbit for each location you want to send the logs (10 remote servers = 2.5 mbit). That assumes even log generating - if you've got heavy periods where logs are generated at twice the speed and you don't account for that, you'll end up with mirror lag during those times.

    It's not exact, and it assumes no compression is happening along the way, but it's always the ballpark I've used and it's served me well.
    LVL 23

    Author Comment

    Exactly the calculations I was doing.  And yes, we have "hot times", but I'm not too concerned, as long as the mirroring server catches up at some point during the day (or early morning or whatever).
    LVL 23

    Author Closing Comment

    Thanks for the info - much appreciated!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now