1

Is there any way to implement Always On Availability Groups using SQL Server 2019 Enterprise Edition on a Single Virtual Server.

The goal is not to have any HA or DR capability, but merely Read-Scale as a method to distribute read operations to a second replica. The thought is to have two or more instances on the same virtual server, with each instance having a portion of the RAM assigned based on load experienced for reading vs. writing. The memory for each SQL instance would be less cluttered, and less locking etc would occur, and performance would be expected to improve even with a single server.

Attempting to do this with two instances on the same server, SQL complains in the AG Creation Wizard that the name of the replica node has the same base server name included, even though there are two separate instances. Essentially, it is interpreting the base server name of the instance name as the 'node' (even though the setup for read-scaling does not require WSFC node setup), and thus the replica is not allowed to be created on the same node.

Is there a way around this?

Thanks!

Script I tried without success when instances were on same server:

CREATE AVAILABILITY GROUP [TEST_AG1]
WITH (
 AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
 DB_FAILOVER = ON,
 DTC_SUPPORT = NONE,
 CLUSTER_TYPE = NONE
)
FOR DATABASE [Northwind]
REPLICA ON N'ROBSWS2019\SQLA' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2019.AUSTINLANE.local:5022', 
 FAILOVER_MODE = MANUAL, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
),
 N'ROBSWS2020\SQLB' WITH (
 ENDPOINT_URL = N'TCP://ROBSWS2020.AUSTINLANE.local:5022', 
 FAILOVER_MODE = MANUAL, 
 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
 BACKUP_PRIORITY = 50, 
 SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
);

GO

3 Answers 3

2

I definitely understand the comments from Lee and Rodrigo, and really appreciate you pointing me at those additional resources. I think your observations are generally correct.

However, I have discovered an approach that may bear fruit on this endeavor. Rafael Rodriguez has accomplished this through the use of Docker Containers. He has an article about how he did this AOAG Read-Scale Clusterless.

He also has the entire project in a git repository.

I downloaded it and fired it up in Docker on my Win10 workstation. I was able to connect to both instances from SSMS, add a table to the primary and then see it show up in the secondary replica!

I think this essentially answers this question. My next step is to set up the particulars of my use case, and determine a testing strategy to get benchmarks and performance improvement data. I will try to circle back and post any results.

Thanks to everyone for helping me move forward on this project! Merry Christmas!!!

1

I don't know if this is possible, but I wonder if this will provide your intention. Scaling to the same machine requires you partition resources like CPU and memory.

If you want less locking, you tried to use Snapshot Isolation Level? AlwaysON works using this technology to allow secondary reads while redo is writing... (Check this: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver15)

Using the same machine maybe don't will provide your intents because you will need to divide CPUs between two instances to avoid CPU level concurrency.

If you still want to create on the same machine, check if doing it via a script you can build de AlwaysON manually.

2
  • Yes, I am expecting to divide CPUs and memory between the two instances. The expectation is that by having two separate instances, each with their own memory and resources, the amount of locking/queing/complexity will be reduced overall and provide better performance than a single instance on the same machine with the combined CPUs and memory. Snapshot Isolation Level is definitely already in our mix for performance, but is only a part of our overall performance solution and needs.
    – RobGInDFW
    Dec 21, 2020 at 21:00
  • I have attempted with a script, but maybe there are more options that I need to consider. Above in the edited question is what I have tried at this point with no joy when the two instances are on the same server.
    – RobGInDFW
    Dec 21, 2020 at 21:00
0

Read scale is going to be possible only with two VMs. If you install two instances on the same VM, then there really is no read scale advantage, even if you could get the install process to work they way you're trying. The same VM resources are supporting both instances. The whole point of read scale is to have reads happen on separate resources on a separate VM so they don't impact the resources on the primary. I've never seen the documentation indicate that you can do what you're trying to do. A minimum of two nodes is always in view when discussing read scale.

See here Also see here

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.