SQL Server AlwaysOn is a utterly cool technology, but it is not a technology itself which solves all the problems you may have.
In particular, it is not just about tweaking a couple of settings and adding servers as needed. A careful planning and thorough tests are mandatory in order to succeed without affecting the existing service or going overtime. So I wanted to sum up some tips I found during a project I am following with a huge deployment of Team Foundation Server 2013 which uses SQL Server AlwaysOn as an underlying High Availability solution.
- Size your storage
I am dealing with TBs of data, so I am experiencing that TBs of storage flies away in a snap. Do not underestimate your storage size and do not rely just on SANs. Importing your data takes time and space, especially if you restore your databases to the secondary replica using the Full mechanism of backup-and-restore provided by SQL Server AlwaysOn.
- You can’t mix and match all the HA technologies
Not all the SQL HA technologies are supported by TFS in the first place. MSDN is pretty clear on this, no replication and no log shipping. Log shipping could technically work, but you won’t be supported by the Microsoft CSS, so it is a no no. You can mix a clustered SQL Server with another one in another location within the same AlwaysOn Availability Group though, and it is pretty robust. Keep in mind anyway that TFS is pretty agnostic about the underlying SQL deployment though, in fact the only feature which requires a specific configuration is AlwaysOn Availability Groups. - The MultiSubnetFailover setting
When configuring Team Foundation Server for using AlwaysOn Availability Groups, remember to tick the specific checkbox about this setting. Beware of the setting: if you are not directly using an AG but you select it at TFS configuration time, you are going to be in trouble. If you are restoring it to a new server, launch TFSConfig RegisterDB to enable it:
TFSConfig RegisterDB /SQLInstance:MyDB\namedinstanceifneeded /databaseName:Tfs_Configuration /usesqlalwayson
- All the Team Foundation Server databases must be in the AlwaysOn Availability Group
No exceptions to this – if you use it, all the DBs must be contained in the AG.
