1

I'm drawing up a proposal for upcoming infrastructure changes. This will include a production server and reports/data warehouse server, each with Always On. To keep hardware and licencing costs down, is it possible to run in a configuration of Server-A running Prod-AG Primary and Rep-AG Secondary, and Server-B running Rep-AG Primary and Prod-AG Secondary?

I presume each server would need 2x of the following WSFC instances,sql instances, AG's, listeners, DNS names/ports.

I hope this makes sense, here's a diagram of what I think it will looks like.

Desired Solution

In the case of a fail-over on either node, the workload/business need isn't that great that running off the same server for a couple of hours would be a major issue.

I've only found a couple of mentions of a similar setup that kind of worked but no definitive information from Microsoft or anyone who's successfully ran this setup.

SQL Edition will be 2017, most likely standard, I don't think we'll be approved for Enterprise. OS will be Windows Server 2016 Core.

5

Yes, you can have more than one availability group on a cluster. Each availability group is completely independent of the others, and can be failed over to any node in the cluster separately. From Configure SQL Server 2012 AlwaysOn Availability Groups for SharePoint 2013:

Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers. This reduces the need to have idle high performance standby servers to handle the full load of the primary server, which is one of the many benefits of using availability groups.

The is of course relevant regardless of whether SharePoint is involved.

You will not need additional instances--just one default instance of SQL Server on each node. And I think you may be confused about WSFC instances. You simply set up the cluster, install SQL Server on each node, and then create the availability groups. When you create the AGs, the setup process will create all of the cluster roles and resources that are required.

Search YouTube and watch one being set up and I think that will make things clearer (https://www.youtube.com/watch?v=VKCqRgqLAuo). The documentation gets murky because it continually refers to WSFC instances and availability groups, but if you're using straight AGs, you aren't going to be using WSFC instances. It makes it appear far more complex than it is.

  • Thanks for the answer! In terms of WSFC, i have used the method you described before a few years back, I was thinking of WSFC for backup disks so in the event of a failover the backup paths stay relative to the active server at all times and there is a centralized repository. Or is this role and the SQL AG mutually exclusive and not tied into the separate shared disks? – Ollie Jan 17 '18 at 13:53
  • It is not tied to any shared disks, but once the AG role is created, you could put a shared disk into the associated role so that it would follow the AG. – Tony Hinkle Jan 17 '18 at 14:26
  • Great idea, I think that covers everything, Thanks again. – Ollie Jan 17 '18 at 15:33
3

Yes, but you don't need different instances to do that. You can do just one instance per server, and run different Availability Groups. In your diagram, just remove Instance B2 on both nodes, and run the reporting AG on the Prod instances.

  • Thanks Brent, that's great, i was worried about having to carve out resource on an instance level, now I'll be able to configure Resource Governor ready to handle any resource contention in a fail-over situation! – Ollie Jan 17 '18 at 13:39
0

I like the 2 instance approach because it allows to allocate CPU and memory per instance, as well as more granular security, such as granting sysamin access on a instance basis. On a single instance one could use query governor, but that is an enterprise only feature and requires more setup. The downside of a multi instance approach is you lose the ability to do cross db commands, if you are copying data from prod to reporting, you can still do cross server commands, but those are less efficient.

  • Welcome to DBA.SE! You may want to take the tour, to better understand how the site works. This is a question and answer site, not a discussion forum. Your response is less a full answer of the question asked, and more a comment on the existing answers that suggest not setting up multiple instances. – RDFozz Oct 24 '18 at 19:57

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.