The Surly Admin

Father, husband, IT Pro, cancer survivor

Announcing the PS.SQL Module

I’ve been doing a ton of work with SQL and PowerShell over the last year and a half and have come up with some pretty good tools to help me along the way.  We even built a module out of them at work and use it in a dozen or more scripts every day, when it finally occurred to me that my readers might like to use them too.  And the PS.SQL Module was born.

Get it on GitHub

If you’d like to take a look at the module, you can find it on Github here.  This is the first commit to the master repo so it’s still a little rough around the edges but I’ll be working on that over the next couple of weeks and should have it cleaned up soon.  I love the idea of GitHub for PowerShell modules because it allows people to fork it, make some additions and even submit their changes to get it incorporated into the master module.

If that all sounds great, but you find yourself perplexed at how GitHub works, you are not alone!  Working with GitHub has been driving me nuts for months until I finally just started reading, looking, playing with it and now I think I have a pretty good grasp of the concept.  I’m no expert, and I don’t use the command line for working with it, but I plan on doing another post that explains GitHub at a very high level.  I think for script writers, we have more modest needs and just about every document I’ve read about GitHub is written for a developer who has more complex needs then you and I!  So this upcoming post will be from a PowerShell scripters perspective.

Functions in PS.SQL

But let’s get back to PS.SQL.  Here are the functions within the module as of this writing:

  1. Get-AGCluster: Use this function to get a basic configuration of a SQL AlwaysOn Availability Group.  The objects produced by this function are used in Get-AGDatabaseState, Get-AGReplicaState and Set-AGClusterFailover.
  2. Get-AGDatabaseState: reports database information and it’s association with the AG.  What database is on which AG, etc.
  3. Get-AGReplicaState: reports the replication and AG health
  4. Get-SQLAuthenticationScheme: will tell you what authentication scheme SQL is using.  If your SPN’s are set properly this should report Kerberos, which is typically what you want.
  5. Get-SQLJobsStatus: Will return objects on the status of your SQL jobs for a server.
  6. Invoke-SQLQuery: This is the backbone of the module, and lets you quickly and easily run a SQL query against one, or many SQL servers.
  7. Move-RemoteClusterGroup: This is not a SQL related function, but instead lets you failover the Windows Failover Cluster Groups to different nodes.  Since Availability Groups use WFCG’s it is needed by Set-AGClusterFailover.
  8. Shrink-SQLLogFile: point the script at a SQL server, receive a list of databases and select the ones you want to attempt to shrink the log for.  Yes, I know Shrink is not an approved verb and I’ve been resisting renaming it for the longest time because Shrink just makes sense.  But I gave in and the next version of PS.SQL will rename the function to Resize-SQLLogFile.

Where is this Set-AGClusterFailover function I kept mentioning?  It’s still in development and you’ll find it on the dev branch of PS.SQL.  It works fine for failing over to synchronous nodes but I haven’t fully worked out the code for failing to asynchronous nodes yet.  There are also a couple of reports I want to include in the module for backups and job status.


I always invite contribution to my scripts, so please feel free to fork the dev branch–please don’t fork the master as I’ll reject any pull requests directly to it–and there’s no need to update the PowerShell module file (PS.SQL.psm1) as I’ll take care of that part.  Simply go into source, and work from inside there.  Private folder is for help functions that you don’t want users to use, while the Public folder is for the user functions.  Place your Pester tests in the Test folder.

That’s it for now!


February 19, 2016 - Posted by | MS SQL, PowerShell | , , , ,


  1. How does this differ from the SQLPS modules or the built in SMO commands?? Is this just a wrapper for those?

    Comment by Jeremy Green | February 20, 2016 | Reply

    • Great question, thanks Jeremy. I’m not a big fan of the PowerShell Provider for SQL. Not sure if you’ve used it but it’s soul crushingly slow. Also, I found working with it to be counter intuitive–the first version of my “AG” functions all used SQLPS and it took me a long time to develop. As for SMO, that’s a lot better from a speed perspective, but I just found it more difficult to work with–and frankly I’m more familiar with running SQL queries. But even with SMO’s speed, I found running a SQL query to be significantly faster so it just made sense to go with the thing I was more familiar with–and have it be faster to boot!

      Both techniques also require an install to make functional. My module does not, it will work right out of the box. Check out the GitHub repo too, I just added functions for getting the last SQL jobs and backups runs, and the ability to make some nice looking reports out of that data (if you want).

      Comment by Martin9700 | February 20, 2016 | Reply

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: