The Surly Admin

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.

Contributing

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 Martin9700 | MS SQL, PowerShell | availability groups, clusters, Invoke-SQLQuery, Modules, sql