Working with SQL using PowerShell

I’ve seen a lot of people ask about how to do this, so I thought I’d provide some information on the matter. Previously I had a project in which I had to pull a ton of data from an extended SCCM database and massage it before importing into MDT to setup location settings and things like that for an automated zero-touch install with very granular configuration settings determined based on workstation location.

I had to learn this the hard way, but it saved me weeks worth of painstakingly combing through records, and was also pretty fun once I dug into the guts, and was able to get old customized SQL records to jive with the Microsoft Deployment Toolkit Database!  It’s fun to teach machines to talk to each other!

Because many PowerShell enthusiasts may not be familiar with SQL, in this post I’ll walk through the steps to install SQL, setup a database and then create a table, before digging into how to directly influence SQL from the PowerShell prompt.

Setting up our SQL Database Engine

First things first, we’ll create our SQL Test DB. I’ll start with Sql Server express 2008 with tools, since it is so common and easy.  Get the bits here http://www.microsoft.com/en-us/download/details.aspx?id=22973.  Once it finished downloading, run the install, selecting Installation and then ‘New SQL Server Stand-alone installation or add features to an existing installation”

sql01
You want the top option

The SQL install wizard will check system state before getting you too deeply into the install.

sql02

If you run into this ‘Need to Restart’ rule, you can bypass the restart by deleting the items under the following key.

HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager and delete all items under PendingFileRenameOperations (Taken from http://www.sqlcoffee.com/Troubleshooting051.htm).  You can then rerun the Rule Check by clicking re-run.

sql03
All systems are go!

 

Install Setup Support Files, if needed, then correct any dire errors under Setup Support Rules, if encountered.  Select Free edition, then at the Feature selection screen pick at a minimum Database Engine Services and Management Tools – Basic

sql04

On the next screen, pick where you want your DB to go, and specify an instance name (I’m leaving this default).  Click Next.

For Accounts, select NT Authority System and local service, as this is just our test.  If you’re setting up production SQL, you should know that many applications which run on SQL won’t install if you leave the barn-doors open from a security standpoint.

sql05
Good enough for a test environment, but not recommended when going production

Click Next

sql06
Ensure you specify Add Current User to SQL Server Administrators

next next next next next next next, and then when this loading bar shows up, go ahead and grab a sandwich.

sql07
It will pause at this screen until roughly the heat-death of the universe.

Once the wizard finally completes, you should have SQL Installed!

Creating a Table

Alright, now that the fun of the installation wizard is behind us, it is time to open up management studio and create our first SQL database and table, then we’ll do some PowerShell!  First things first, fire up Management Studio.  and for your engine, select localhost, and leave the authentication as ‘Windows Authentication’ if you followed my steps earlier.

sql08

Now, click localhost in Object explorer on the side, then click databases, right-click and make a new database.

sql09

Pick whatever you’d like for a name. I choose FoxDeploy.  You can safely leave everything else as the default for our purposes, then pick next. Select your Database and browse to tables and pick new table

Pick a name for the table on the far right side, and then add some columns to your sheet. In my screen shot, I’ve selected the datatype of nchar(10), but I would recommend picking a type such as MAXVARCHAR or something similar in your own test. The nchar(10) datatype will limit the number of characters you can input, and can give you some puzzling errors.

sql10
On the right side, select the name of the table, I choose Powershell,

 

Finally, click this button to generate a change script you’ll execute which will spin up the table we need to insert data.

sql11

A box should appear, offering you the option to create a Save Change Script.  This is incredibly strongly recommended if ever touching production SQL.

sql12
You can copy the query out of this window and run it directly, which saves a step of running the transaction log file created

 

Click New Query, paste the script in, and hit f5! And hold on to your butts!

sql13

If everything worked correctly, you should receive confirmation in the messages window below.

sql14
Most of the time in SQL you won’t see this message….

 

Now hit F5 at the base level of the DB view and you should see your new table

Now we have a database engine running locally, with a database named foxdeploy, and a table titled powershell. The table should have two columns, filename and size.  This is another good time to grab a drink, as we’re finally about to begin coding!

SQL And PowerShell – Better Together?

Before I get into how to do the PowerShell bit, I want to tell a story which came up when I thought of this section heading.

Years ago I worked at a bank in telemarketing position. One day, we had a huge meeting where they had us hundred employees go into a room, and gave us mouse pads, shirts, and mugs, then go to our seats.  You can always tell how cheesie or impactful a reorg will be based on the amount of swag they give out. They even had candy in our seats when we got there.  My cheese-alert was off the radar.

They splurged for thousands and thousands of custom re-packaged Reeses’ cups with the company logo on it, with the saying ‘like chocolate and peanut butter, customer service and sales are better together!’ on the wrapping.

Turns out they were essentially laying off the custom service folks and making us do their jobs. Yay!  Who doesn’t want to call in to activate their credit card and get a hard-sale for a home-equity line, or call about an over limit fee and be sold useless credit insurance.

At least we got free candy.

reeses

 

Well, as it turns out, PowerShell and SQL do work pretty well together.  Better than a Reese’s cup…nah, maybe not quite that well.

Most of the magic of pulling info from and putting stuff into SQL revolves around using the Invoke-SQLCmd cmdlette to perform native language queries on the SQL databases and tables. This useful tool is installed as part of SQL Management Studios.  What we’ll be doing is gathering information with PowerShell and sending it down the pipeline to

To do our work, we’re concerned with the following syntax of the Invoke-SQLCmd cmdlette:

PS SQLSERVER:\> "database","serverinstance","query" | % {get-help Invoke-Sqlcmd -Parameter $_}

-Database
A character string specifying the name of a database. Invoke-Sqlcmd connects to this database in the instance that is specified in
-ServerInstance.
If -Database is not specified, the database that is used depends on whether the current path specifies both the SQLSERVER:\SQL folder
and a database name. If the path specifies both the SQL folder and a database name, Invoke-Sqlcmd connects to the database that is
specified in the path. If the path is not based on the SQL folder, or the path does not contain a database name, Invoke-Sqlcmd
connects to the default database for the current login ID. If you specify the -IgnoreProviderContext switch, Invoke-Sqlcmd does not
consider any database specified in the current path, and connects to the database defined as the default for the current login ID.

Required? false
Position? named
Default value
Accept pipeline input? false
Accept wildcard characters? false

-ServerInstance A character string or SMO server object specifying the name of an instance of the Database Engine. For default instances, only specify
the computer name: "MyComputer". For named instances, use the format "ComputerName\InstanceName".

Required? false
Position? named
Default value
Accept pipeline input? true (ByValue)
Accept wildcard characters? false

-Query
Specifies one or more queries to be run. The queries can be Transact-SQL or XQuery statements, or sqlcmd commands. Multiple queries
separated by a semicolon can be specified. Do not specify the sqlcmd GO separator. Escape any double quotation marks included in the
string. Consider using bracketed identifiers such as [MyTable] instead of quoted identifiers such as "MyTable".

Required? false
Position? 1
Default value
Accept pipeline input? false
Accept wildcard characters? false

We’ll be specifying a ServerInstance, Database and a query to run. Now, the -Query parameter is expecting native SQL language, here is an example of the SQL Transaction Query we’ll be running:

insert into Tablename (ColumnName1, ColumnName2) VALUES ('Value1', 'Value2')

Not too bad to work with. You specify the ColumnName first, and then the Values to go into each row within that column.  This won’t be too hard to do, but let’s define our $database, $server and then do a test-run to give it a try, remembering that our column names are filename and size.


$database = 'foxdeploy'
$server = '.'
$table = 'dbo.powershell_test'

Invoke-Sqlcmd -Database $database -ServerInstance $server `
-Query "insert into $table (filename, size) VALUES ('test','45')"

When you execute this code, you won’t get any update at all from the console.  It is necessary to test and see that the change occurred:

Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "select * from $table"

sql15

Sweet!  Like Candy!

The Practical-ish Example

Now, let’s use a practical example. In this situation, let’s list out the ten biggest files in a folder and select the filename and size, and then import those properties into our SQL Database. Keep in mind, the sky is the limit here, you could be polling for biggest mailboxes and then doing something with those users, dump records from AD into an analysis services instance for some in-depth reporting, do some datamining or whatever you can put your mind to.  This is purely proof-of-concept.

For a preview, these are the files in the directory I’m listing out:

sql16
If all goes well, these should appear as entries in our DB

What we’ll do is setup variables to simplify our command structure (and let us re-use this code in the future).  We’ll then get  and sort a list of the files, and then pull values from each record, which we’ll store in a variable.  We’ll then use Invoke-SQLCmd to act on this data, and insert one row for each item:


$database = 'foxdeploy'
$server = '.'
$table = 'dbo.powershell_test'

Get-ChildItem c:\temp | sort Length -desc | select -First 10 | % {
    $Name   = $_.Name
    $FileSize = $_.Length
    $query = "insert into $table (filename, size) VALUES  (`'$Name`', `'$FileSize`')"
Write-Host "About to Execute:`t  $query"
Invoke-Sqlcmd -database $database -ServerInstance $server -query "insert into $table (filename, size) VALUES     (`'$Name`', `'$FileSize`')"
}

And now, let’s run one more query to see if our changes took…


Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "select * from $table"

 

sql17
Blam! We’re pulling this data that we just added into SQL back from our database!

It worked!  To round things out, here is a screen shot from SQL management studio which shows that the entries now exist within the table from that side too.

sql18

Winding Down

While it is very unlikely that you’ll need to import a list of files into your SQL DB anytime soon, but just think of the possibilities.

You can take these principles and do anything you put your mind to with them.  This is one of the best features of PowerShell, this infinitely rewarding aspect of it.  A little bit of tinkering in your spare time one day can lead become the start of a powerful new tool to you’ll come back to over and over again in the future.  Just ten minutes of reading up on best practices can lead to a lifetime of positive ripples.

Be sure to check back later, when I’ll flesh this out with some code samples of deleting rows, updating rows, and creating/dropping tables. I hope this was helpful!

Advertisements

7 thoughts on “Working with SQL using PowerShell

  1. ramblingcookiemonster September 9, 2014 / 12:06 am

    Cool stuff! I like that you walked through setting up SQL, it can be intimidating not knowing where to start. I tried a quick post on the topic a while back, but never got into deploying SQL.
    A few tips that might come up!
    * You can get SQLPS through the SQL Feature Pack, no need for a full install or even management studio.
    * Invoke-SqlCmd had a number of bugs the last time I checked. Chad Miller, a (former?) SQL MVP, wrote Invoke-SqlCmd2 to address this, a few folks added helpful features like parameterized queries. Latest that I know of is available here: https://raw.githubusercontent.com/RamblingCookieMonster/PowerShell/master/Invoke-Sqlcmd2.ps1. Only dependency is accessibility to the sub-400-line function.
    * Parameterize queries if you’re going to pass in user input. Seeing PowerShell variables in a query is a bit scary, folks should know this is a major, major no-no unless they are hard coded. This has been important for years, yet I still occasionally have to slap wrists at work: http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/
    Thanks for bringing more attention to SQL. It’s quite flexible for your own purposes, and there is a wealth of data exposed through SQL and only SQL, including a number of Microsoft products (e.g. OpsMgr datawarehouse)
    Cheers!

    • FoxDeploy September 9, 2014 / 12:13 am

      Thanks for the comments. Could you expand on the dangers of a parameterized query? Is this because of the chance of someone manipulating your code to drop tables and things?

      • atamido December 2, 2014 / 3:37 am

        That is correct. It completely avoids this situation: http://xkcd.com/327/

        In addition to security, it also avoids errors being thrown because of unexpected characters/data in your queries.

  2. ramblingcookiemonster September 9, 2014 / 12:52 am

    Exactly! It’s a reasonably simple safeguard against malicious (or otherwise) SQL injection. In PowerShell, we have similar concerns with Invoke-Expression usage http://blogs.msdn.com/b/powershell/archive/2011/06/03/invoke-expression-considered-harmful.aspx.
    There’s an example in the Invoke-SqlCmd2 comment based help, and there is an example snippet showing how you could use this to simplify typical update and insert statements here: https://gist.github.com/RamblingCookieMonster/4074033ea5eec6c411d0
    I usually err on the side of caution. Even when variables aren’t exposed to a user, I tend to parameterize the query just in case any changes in code down the line expose them. It’s very quick and becomes second nature, a very small price to pay IMHO

    • FoxDeploy September 9, 2014 / 9:48 am

      This is excellent feedback. Thank you so much! I’m strongly considering rewriting my query section for greater safety.

    • FoxDeploy September 10, 2014 / 9:54 pm

      In your opinion, what if I were to break this into four separate, individually signed scripts with parameters. Would that be secure enough

      • ramblingcookiemonster September 11, 2014 / 1:32 am

        Hello again!
        Personally I don’t worry about signing scripts. In my environment, the benefits of not signing outweigh the risks.
        By parameters, do you mean SQL parameters or PowerShell parameters? If SQL parameters, absolutely that would be good enough. I drafted up a quick modification of one of your examples replacing it with Invoke-SqlCmd2 and SQL parameters here: http://poshcode.com/paste/514/. I might be failing at google, but I don’t see the option for this in Invoke-SqlCmd, which IMHO is a terrible omission!
        You could experiment with this if desired. Try creating a file name with a semi-colon in it and some interesting sql code after the semi-colon. Even if you didn’t have malicious intent, if you ever ended up with an odd environment, parameterized queries would help limit the risk.
        All this being said – I’m just a SQL consumer, and a recent one at that. If you hit up #SqlServer on Twitter you might find folks who can provide more wisdom than I.
        Cheers!

Leave a Reply

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

WordPress.com Logo

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