How to create site collection on availability groups enabled SharePoint 2013?

If you are utilize SQL Server 2012 AlwaysOn Availability Group for a SharePoint 2013 farm, you will be challenged to create the site collection into right SQL instance and add the content DB dynamically into the Availability Group. As Microsoft recommended, SharePoint 2013 will utilize at least two AlwaysOn Availability Groups. One is for configuration and service DBs we named as AG1, and the other one is for the content DBs we named as AG2. If you create the site collection from central admin, the site might not be created inside the right Availability Group that should be AG2. This blog will provide the details procedure how you create the site into AlwaysOn Availability Group.

First you need to set up the AlwaysOn Availability Group alias on SharePoint server using SQL Server Client Network Utility.

1. On the target server, on the Start screen, click Computer.  
2. Browse to C:\Windows\System32.
3. In the SQL Server Client Network Utility window, click the Alias tab. 
4. On the Alias tab, click Add. 
5. In the Server alias box, type the name for your alias, for example AG1. 
6. Under Network libraries, click TCP/IP. 
7. In the Server name box, type the name of your SQL Server computer, and then click OK. 
8. Repeat to add all other alias 
9. Close the SQL Server Client Network Utility. 
10. In Computer, browse to C:\Windows\SysWOW64. 
11. Repeat steps 4 to 10. 
12. Repeat these steps on all your SharePoint 2013 web servers and application servers.

Second you need to create the database from the central admin or powershell. You could refer Microsoft site for details. Please note you need to enter the right Availability Group alias like AG2 in both central admin or powershell.The powershell command will like the one below.

New-SPContentDatabase WSS_Content_MyDatabase -DatabaseServer AG2 -WebApplication http://sitename

Third you could use powershell to create the site collection while passing the right site template and Availability Group alias like AG2.

 

$template = Get-SPWebTemplate "EDISC#0"   # This is eDiscovery Center template

New-SPSite -Url “https://sharepoint.mycompany.com/sites/eDiscovery” -OwnerAlias “na\spinstaller” -Template $template –ContentDatabase WSS_Content_MyDatabase

Last you should add the DB into the availability group other database instances. The powershell you can use is listed below with comments.

############################ Update with correct values ################################
$DatabaseName = "WSS_Content_MyDatabase"    # This is the database created
$BackupShare = "\\SPSQLInstance1\Backup"         # This is the shared location on SQL instance 1
$PrimaryInstance = 'SPSQLInstance1\AG2'            #  We need to be able to look up this value#
$SecondaryInstance = 'SPSQLInstance1\AG2'         #  We need to be able to look up this value#
$AGGroup = "AG2"
#######################################################################################
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$DateTime = Get-Date -Format MMddyyyy_HHmmss
$DatabaseBackupFile = "$BackupShare\$DatabaseName$DateTime.bak"
$LogBackupFile = "$BackupShare\$DatabaseName$DateTime.trn"

$MyAgPrimaryPath = "SQLSERVER:\SQL\$PrimaryInstance\AvailabilityGroups\$AGGroup"
$MyAgSecondaryPath = "SQLSERVER:\SQL\$SecondaryInstance\AvailabilityGroups\$AGGroup"

### Change to Full Recovery Model - this is required
$Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $PrimaryInstance
$Database = $Server.Databases[$DatabaseName]
$Database.DatabaseOptions.RecoveryModel = [Microsoft.SqlServer.Management.Smo.RecoveryModel]::Full
$Database.Alter()

### Backup Database ###
Backup-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance $PrimaryInstance

### Restore Database ###
Restore-SqlDatabase -Database $DatabaseName -BackupFile $DatabaseBackupFile -ServerInstance $SecondaryInstance -NoRecovery

### Backup Transaction Log ###
Backup-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance $PrimaryInstance -BackupAction 'Log'

### Restore Transaction Log ###
Restore-SqlDatabase -Database $DatabaseName -BackupFile $LogBackupFile -ServerInstance $SecondaryInstance -RestoreAction 'Log' -NoRecovery

### Add Database to AG ###
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database $DatabaseName
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database $DatabaseName

Now, the site collection is created in the right AlwaysOn Availability Group AG2 and all SQL instances are synchronized the content.