Moving to GitHub Pages has served me well for a few years, but it’s time for a move!

I gave GitHub pages and Jekyll a try this weekend, I’m sold.

The content here will remain. This site will be (mostly) static going forward, all new content will be posted on my GitHub page.


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


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:


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:


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


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:


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!

Testing DSC Configurations with Pester and AppVeyor

I spent the last few days tinkering with AppVeyor. It’s an interesting service to help enable continuous integration and delivery in the Microsoft ecosystem.

Last night I realized it might offer a simple means to test the outcome of your DSC configurations. Here’s the recipe for a simple POC, with plenty of room for you to tweak and integrate with your existing processes:

  • Create a DSC configuration you want to test
  • Create a short script to apply that configuration
  • Create some Pester tests to verify the desired state of your system, post configuration
  • Create the AppVeyor yaml to control this process
  • Add this to an appropriate AppVeyor source (Example covering GitHub)
  • Test away! Make a change to the DSC code, commit, and AppVeyor spins up a VM, applies the DSC configuration, and your Pester tests verify the outcome

This certainly isn’t a perfect method, but it would be a simple way for anyone to get up and running writing and testing DSC configurations and resources.

I’m going to make the assumption that you are familiar with GitHub, Pester, and some of the basics of AppVeyor from my first overview.

Wait, what is DSC?

Windows PowerShell Desired State Configuration is a configuration management platform from Microsoft. It’s still young, but is fast tracked for the Common Engineering Criteria, receives a good deal of attention from the PowerShell team, and the guy who brought us PowerShell is quite excited about it. It’s probably something you should be paying close attention to.

There is quite a variety of resources to get started with. The DSC Book from is a nice overview, and the soon-to-be-published MVA series was quite helpful (Getting Started, Advanced).

Let’s look at some caveats to testing DSC over AppVeyor.

Yes, but…

  • You could do much of this on your own, with tools like Client Hyper-V and AutomatedLab. You might even have a similar toolset in place at work.
  • No testing of distributed systems. This allows testing on a single VM.
  • Limited selection of operating systems to deploy on. There is an OS option in the yaml, presumably this means we may see more.
  • Your configurations cannot restart the VM. Hoping this will change, but depending on their architecture and design, this may be tough.
  • You’re deploying on a hosted service. If your DSC configurations are integrated with your CMDB and other internal resources, this may be a show stopper.
  • A good DSC resource should have a solid ‘test’ function. That being said, a single DSC resource’s test function might not handle the intricate combination of resources applied to a system.

Right. I’m sure I’m missing others as well. Certainly not perfect, but if your goal is to write and test some general DSC resources or configurations, and you don’t have the tools in house, this is a fantastic and simple way to spin up fresh VMs, configure them with DSC, and verify that the resulting system is now in the desired state.

Another caveat – building your own system that would perform a similar functionality would be an incredibly valuable experience. They always say “don’t re-invent the wheel,” but if your goal is experience and to learn, re-inventing the wheel is a great way to get there!

Pick a source

I’m going to stick to GitHub for this. Keep in mind that AppVeyor is only free for open source projects. Consider your security posture before uploading any sensitive DSC configurations.


Pick a DSC configuration to test

We’re going with an incredibly basic example here:

Write a controller script to apply the configuration

There are plenty of ways to do this. Modify this POC example to meet your needs.

Most of this should be self explanatory. We force application of the ContosoWebsite configuration from WebServer.ps1. The one extra bit is that we save the path to the resulting MOF file in Artifacts.txt. We upload this later on.

Write your Pester tests

Go crazy. I wrote some very simple, limited tests for this POC:

Keep in mind that you aren’t limited to one pass. You could theoretically apply your configuration, test, add a ‘mischief’ script that messes with the configuration, and test to verify that your DSC configuration brings things back in line.

Write the AppVeyor yaml

We’re almost done!  At this point, we’re going to tell AppVeyor what to run. There’s a lot more you can configure in the yaml, so be sure to flip through the options and experiment.

What does this do?

  • We ignore any commits that match ‘updated readme’, to avoid unnecessary builds.
  • We run appveyor.dsc.ps1, which applies the DSC configuration and saves the mof path.
  • We run appveyor.pester.ps1, which invokes the pester script, sends test results to AppVeyor, and tells us if the build passed.
  • We upload the mof file, which will be available on the artifacts page for your AppVeyor project.

Tie it all together

We’re good to go! We create a GitHub repository, add this to our AppVeyor projects, and make a commit (covered here). Browse around the project on AppVeyor to see the results:


That’s it – we now have a simple framework for automated DSC resource and configuration testing. There’s a lot more you might want to do, but the simple POC material is in the AppVeyor-DSC-Test repository on GitHub.