0

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.

  • Where is the fileshare witness located ? How are you defining quorum votes ? For your configuration, if you loose fileshare witness, you loose cluster ! Will this be a multisubnet cluster ? – Kin Shah Feb 24 '16 at 16:28
1

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.

|improve this answer|||||
  • Thank you! The file share will be on a fault tolerant VM with uptime of 5 9's. We are choosing asynchronous even though it will be in our main data center because we'll be launching the replica to a remote site as soon as the bandwidth has some more testing done to it. As far as the hard node coding--we are doing that for security reasons. The MS Access folks have inadvertently updated the pharmacy database on more than one occasion--thus the read-only replica. Of course, in an emergency situation we could manually failover and change a few connection strings. – Sting Feb 26 '16 at 20:56

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.