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