Always On Availability groups is clearly a very flexible and easy High Availability (HA) and Disaster Recovery(DR) solution compared to the failover clustering technology. I love it. Really.
- It is a HA solution in sync commit mode because you can failover at any time to another database replica and for instance patch a node.
- it is also an great DR solution if you want to be able to switch from a farm in one data center to another one in another datacenter. Having 2 farms instead of one stretched farm is the Microsoft recommended architecture. In this case you will choose the async commit mode to make sure the database in both farms are synchronized.
Yesterday I tried to apply the SharePoint 2013 SP1 to my farm : all SharePoint databases were on the same availability group (don’t do that, more details in a few seconds). yes, all databases ! including the usage database database (you won’t see in the following picture, because I’ve removed, and will tell you why in a few seconds; but anyway, it is supported but not recommended by MS)
The upgrade miserably failed when I ran the Configuration Wizard-> so I checked the upgrade_xxx__.log file and I found this :
“… Timer job is exiting due to exception: System.Data.SqlClient.SqlException (0x80131904): The operation cannot be performed on database "WSS_Logging" 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. ALTER DATABASE statement failed. at ….”
If a database is involved in an availability group, you can’t alter it (for instance, I’ve added a new table in the Northwind db and it the new table has been synchronized in the second replica).
==>Remove the Logging(Usage) db from the replica, patch your farm and add it afterward.
The following pictures are what Microsoft supports today :
So yes: Usage database is supported in Sync mode, but not recommended. if you really want to use it, remove it from the availability group before applying a CU or a SP and add it when the upgraded is completed.
Also I suspect that adding the usage db in an availability group will stress your network. So again, don’t do that.
Here is is what Microsoft did announce at the SPC14 (Neil Hodgkinson).
Also don’t forget to define a database alias when you install SharePoint; because later on this alias can point to the Availability group listener if you decide to use Always On Availability groups.
If you don’t, you will suffer because you will have to reconnect all your service DBs (and config DB) to the Availability group listener. This is obvious. But don’t forget it. I did : –) (on a quick & dirty test farm). And if you use AutoSPInstaller to install SharePoint you have the option to specify an alias. No excuse !
You can also watch Neil Hodgkinson session.