We need to know the risks/pitfalls of a particular AlwaysOn configuration. We want an AlwaysOn configuration with the following requirements(below).
My question is: Is this a sound configuration or am I missing something vital or is there a linchpin I'm missing that invalidates this setup?
Source SQL Server:
Runs on virtual machine A
Pharmacy application connects to a primary database with full access
Target/Replica SQL Server:
Runs on virtual machine B
Microsoft access connects to replica(of above) database with read-only access to run lots of reports
Note from Microsoft white paper (we want to connect this way for both nodes–node\instance-name versus listener):
You can connect to the secondary replica using node\instance-name: Configure the primary replica to PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE) and use ‘ApplicationIntent=ReadOnly’ on your connection string for reporting workload. In this case, if you mistakenly connect your reporting workload to a physical node that is already running as a primary replica, the connection is denied.
Fail over is manual process. Should A go down, we'd manually bring up the replica server B as the main server–asynchronously for starters — even though there would be some room for data loss.