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

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.
wjb313Asked:
Who is Participating?
 
arnoldConnect With a Mentor Commented:
~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.
0
 
arnoldCommented:
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.

http://technet.microsoft.com/en-us/library/cc917681.aspx
0
 
wjb313Author Commented:
We'll try that today. Thanks for your insight.
0
 
wjb313Author Commented:
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?
0
 
wjb313Author Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.