SharePoint Database Availability Group Cmdlets

The SharePoint 2013 April 2014 Cumulative Update includes three new SQL Availability Group cmdlets:

Get-AvailabilityGroupStatus

Add-DatabaseToAvailabilityGroup

Remove-DatabaseFromAvailabilityGroup

These cmdlets allow you to manage the SQL Availability Group. First, a few notes about the Availability Group.

Note, the below information about the AG needing to reside on the SQL instance with the Configuration database has changed in later releases. You may use other Availability Groups that do not contain the SQL instance with the Configuration database. This is only true when not using the -ProcessAllDatabases switch, of course.

The AG must be the connection in use by the Configuration database as checks for the Database Availability group will be executed against this connection. If the Database Availability group happens to be on another set of SQL Servers that is not the Configuration database SQL server connection, those checks will fail. The Availability Group must have a Database Availability Group Listener. Again, without it, the checks will fail.

To confirm that the check will succeed, modify the AGName parameter in this T-SQL query and run it against the SQL instance used by the Configuration database:

Let’s start with a brand new farm. Two SQL Servers, SQLAO1 (Primary), and SQLAO2 (Secondary) in a SQL Availability Group (Synchronous mode) named SPAG (DNS name is SPAG.nauplius.local). A dummy test database has been created in order to set up the AG, but may be deleted. The SharePoint server is SharePoint 2013 SP1 with April 2014 CU. No SQL Alias will be used for this set up since we have database mobility between SQL Servers due to AlwaysOn. Starting with an elevated SharePoint Management Shell…

Because SQLAO1 is the Primary Replica, the Administration and Configuration databases are present on it, but not SQLAO2. The next step is where the AlwaysOn fun begins. Validate a good backup location is available for each SQL Server (note that your SQL Server service account(s) will need access to this location). Run the following command from the SharePoint Management Shell:

And that is it! Your Configuration and Administration databases are now part of the SQL Availability Group (in a real sense), there was no having to flip-flop SQL Aliases around, the databases are automatically backed up and synchronized, as are the SQL Logins! This also eliminates the need for a Database Administrator to assist with getting SharePoint databases into an Availability Group, because it can now be done by the SharePoint Administrator!

You can also now see the status of the Availability Group:

If you run Add-DatabaseToAvailabilityGroup, but the cmdlet errors out, an Availability Group object may be created within SharePoint regardless. To fix this, simply run:

For databases that are part of the SharePoint SPAvailabilityGroup (rather than simply being in an AG as done from SQL Management Studio) will also be aware of that through their object.

On the SharePoint SPAvailabilityGroup object itself, we can also force a failover to one of other nodes in the Availability Group:

Yep, as a SharePoint Administrator, I do not have to touch SSMS or SQL PowerShell to execute an AG failover anymore!

If, down the road, you create a new database (Content or Service Application) and need to individually add it to a new or existing Availability Group, again use the Add-DatabaseToAvailabilityGroup cmdlet. Pay attention to what the Primary replica is, as the last backup path will be used if the FileShare switch is not specified. So for my above example where my FileShare path is on SQLAO1, but I’ve failed over to SQLAO2, if I attempt to use the Add cmdlet, the backup will fail with Access Denied. Instead, I created a new share on SQLAO2 and ran:

Of course, you could use a common CIFS location that the SQL Server service accounts had NTFS Modify access to, as well.

The last cmdlet, Remove-DatabaseFromAvailabilityGroup, will remove databases from the SharePoint SPAvailabilityGroup object. If you attempt to run the cmdlet against a database that currently exists within SharePoint (for example, is still attached to a Web Application), the cmdlet will fail with an error similar to:

This is a little bit misleading (and there is a slightly better error in the ULS logs). If you use the -Force parameter, what it does is it removes it from the Availability Group, deleting the database from the Secondary nodes of the SQL Availability Group (by default), but the database will still be attached to SharePoint. If you need to remove a database from an Availability Group, but wish to keep copies of the database on the Secondary nodes, use the -KeepSecondaryData parameter. The database, on the Secondary nodes, will enter a Not Synchronizing state, while the database with the active connection will no longer display a synchronizing state as it is no longer part of the Availability Group.

