Windows Powershell: Useful commands for the SQL DBA

What is Powershell?
The tool is available on Windows Servers by default and is using to scripting purposes as well as many other functionalities such as Automating Windows / SQL Server functions.

How to Access:
Powershell can be accessed through this icon on desktop area Pshell1 or  through executable file on the root drive (2.0 is the version here, there are versions 1.0,2.0 and 3.0). –  %SystemRoot%\system32\WindowsPowerShell\v2.0\powershell.exe

Useful Commands for SQL Server DBA

For a database administrator is very useful to have an utility to run remote commands to query data from multiple servers across the same domain. This can be done also using native DOS prompt (SQLCMD), but powershell has some interesting features as well and allows more customization. Here is a way to deploy health check scripts to route the commands to any other Server on the same domain.

1. Make sure the environment has RDP connection to other servers (such as Production to Dev/Test and back/forth).
2. Save your script on any location. Example C:\Scripts\SQL\Health_check.sql
3. On this same directory, create a new empty text file and enter this piece of code. The first 2 lines are to make Powershell able to use SQLCMD cmdlets, otherwise the string fails to start.
Then, $sql is a variable which receive user input (the SQL Instance name)  and once this data is given, the invoke-sqlcmd will execute the script.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Job Failure Check: Enter SQL Instance Name’
invoke-sqlcmd -inputfile “F:\MSSQL\jobfailure.sql” -serverinstance $sql -Verbose

Save the file with .ps1 extension so Powershell can recognize it as a Script. This is the result (example using sqlhc.ps1 file)

Pshell2

Here we input the SQL Instance name for a quick Instance health check.
Note:  This may different from each client environment: If the single instance name does not resolve, try using with full qualified domain name + port or VIP Address + Port or Cluster Group name + Virtual Server Name.

Result:

Pshell3.jpg

The same script can be adapted to hold another variable. Very useful when you want to run scripts agains a specific SQL Server database. Example below is for a User Database Space Check script.  The $sql variable is the input for SQL instance name, and $db the Database name.

Add-PSSnapin SqlServerCmdletSnapin100 -ErrorAction SilentlyContinue
Add-PSSnapin SqlServerProviderSnapin100 -ErrorAction SilentlyContinue
$sql = Read-Host ‘MSSQL Database Files Check: Enter SQL Instance Name’
$db = Read-Host ‘MSSQL Database Files Check: Enter Database Name’
invoke-sqlcmd -inputfile “F:\MSSQL\dbspace.sql” -serverinstance $sql -database $db  -Verbose

Pshell4

Power_prompt_space.jpg

With Powershell we can also manipulate the Services Console and Event Viewer:

Querying a remote server for running SQL Instances (any status)
get-service MSSQL*,SQLAGENT*  -computername <servername>  | Format-Table -AutoSize -Wrap

Pshell5

Querying a remote server for running SQL Instances (stopped or started status)

get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap
get-service MSSQL*,SQLAGENT*  -computername <servername>  | where-object {$_.Status -eq “Stopped”}  | Format-Table -AutoSize -Wrap

Querying a list of Servers for running SQL Instances:
notepad comp.txt
get-service MSSQL*,SQLAGENT* -computername (gc .\servers.txt) | where-object {$_.Status -eq “Running”}  | Format-Table -AutoSize -Wrap

Pshell6.jpg

Retrieving Event Viewer Logs data from remote servers:

In the -logname parameter, change to System, Application or Security. You can also modify the amount of data to be displayed on screen.
get-eventlog -logname System -entrytype Error -newest 20 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

Finding 10 recent reboot commands on a computer
get-eventlog -logname System -source User32 -newest 10 -computername <insert_server_here> | Format-Table -AutoSize -Wrap

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s