1

I have two SQL Server instances running on Windows Server, SQL Server cluster with 2 nodes with shared storage (Active, Active type).

The SQL Server version running is SQL Server 2012 Enterprise edition.

Can we implement AlwaysOn on this? Is it feasible then what are the complexities and steps?

Improve this question
0

1 Answer 1

Reset to default
2

Yes and no. You cannot configure it in such a way that each server hosts a copy of the database. They both share the same storage, so that is logical. You have a SQL Server cluster OR you configure AlwaysOn between the 2. However, you can host an AlwaysOn copy on a SQL Server failover cluster instance (FCI). In that case, you need one or more other SQL Servers to host the synchronous or a-synchronous copies. The picture below might illustrate this a bit (no sure where I got it from, it is from my own library):

Mind the difference between the two: a SQL Server failover cluster hosts an instance of SQL Server using 2 servers in an active/passive configuration. An AlwaysOn node hosts a database or group of databases on a single instance.

Here are some possible usefull links:

https://blog.papercut.com/the-ultimate-guide-to-high-availability-methods-for-microsoft-sql-server/

https://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

https://techcommunity.microsoft.com/t5/Premier-Field-Engineering/An-overview-of-High-Availability-and-Disaster-Recovery-solutions/ba-p/370479

Is this the configuration you currently have?

enter image description here

If so, then you can create a setup like the one below. You can see that there is an extra SQL Server node with 1 instance that hosts an a-synchronous copy for each database of the availability group (where a-synchronous would be common for a DR setup). You can use synchronous copies technically, but you would not use that for a DR scenario. Synchronous copies rely heavily on latency and is therefore suitable for an High Availability scenario. For your understanding; look at the AlwaysOn layer as a seperate thing. There is no difference in configuring the AlwaysOn group on single server instances or a Failover Cluster Instance. AlwaysOn is configured at the database level.

enter image description here

Be aware that you need to buy an extra license in this configuration. However, recently there was a change in licensing for SA customers, which would make it free:

https://cloudblogs.microsoft.com/sqlserver/2019/10/30/new-high-availability-and-disaster-recovery-benefits-for-sql-server/

Also; don't forget to look how you would setup your maintenance scripts and that instance level configuration is not copied to the other server. If you require things to be in sync, then you need to script that or update it manually every time you change something.

I found this easy setup guide for you to configure the AlwaysOn group:

https://www.sqlservercentral.com/blogs/alwayson-availability-groups-step-by-step-setup

Also be aware that mixing 2 seperate technologies (Failover Cluster Instances and AlwaysOn) make things more complex. It is more complex to troubleshoot then when you have only 1 technology. You could setup a similar HA/ DR setup with AlwaysOn only like below. Just be aware that you need double the storage space (which you don't need for a cluster setup) and you would have some more latency (because you need to wait for the commit of the synchronous copy):

enter image description here

There are a lot of things you need to make decisions on, because all the technologies have their strong and weak points. The only option to learn them all is to read about it, but I think you get a fairly good jumpstart with all this information.

Oh and you might want to delete your Answer earlier. I saw you also set it in the comments, so it is double.

Improve this answer
5
  • Thanks Niels Since I have two SQL instances running on Windows, SQL cluster having 2 Node with shared storage( Active, Active type) SQL sever version is running on SQL 2012 Enterprise edition Can we implement Alwayson on this current setup for DR ie disaster recovery as HA is already there. I am asking Is it doable and how? Thanks
    – mukki
    Commented Jun 2, 2020 at 12:15
  • You are talking about 2 instances and a 2 node cluster. So if I understand you correctly, you have a 2 node SQL Server cluster with 2 instances running on top of it? Do you have a 3rd SQL Server for the DR copy or are you trying to achieve this setup with the 2 SQL Server mentioned? Remember that if you have a cluster of 2 nodes and you want a DR copy, you will need a 3rd server, but if you can provide the given information, I can tell you more on how to set it up.
    – Niels Broertjes
    Commented Jun 2, 2020 at 12:47
  • Hi Niels I already have 2 node running with two separate instances active, active this is the current HA setup having SQL 2012 enterprise edition servers. In this i need to add and create new DR node by adding new server and decide on methodology.
    – mukki
    Commented Jun 2, 2020 at 16:24
  • I don't know if I understand you correctly. An instance cannot be active/active on both nodes. I thought with Active/active that you meant the storage paths. You could only have an 'active/active' setup if you have one instance on each cluster node active (then each cluster node also hosts 1 passive instance). I have drawn a diagram and put it in the previous answer. Is that the configuration you currently have?
    – Niels Broertjes
    Commented Jun 2, 2020 at 18:24
  • Yes thanks, Neils configuration is the same as you mentioned
    – mukki
    Commented Jun 3, 2020 at 1:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.