SQLite and PowerShell

March 2015 EDIT: This post has been updated and moved to my new GitHub pages site.

I’ve been planning on sharing some fun projects that involve SQL. Every time I start writing about these, I end up spending a good deal of time writing about MSSQL, and thinking of all the potential caveats that might scare off the uninitiated. Will they have an existing SQL instance they can work with? Will they have access to it? Will they run into a grumpy DBA? Will they be scared off by the idea of standing up their own SQL instance for testing and learning?

Wouldn’t it be great if we could illustrate how to use SQL, and get an idea of how helpful it can be, without the prerequisite of an existing instance with appropriate configurations and access in place?

SQLite

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.”

What do you know, sounds pretty close to what we are looking for!  We want to use this in PowerShell, so where do we start?

Looking around, you’ll stumble upon Jim Christopher’s SQLite PowerShell Provider. If you like working with providers and PSDrives, this is probably as far as you need to go. There are other examples abound, including interesting solutions like Chrissy LeMaire’s Invoke-Locate, which leverage SQLite behind the scenes.

I generally prefer standalone functions and cmdlets over providers. I’m also a fan of abstraction, and building re-usable, simple to use tools. The task-based nature of PowerShell makes it a great language for getting things done. We can concentrate on doing what we want to do, not the underlying implementation.

I was looking for something similar to Invoke-Sqlcmd2, which abstracts out the underlying .NET logic to provide simplified SQL queries, the ability to handle SQL parameters, PowerShell-esque behavior for DBNull, and other conveniences.

Invoke-SQLiteQuery

I spent a few minutes with the SQLite binaries and examples from Jim and Chrissy, and simply duct-taped SQLite functionality onto Invoke-Sqlcmd2. Let’s take a look at what we can do

Download and unblock Invoke-SQLiteQuery, and you’ll be up and running, ready to work with SQLite. Let’s create a data source and a table:

Init

That was pretty easy! We used a SQLite PRAGMA statement to see basic details on the table I created. Now let’s insert some data and pull it back out:

InsertSelect

In this example we parameterized the query – notice that @full and @BD were replaced with the full and BD values from SQLParameters, respectively.

Let’s take a quick look at using SQLite in memory

Memory

Typically, we might use Datarow output from MSSQL and SQLite queries. As you can see above, using Datarow output leads to unexpected filtering behavior – if I filter on Where {$_.Fullname}, I don’t expect any results to come back with no fullname. Thankfully, we have code from Dave Wyatt that can quickly and efficiently convert output to PSObjects that behave as expected in PowerShell.

We did the querying above in memory. Let’s run PRAGMA STATS to see details on the in-memory data source. If we close the connection and run this again, we see the data is gone:

MemoryGone

Next steps

That’s about it! If you want simplified SQLite queries in PowerShell, check out Invoke-SQLiteQuery. If you delve into the MSSQL side of the house, check out Invoke-Sqlcmd2 from Chad Miller et al. It was used as the basis for Invoke-SQLiteQuery and behaves very similarly.

Now I just have to find more time to write…

Disclaimer: This weekend was the first time I’ve used SQLite. If I’m missing any major functionality, or you see unexpected behavior, contributions or suggestions would be quite welcome!

SQL For PowerShell, For SQL Newbies

This won’t be an in depth post, just a few tidbits I’ve picked up while diving into writing a few functions involving SQL data and databases.  Note that when I say ‘SQL newbies’, I include myself – if you have any suggestions or tips, they would be quite welcome!

You don’t need to install SQL components

If all you are shooting for is T-SQL queries, grab Invoke-Sqlcmd2 and get to work.  It’s quite simple to use, and there are no dependencies.  Thanks go to Chad and the other contributors!  A few examples:

  • Execute a basic query against one SQL instance:

sqlcmdBasic

  • Execute a basic query against three SQL instances, using pipeline input and appending the instance to each result:

