Start and stop SQL Server cluster roles using script

Question

Hi Team,

We have around 5 SQL Server clusters (Active/Passive)

How to remotely bring SQL Cluster roles offline/online either using PowerShell?

Each server contains multiple SQL instances.

Environment:

Windows Server 2019

SQL Server 2017

Answer 1

Hi Vijay,

I would suggest using the PowerShell module dbatools

https://dbatools.io/commands/#WSFC

I would proceed as follows:

• Create a text file containing all Cluster names
• step through the file
• get all resource groups that are relevant (like name containing a string %SQL%)
• Restart the whole group, move the group, or restart just the SQL Server engine
• Run a loop that connects to that SQL Server instance to check the availability

Here is a basic blog post from Rob on how to use dbatools to find and restart SQL Server services

https://sqldbawithabeard.com/2018/12/03/getting-sql-services-starting-stopping-and-restarting-them-with-dbatools/

I hope my answer is helpful to you,

Your

Bjoern Peters

If the reply was helpful, please upvote and/or accept it as an answer, as this helps others in the community with similar questions. Thanks!