I had a story at my company where I had to migrate the SharePoint databases to a fresh AlwaysOn Availability Group (AOAG) SQL Instance. This move wasn’t an issue until we found out that our backup script (which is performing a “Backup-SPFarm” cmdlet) failed to re-provision the User Profile Synchronization (UPS) Service.
The issue all came down from the fact that the UPS database was now configured with the AlwaysOn feature, therefore, any operations to the DB couldn’t occur anymore.
The error message in the ULS was :
SqlError: ‘The operation cannot be performed on database “UserProfile-Sync-DB” because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.’ Source: ‘.Net SqlClient Data Provider’ Number: 1468 State: 1 Class: 16 Procedure: ” LineNumber: 5 Server: ‘AG Listener Name’
When the move to the new AlwaysOn SQL server happened, the operation went pretty smoothly since we are using SQL alias. We only updated our current alias name to redirect to the new SQL instance.
After some research, we found out that this is a “internet” known issue and can also occur on the Usage and Health database when performing patching of SharePoint farms.
Hence, we decided to modify the configuration and implement a better integration between SharePoint and AlwaysOn by using the SharePoint Database Availability Group cmdets. It will give us the flexibility to manage the databases in the AOAG directly from SharePoint.
In this initial setup, SharePoint isn’t aware of the AlwaysOn service running beneath it. It only sees the SQL Alias and has no way to know which SQL server is running behind the SQL Alias ; it is a regular connection string.
By explicitly declaring to SharePoint that the databases are hosted by a HADR system, SharePoint Admins keep some visibility and control over the AOAG.
Availability Group Listener (AGL) and port attribution
When your AGL is configured to use the default port (1433) and you don’t use SQL alias, you will surely have no issue when configuring your environment.
The troubles arises when you use SQL alias or have a custom port defined for the AGL, let’s say 25066 for the example.
- When using a SQL alias (redirecting to the AGL), SharePoint will fail retrieving the “Availability Group listeners”.
- When using a custom port for the AGL, SharePoint will check the database server by using a trimmed version of the data source.
You can use SQL Alias with Availability Group Listener as long as the SQL alias uses the same DNS entry as the AGL.
Availability Group Custom Port
You must use a SQL Alias to bypass the SharePoint port validation.
Solution (for both scenarios)
Some in-depth details :
I was able to pinpoint this issue by decompiling the SharePoint assembly and verify the logic behind the powershell cmdlets. I found the root issue in the “UpdateDataSource” method of the Database object.
When trimming the datasource from its port, it then sends the port-less server string to the method “ChangeDatabaseInstance” which itself calls “ValidateDatabaseServer”.
At this point, it won’t be able to validate the connection to the SQL server because of the modified server string.
PS: I wasn’t able to find anything on the web regarding this particular issue except 1 slide that confirmed my troubleshooting.
Ref: http://blogs.technet.com/b/fromthefield/archive/2015/04/23/sharepoint-2013-amp-sql-alwayson-sharepoint-evolution-conference-2015.aspx – Slide 30