2

So i'm taking my first steps into deploying an AlwaysOn solution at my company. I've set it up in a test environment so know the setup/config steps required, however what we don't know is how our systems are going to perform once AlwaysOn has been switched on in production, as we haven't done any performance testing with production-like load.

We have about 46 databases that need to be synchronized across to DR - totalling around 1 TB in size. This is a data warehousing system and is used for reporting during the day (all reads) and batch loads at night (all writes).

We are not using AGs to provide HA - this is for DR across two data centers using asynch mode, but with added bonus of up to date read-only data on DR for the analytics team to do their ad hoc analysis without interfering with regular business reporting. In a DR scenario failover would be manual.

Reading posts about AG performance, I know there are things we could tweak such as max worker threads and using a separate NIC for mirror traffic to improve performance - so we have options should we run into issues. I haven't really ever looked into network latency/bandwidth performance metrics before so specifics would be helpful.

How should we run an Availability Groups network test, and what network latency or bandwidth numbers should we watch during the test?

1
  • 2
    This is pretty wide-ranging. Your title suggests that it's specifically about testing the network, but you've got several different questions in the body of the post. I'm going to try to edit it down to just your main question. Don't take offense, but when you've got multiple questions, your best bet is to separate them into...separate questions. It'll help each one get a targeted, refined answer.
    – Brent Ozar
    Sep 21 '17 at 11:46
2

The easiest way to test AG network throughput is to rebuild one of your largest indexes.

While the index rebuild is running (and afterwards), measure the latency for each replica using any of these scripts:

This will give you a rough idea of how far behind each replica gets. If performance doesn't meet your goals, then you can drill into more granular measurements.

Keep in mind that network latency & bandwidth isn't the only issue that can affect replica latency. There's also log file write latency and redo thread throughput on the replicas. Starting at the high level of just checking synchronization lag will tell you whether or not there's a problem at all first, and then you might be able to just avoid drilling down into details altogether - especially since you wrote that this is a DR-only setup, not for HA.

1
  • Thanks Brent, appreciate the response. The referenced scripts will be very useful for measuring our latency. Index rebuilds are a good idea- in our case as it's a data warehouse we overnight batch load jobs which will be good for generating network load suitable for our tests. As you say - there may be no problem at all. We will only have one replica and we are only talking about 1 TB of data in total across all the databases. Plus i'm hoping SQL 2016's improved log transport/redo rates will mean we don't have to worry at all :)
    – Doodles
    Sep 22 '17 at 9:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.