0

The best guidance I've found is this, though I'm having difficulty understanding some aspects of it. A summary of the advice is to create farms such as:

farm 1:

  • Availability Group 1 with Config database on SQL1 and SQL2, in sync mode
  • Availability Group 2 with content database on SQL2 in Async mode (syncing with sql3 in farm2)

farm 2 (DR farm)

  • Availability Group 3 with Config database on SQL3 and SQL4, in sync mode
  • Availability Group 2 with content database on SQL3 in Async mode

In the above, a failure in SQL2 will require a failover to Farm2, as SQL1 has no access to the content db.

Do I understand correctly that I need to tell my DBAs the following:

  • Keep SQL2 (with the content dbs) as the active node
  • Never simply failover to SQL1, as users will lose access to all content (patching SQL2 will require a failover to the DR farm, for example)
  • Restore the search config database to the dr farm any time the search config changes, then recreate the search service and do a full crawl

This also means that when I configure SharePoint:

  1. Provide the listener name for the system databases (this allows SP to keep functioning when sql fails over)
  2. Provide the alias of SQL2 for the content databases (this allows me to switch the server for the content dbs)

Any thoughts on this?

1 Answer 1

1

Your Content and most Service Application databases should be in Sync mode between SQL1 and 2. This will give you an 'any time failover' option, including during business hours. The only database you should not have in Sync mode is the Usage database (this database can be unavailable). For the Usage service, point at a specific database.

Your Prod -> DR via ASync is correct -- and do not sync Usage.

For Search, I would instead just plan on manually keeping the Search schema in sync. Easier to deal with, in my opinion.

For SharePoint, during install, yes use the AG Listener name! SQL Alias should not be used in an AOAG configuration. This applies to all Service Applications (sans Usage) and Content Databases created.

5
  • Using sync mode for content and service dbs between sql1 and 2 makes sense for HA, but I thought I couldn't have them participating in both sync and async? Perhaps I'm missing something about availability groups? Can one availability group operate in both modes? Or, can the same databases belong to two AGs? I thought both of these were not options, thus my confusion about how to get this set up.
    – Mike2500
    Aug 15, 2019 at 16:22
  • You would have an ASync node (SQL3) as part of the AOAG. Take a look at blogs.msdn.microsoft.com/sambetts/2016/12/05/….
    – user6024
    Aug 15, 2019 at 16:53
  • So the AG supports having a third node that his async (while the first two are in sync mode)? The article you linked is assuming this isn't possible, thus the discussion around SQL2 being a single point of failure for the primary farm. The solution in the article isn't syncing content between sql1 and 2, but rather configuring sql2 as failover cluster. Perhaps the whole series of articles is simply wrong?
    – Mike2500
    Aug 15, 2019 at 17:15
  • Yes, you can have a few ASync nodes (depends on the version of SQL). The ASync node is a 3rd leg. No, the articles aren't wrong -- it's about creating HA in both Prod and DR. It's complex and burdensome. You could consider going to a single SQL Server node in the DR location and use a SQL Alias for your DR Config/Admin/Search databases. It depends on how long you plan to stay on DR.
    – user6024
    Aug 15, 2019 at 17:17
  • Thanks Trevor, I was misunderstanding the ramifications of having the DR farm enabled for HA. Your last comment cleared everything up. Much appreciated!
    – Mike2500
    Aug 15, 2019 at 19:32

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.