Tuesday, 21 March 2017

Enable SQL Server 2016 AlwaysOn Availability Groups Using Windows PowerShell

Always On Clusters are perhaps one of the best things in SQL Server but they take time to Setup unless you have it scripted out.
Here is one of the problems going to each node and enabling always on, now for default named instance this can be easy using the SQLPS and Enable-SqlwaysOn cmdlets.

Using the node name and cycling throw each node of the cluster we can enable always on without entering the names of the nodes one at a time.

foreach ($node in Get-ClusterNode) {Enable-SqlAlwaysOn -ServerInstance $node -Force}

Now that might work for default named instance but what if you have named instance on your server?  Well here is a just as easy way to deal with it using one extra trick from the SQLPS the SQLSERVER:\SQL directory.  Using this we can Get-ChildItem to retrieve the instance name.

foreach ($node in Get-ClusterNode) {$loop = Get-ChildItem SQLSERVER:\SQL\$node\
Enable-SqlAlwaysOn -ServerInstance $loop.name -Force}

This simple trick allows me to build an always on cluster with named instance just as easy as default named instance without even needing the instance names to run.

No comments: