[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2010-01-06
Medium Priority
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
  • 3
  • 2
LVL 81

Expert Comment

ID: 26196331
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

ID: 26199803
We'll try that today. Thanks for your insight.

Author Comment

ID: 26287997
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 81

Accepted Solution

arnold earned 1000 total points
ID: 26289086
~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

ID: 26292270
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!

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background Information Recently I have fixed file server permission issues for one of my client. The client has 1800 users and one Windows Server 2008 R2 domain joined file server with 12 TB of data, 250+ shared folders and the folder structure i…
For anyone that has accidentally used newSID with Server 2008 R2 (like I did) and hasn't been able to get the server running again because you were unlucky (as I was) and had no backups - I was able to get things working by doing a Registry Hive rec…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Suggested Courses

829 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