SQL Server 2012 AlwaysOn Availability Groups setup scripts - Part 3

4. Setup 2 nodes alwayson availability group 
    a) Enable alwayson on both 2 nodes
enable-sqlalwayson -Path "SQLSERVER:\SQL\SQL2012-01\DEFAULT"
enable-sqlalwayson -Path "SQLSERVER:\SQL\SQL2012-02\DEFAULT"

    b) Restore database "MYHA" to secondary server "SQL2012-02"
Backup-SqlDatabase -Database "MYHA" -ServerInstance "SQL2012-01" -BackupAction Database -CompressionOption On -backupfile "\\WIN-JIASUMAU0DF\Backup\MYHA_FULL.bak"

Restore-SqlDatabase -Database MYHA -ServerInstance sql2012-02 -BackupFile "\\WIN-JIASUMAU0DF\Backup\MYHA_FULL.bak" -NoRecovery

Backup-SqlDatabase -Database "MYHA" -ServerInstance "SQL2012-01" -BackupAction log -CompressionOption On -backupfile "\\WIN-JIASUMAU0DF\Backup\MYHA_log.trn"

Restore-SqlDatabase -RestoreAction Log -Database MYHA -ServerInstance sql2012-02 -backupfile "\\WIN-JIASUMAU0DF\Backup\MYHA_log.trn" -NoRecovery


    c)Create endpoint for alwayson availability group on both 2 nodes
$endpoint=New-SqlHADREndpoint -Port 5022 -Owner sa -Encryption Supported -EncryptionAlgorithm Aes -Name AlwaysonEndpoint -Path "SQLSERVER:\SQL\SQL2012-01\DEFAULT"
Set-SqlHADREndpoint -InputObject $endpoint -State Started

$endpoint=New-SqlHADREndpoint -Port 5022 -Owner sa -Encryption Supported -EncryptionAlgorithm Aes -Name AlwaysonEndpoint -Path "SQLSERVER:\SQL\SQL2012-02\DEFAULT"
Set-SqlHADREndpoint -InputObject $endpoint -State Started

     d) Create login for sql server service account and grant connection permission on alwayson endpoint
CREATE LOGIN [MSFT\sqlsvc] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[AlwaysonEndpoint] TO [MSFT\sqlsvc]

GO

      e)  Create alwayson availability group "MYAG"
$primaryServer = Get-Item "SQLSERVER:\SQL\SQL2012-01"
$secondaryServer = Get-Item "SQLSERVER:\SQL\SQL2012-02"

$primaryReplica = New-SqlAvailabilityReplica `
-Name "SQL2012-01" `
-EndpointUrl "TCP://SQL2012-01:5022" `
-FailoverMode "Manual" `
-AvailabilityMode "SynchronousCommit" `
-AsTemplate `
-Version 11

# Create the initial secondary replica
$secondaryReplica = New-SqlAvailabilityReplica `
-Name "SQL2012-02" `
-EndpointUrl "TCP://SQL2012-02:5022" `
-FailoverMode "Manual" `
-AvailabilityMode "SynchronousCommit" `
-AsTemplate `
-Version 11

New-SqlAvailabilityGroup -Name MyAG -AvailabilityReplica ($primaryReplica, $secondaryReplica) -Database "MYHA" -Path "SQLSERVER:\SQL\SQL2012-01\DEFAULT"

Join-SqlAvailabilityGroup -Name MyAG -Path "SQLSERVER:\SQL\SQL2012-02\DEFAULT\AvailabilityGroups"

Add-SqlAvailabilityDatabase -Database MYHA -Path "SQLSERVER:\SQL\SQL2012-02\DEFAULT\AvailabilityGroups\MyAG"


New-SqlAvailabilityGroupListener -Name SQL2012HA -StaticIp '192.168.1.41/255.255.255.0' -Path "SQLSERVER:\SQL\SQL2012-01\DEFAULT\AvailabilityGroups\MyAG"


    f) Config cluster quorum
Import-Module FailoverClusters
Set-ClusterQuorum -Cluster SQLCluster -NodeAndFileShareMajority \\WIN-JIASUMAU0DF\fsw