October 8, 2007 at 2:40 am
Hi,
I have data in two different data centres, sat on Win2k3 servers in SQL Server 2005 Enterprise Edition.
Data centre 1 contains all the live databases, data centre 2 is the DR site. We will be transferring data using SQL Server High Availability mirroring and I need to measure the amount of transactions and data changes that are applied to the live databases in data centre 1 to get a better idea of how much data will be sent down the network pipe from data centre 1 to data centre 2. I want to measure this information using Perfmon. The link between the two data centres will be one of the last things to be put in place so I can't simply setup a "test Mirror" and use the Mirror Monitor.
Any help with Perfmon counter suggestions would be greatly appreciated.
Thanks in advance.
www.sqlAssociates.co.uk
October 8, 2007 at 4:26 am
Hi
Must confess dont know much about this one. Just searching got me this info abt this counter -SQLServer:Database Mirroring . May be you could use the other counters to measure "performance".
Hope i have been of some small help.
"Keep Trying"
October 8, 2007 at 6:16 am
I seem to have been posting somewhat terse replies today! I wonder if you really read what you posted? You've going to set up a high availability link, untested and with no idea of if it will work or support the throughput - so if it doesn't work then it won't really be high availablity will it?
Having set up various forms of DR to and from data centres, but not mirroring, what I will say is that the pipe between centres is likely to be the limiting factor. Usually these pipes support more than just sql server and you'll probably find you will be short of bandwidth - I may be wrong and if you've a 4gb pipe please accept my apologies. Distance will also play a part in latency. I capture trending and performance data on servers, have done for many years this is the 2000 version
http://sqlblogcasts.com/blogs/grumpyolddba/archive/tags/Trending+and+Statistics/default.aspx
it works fine on 2005 too. The performance dashboard is also a useful tool to deploy.
As a rough rule of thumb, the size of your daily transaction logs is likely to be the amount of data you'll send to the mirror, don't forget if you've got some hefty index rebuilds this will also get mirrored.
I don't know how many databases you're planning to mirror but there are limits on numbers btw.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 6:22 am
Thanks for your comments Colin. The proposed pipe size is 1GB, and Microsoft are heavily involved in the project and have gauranteed that it will work.
The main reasoning behind testing the throughput is to justify the bandwidth allocation based on current db activity, and expected growth over the next 5 years. My post was slightly "terse" as in, brief yet to the point.
Thanks again.
www.sqlAssociates.co.uk
October 8, 2007 at 6:37 am
a gig sounds good but i expect that's also your network bandwidth and I bet at times response may not be as good as you hope! Mirroring is very simlar to log shipping in principle so measure your logs and do some averages.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 9:21 am
Greetings,
Put simply (On a time crunch here):
If you are using mirroring, "roughly" the data sent from datacenter 1 to datacenter 2 will be the size of you database(s) log(s) + the "somewhat" low traffic between your witness server and mirror servers.
What you have to watch for is the times it will take to move the "biggest logs" over to the mirror server.
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
October 8, 2007 at 9:21 am
Log size is a good indication because this data is what's being sent by database mirroring.
However it's sent as it's written to the primary log, so if you have peak log sizes, that will determine bandwidth requirements. It's still somewhat of a guess, but if you run logs every minute, check the largest ones and I'd guess based on that you can estimate some amount of bandwidth.
Unless you have QOS capabilities though, you might find issues as Colin mentioned.
October 8, 2007 at 9:32 am
I'm never too impressed with strings of letters in a post - but I do object to anything to do with guns - here in the UK we're suffering a number of children / teenagers dying from gun crime - I find your post script highly offensive.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2007 at 9:39 am
Thanks for your comments guys, it's much appreciated.
You all seem to be pointing towards looking at the log file sizes to get a rough estimate. Can someone expand on that a little for me? We have a LUN/drive that contains all the log files for all the databases, could I make a note of the current sizes for each file, come back in 24hrs and make a note of the sizes again, and the difference is the amount of data that will need to be taken into account? (assuming there are no peaks?)
Thanks in advance.
www.sqlAssociates.co.uk
October 8, 2007 at 9:47 am
Greetings,
I would approach it this way If you backup your logs:
1 - I would push back the delete of the old files as much as the functional or the space on the drive needs to be in the maintenance cleanup task (if you have one).
2 - I would wait for a while then check the files in the directory where you save your logs ordered by size.
3 - Then analyze the top x biggest files and do a bandwidth ratio to see how long if would take to transfer those files.
Thank you
MBA
MCSE, MCDBA, MCSD, MCITP, IBM DB2 Expert, I-Net+, CIW
Proud member of the NRA
-Anti-gun laws prevent law abiding citizens to buy guns and defend themselves against bad guys who do not care about the law and get their gun illegally.
- Democracy is 2 wolves and one sheep talking about their next dinner. Freedom is 2 wolves and one armed sheep with a .357 magnum talking about their next dinner.
October 8, 2007 at 9:50 am
Got you, thanks very much.
www.sqlAssociates.co.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply