locked
SharePoint and Always On Basic Availability Groups? RRS feed

  • Question

  • I have a client who wants Always On configured on SQL, but only has standard licensing for SQL. Naturally a single Always On Availability Group would be a good solution, but that requires enterprise licensing. Basic Availability Groups have the limitation of allowing only one database per group, but you can set up several of them.

    Would SharePoint play well with one Basic Availability Group per database? Any gotchas?


    Brandon Murray

    Friday, June 30, 2017 12:15 AM

All replies

  • No, as various cmdlets only accept a single database parameter for multiple databases (Config/Admin, then Search).

    Trevor Seward

    Office Servers and Services MVP



    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Friday, June 30, 2017 12:27 AM
  • Could you unpackage that comment for me a little further? Not following what PowerShell cmdlets have to do with BAGs...

    Brandon Murray

    Friday, June 30, 2017 12:38 PM
  • I really need an answer to this. A key decision on a critical project is being held up due to the client's desire to use BAGs.

    Brandon Murray

    Wednesday, July 5, 2017 3:10 PM
  • Brandon,

    Trevor Howard means that there are AG-specific SharePoint PowerShell cmdlets to manage AGs. You can't use these cmdlets against BAGs. I assume, because of BAG limitations or SP2016 doesn't know about BAGs or the SP team decided against coding for BAGs. (SP2013 certainly woudn't know about BAGs unless a CU added it; I don't follow SP2013.) Maybe in the future, as SQL mirroring is being deprecated and the SQL team keeps adding to SQL Standard capabilities, BAGs will be able to be managed from SharePoint.

    I investigated BAGs, as we want to use them too with SharePoint 2016. We have a small SP2010 -SQL2008R2 implementation; one server each. We will be migrating to three SP2016 servers with two SQL Standard 2016 servers. BAGs sound great; beats restoring all databases from backup. I did a lot of research. There's virtually nothing on them and SharePoint from Microsoft or the SQL or SharePoint communities. Maybe because BAGs are so new. But I did come across replies to blog posts that indicate a definite interest is there. Maybe at some point Microsoft or the communities will offer guidance, particularly since, again, mirroring is being deprecated.

    In addition to no PowerShell cmdlet support, there's other considerations about BAGs that I found. For example, I don't see how to use a listener(s), at least not effectively. Also, even with AGs, not all SharePoint databases can be in an AG. (As stated here: https://technet.microsoft.com/en-us/library/jj715261(v=office.16).aspx - also see https://technet.microsoft.com/en-us/library/jj841106(v=office.16).aspx) Which says to me that those databases need to be restored from backup anyway, ergo, even AGs are not the whole SharePoint databases resilience or DR story.

    I concluded that at this point because of BAG limitations, BAGs can't be used for database failover. It seems, with BAGs all SharePoint databases must be active on the same server. You can't have some databases active on one server and some others active on the other server at the same time. I do think that BAGs can be used instead of mirroring in a server failover scenario, and manual failover at that, not automatic. Would need to be managed entirely from SQL. Still thinking it through, but e.g. set up BAGs for the databases that can be in AGs, don't set up a listener, set up an SQL Alias on the SharePoint servers to point to the primary SQL server. In a database(s) fail, restore that database(s) from backup to the primary SQL. In a primary server fail, restore any non-BAG databases as required, from backup to the secondary, make the secondary SQL into primary (take whatever SQL steps needed, e.g. in SSMS...), point SQL Alias to secondary now primary, do whatever additional needed to make SharePoint happy (indexes, crawl, user profile sync, whatever...)

    Joan

     
    Thursday, July 6, 2017 4:10 PM
  • No, the reason you cannot use BAGs is due to a BAG only allowing a single database per BAG. But New-SPConfigurationDatabase as well as New-SPEnterpriseSearchServiceApplication have multiple databases that use the same connection string. These connection strings aren't adjustable, either. So given you have 2 or 3 databases using the same connection string with no opportunity to modify them, you can't use a connection string pointing to a BAG due to BAG's limit of 1 database per BAG.

    Trevor Seward

    Office Servers and Services MVP



    Author, Deploying SharePoint 2016

    This post is my own opinion and does not necessarily reflect the opinion or view of Microsoft, its employees, or other MVPs.

    Thursday, July 6, 2017 4:14 PM
  • Trevor, so why don't you just configure the farm first, then set up the BAGs? That's what I was planning to do anyway.

    Joan, thanks for your input and experience. Did you actually go to the step of configured BAGs and seeing what happened?


    Brandon Murray

    Thursday, July 6, 2017 6:04 PM
  • Brandon,

    No, we are waiting for the hardware to come in. To Trevor Howard's answer, I did primarily mean by "BAG limitations" the one database per BAG limit. The single, no-read, no-backup replica per database is another major limit. But I also was going to set up the farm first, and for the farm, service apps/web apps DatabaseServer parameter use an SQL Alias, then set up BAGs for the existing databases. So I don't understand why the New-... is a consideration.

    I don't see where in the connection string you are required to specify anything but DatabaseName and DatabaseServer. But seeing Trevor's answer, and me being a literal thinker - and this is a wild thought - after the BAG is created, does SharePoint then only see the BAG name and not the database within it? In other words, does SharePoint lose sight of / can no longer find the databases to connect to? Maybe that's the key point I'm missing, or there's another key point I'm missing.

    Again, this is for SQL server failover, I would not attempt a BAG failover. And I don't need all databases to be in BAGs. Even just the content databases would be great. To save time building/setting up a new server and restoring from backup.

    Joan

    Thursday, July 6, 2017 8:14 PM
  • One clarification - I'm talking only about SharePoint and BAG database failovers. BAG database failover in a situation where an application uses a single database is a different story.

    Joan

    Thursday, July 6, 2017 9:09 PM
  • I understand there would be 1 BAG for every Sharpoint Database.
    And a listener for each database. A mess to adminstrate.

    I am currently testing it for SharePoint with only one listener.

    After Failover for the BAG with the single listener all requests point towards the active Server.

    With manual scripted failover for updating there is a gap of a few seconds for the later databases.

    So i put the listener to the Content database and then search and then the other databases.

    Not shure if i better use databse mirroring instead.

    Tuesday, August 8, 2017 2:05 PM