PowerShell and SQL Server – Part 1

I recently watched a video of Mike Robbins’ presentation at the PowerShell and DevOps Global Summit 2016. It’s titled “Building Unconventional SQL Server Tools in PowerShell with Functions and Script Modules“. It makes for interesting viewing. One of Mike’s comments stuck with me; that using the .Net framework was the way he chose to interact with SQL Server because it didn’t rely on any of the SQL Management Objects, nor did it require PowerShell to be installed on the target server.

Like Mike I’ve been dealing with SQL Server since version 6.5 and PowerShell since version 1.0, although I can’t claim as much in-depth SQL knowledge as Mike. In my day job I deal with the whole gamut of SQL Server versions and finding a consistent way of dealing with all of them from a PowerShell perspective has been… interesting.

The .Net approach is great, because as far as I can tell the System.Data.SqlClient class has been available in .Net framework since version 1.1, so it should be available on almost any machine with any version of PowerShell.

In this, the first of a series of articles, I’m going to talk about the basics of accessing SQL Server using PowerShell and the .Net framework . Later articles will address different topics, including inventorying and monitoring SQL Server using this approach.

First the basics.

To get SQL Server connectivity going we need to use 3 .Net objects: An SQLConnection, an SQLCommand and an SQLDataReader. The SQLConnection establishes connectivity with the target server, the SQLCommand defines what task is performed and the SQLDataReader receives the results, if any.

Creating an SQLConnection is easy enough:

$connection = New-Object System.Data.SqlClient.SqlConnection

In order for the object to connect to the server it needs a Connection String. Typically this will contain a Data Source (server and if necessary instance), Initial Catalog (database) and authentication details. For a connection using SQL Server authentication the Connection String might look like:

"Data Source=SQLSERVER;Initial Catalog=Database1;User ID=sqluser;Pwd=4P4$$w0rd"

So to make the connection we set the object’s Connection String and call its Open method :

$connection.ConnectionString = "Data Source=SQLSERVER;Initial Catalog=Database1;User ID=sqluser;Pwd=4P4$$w0rd"
$connection.Open()

Next we need to decide what we want to do with our connection. Typically we’ll want to run a query or other T-SQL. We use the SQLCommand object for this and we’ll set the CommandText property with the T-SQL we want to use and the Connection property with the Connection object we previously created.

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = "SELECT CustomerName, PostCode FROM Customers"
$command.Connection = $connection

I won’t go in to the details of T-SQL here, but bear in mind that the CommandText property doesn’t do any validation. The first that you know about any typos is when you execute the command. In our example we’re going to use the SQLDataReader object to execute our SQL command.

$reader =  $command.ExecuteReader()

We can now move through the returned data by using the Read method of the SQLDataReader object:

while ($reader.Read())
{
    Write-Output "$($reader['CustomerName']),$($reader['PostCode'])"
}

Once we’re done then we can tidy up by closing the SQLDataReader:

$reader.Close()

This is a good first example but what we haven’t got is an object containing all of our data, like we would have from Import-CSV. We could create a PSCustomObject and map the SQL columns, but there’s an easier way. The .Net framework includes a System.Data.DataTable object class. We can load our SQL data into a DataTable and then treat it like any other object (by piping it to Out-GridView for example):

$datatable = New-Object System.Data.DataTable
$datatable.Load($reader)
$reader.Close()

That about covers it for Part 1.

Building Unconventional SQL Server Tools in PowerShell

Advertisements
This entry was posted in PowerShell. Bookmark the permalink.

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