I was and still am a big fan of SQLPSX (SQL PowerShell Extensions). It´s is a complete PowerShell library for SQL Server written by Chad Miller and Mike Shepard, and I was one of the developers on some of the modules. It’s composed of 13 modules with 163 advanced functions, two cmdlets, and seven scripts for working with ADO.NET, SMO (SQL Server Management Objects), SQL Server Agent, RMO (Replication Management Objects), SSIS (SQL Server Integration Services), SQL script files, PBM (Policy Based Management), Oracle, and MySQL. It uses the PowerShell ISE as a SQL and Oracle query tool. Also, optional backend databases and SQL Server Reporting Services 2008 reports are provided with the SQL Server and PBM modules. It works with any version of SQL Server, 2005 or later.
The idea behind SQLPSX is “Give a man a fish, and he eats for a day. Teach a man how to fish, he eats for a lifetime.” There are plenty of great libraries that, for example, give the fish to you. If you want a fish baked instead of fried, you will be in trouble, because the function only returns fried or with a lot of parameters to expose this misleading flexibility. SQLPSX has the flexibility to do whatever you want because you choose the output. SQLPSX doesn’t try to be an end-to-end solution for common problems. It is a toolbox you can use to assemble your own solutions.
Installing SQLPSX
Mike Shepard has made the SQLPSX available on GitHub, and you can just download it and add it to your profile. I like to put it in $PsHome, or all users all hosts: c:\windows\system32\windowspowershell\v1.0\Modules\
- Unzip the file downloaded from GitHub into a temp directory.
- Open PowerShell and navigate to the Modules folder of the unzipped folder.
1cd C:\temp\SQLPSX-master\SQLPSX-master\Modules
- Run this command to unblock the files.
1get-childitem -Recurse | unblock-file
- Copy the contents of the Modules folder from temp into the Modules folder in the $PSHome path.
NOTE: Always be sure to fully review any PowerShell modules or scripts before executing them.
For a good understanding of the profiles you can have in PowerShell, review this article: Understanding the Six PowerShell Profiles.
Playing with SQLPSX
To use the module, you must import it:
|
1 |
Import-Module sqlpsxserver |
Then you will have access to all the features of this powerful library:
|
1 |
get-command -Module sqlpsxserver |
You can see from the list that there is a wealth of commands for managing SQL Server. To get the information about a SQL Server instance run:
|
1 |
Get-SqlServer -sqlserver <server\instance> |
Gathering Information from SQL Server
Several commands allow you to gather information from the instance:
Command |
Description |
||
|
Get the server process |
||
|
Check the version |
||
|
Return a list of the databases |
||
|
Return the system databases |
||
|
Return all the information about the Master database |
||
|
Return a list of the tables in Master |
||
|
Check for tables |
||
|
Check for procedures |
||
|
Check for views |
||
|
Creating the script of all the tables in the database |
||
|
Check the permissions |
||
|
List the logins |
||
|
Check for invalid logins (from restore) |
||
|
Check error Log |
||
|
Return the server roles |
||
|
Check to see if XP_CMDSHELL is enabled |
||
|
Check the default backup compression setting |
||
|
Check the priority boost setting |
||
|
Check the max server memory value |
||
|
Check the max degree of parallelism setting |
||
|
Get a list of failed SQL Agent jobs |
You can control the output of each command by using the format-table or format-list cmdlets, for instance:
|
1 2 |
get-agentjob <server\instance> | Where-Object {$_.lastrunoutcome -eq 'Failed'} |Format-list get-agentjob <server\instance> | Where-Object {$_.lastrunoutcome -eq 'Failed'} | SELECT Parent, Name, LastRunDate | Format-table |
SQL Maintenance with SQLPSX
DBAs should be spending time on more important work, not manually repeating the same tasks day after day. Automating much of this work using the SQLPSX library is possible. Here is a list of useful commands to make changes or perform maintenance:
Command |
Description |
||
|
Adding a new login |
||
|
And add a user with an existing login |
||
|
Or remove a user |
||
|
And remove a login |
||
|
Checking index fragmentation > 50% in one table |
||
|
And if it is bad, invoke a rebuild or reorg |
||
|
Or get the statistics |
||
|
And update statistics |
||
|
Add a new database |
||
|
Remove a database |
||
|
Perform a database check |
Send Results in an Email
You may have a process that returns the last backup date of the databases, and the output is using write-host, but what I have is a procedure that returns the last backup date of my databases and sends it by email to me.
First export the list of backups to a CSV file and then email as an attachment.
|
1 2 3 4 5 6 7 8 9 10 11 |
get-SqlpsxDatabase -sqlserver DESKTOP-SQEVVO1\SQL2017 | Select name, lastbackupdate | export-csv c:\temp\lastbackup.csv -NoTypeInformation -noclobber $SmtpServer = 'smtp.live.com' $SmtpUser = 'youremail@outlook.com' $smtpPassword = 'youroutlookpasword' $MailtTo = 'youremail@outlook.com' $MailFrom = 'youremail@outlook.com' $MailSubject = "Last Backup" $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $SmtpUser, $($smtpPassword | ConvertTo-SecureString -AsPlainText -Force) Send-MailMessage -To "$MailtTo" -from "$MailFrom" -Subject $MailSubject -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -Attachments c:\temp\lastbackup.csv |
Automating the Daily Checklist with SQLPSX
As you can see from the examples shown here, you can gather a large amount of information from SQL Server and perform many maintenance tasks using this library. One of the daily tasks of the DBA in charge is the daily checks. Every morning, by reviewing these, you have a way to guarantee that your environment is online and available. In the days of Kubernetes, Docker, CDI CDR, and large estates, DBAs should not do this kind of job manually. Well, in this article I will show how to automate some of those daily checklists.
Setting Up
Create a folder called c:\checklist and a text file called InstanceNames.txt with all the SQL Servers listed. Create a text file called hostnames.txt with the name of the servers (instead of SQL Server instances here, add hostnames of the servers). The folder also will accommodate all the CSV files that will be created. The text file instance names are the SQL Server instance names to be checked, and the hostnames are the servers that need checks like disk space.
Failed Jobs
Create a file called failedjobs.ps1. With SQLPSX, collecting this information becomes very simple: just check the property lastrunoutcome:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Import-Module agent import-module sqlpsxserver get-content C:\Checklist\InstanceNames.txt | ForEach-Object { $servername = $_ $nameserver = $_ -replace ('\\','_') #get the agentjobs Remove-Item "c:\checklist\$($nameserver)_failedjobs.csv" -ErrorAction SilentlyContinue get-agentjob $servername | Where-Object {$_.lastrunoutcome -eq 'Failed'} | foreach-object { [pscustomobject][ordered]@{'Sever Name' = $servername 'Job Name ' = $_.name } | Export-Csv "c:\checklist\$($nameserver)_failedjobs.csv" -NoTypeInformation -NoClobber -append $Csvs +="c:\checklist\$($nameserver)_failedjobs.csv" } } |
Disk Space
This step uses the code written by Aaron Nelson you can find here. Add the code to a file called diskspace.ps1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Get-Content C:\Checklist\hostnames.txt | ForEach-Object { $servername = $_ Remove-Item "c:\checklist\$($servername)_diskspace.csv" -ErrorAction SilentlyContinue $params = @{Computername = $servername query = "select SystemName, Name, DriveType, FileSystem, FreeSpace, Capacity, Label from Win32_Volume where DriveType = 2 or DriveType = 3" } Get-WmiObject @params | select SystemName , Name , @{Label="SizeIn(GB)";Expression={"{0:n2}" -f($_.Capacity/1GB)}} , @{Label="FreeIn(GB)";Expression={"{0:n2}" -f($_.freespace/1GB)}} , @{Label="PercentFree";Expression={"{0:n2}" -f(($_.freespace / $_.Capacity) * 100)}} , Label | Export-Csv "c:\checklist\$($servername)_diskspace.csv" -NoTypeInformation -noclobber -append } |
Databases Without a Backup in the Last 16 Hours
This script will record any database that hasn’t been backed up for 16 hours. Add the code to dbnobackup.ps1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import-module sqlpsxserver Get-Content C:\Checklist\InstanceNames.txt | ForEach-Object { $servername = $_ $nameserver = $_ -replace ('\\','_') Remove-Item "c:\checklist\$($nameserver)_dbnobackup.csv" -ErrorAction SilentlyContinue Get-SqlPSXDatabase -sqlserver $servername | Where-object { $_.status -eq 'Normal' -and $_.lastbackupdate -le (get-date).addhours(-16) } | ForEach-Object { [pscustomobject]@{ 'Database Name' = $_.name 'Last Backup Date ' = $_.LastBackupDate 'Last Differential Backup Date' = $_.LastDifferentialBackupDate 'Last Log Backup Date' = $_.LastLogBackupDate } | Export-Csv "c:\checklist\$($nameserver)_dbnobackup.csv" -NoTypeInformation -NoClobber -append } } |
Index Fragmentation
This script will return all indexes for rebuild if the fragmentation is >= 30. If >=10 and < 30, it will show reorg. No action is done. It just reports the information to the CSV file. The name of the file is indexfrag.ps1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
#Checking index fragmentation Get-Content C:\Checklist\instancenames.txt | ForEach-Object { $ServerName= $_ get-SqlpsxDatabase -sqlserver $_ -PipelineVariable DatabaseName | Get-SqlTable | ForEach-Object { $tablename = $_ Get-SQLIndex $_ | Get-SQLIndexFragmentation | ForEach-Object { $nametosave = $servername.Replace("\","_") if ($_.AverageFragmentation -ge 30 ) { [pscustomobject][ordered ]@{ "Server Name"=$servername "Database Name" = $databasename "Table Name"=$tablename "index_Name" = $_.index_name "Pages" = $_.pages "AverageFragmentation" = $_.AverageFragmentation "Action" = "Rebuild"} | Export-Csv "C:\checklist\$($nametosave)_fragmentation.csv" -NoTypeInformation -noclobber -append } elseif ($_.AverageFragmentation -le 30 –and $_.AverageFragmentation –ge 10 ) { [pscustomobject][ordered ]@{ "Server Name"=$servername "Database Name" = $databasename "Table Name"=$tablename "index_Name" = $_.index_name "Pages" = $_.pages "AverageFragmentation" = $_.AverageFragmentation "Action" = "Reorg"} | Export-Csv "C:\checklist\$($nametosave)_fragmentation.csv" -NoTypeInformation -noclobber -append } } } } |
Data and Log Files
This script returns information about the space used and free in the data and log files. The name of the file is DataLogFiles.ps1.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import-module sqlpsxserver Get-Content C:\Checklist\instancenames.txt | ForEach-Object { $servername = $_ $nameserver = $_ -replace ('\\','_') Remove-Item "c:\checklist\$($nameserver)_LogFiles.csv" -ErrorAction SilentlyContinue Remove-Item "c:\checklist\$($nameserver)_DAtaFiles.csv" -ErrorAction SilentlyContinue Get-SqlPSXDatabase -sqlserver $servername | ForEach-Object { $_.FileGroups.Files | Select-Object Name, FileName, GrowthType, @{ N = 'Growth MB'; E = { [math]::Round(($_.Growth * 1024)/1MB, 2) } }, @{ N = 'File Size MB'; E = { [math]::Round(($_.Size * 1024)/1MB, 2) } }, @{ N = 'Database Used Space MB'; E = { [math]::Round(($_.UsedSpace * 1024)/1MB, 2) } }, @{ N = 'Max Size MB'; E = { [math]::Round(($_.MaxSize * 1024)/1MB, 2) } }, @{ N = 'Free Space on Disk GB'; E = { [math]::Round(($_.VolumeFreeSpace * 1024)/1GB, 2) } } | Export-Csv "c:\checklist\$($nameserver)_DataFiles.csv" -NoTypeInformation -noclobber -append $_.LogFiles | Select-Object Name, FileName, GrowthType, @{ N = 'Growth MB'; E = { [math]::Round(($_.Growth * 1024)/1MB, 2) } }, @{ N = 'Max Size MB'; E = { [math]::Round(($_.MaxSize * 1024)/1MB, 2) } }, @{ N = 'Size MB'; E = { [math]::Round(($_.Size * 1024)/1MB, 2) } }, @{ N = 'Used Space'; E = { [math]::Round(($_.UsedSpace * 1024)/1MB, 2) } }, @{ N = 'Free Space on Disk GB'; E = { [math]::Round(($_.VolumeFreeSpace * 1024)/1GB, 2) } } | Export-Csv "c:\checklist\$($nameserver)_LogFiles.csv" -NoTypeInformation -noclobber -append } } |
New Logins
Check for new logins created in the last 24 hours. The name of the file is Newlogins.ps1.
|
1 2 3 4 5 6 7 8 9 10 11 |
import-module sqlpsxserver get-content C:\Checklist\InstanceNames.txt | ForEach-Object { $servername = $_ $nameserver = $_ -replace ('\\','_') Remove-Item "c:\checklist\$($nameserver)_newlogins.csv" -ErrorAction SilentlyContinue Get-SqlLogin -sqlserver $servername | Where-Object {$_.creationdate -ge (get-date).adddays(-1)}| export-csv "c:\checklist\$($nameserver)_newlogins.csv" -NoTypeInformation -NoClobber -append } |
After the checks
The last step will look for CSV files and send them by email. I am using Outlook’s SMTP server but change to your credentials. This file will be called sendemail.ps1.
|
1 2 3 4 5 6 7 8 9 10 |
$csvtosend = @() $csvtosend = (dir "c:\checklist\*.csv").fullname $SmtpServer = 'smtp.live.com' $SmtpUser = 'youremail@outlook.com' $smtpPassword = 'youroutlookpasword' $MailtTo = 'youremail@outlook.com' $MailFrom = 'youremail@outlook.com' $MailSubject = "Daily Check" $Credentials = New-Object System.Management.Automation.PSCredential -ArgumentList $SmtpUser, $($smtpPassword | ConvertTo-SecureString -AsPlainText -Force) Send-MailMessage -To "$MailtTo" -from "$MailFrom" -Subject $MailSubject -SmtpServer $SmtpServer -UseSsl -Credential $Credentials -Attachments $csvtosend |
Creating the jobs
Now it´s time to create the job and add the steps with the files. Create a job called Checklist. Use the name of the file for each step. The step type will be Operating system (CMDEXEC). The command for each step will be:
|
1 |
PowerShell.exe “c:\checklist\<nameofthecheck>” |
There should be one step for each check, and the final step will be the sendemail.ps1.
Be sure the account running the SQL Agent job has rights in the paths and, more important, Windows authentication in the SQL Server instances. Otherwise, you may want to use a Proxy account to run the job. If you have tons of SQL Server instances, a better approach is to store the results in tables and then create a report instead of emailing files.
Conclusion
As you can see, there are plenty of commands that can apply in the server, database, table, index level. Just use get-command -Module sqlpsxserver to check the available cmdlets of SQLPSX. As I said, the beauty of SQLPSX is the flexibility of the output. Of Course, you need a little bit more knowledge of PowerShell, but now that you have the tools, you can build whatever you need!
Load comments