Sql-server – Load balancing Availability groups with SQL Server Standard

availability-groupsload balancingsql serversql-server-2016

So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration.
Is it smart? Are there any issues I am not thinking of?

We have SQL Server Standard, and as such, you can only have 1 DB per AG (we have 20 databases), and no Read-Only secondaries.
Basically meaning, the primary server is doing ALL the lifting, with the secondary doing a lot less.
So essentially, you are paying for resources for Node 2, that are sitting at 10% workload, while node 1 is at 70/80% workload.
Both nodes are fully licensed with regards to SQL Server cores.

What I have done, to assist this, is slit the database primaries up.
So about 50% of the databases are Primary on node1, while the other 50% are primary on node2.

The results:

The applications all connect great to either node via their respective listener.
If a failover occurs, just the databases on the failing node are affected, and failover to the other node (we have tested this fairly well).

Each node, can now split the load, essentially load balancing.
It is a manual process to set it up this way and when deploying new DBs and groups they go to the lighter node. but a small price to pay for "more" hardware punch without much cost (licenses which we already have and a bit of admin).

What are your thoughts on this, guys?

Best Answer

I kind of like your idea, although it defeats the idea of a primary and second nodes. You should be sure to evaluate if one server can take the load of both, as if one goes down, then one server will have all the load.

You should also be careful, if the activity on one server gets to high in time, it might affect the "seconday" node activities and cause I/O bottlenecks. (It all depends on how your disks are confugred.)

But other than that, it could be a good alternative and when your organization gets bigger, you will be able to afford another server to remove some load from your current setup. Just make sure to review carefully your emergency scenarios and to test them well.

Related Solutions

Sql-server – High latency with SQL Server 2014 Synchronous AlwaysOn

Turning my comment into an answer.

There seems to be some misunderstandings of how certain performance counters work when it comes to availability groups. Only Synchronous Commit replicas will be counted toward Transaction Delay (ms)/Sec Asynchronous Commit replicas will not change this counter as they transfer no mirrored transactions (yes, this could have been called something better).

Let me correct some of the assumptions in the thought process in the original question.

2.Using AG1 as Sync has a Transaction Delayed time of 1000ms/sec which is very bad and we can't figure out why. HADR_SYNC_COMMIT wait time is all over the place.

Transaction Delay (ms)/Sec, in a vacuum, means only that you have at least two synchronous commit replicas. This is because ONLY synchronous commit replicas have this value as we wait for the acknowledgements from the other synchronous replica that the data is hardened.

In order to have a complete picture and get an AVERAGE of how much latency overhead PER TRANSACTION we will need to capture the Mirrored Transactions/Sec counter. This counter will give us how many synchronous commit transactions were "mirrored" to other synchronous replicas. If we take the Transaction Delay (ms)/Sec and divide it by the Mirrored Transactions/Sec counter the end result will be the average delay in ms per transaction.

3.Using AG1 with Async we have 0 latency, but that is something we can't use.

This is not accurate at all. Ther, in fact, is GREATER latency is getting the transaction blocks hardened on the asynchronous commit replicas it's just that we don't wait for them to be hardened like we do with synchronous. Thus, there is still a delay (your network doesn't magically become a non-wait environment) it's just not counted in that specific counter as that counter ONLY contains information for synchronous commit replicas and transactions.

6.During the insert which is a single session running sequential insert commands (or update, or delete) I ran a trace on that session and saw that the duration was exactly the same as the graphs below.

There is not nearly enough information to make me believe this is the case. Even on some pretty terrible infrastructure I normally see 200-300 ms and nothing near 1 second PER individual transaction.

7.When running the same test without Sync (meaning Async, or directly on each node) I didn't see any latency, the duration for each operation was 0 (zero).

See my response to 3. which is also the same answer to this.

Follow Up

Capture both counters, Transaction Delay (ms)/Sec AND Mirrored Transactions/Sec and post back the results.
Test against your specific workload as the change in workload types will greatly factor in to response times.

Sql-server – Login in Always On Availability Groups

Putting in a simple language :

A login gives you access to server.

A user gives you access to database.

A login must map a user so that you can access the database.

For SQL Authentication, you should create the login with SAME SID and map it to your user database/s on all the nodes with proper permissions (follow the principle of least privileged). This script will help you or this SSMS addin from sqlskills.

For Windows Authentication, you should create a login and map it to your user database. Create/transfer the same login to other nodes.

A listener is a virtual interface and has nothing to do with logins or users.

Best Answer

Related Solutions

Sql-server – High latency with SQL Server 2014 Synchronous AlwaysOn

Sql-server – Login in Always On Availability Groups

Related Question