As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward.
One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your latency between the servers to ensure you're not creating a bottleneck that is going to hamper write performance.
Synchronous commit AGs require all transactions to be hardened to the log on the secondary prior to committing them on the primary, so if your latency across the 150 miles between servers is too high, this can cause substantial performance issues.
Clusterless AG came to my find but it seems HA wont be available as
automatic failover might not work. Am I missing something here or is
No, you are correct. Clusterless AGs, or read-scale availability groups, are not an HA option. There is no automatic failover in a read-scale availability group. See this link here which states:
This is not a high-availability setup. There is no infrastructure to
monitor and coordinate failure detection and automatic failover.
Without a cluster, SQL Server can't provide the low recovery time
objective (RTO) that an automated high-availability solution provides.
If you need high-availability capabilities, use a cluster manager
(Windows Server Failover Cluster on Windows or Pacemaker on Linux).
The read-scale availability group can provide disaster recovery
capability. When the read-only replicas are in synchronous-commit
mode, they provide a recovery point objective (RPO) of zero.
To your second point-
We can have an outage up to 8 hours to convert db mirroring to AG if
You could do the following:
- Enable clustering on Server A and Server B.
- Configure the Availability Group with both SQL instances on Server A and Server B as replicas
- Stop log backups on both servers
- Remove mirroring from DBs on Server A/B
- Add databases to AG on Server A
- Join databases to AG on Server B - This should be fairly seamless as the DBs on Server B should in recovery since mirroring was disabled. As long as no log backups have occurred, you should be able to sync the DBs in the AG without backup/restore or seeding.
- Update App connections to use the listener name.
Some of these steps require the servers or SQL Server instances to be restarted, so it will be multiple outages but each is shorter than your allowed 8 hours.
This is a very simple overview, and the process needs to be tested and any problems identified and resolved prior to running through this in production. If you have a non-production environment, start there and script every step so you can replay the actions in production accurately.
If you don't have a non-production environment, ask your sysadmins to clone your servers in an isolated network where you can practise the change. If you're running physical servers, then testing without a non-production environment is going to be difficult, but you could build a similar environment in Azure or AWS to dry-run the process.
Here is a useful link on configuring clusters for AGs. Its a bit older but the information is still valid. If you're not confident with this process, especially if you do not have a non-production environment available, it may be better to engage a third-party contractor/consultant/expert who can plan and execute this for you.