One potential bug to note is that it appears the Secondary replicas do not have the Max Degree of Parallelism (MAXDOP) set to 1 at any point by SharePoint. Make sure to set it manually prior to deploying SharePoint databases as this can cause certain operations to fail, such as creating new Content Databases.

The December 2014 CU has added this note when adding the Configuration database to the Availability Group via the Add-DatabaseToAvailabilityGroup cmdlet:

16 Comments

  1. Trevor, what does the -ProcessAllDatabases switch do on the Add-DatabaseToAvailabilityGroup cmdlet? I’m guessing maybe its something to do with forcing the database to meet the AG prerequisite requirements, i.e. backed up and recover model “Full”?

    • ProcessAllDatabases processes each SPDatabase object attached to the farm, adding them to the Availability Group. The alternative is to specify the -DatabaseName parameter, which processes the single database specified.

  2. Thank you! This is timely new functionality.

  3. Hi Trevor, one more question! The -AGName parameter for the Add-DatabaseToAvailabilityGroup cmdlet. Is this the Availability Group Name (as parameter name would suggest) or the Listener Name (or SQL client alias on SP server if using such an alias). In your examples the group name and listener name are the same so it’s not immediately clear.

  4. Trevor, we have the AG listener set as the SQL server in the configuration of SharePoint, however, none of the above commands work. They all bring back nothing. We are on the December 2014 CU. Does it have to point to the AG or the AG listener (obviously this one isn’t working).

    We’re trying to make the life of our Dba better.

    Thanks for your help,

    John

  5. Are these commands specific to SharePoint? If so, why do they not include the usual “SP” prefix on the noun component of the name?

  6. one more question: regarding “The AG must be the connection in use by the Configuration database as checks for the Database Availability group will be executed against this connection”. Larger farms often have certain service application DB’s and content DB’s running on different SQL servers than config DB. Will these commands not work for those databases? Thanks!

  7. Pingback: SharePoint 2013, AlwaysOn, Availability group and SQL alias | Wes'Point

  8. Is there any further documentation available on these cmdlets? I haven’t found much.

    In our testing; the -AllDatabases will always attempt all databases (it does not have any logic to ignore databases that have only been replicated over; or for example only new databases).

    We also had issues with it bombing out part-way through with an error message that a database had been modified by a SharePoint process… it wasn’t very verbose. It said to check the trace logs; but the SharePoint admins weren’t sure where that was.

    However later going back and adding databases in one by one works.

  9. Great article!

    It seems that Add-DatabaseToAvailabilityGroup does not support named instances. It connects successfully to the named instance for the configuration database (and service application) availability group “SvcAG.company.local\TEST” and runs some checks to see that the configuration is not already done.
    But when it should connect to the master database of the content database availability group “ContAG.company.local\TEST”) it defaults to 1433 instead of the named instance port.
    Seems it does not use the server property of the SPDatabase object in this case. And neither is there a -DatabaseServer parameter to the cmdlet.

  10. Hi Trevor,

    While trying to add my SharePoint database to always on high availability using the Add-DatabaseToAvailabilityGroup -databasename “SP_Content_Portal “-AGName “SPSQLAG” -FileShare “\\\dalspsql02\backups”

    I am getting the below error can you help me out in resolving the issue please?
    Add-databasetoavailabilitygroup : the backup set holds a backup of a databse other than the existing SP_Content_Portal’ database.
    RESTORE DATABASE is terminating abnormally.
    At line:1 char:1
    +Add-DatabaseToAvailabilityGroup -databasename “SP_Content_Portal “-AGName…..
    +——————————————————————————-
    +categoryInfo :InvalidData: (Microsoft.Share…ailabilityGroup:SPCmdletAddDatabaseToAvailabilityGroup)
    {Add-DatabaseToAvailabilityGroup], sqlexception
    +FullyQualifiedErrorID: Microsoft.SharePoint.Powershell.SPCmdletAddDatabaseToAvailabilityGroup

Leave a Reply