Prepare Windows Cluster SharePoint

This part demonstrate how to configure windows cluster for two server, to be used as SQL Cluster.

Before you start

· You need to have two network adapters on each node, one Public and one Private(for heartbeat communication).

· Shared storage (like SAN storage) should be present and connected to both cluster nodes  with at least:

  • Quorum Disk (5GB)
  • DTC Disk (1GB)
  • SQL data files and log file disk(s)

· domain user account (SPSadmin): add SPSadmin user as administrator on both servers

· Prepare a preserved static IP and Cluster Name to be used.

· Prepare a preserved static IP and DTC Name to be used.

Windows Cluster Configuration

1. Install latest windows updates on all server nodes.

2. Install Application role and IIS role on both SQL DB server nodes

3. Install Fail over clustering feature on both SQL DB server nodes.

4. Provide a Cluster Name and Cluster IP for the database nodes:

Note: make sure that the public network is used here not the private (heartbeat)

5. Below are the servers info

6. Cluster Disk files are configured as the following:

7. Configure DTC as clustered service , this is a pre requisite for SQL Cluster installation

8. DTC cluster configuration

9. Assign the DTC a cluster disk

10. Create SQL Group which is a logical group to include all SQL resources in :


Configuring SQL Server 2014 AlwaysOn Availability Group using PowerShell


Earlier on I was trying to search for some scenarios to implement AlwaysOn Availability Groups using PowerShell, although I was able to find some interesting post however non of them represented a complete scenario starting from a fresh windows server installation (using PowerShell), so I decided to write this blog as quick walkthrough thePowerShell scripts needed.

Sample Scenario

In this scenario I will be using a 2 Nodes setup with the following software already installed:

  • Windows Server 2012 R2
  • Both Servers are joined to the domain

Configuration Steps:

To make the scenario simple I be using a Domain Account that has a Local Administrator Permission on both nodes. In addition, all scripts below will be running using PowerShell (Run as Admin) so Lets get started:

1- Install SQL Server

We need to install a standalone setup on each node. I will do that using the below sample unattended SQL Setup Script:


2- Add Windows Failover Cluster

We need to install it on each node. I will do that using the below script:

Import-Module ServerManager

Add-WindowsFeature Failover-Clustering –IncludeAllSubFeature

Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools

3- Configure Windows Failover Cluster

Run the below script on the 1st Node and make sure to update the configuration parameters with your values:


Configuration Parameters

$Server1 = “Server1”
$Server2 = “Server2”
$IPAddress = “X.X.X.X”
$AlwaysOnClusterName = “ClusetrName”
$QuorumFileSharePath = “\FileSharePath”


Create Cluster Service

Create a new cluster ‘AlwaysOnCluster’ with nodes.

Import-Module FailoverClusters
New-Cluster –Name $AlwaysOnClusterName –Node $Server1,$Server2 -StaticAddress $IPAddress -NoStorage

Add Quorum

Set-ClusterQuorum -NodeAndFileShareMajority $QuorumFileSharePath

4- Configure AlwaysOn Failover Cluster

Now for the fun part, I have configured the whole AlwaysOn Configuration in one script that needed to be ran on the 1st Node. In addition, I have added the proper comments so take your time to review it  and make sure to update the configuration parameters with your values:

#Set execution policy
Set-ExecutionPolicy unrestricted

Import SQLPS Module

Import-Module “sqlps” -DisableNameChecking


Configuration Parameters

#Server Names
$Server1 = “Server1”
$Server2 = “Server2”
$ServerInstance1 = ‘$Server1Instance’
$ServerInstance2 = ‘$Server2Instance’

#AlwaysOn TempDB Names
$tmpDB1 = “AG1-TempDB”

#Availability Group Names

#Availability Group Listener
$AGListner1Name = “Listener1”
$Listner1IP_Mask = “X.X.X.X/X.X.X.X”
$ListnerPort= “1433”
$FileSharePath = “\FileSharePath”

#Service Accounts
$SQLSVCAccount = “DomainSVCAccount”
$AGDomain = “” #Keep the ‘.’ before domain name

