Need to determine the quantity of data written to SQL Database in given time period

Posted on 2010-01-06
Last Modified: 2013-12-01
We're working on a project to backup a SQL database between two locations using data mirroring and I need to determine the quantity of data written to the database per unit time so that we can accurately predict the RTO and RPO the data mirroring will give us, ie, how much data is being written per second/minute and will thus have to travel over the wire in order to mirror at the second location. I've looked into some perfmon counters but am not fluent in sequel and cannot easily determine how to find what I'm looking for.
Question by:wjb313
    LVL 76

    Expert Comment

    Setup a transaction log backup on a 15 minute interval. This is the amount of data that will need to flow through to the mirror within that time period.
    It all depends on the type of mirroring you want.  You could setup an Asynchronous mirroring.

    Author Comment

    We'll try that today. Thanks for your insight.

    Author Comment

    I had some other challenges in order to get to this point. The DB was set up in Simple Recovery mode and that seemed like a problem as this is mission critical data. I took the necessary steps to change to Full Recovery mode and set up the necessary maintenance plan/backup schedule.

    One of the subplans I set up was for Transaction Log backups - I used every 30m to start. The initial backup of Transaction Logs was 71029 KB; subsequent backups were 8141 KB, 8144 KB, 8719 KB, 6666 KB, and 9997 KB. It's safe, then, to say that this is the quantity of data I'm looking for - between 6-10 MB - correct?
    LVL 76

    Accepted Solution

    ~6 to 10 mb every 30 minutes but you may want to double/tripple the estimate of the data change for cushion and growth of use..
    Excluding the VPN overhead your mirroring for this database shoud not exceed the average of less than 64kbps as long as the amount of transactions are within the 6666KB to 15,000KB every 30 minutes and are equally spread. If you have all the transactions within a five minute period, this is when the mirroring bandwidth will spike in usage. Presumably you have a sophisticated router where you can setup a QoS to prioritize data and limit the impact of the mirroring burst.

    Author Comment

    The Data Center has dual Sonicwall NSA 240s in a High Availability configuration; the main office has the same configuration with Sonicwall TZ210s. The mirroring solution hasn't been finalized but will likely be a virtualized environment (DB and Terminal Server on vSphere) using HP Storage Mirroring Enterprise Edition to mirror the entire storage array between the two locations. The amount of data changing on the servers is not going to be much more than what we're determining for the DB as that's it's main function but we'll definitely need a bit of cushion. Thanks for your help!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
    New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
    This tutorial will walk an individual through the steps necessary to configure their installation of BackupExec 2012 to use network shared disk space. Verify that the path to the shared storage is valid and that data can be written to that location:…
    This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…

    760 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

    9 Experts available now in Live!

    Get 1:1 Help Now