Tuesday, June 25, 2013

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 Win2012 Primary
SQL2012-02 Win2012 Secondary
SQL2012HA N/A AG Listener
SQLClUSTER 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
        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   

         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)

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)
Here cluster node 1 is "SQL2012-01", cluster node 2 is "SQL2012-02", cluster name is "SQLCluster", cluster ip is ""

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

1 comment: