We're performing maintenance on our phone system today. If you are unable to reach us by phone, you can email us instead. For sales queries email sales@red-gate.com, you can visit our support center for help with our products, or for anything else email info@red-gate.com.

What Is SQLPSX?

SQLPSX is a useful library you can add to PowerShell to automate many DBA tasks. In this article, Laerte Junior demonstrates several useful commands and shows you how to create a SQL Agent job using this library to run daily DBA checks.

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\

  1. Unzip the file downloaded from GitHub into a temp directory.
  2. Open PowerShell and navigate to the Modules folder of the unzipped folder.

  3. Run this command to unblock the files.

  4. 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:

Then you will have access to all the features of this powerful library:

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:

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:

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.

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:

Disk Space

This step uses the code written by Aaron Nelson you can find here. Add the code to a file called diskspace.ps1.

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.

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.

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.

New Logins

Check for new logins created in the last 24 hours. The name of the file is Newlogins.ps1.

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.

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:

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!