0

I have SQL 2016 Standard Edition to install and configure a HA/DR solution. I need automatic failover in a cluster of two nodes. I am proposing SQL FCI over database Mirroring for the following reasons:

  • FCI will need only two nodes for the configuration and will failover automatically
  • Database Mirroring is being phased out by Microsoft and moreover, in order to achieve automatic failover, we will need an extra server (for witness) in addition to the two nodes of the cluster. Hence requiring 3 difference SQL instances in 3 VMs.

Would you agree with my reasoning and are there any other things I need to consider please? Automatic failover is essential in the requirements.

Thank you.

0

I would add that the db mirror is a database HA/DR solution. Instead FCI is an instance HA/DR solution. That means it covers system databases and things like logins, linked server, jobs etc...

1
  • Thank you all so much for all your contributions, there has been a lot to take in from all your very helpful contributions, thank you so much. To answer some of the questions, yes, they are VMs with shared storage in an WSFC instance. @MBuschi thanks. I will mention others separately as I am unable to mention you all in one go. BTW, apologies for my late response. Thank you all again – PTL_SQL Jun 11 at 13:26
0

This is a very open question and a lot of information is missing.

  • Will your servers be running on hardware or are they already virtualised (VMware, Microsoft, other...)?
  • Local disks or SAN?
  • Do you realise that a FCI (Failover Cluster Instance) requires you to configure WSFC (Windows Server Failover Cluster)?

If you decide on FCI then you are introducing a whole lot of complexity. Opinions may vary, but based on the document Always On Failover Cluster Instances (SQL Server) (Microsoft | SQL Docs) you can still have single points of failure.

Example:

(Emphais mine)

Contrary to the availability group, an FCI must use shared storage between all nodes of the FCI for database and log storage. The shared storage can be in the form of WSFC cluster disks, disks on a SAN, Storage Spaces Direct (S2D), or file shares on an SMB. This way, all nodes in the FCI have the same view of instance data whenever a failover occurs. This does mean, however, that the shared storage has the potential of being the single point of failure, and FCI depends on the underlying storage solution to ensure data protection.

So your assumption:

FCI will need only two nodes for the configuration and will failover automatically

.. isn't complete. You will require shared storage as well in the background.

WSFC is a complex setup as is described in Before Installing Failover Clustering (Microsoft | Docs).

Answering Your Question

Would you agree with my reasoning and are there any other things I need to consider please?

Well see above. And I would not agree. Mirroring is very trivial and can be configured to run in a VMware environment, with a lower complexity and low requirements. Yes, you need 3 servers for automatic failover, but they come cheap in a virtualised environment.

FCI and WSFC can be tricky to install in a VMware environment. And come to think of it, why would your require a HA/DR configuration on the SQL Server level, when you have it at the VMware level?

  • FCI will need only two nodes for the configuration and will failover automatically

True, but with shared storage in the background.

  • Database Mirroring is being phased out by Microsoft and moreover, in order to achieve automatic failover, we will need an extra server (for witness) in addition to the two nodes of the cluster. Hence requiring 3 difference SQL instances in 3 VMs.

True, but comes cheap in a virtualised environemnt. And is easier to configure as you don't need an underlying WSFC and shared storage.

If we take the question's title as its own question:

Why choose SQL FCI over Database Mirroring

..then I can answer this with Microsoft's recommendation:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead.

There are pros and cons to both implementations, but I find the simplicity of mirroring outweighs the complexity of WSFC and FCI.

6
  • Why would you recommend disregarding this? "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On availability groups instead." here – George.Palacios Jun 8 at 7:56
  • @George.Palacios I agree with Microsoft's recommendation, but I voiced my opinion. – John K. N. Jun 8 at 8:56
  • 1
    Basic Availability Groups are the replacement for database Mirroring. – David Browne - Microsoft Jun 8 at 15:13
  • Thank you all so much for all your contributions, there has been a lot to take in from all your very helpful contributions, thank you so much. To answer some of the questions, yes, they are VMs with shared storage in an WSFC instance. @John thanks. I will mention others separately as I am unable to mention you all in one go. BTW, apologies for my late response. Thank you all again. – PTL_SQL Jun 11 at 13:22
  • @George.Palacios, thanks for your helpful comment – PTL_SQL Jun 11 at 13:26

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.