September 20, 2017 at 10:10 am
Hi all
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.
Our thinking has been to create one AG with all 46 databases - I don't suppose there are any performance gains in separating these out into different AGs? In a DR scenario failover would be manual so doesn't really matter whether the DBs are in separate AGs as we will have scripts to initiate the failover anyway (all databases need to be failed over to the other side but a few min in between doesn't matter).
I'll be honest and say that we were all in agreement to 'slap it into production' and see what happens - we are using asynch mode which has better performance and we are not talking VLDBs here, so are not expecting any problems.... but I'm getting a little nervous now. 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.
But I'd prefer not to go into this blind and understand the potential impact before going into production. What do I need to be thinking of? Bear in mind that I haven't really ever looked into network latency/bandwidth performance metrics before so specifics would be helpful.
Thanks 🙂
September 20, 2017 at 10:26 am
One thing to be aware of is the log transfer load. If you look at the log backup sizes over time, in aggregate, you'll get an idea of the network load that needs to be transferred over time.
It's not the db size, it's the log workload, changes, that need to be moved. Likely with a DW, it's the load process that might cause some issues with the network.
September 21, 2017 at 2:35 am
Thanks Steve
We don't currently to transaction log backups - everything is in simple recovery mode and we rely on nightly backups as our current DR solution. So I don't know what log backup sizes are! It sounds like it would be worth switching to full recovery mode and configuring transaction log backups on production for a few weeks before setting up AlwaysOn - that would give us an idea of the log back up sizes with the added bonus of understanding the disk space requirements.
September 21, 2017 at 2:32 pm
I saw a post today from Brent Ozar on this. They recommend doing index rebuilds as a way of testing the network load.
If you're in simple mode, maybe switch, or otherwise try to determine what the change load is on your system.
September 22, 2017 at 3:42 am
Yes - that was my post on Stack Exchange which Brent responded to. Index rebuilds are a good test - or in our case we have overnight batch loads into the data warehouse. I can then use the scripts referenced by Brent to monitor AG performance. However, before all of that I'd like to switch our databases to Full Recovery mode with t-log backup jobs to start understanding the network load.
Here's a link to my other forum post with Brent's response for those interested:
https://dba.stackexchange.com/questions/186525/how-to-go-about-testing-your-network-for-an-alwayson-implementation
Thanks for your interest Steve - appreciate it 🙂
September 26, 2017 at 3:16 am
doodlingdba - Wednesday, September 20, 2017 10:10 AMHi all
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.
Our thinking has been to create one AG with all 46 databases - I don't suppose there are any performance gains in separating these out into different AGs? In a DR scenario failover would be manual so doesn't really matter whether the DBs are in separate AGs as we will have scripts to initiate the failover anyway (all databases need to be failed over to the other side but a few min in between doesn't matter).
I'll be honest and say that we were all in agreement to 'slap it into production' and see what happens - we are using asynch mode which has better performance and we are not talking VLDBs here, so are not expecting any problems.... but I'm getting a little nervous now. 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.
But I'd prefer not to go into this blind and understand the potential impact before going into production. What do I need to be thinking of? Bear in mind that I haven't really ever looked into network latency/bandwidth performance metrics before so specifics would be helpful.
Thanks 🙂
See my stairway to AlwaysOn starting at this link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply