1

In our current set up we have 2 node SQL failover cluster in data center DC1 with mirroring enabled. And similarly in DC 2 a 2 node SQL failover cluster. DC1 acts as primary via DB mirroring in one point of time and DC2 mirror. Above runs on Windows server 2012R2

How can I convert the above setup from sql cluster to get cluster so that can leverage always on. I know this is sort of s big question but needed to know how can this be achieved with some overview and I can do some poc on that. We are thinking to upgrade to sql 2016 on Windows 2016 if possible.

Please guide for any real time experiences or blogs I can go through to do such migration.

Edit-- Per Answer below which needs the DR server at different DC to be out for route seems to be last option but i will still test to figure out the timings.

Also, is it possible to have setup like configure LS on current prod server with 4 node new built multi site cluster for alwayson AG? And during cutover we can simply stop the LS job on prod and convert the LS secondary on 4 node cluster to alwayson AG configuration

Improve this question
1
  • Why not just simply replace mirrorong with AG, one replica on existing FCI in DC1, and secondary in DC2? Commented May 1, 2019 at 7:54

1 Answer 1

Reset to default
3

To migrate from two Failover Clustered Instances (FCI) to an Availability Group (AG) that spans four Availability Replicas is fairly straight forward but somewhat of a lengthy process. Also, some downtime is required to point your application to the Availability Group Lister and manually fail over to one of the Secondary Replicas.

This is a high level overview of the process so use the links provided to plan:

  1. Disable Database Mirroring
  2. Completely uninstall the FCI and destroy Windows Cluster at DC2
  3. Install Standalone SQL Instances on the two nodes at DC2
  4. Join the two new stand-alone servers at DC2 to Cluster at DC1 and configure the AG
  5. Take down time to fail over to to a Secondary Replica in DC2 and update Application to point to Listener
  6. Remove FCI from AG
  7. Completely uninstall the FCI at DC1
  8. Repeat Step 3 at DC1
  9. Add new stand-alone instances to AG

All of this can be done on SQL Server 2014. There are some benefits to upgrading to 2016 but I would recommend you read this to help plan as there are a lot of similar steps to upgrade.

Improve this answer
5
  • 2014 is really buggy. There even is a bug that stops sp_send_dbmail silently. 2016 is good, 2017 is better. The QUERY store alone is worth it. Query Store Overview - Microsoft Doc. Oh, and 2017 has resumable index operations - Guideline Microsoft Docs Commented Apr 28, 2019 at 6:55
  • @clifton_h to be clear I was not advocating not to upgrade to 2016. When I mentioned "there are some benefits to upgrading to 2016" I was speaking specifically any benefits 2016 might provide in migrating from Failover Clustered Instances to Availability Groups, such as Automatic Seeding. Commented Apr 28, 2019 at 17:33
  • Fair enough, my comment is mostly just a comment, as your answer is good. As an extra note, 2016+ also has load balancing for AO. Configure read-only routing load balancing Commented Apr 28, 2019 at 18:04
  • @KenWilson: Thanks. Its a good one. However the problem with above approach is we cannot afford to have DR down for that long per company standards atleast in prod/dr scenario. Can there be a approach like setting up log shipping from SQL FCI to new build 4 node geo cluster in prior Commented Apr 30, 2019 at 0:29
  • @BeginnerDBA I guess the requirements for down time would be a good thing to know. I believe steps 1-4 could be done inside of a day and if that is too long you can do half the cluster at DC2. However, if you can build four 2016 stand alone instances first. Then you could just follow step 4 to join all the servers to the current cluster at DC1. Then follow step 5, failing over to one of the 2016 nodes at DC1, and 6. Commented May 1, 2019 at 22:08

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.