Learning something completely new can pose as a challenge. This is something I have always felt when working with SQL Server. Till about 3-4 years back, I used to be so comfortable with TSQL that I was looking for things people were learning. That is the timeframe when I was introduced to the world of scripting with PowerShell. Lesser did I know that this is the future and how Microsoft has gone ahead and made life for people who shy away from the world of GUI so easy. PowerShell is no second class citizen when it comes to working with SQL Server too. In my blogs earlier, I had shown a number of ways to play with PowerShell scripts.
In this blog, I wanted to take a different spin to this because I personally thought to bring couple of important scripts that each one of us must know when working with SQL Server AlwaysOn. While setting up AlwaysOn, I have personally been using them from time to time and thought this is best suited if I share it to general public. I know these are not a big deal if you are a pro to PowerShell, but I think the learnings need to be shared.
The first script that I wanted to share is the step while working with SQL Server AlwaysOn, everyone needs to do this task. You will need to enable the Windows Features of Failover Clustering on each of the nodes that are part of AlwaysOn Availability group. Doing this using GUI by remoting into each of the servers can be a pain for sure.
This is exactly the place where automation using scripting can be quite helpful. Below script goes through 4 nodes (SQLNodes01 – SQLNodes04) and enables this Windows feature remotely via PowerShell.
$ServerGroup = new-pssession -computername SQLNodes01, SQLNodes02, SQLNodes03, SQLNodes04 -credential mycompany\sql
invoke-command -session $ServerGroup -scriptblock {Import-Module ServerManager}
invoke-command -session $ServerGroup -scriptblock {Add-WindowsFeature Failover-Clustering}
Don’t you think this is handy little capability that you want to have. I generally use this capability for building my consulting engagements of VMs to show SQL Server AlwaysOn.
The second script is an inspiration based on a recent task that I had to do in my consulting exercise. One of my client was having a 6 node AlwaysOn setup and was asking me which of the nodes are ready for doing a failover. I thought this was a simple yet powerful question. I had a PowerShell handy for this specific use case as shown below.
Get-ChildItem SQLSERVER:\Sql\BIGPINAL\DEFAULT\AvailabilityGroups | Test-SqlAvailabilityGroup | Where-Object { $_.HealthState -ne “Healthy” }
In this script, we will get all the nodes that are not healthy and we can easily take a call. But a simpler script is shown below for reference. Such simple questions can easily be answered using the DMVs available inside SQL Server.
— For Database States
SELECT replica_server_name ,database_name ,is_failover_ready FROM sys.dm_hadr_database_replica_cluster_states drcs INNER JOIN sys.availability_replicas ar ON drcs.replica_id = ar.replica_id ORDER BY 1,2
In the above script, the is_failover_ready column defines how the state is.
The list of scripts available in the case of SQL Server AlwaysOn is enormous. I am sure the internet is filled with many. In this blog, I wanted to bring some of the common scripts I have been using at length when working with AlwaysOn. I hope you will find these useful too as you start working with SQL Server AlwaysOn Availability Groups.
Leave a Reply