As always, the job of the DBA is to present the technical opportunities along with their risks and costs to management and get them to tell you in writing which option to implement.
There are a number of ways to tackle this problem. All of them have some issues. I have used all three of block-level replication, SQL replication and Availability Groups, and my choice for lowest cost and risk is AGs.
If you set up your servers as a distributed Windows cluster you can use a standard AG and listener to deal with HA for your application databases. There are two main ways to setup the distributed cluster: a) common subnet within a vpn; b) local subnets on each node. Most people today do the local subnet route as it is 'easier' to manage so I will assume you go this way.
You will need an Enterprise license to do AGs with a listener, but my experience is the cost is worth while. When you install SQL remember to select the extra option for the distributed cluster. Also manually set the TTL for the listener to a low value both in DNS and its equivalent in the cluster properties - particularly important in a distributed cluster to allow your apps to connect after a failover.
After the install you can test it by adding all your user DBs to the same AG and you should find these fail over cleanly, and you can access them all via the listener name.
Now for the main problem - system DBs. There is no AG support in SQL2019 for system DBs, and also none using replication. You will need to build a process for copying over everything your organisation adds to master, msdb and model. This includes logins and rights for master and jobs for msdb, plus any changes to all of these. Possibly your best option for this is to exploit the DBATools procedures - these have a wide base of people maintaining them and work well. However you will have to build your own process to run them. Most items can be put into SQL jobs and run on a schedule with with restricted authorities. However, some items will need to run using both sysadmin and Windows local admin rights, the lowest risk for these is to run them manually. Dealing with system DBs is the worst aspect of using AGs but overall AGs IMHO give both the lowest risk and cost.
Migrating to this gives a different issue. The easiest way is to use DNS aliases. I prefer a two-level approach to aliases. A) Each set of HA servers has its own Failover Alias, this should be targeted at the AG listener. B) Each set of applications and databases has its own Application Alias. This should be targeted at the Failover Alias for the HA set that is hosting its DBs. All application connection strings need to be altered to use the relevant application alias. This can all be finished before you start the build of your new servers.
Migration needs testing, often repeatedly until you are confident everything will work. When you do the final cut-over, you then change the failover alias from pointing to your old server to pointing at the new AG listener. No connection string changes should be needed at this stage.
Your aim is good, to reduce business risk and improve resilience. Whatever way you do this will need careful planning and support from management. You will learn new skills and make some mistakes. However, the end results should be appreciated way above your line management level.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara