1

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.

  • 1
    SQL Server 2014 standard edition does not supports AlwaysOn Availbility replicas, so that possibility is ruled out. You need to have enterprise edition. Yes SQL Server 2016 standard will support it. – Shanky May 24 '16 at 9:09
  • Hi Shanky, Gotcha!!! But we can't go for Enterprise edition because of budget limitation and we have already purchased 2014 Standard edition. So we have to find the best possible solution from the available features and software. We are planning to use DB Mirroring for production and we can use Log shipping for replicating data to Reporting Box. But not sure whether it is possible or not? And if possible then how to do it? Anyway thanks for replying.... :) – sachin-SQLServernewbiee May 24 '16 at 9:21
  • 1
    You cannot use mirroring as well because in mirroring the mirror server is always in restoring state so you CANNOT run anything on it. You can have snapshot on mirror server but that would require SQL Server edition to be enterprise. You can go with Logshipping or replication. My choice would be Logshipping – Shanky May 24 '16 at 9:38
0

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

  • 1
    As @Shanky commented above, no, mirroring can't be used to offload reporting. Snapshots must be used to support reading the mirror, and snapshots are Enterprise only. – Aaron Bertrand May 24 '16 at 12:07
  • Yaa @AaronBertrand, Shanky, You both are right. We are planning for Logshipping as of now. But we have another concern. I have raised a separate question for that Please find the link dba.stackexchange.com/questions/139363/… – sachin-SQLServernewbiee May 24 '16 at 13:07

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.