Sql-server – High availability SharePoint farm with restricted conditions

disaster recoverylog-shippingsharepointsql servertransactional-replication

Currently, our SharePoint farm has restricted conditions as below

  • Shared folder is not allowed
  • FTP/SFTP is not allowed
  • SQL FILESTREAM is not allowed
  • SMB protocol is not allowed
  • Using Windows Server Failover Clustering (Not SQL AlwaysOn FCI) for SharePoint database servers

I want to setup a DR farm for the existing production.
First, we tried with log shipping but it turned out that log shipping does not co-exist with Failover Clustering, as stated here
https://msdn.microsoft.com/en-us/library/bb500117(v=sql.105).aspx

Then, I came up with idea of using transactional database replication, but still, have considerations that couldn't find elsewhere

  • Does Transactional Replication require shared folder? (For example, to copy log file)?
  • Can Transactional Replication co-exist with Failover Clustering?

Please propose if there's any possible Disaster Recovery solution for this requirements

We don't use Failover Cluster at instance level but server level

Best Answer

Note: This part of the answer corrects the misconception about Log Shipping not being compatible with Failover Clustering.

As @sp_BlitzErik implies in the comments, Log Shipping is very much compatible with Failover Clustering. I've seen others fall prey to this implied lack of interoperability as it seems the MSDN article you link to is a direct reference of this MS Blog post from 2013. It seems even @Shanky commented about this discrepancy directly on that article as well.

Long story short, that article is incomplete. It should also reference Failover Clustering under Log Shipping along with a handful of other DR technologies.

Final note on using log shipping with Failover Clusters; the main caveat is to ensure your TLogs are being backed up to a clustered share that can failover along with the instance and other clustered services.

Answer: Now to address the rest of the answer.

You've got a few options. Really what you're able to do is often limited by your budget (e.g. licensing, hardware, etc.), so any option proposed will need to fall within your budgetary constraints. That being said, here some options I can think of, but by no means do I consider this a complete list of your available options:

  1. Log Shipping: For the sake of completeness, you CAN configure log shipping on a failover cluster. As I say above, be sure to configure the backup location as a shared folder that fails over along with the other clustered resources (e.g. SQL Server Service, SQL Agent, etc.).
  2. Multi-Subnet Failover Cluster: This is adding onto what it sounds like you already have, but you can include a different site into your failover cluster. Proper care will need to be taken so that automatic failover to your DR site is only done when necessary. Additional constraints are replicating the data to the DR site. Configuration of this approach is not simple and will depend a great deal on your Windows Version(s), SQL Server version, budget, etc.
  3. SAN-to-SAN Replication: This is generally a hardware based approach and will depend upon your SAN vendor. Most Enterprise-level SAN solutions support SAN-to-SAN replication and in conjunction with virtualization/etc. you can run across some elegant and expedient DR solutions.

Disaster Recovery is a complex topic and providing anything resembling a complete answer can't be provided on any forum answer. If you're new to this or feel overwhelmed, I heavily suggest reaching out to MS, a MS Partner, a Consulting Firm/Consultant, etc. and ask for help.

Related Question