#AlwaysOn EndPoints
$AOEndpointName = “AlwaysOnEndpoint”
$AOPort = “5022”
$AOEncryptionAlgorithm = “AES”


Enable AlwaysOn on Servers

Enable-SqlAlwaysOn –ServerInstance $Server1
Enable-SqlAlwaysOn –ServerInstance $Server2


Create Endpoints

#####1st Server
$AOtmpPath = “SQLSERVER:SQL$Server1default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

start the endpoint

$AOtmpPath = “SQLSERVER:SQL$Server1defaultendpointsAlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;

####2nd Server
$AOtmpPath = “SQLSERVER:SQL$Server2default”
New-SqlHadrEndpoint -Path $AOtmpPath -Name $AOEndpointName -Port $AOPort -EncryptionAlgorithm $AOEncryptionAlgorithm

start the endpoint

$AOtmpPath = “SQLSERVER:SQL$Server2defaultendpointsAlwaysOnEndpoint”
Set-SqlHadrEndpoint –Path $AOtmpPath –State “Started”;


Grant Permissions for EndPoints

$SQLPermissionQry = ”
USE [master]

Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance1
Invoke-Sqlcmd -Query $SQLPermissionQry -ServerInstance $ServerInstance2

#Create Temp DB for AG
$AOtmpPath = “SQLSERVER:SQL$Server1default”
$svr = Get-Item $AOtmpPath
$db1 = New-Object Microsoft.SqlServer.Management.Smo.Database($svr, $tmpDB1);

#Initial Backup for the DB
cd “SQLSERVER:SQL$Server1defaultdatabases”
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1;


Backup & Restore TempDBs to prepare for AlwaysOn

Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak”;
Backup-SqlDatabase –ServerInstance $Server1 –Database $tmpDB1 –BackupAction Log –BackupFile “$FileSharePath$tmpDB1.trn”;


cd “SQLSERVER:SQL$Server1default”
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –BackupFile “$FileSharePath$tmpDB1.bak” –NoRecovery;
Restore-SqlDatabase –ServerInstance $Server2 –Database $tmpDB1 –RestoreAction Log –BackupFile “$FileSharePath$tmpDB1.trn” –NoRecovery;

#Create AG Replica
#It assumes SynchronousCommit + Automatic Failover
$PrimaryRepTCP = “TCP://$Server1$AGDomain” + ‘:’ + “$AOPort”
$SecondaryRepTCP = “TCP://$Server2$AGDomain” + ‘:’ + “$AOPort”

$Primary = new-sqlavailabilityreplica -Name $Server1 -EndpointUrl $PrimaryRepTCP -ConnectionModeInPrimaryRole “AllowAllConnections” -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;
$Secondary = new-sqlavailabilityreplica -Name $Server2 -EndpointUrl $SecondaryRepTCP -ConnectionModeInSecondaryRole “AllowAllConnections” –AvailabilityMode “SynchronousCommit” –FailoverMode “Automatic” -AsTemplate -Version 11;

#Create a new AG
$ag = New-SqlAvailabilityGroup -Name $AG1Name -AvailabilityReplica ($Primary, $Secondary) -Database $tmpDB1
#Join Availability Replica

$AOtmpPath = “SQLSERVER:SQL$Server2default”
Join-SqlAvailabilityGroup –Path $AOtmpPath –Name $AG1Name;

#Join Replica Database on a Secondary replica
$agpath1 = “SQLSERVER:SQL$Server2defaultAvailabilityGroups$AG1Name”
Add-SqlAvailabilityDatabase –Path $agpath1 –Database $tmpDB1

#Create a Listener using Static IPs
$agpath1 = “SQLSERVER:SQL$Server1defaultAvailabilityGroups$AG1Name”
$ag = Get-Item $agpath1; #Validate AG Path
New-SqlAvailabilityGroupListener -Name $AGListner1Name –Path $agpath1 –StaticIp $Listner1IP_Mask –Port $ListnerPort;