Design - New Always on Availablity Group

High Availability
1

We are moving to a new data center and a new design. We want to utilize the always on Availability groups and we are having difficulty coming up with a design we think will work.

I have found a few references on the internet but none are answering our questions. I was wondering if any had any suggestions for reference sources.

2

Hi,
Please help to share us some of your concerns or any issues that you are facing at the moment, that will help us to provide useful info, or even help you to design it.

3

Our issue is that we haven't done this before.

  1. we are upgrading from 2005 to 2014
  2. we are consolidating several data centers into 1
  3. one team member supported this prior but never built it. and the environment supported was much, much larger
  4. not really following how to design it, because it would seem that only 1 server (node) does all of the heavy lifting and we would want to divide up heavy lifting per business units\to server I would think.
  5. basically, it looks to easy so we feeling we are completely messing something up so we need someone else with knowledge and possibly a similar architecture to validate what we are thinking.
4

Availability Groups allow you to have readable secondaries, provided you have licenses for those. You'll need to describe your environment more in order for us to help.

Here is how we had one system designed at a previous job:
3 servers at primary site: 1 for write, 1 for reads, 1 as a failover server
3 servers at DR site: 2 for async reads, 1 as a cold standby since this was SQL 2012 that only allowed 4 secondaries

Most of the apps that connected to it were using drivers that could make use of the ApplicationIntent and MultiSubnetFailover connection parameters. The ones that couldn't had to point to the underlying SQL instance where we wanted it to connect to and had to be changed if we did a failover.

Describe your environment in more detail. Need to know your HA and DR requirements, plus database drivers, reads vs writes, etc.

5

We have a clean slate, basically to build on.

  1. 4 servers\nodes
  2. plan was Primary, Secondary, Secondary Read Only, 4th? not 100% sure how we want to utilize
  3. Apps are built pulling a lot of the same information from tables via stored procs and use a lot of Nhibernate code. also developed using a lot of Cache usage and temp tables
  4. about 100 dbs, but pnly about 9 tb of mdf\ldf\ndf files.
  5. we are only converting our Prod environment.
  6. DR plan hast to be completely revised and so does the HA.
6

Nhiberate - yikes. I've spent a ton of time troubleshooting performance issues when apps use it. Make sure you are specifying varchar for parameters in your code when the database column is varchar. It'll use nvarchar, and you'll get an implicit conversion which causes a scan.

Do you guys have clustering experience? AGs require a lot of knowledge about clustering, votes, quorum, TTL, etc. Lots of people have just implemented an AG and then wonder why their primary server goes down when they reboot the secondary node. The right quorum configuration is critical. With more nodes and across different sites (such as DR), the right votes are critical.

For 2 readable secondaries, the AG is only going to route to one of them. But you can setup a DNS alias to get to the second one.

7

All of us are experienced on maintaining not building & design That is why I am trying to find the best resources to look at.

The more we discuss amongst ourselves, this the more I feel we are missing a BIG piece.

The references I have found aren't very clear.

8

I believe you might have to define what you want first, then put out a design and all of us and help to review it and see what other things can be modify.

9

You guys are missing what I am asking. Where do you suggest that I look for more in-depth detailed information on doing this.

I appreciate the input but I can't design something that I don't know enough about. I need better resources and I am not finding any.

10

I'll link you to my employer, Brent Ozar. Type this into google: site:brentozar.com availability groups

Lots and lots of detailed information on the subject. When you have a more specific question, get back to us here!