Moving SharePoint 2016 databases into SQL AlwaysOn group

A year or so  ago I deployed an internal portal of our company based on SharePoint 2016. We were starting to use AlwaysOn on our SQL servers, but I was not sure if this would be applicable for the SharePoint 2016 and decided to use one of the SQL servers of this AlwaysOn group to store SharePoint databases. It was running fine but the need to move the content databases as well as service applications databases to the AlwaysOn arised a couple of weeks ago. That meant that these databases would stay on this server and their copies will appear on the second SQL server. AlwaysOn group listener appears as a separate SQL server, so this database move will look like a move to another database server.

First of all to get the list of all databases we will run a PowerShell cmdlet:

Get-SPDatabase | ft Name,DatabaseConnectionString

You can see all the databases that reside in this SharePoint 2016 deployment. Some of these databases are Content databases, while the others are service application databases. Also, you can see that all of them are using SQL01 SQL server. The SQL server name is shown in the beginning of the connection string.

There are multiple articles on docs.microsoft.com related to SharePoint database relocation to a new SQL server. The most appropriate article is Move all databases in SharePoint Server which describes the method of moving all SharePoint databases at one time. The procedure described in the article is as follows:

1. Prepare the new database server.
2. Close all open SharePoint Management Shell windows.
3. Stop all services that are related to SharePoint Server and Internet Information Services (IIS).
4. Detach the databases from the current SQL Server instance.
5. Copy or move all files that are associated with the databases (.mdf, .ndf, and .ldf), to the new destination server that runs SQL Server.
6. Make sure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases are configured correctly on the new destination database server.
7. Attach the databases to the new destination server that runs SQL Server.
8. Use SQL Server connection aliases to point to the new database server and update all web servers.
9. Restart all services that you stopped in step 3.

This procedure describes the situation when we are moving the databases off the SQL server, but in my case there was no need to do that because this SQL server is a part of SQL AlwaysOn Availability Group. So, I had no need to perform steps 4 and 5. Instead, I had to add all the required databases into the AlwaysOn group.

To start the procedure I had to stop the services on my SharePoint server, so it won't try to contact the databases on my SQL Server. You need to stop the following services according to the article:

• SharePoint Administration
• SharePoint Timer
• SharePoint Tracing
• SharePoint User Code Host
• SharePoint VSS Writer
• World Wide Web Publishing Service
• SharePoint Server Search 16

To be able to add the SQL databases on another SQL server, you need to have all the required logins, roles and permissions on all the SQL servers that appear as a part of AlwaysOn. In my case there were some domain accounts which I used during the deployment of the SharePoint.

I have created these account on the second SQL server and was ready to add SharePoint databases to AlwaysOn Availability Group. But before that I had to check if all the paths of SharePoint databases existed on the second SQL server. This is required to be able to run the wizard or you'll get an error, stating that there is no path on the second server. You need to check that on all the SQL servers that reside in you AlwaysOn Availability Group. In my case, SharePoint databases are located in the root data directory of SQL server and both the servers are configured in the same way. With that in the mind, I was ready to start the wizard and to check if I would be able to add all the required databases.

Some SharePoint databases met prerequisites but some of them did not. Luckily, I just had to change the recovery mode from Simple to Full for each database. This is one of the prerequisites to add any database to AlwaysOn Availability Group.

Before adding the database to the AlwaysOn Group you'll need to create a full backup of all the databases that ask to do so. You need to perform this task using SQL Management Studio and after that you should re-run the wizard.

Finally, you need to reconfigure your SharePoint databases to point to a new DNS name of your AlwaysOn Availability group. I did it by simply copying and pasting GUIDs from the output of PowerShell cmdlet:

Get-SPDatabase | ft Name,DatabaseConnectionString
$db = Get-SPDatabase -Identity <GUID>
$db.ChangeDatabaseInstance("<AlwaysOn Availability Group FQDN>")

You need to do that for each content and service application database listed in the output of Get-SPDatabase cmdlet. Or you can run the following piece of PowerShell code:

$databases = Get-SPDatabase
foreach ($db in $databases) { $db.ChangeDatabaseInstance("<AlwaysOn Availability Group FQDN>") }

When you are done with setting a new name of SQL server for all the databases of your SharePoint environment, you can enable all the services and start IIS on your SharePoint server.