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
Witness:
File share
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:
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.
Best Answer
The only possible concern in your setup is the Availability Mode and Failover option. If your virtual machines are in the same datacenter or relatively close geographically with a good connection between them you should look into using Synchronous-commit mode and Automatic Failover for the best recovery scenario with no data loss and minimal downtime, especially if it fails during a time it may be difficult to get someone to manually fail it over.
If they are they significantly geographically apart and connectivity isn't reliably 'good' then your setup is fine unless data loss is considered catastrophic. The Asynchronous route is primarily for use when performance is sporadic or far from guaranteed.
While specifying the connection has some value, it may be overkill in such a setting if for some reason your reporting application loses connectivity to the Secondary Replica. There is no reason it couldn't report off the Primary Replica aside from any possible performance concerns, but that may be a valid enough reason to insure the Primary denies the connection.