sqlcmdPipeline

  • Compare the resulting data.  The first example uses the default output type (-As DataRow) and gives us errors.  The second example uses the ‘-As PSObject’ parameter to avoid the errors:

sqlcmdGTError

sqlcmdGTFix

  • Checking for values in columns.  I only want a list of servers with a value in VCNumCPU, but the results include rows where this has the DBNull value.  Same fix:  If you want to work with data in PowerShell, stick to the ‘–As PSObject’ output type.  It will waste less time than trying to deal with DBNull yourself : )

sqlcmdExist

sqlcmdExistFix

  • Parameterize your queries or face everyone’s wrath!

sqlcmdParameters

You can install minimal footprint SQL components

Perhaps you want to do more than just run T-SQL queries.  Your DBA might tell you to install the Management Studio, or SQLPSX (details).  If these fit the bill, go for it – if you want a minimal footprint, you can probably get away with only adding the SQL Server Management Object assemblies that you need.

Your DBA might even insist on using PowerShell from SQLPS.  No thank you.

This TechNet article describes how to load the SMO assemblies in any PowerShell session – In the PowerShell ISE, which is much more comfortable than SQLPS, or perhaps in a scheduled task.  Here are the steps you can take:

  • Browse to the SQL Server Feature Pack of your choice
  • Download and install SQLSysClrTypes.msi, SharedManagementObjects.msi, and optionally, PowerShellTools.msi (32 or 64 bit…)
  • Add the SMO assemblies as needed in PowerShell!
#Define assemblies. In this example, I only need the SMO assembly            
    $assemblylist =   "Microsoft.SqlServer.Smo"            

#Loop through assemblies, load them up.            
    foreach ($asm in $assemblylist){            
        Try{            
            $asm = [Reflection.Assembly]::LoadWithPartialName($asm)            
        }            
        Catch{            
            Write-Warning "$($_ | out-string)"            
        }            
    }            

#Do stuff using SMO.  Example:            
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" SomeServer            
    $srv.Information.Version            

#More details:            
    #http://technet.microsoft.com/en-us/library/hh245202.aspx            
    #http://stackoverflow.com/questions/12923074/how-to-load-assemblies-in-powershell

Extend PowerShell

Now that you know how to run quick T-SQL queries with no dependencies apart from including the Invoke-Sqlcmd2 code, look for opportunities to extend PowerShell!  Write some functions.  Or get ambitious and write a module.

How is this helpful to a non-DBA?

There are a wide variety of products out there that provide no, or minimal PowerShell support, while storing vast troves of useful data in a database.  In some cases, the queries may be readily available with a quick search.

A real world scenario:

  • A co-worker noticed a Microsoft Systems Center Operations Manager (SCOM) alert that he was worried about, asked if we could pull details on how common this alert was over the past few months
  • SCOM stores long term monitoring data in the data warehouse DB.  If I run Get-SCOMAlert, I get alerts from the past two weeks.  Is that helpful?  Certainly not here.
  • Do I want to figure out how to run the pre-made report in the SCOM console, assuming there is one?  Absolutely not.
  • I spent perhaps 30 minutes diving into queries already written by Kevin Holman and a few others, perhaps an hour to write a wrapper.  In the comment based help I included the following example for my co-worker
  • Pull all the alerts from the data warehouse DB.  Could have specified dates, but I wanted everything:

DWAlert1

  • Group the alerts by month, to see if there is a regular distribution:

DWAlert2

  • 634 in one month, 80 in the next highest month.  We have an anomaly!  Now group by days in the problem month

DWAlert3

  • 582 on a single day.  We checked change control and the events calendar, noticed that we have a network outage on that date.  My co-worker’s question was answered – not a regular occurrence.  We now have a tool the team can use to filter, group, and sort SCOM DW alerts in PowerShell to their heart’s content.

Other resources

I’m not too familiar with the SQL side of the house, but I’ve heard the following resources might be helpful:

Have other resources?  Drop them in the comments or find me on Twitter @PSCookieMonster.

Cheers!