Sql-server – Setting up HA and a synced up reporting box all together using SQL Server 2014 Standard Edition

high-availabilitysql serversql server 2014

Scenario:

We are going to upgrade our third party application. Also we are going to upgrade our DBMS from SQL Server 2008 R2 to SQL Server 2014 Standard edition. We also wanted to have a high availability option for our back end. The HA options available for SQL Server 2014 Standard editions are

  • Log Shipping,
  • DB Mirroring(safety full only),
  • Alwayson Failover Cluster Instances (Node support: 2) and*
  • Replication.

We also want to have a third box which should have the exact replica of data that is present in above two. This box will be used for reporting purpose.

Question:

Please provide the best possible setup that can be made with all the options available so that we can have best HA option and also a reporting box with synced-up data.

Note: VMs is not a problem. Currently we are allocated 3 VMs, we can go for 4 as well if needed.

It will great if somebody can provide their insights.

Best Answer

I would investigate the following scenario: Setup a failover cluster to take care of the HA requirement and set up a third box with database mirroring to offload the reporting part.
Take a look at these articles for more details:
https://msdn.microsoft.com/en-us/library/ms191309(v=sql.120).aspx and https://msdn.microsoft.com/en-us/library/ms175511(v=sql.120).aspx

Related Question