Switching from a SQL Server 2016 Failover Cluster to Availability Groups

Question

The DBA role fell into my lap and now I was asked to investigate Always On Availability Groups.

1. So, in the Availability Group environment, do all servers need to have the same specifications? Say I have Server A with a 20 core enterprise environment, do servers B & C have to have 20 Core each?

2. How does Disaster Recovery work? Currently I have a mirror to an offsite with a decent connection, do I have to up the connection bandwidth? Is the DR site part of the Availability Group? I was under the impression (probably 100% wrong), that if the db is in recovery mode, not accessible, you don’t have to pay for the SQL Server licenses. Now with Always On sounds like you are forced to get the licenses even if you are not accessing the server.

3. Using Availability Groups, can I create a primary server for one group and set a different primary server for another AG, is that possible?

Answer 1

Do you need to have the same server configuration for both nodes?

No, this is not required, however it is a best practice. In the event that your secondary replica has fewer cores and less memory then you would see a degradation in performance when failed over to the lower powered machine. This might still give you sufficient performance, only you can make that decision, but it is not a requirement.

One thing I would recommend is to always ensure that you keep the drive letters and paths the same on all machines in an AG, otherwise you'll run into a lot of problems attempting to add data or log files.

How does Disaster Recovery work?

You will need to license your secondary if you allow read intent routing. That is something that gets configured at the AG level. But ALWAYS check with a licensing representative to ensure that you are compliant.

You should not need additional bandwidth for AG traffic if you are already using mirroring. Packets are compressed prior to sending between machines (when running in asynchronous commit mode).

Can I create a primary for one AG and a different AG for another?

Yes.

The limitation here is that a database can only reside within a single AG, and that all nodes must be in the same WSFC. Be aware though (going back to your licensing question) that if a server is a primary for databases then it must be appropriately licensed.

Answer 2

Its good to see that you are working with Alwayson.

Here are the answers for your questions:

Do I need the same config for all the nodes?

Unfortunately its YES, becuase alwayson is helping us to make the business always up. Once the Primary goes down, then Alwayson will failover to another node and make that as the new primary. So it must be have the same config to get the same performance here. Luckily this is the only case you must have same config. If you are using Availability group for Readonly purpose then make the reader with less config.

Alwayson is for DR?

NO, Human errors are also considered as DR. In this case if someone deleted a table, then this statement should immediatly replicated to Seconday. So there is no delyed replica here.

Multiple Availability Groups?

I didn't understand this question, but as per my understanding, You can have more than one availability Group, but you can't add one database to multiple availability Group.