Manually monitoring, multiple SQL Servers service & database availability, might become very challenging. Not for our SQL Super-Girl , Işıl Efe! Let’s join her and understand how she leverages PowerShell to acomplish this complex task.
If you have only one SQL Server, things are quite straightforward in terms of monitoring the status of services and databases. However, if you have multiple SQL servers, I am sure you don’t want to spend several hours going one by one to each server. In order to make things easier, I have created a PowerShell script that will help us with multiple SQL Server environments. This script will monitor:
We will create this demo on SQL 2012 SP1 running on Windows 8.1. As a prerequisite, we have to set PowerShell execution policy to RemoteSigned with the script: Set-ExecutionPolicy RemoteSigned
First, Let´s create 3 txt files called AllServers.txt, status.txt and status_error.txt under the path C:\control_scripts as shown below.
Status.txt and status_error.txt files are empty at this point. AllServes.txt will contain the list of servers we want to monitor. You need to provide the host names of the SQL Servers here. In this test environment, we are using only one server, however we will write the hostname, twice as shown below, to confirm that the code can run in a multi-server environment.
Now, it is time to write the PowerShell script.
We will write the code in 3 basic parts, the first part of the code is a function which gets all SQL instances from a server. The second part of the code will monitor SQL services and the last part will monitor the databases for all servers.
You can save the above code as status.ps1 file under the path C:\control_scripts. The last step is to run this code in a bat file called STATUS.bat.
To execute the script with STATUS.bat, please use the below example:
powershell.exe "C:\control_scripts\status.ps1" -noclobber >status.txt 2> status_error.txt
The successful result will be written to status.txt file as shown below.
If there are any errors, the output will be written to status_error.txt file.
Original content from Işıl Efe. Posted by MEAGBS editor Turgay Sahtiyan.