SQL Server 2012 AlwaysOn Availability Groups setup scripts - Part 1

Here are some scripts which can be used for setting up 2 nodes AlwaysOn Availability Groups, you can make your own automation script based on them.

Environment :

Name	IP Address	OS	Description
SQL2012-01	192.168.1.21	Win2012	Primary
SQL2012-02	192.168.1.31	Win2012	Secondary
SQL2012HA	192.168.1.41	N/A	AG Listener
SQLClUSTER	192.168.1.50	N/A	Cluster Name

1. Configure Powershell enviroment

    a) Configure execution policy on all servers

        set-executepolicy unrestricted

        based on your company security policy, you can use other parameter instead of "unrestricted". here is just a sample

     b)Configure Powershell remote command on all servers

       #winrmsetup.ps1

        Enable-PSRemoting -Force  

        Set-Item wsman:\localhost\client\auth\CredSSP -value true -force   

        Enable-WSManCredSSP -force -role server   

        set-item wsman:localhost\client\trustedhosts -value *    -force 

        set-item wsman:\localhost\listener\listener*\port -value 5985    -force 

        restart-Service winrm   

        winrm get winrm/config   

        winrm enumerate winrm/config/listener   

        #winrmsetup.ps1

         If you want to centralize the installation process, and want to run the code from remote server, ps remote call and winrm need to be enabled and configured correctly. winrm is for powershell remote command call, for instance "invoke-command -computer".

2. Setup Cluster

Before sql server installation, we create a new cluster with 2 nodes(SQL2012-01, SQL2012-02)

#Create-WinCluster.ps1

Import-Module FailoverClusters

$ClusterNode1=Read-Host "Enter Cluster Node 1 Name"

$ClusterNode2=Read-Host "Enter Cluster Node 2 Name"

$CMD={Set-Service -Name ClusSvc -StartupType Automatic}

$useraccount=$env:USERDOMAIN+"\" + $env:USERNAME 

$cred= Get-Credential -UserName $useraccount -Message "Please enter the user name"

Invoke-Command -ComputerName $ClusterNode1 -ScriptBlock $CMD -Credential $cred

Invoke-Command -ComputerName $ClusterNode2 -ScriptBlock $CMD -Credential $cred

Write-Host "Testing Node $ClusterNode1"

Test-Cluster -Node @($ClusterNode1) -Ignore network,inventory,storage

Write-Host "Testing Node $ClusterNode2"

Test-Cluster -Node @($ClusterNode2) -Ignore network,inventory,storage

Write-Host "Clear old info on Node $ClusterNode1"

Clear-ClusterNode $ClusterNode1 -Force

Write-Host "Clear old info on Node $ClusterNode2"

Clear-ClusterNode $ClusterNode2 -Force

$ClusterFqdn=Read-Host "Enter New Cluster Name(Please specify the name without domain information)"

$ClusterIP=Read-Host "Enter New Cluster IP"

Write-Host "Creating Cluster $ClusterFqdn...."

New-Cluster -Name $ClusterFqdn -Node @($ClusterNode1,$ClusterNode2) -NoStorage -StaticAddress @($ClusterIP)

#Create-WinCluster.ps1

Here cluster node 1 is "SQL2012-01", cluster node 2 is "SQL2012-02", cluster name is "SQLCluster", cluster ip is "192.168.1.50"

if cluster service hasn't been installed on all sql nodes, you can run the command below to install the service first.

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering