Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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:
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.
