3

Currently we have a database which is 4 TB in size. We would like to put it in a 2014 Availability Group to scale out reads. Data gets changed a lot inside this database.

I have following questions:

  1. Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?
  2. If network dies for 1hour, how fast can we get the replica in sync?
  3. Do you guys have any consideration I should be following before using Availability Groups?
7

Nismo,

It's not the SIZE of the database that matters (no jokes intended), it's the rate of change coupled with the infrastructure available. For example, a relatively static database might perform poorly on 1Gb connection on an overloaded switch with 5400 RPM sata drives.

If the rate of change (aka look at your log flush bytes) is less than around 200 MB/sec and you have very fast storage (or a ton of front end cache) and extremely low latency networking then I would say you'll be fine.

Let's go over your questions now:

1.Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?

It's not about the size, but the infrastructure. So, yes that's fine.

2.If network dies for 1hour, how fast can we get the replica in sync?

How fast does an unladened swallow fly? Seriously though, there is no way for us to tell you. You'll need to take into account the rate of log generation + latency + blocked redo + sync/async status.

The better question to ask is, "Do I have enough space on my primary for my transaction log to NOT TRUNCATE for an hour?"

3.Do you guys have any consideration I should be following before using Availability Groups?

I would consider taking a serious look at SQL 2016 if these are your concerns. Parallel redo (coming soon), better network utilization, distributed AGs, direct seeding, etc.

If you're intent on using 2014, then the best recommendation is to make sure the server hardware and infrastructure are up to the rate of change you'll be creating. Additionally watch when you do DDL against objects as it could pose potential problems with redo.

If you're worried about the replicas catching up in time, just remove the replicas from the AG, restore the transaction logs to them and then bring them back into the AG. Remember, your primary won't be able to have the log file re-use any VLFs while an AG is "behind" as it'll only be able to mark for re-use up to that LSN.

5
  • 1
    Rebuild/reorg strategy is likely going to have to be different too. – Aaron Bertrand Mar 1 '16 at 13:44
  • Thank you! Sean for detail answer to my very vague questions, I do really appreciate it. I will check on above Network and Log counters you indicated and see what they are at the moment on the storage. If 2016 is going to provide us much better performance, we would wait till 2016 is out. – NismoGTR05 Mar 1 '16 at 21:23
  • @AaronBertrand absolutely true. In fact when I talk about this I tend to push reorgs more for various reasons. This may be somewhat alleviated with 2016 depending on hardware and infrastructure. – Sean Gallardy - Retired User Mar 2 '16 at 5:05
  • @NismoGTR05 Yes, if you don't need to implement this tomorrow, wait for 2016. – Aaron Bertrand Mar 2 '16 at 5:23
  • Yes, we will wait for 2016, thank you so much Sir – NismoGTR05 Mar 2 '16 at 11:06

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.