Monday, October 29, 2012

PowerShell and SQL: Presentation Materials

I gave my first SQL Presentation this month at our local SQL user group here in Austin, CACTUSS. I had a great time giving the presentation and I want to thank everyone who came out to see me.
Below are the files from my presentation.



Regular blog posts will resume next week.

Hasta Luego,

Friday, October 12, 2012

PowerShell and SQL - Getting Data in and out

This week we are going to look at interacting with SQL server using some simple DML commands. Using PowerShell you can perform select, insert, update and delete actions on your SQL Server. There are two functions from the SQLPSX library that we will be using today: Get-SqlData and Set-SqlData.

Note: I am going to be using the SQLPSX modules again. If you don't have them installed go HERE

Getting Data out of SQL Server

First lets look at how to execute a simple SELECT statement against a database. I am going to be using Microsoft's sample database Adventure Works on a SQL 2008 R2 install. The function we are going to be using is Get-SqlData. First thing we want to do is look at the help file to see what it can do.

Get-Help Get-SqlData

On my computer i get this result:

This command has three parameters, two required (dbname and qry) and one optional (sqlserver). If you leave out the sqlserver parameter it will default to localhost. 

NOTE: I am assuming that you have the proper access to the SQL server with your current logged in Windows account. If you do not look at example two when you run the command 'Get-Help Get-SqlData -examples'

Lets grab the titles and names from the person.person table where the first name is John

NOTE:  In the examples in this post I am using PowerShell ISE and typing everything in the script pane first before executing it. 

Import-Module sqlmaint
$sqlserver = 'Nightwing'
$dbname = 'AdventureWorks2008R2'
$qry = "select title, firstname, middlename, lastname
from person.Person
where FirstName = 'John'"

Get-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Here is my result:

Now let's break the script down. First I imported the sqlmaint module. Then I declared three variables ($sqlserver, $dbname, $qry) which I assigned the string literals for my server, the database and my query. Then I ran the Get-SqlData function and used those variables as the input parameters. Why did I do it this way instead of feeding the strings directly to the function? That is an excellent question. I did it for three reasons. 

First is readability, that is a pretty simple query but a more complicated one would be harder to debug in-line. 

Second is for re-usability, when you write a script to interact with SQL server you are probably going to execute more than one query. This way I set the server name and database name once and on every subsequent database call I just need to redefine the $qry variable. 

Lastly, I did it for portability. I can move this script from one server to another and all I have to do is change the $sqlserver variable once and it will work. I don't have to hunt through the code or do a find and replace. This is very helpful when you, as you should be, are testing your code on a development server before moving it to production.

Putting Data Back into SQL Server

Ok, so now we know there are 58 people in the person.person table with the first name of John. Lets write a query to change the last name of one of those guys from Beaver to Rutherford. The primary key on the person table is the column BusinessEntityID. I checked and Beaver's ID is 429. For this we are going to use the Set-SqlData function.

Here are the results for a 'Get-Help Set-SqlData':

If you haven't closed PowerShell ISE then you don't need to re-import the sqlmaint module and you don't have to re-declare the sqlserver and dbname variables. Just open a new script window and use the following.

$qry = "update person.Person
set lastname = 'Rutherford'
where businessentityid = '429'"

Set-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Notice when you ran it, you didn't get any thing back in the window. That's because Set-SqlData does not return a result set. Check out the synopsis for each of the commands in the Get-Help results to see the difference.

We can verify our change with another select statement. Open a new script window and type in the following.

$qry = "select title, firstname, middlename, lastname
from person.Person
where businessentityid = '429'"

Get-SqlData -sqlserver $sqlserver -dbname $dbname -qry $qry

Which returns:

Sum Up

Now you are probably saying to yourself that you could have done all this in SSMS. While that is true, next week I'll show you how to use this new knowledge in conjunction with the last post 'Get Server and DB Information' to gather information about your servers and databases and store it in a database.
I will be giving a presentation on this subject here in Austin at our local PASS group CACTUSS next week. Stop by and check it out.

Hasta Luego,

Friday, October 5, 2012

PowerShell and SQL - Get Server and DB information

This week I am going to go over using the concepts we learned in the last two lessons to actually get some useful information from your servers. If you haven't read the other posts you can access them from the PowerShell link under labels on right side of the site.

The very first thing I did at work when I was handed a list of 75 SQL servers that I was responsible for managing was to document EVERYTHING. I made spreadsheets of server names, instance names, version info, OS info, Agent job schedules. The process was very manual  and I ended up connecting to each server in SSMS. Then I loaded all the data that I had collected into a SQL database so I could run reports. I quickly realized that it would be almost impossible to keep all the data updated. How would I know if a developer added or deleted a database or an agent job or something else. I now have a series of PowerShell scripts that are scheduled in Task Scheduler to run every day to gather this info for me and update my database. I won't get into the structure of my database now or how it is updated but I will show you some snippets of the scripts I use that actually gather the data.

Note: I am going to be using the SQLPSX modules, so if you don't have them installed please see the last post.

Getting SQL Server Information

The first thing we want to get is information about the SQL Server:  version number, SQL edition, and some server level settings. To make our lives easier, we are going to use one of the SQLPSX modules. Like I showed in the last post, we need to import the module before we can use its functions.

Import-Module SQLMaint

Next, lets use the SQLPSX function Get-SqlServer to get the information we want. If you are logged in with a Windows account that has permissions to the sql server you don't need any parameters other than the name of the SQL Server (If you don't have permissions you can use the username and password parameters to specify the credentials. Check out get-help for more info).

Get-SQLServer -sqlserver 'Nightwing'

Note: if the instance you are trying to connect to is not a default instance, you need to include the instance name in the sqlserver parameter. For example, I have another instance on the computer Nightwing named Bludhaven

Get-SQLServer -sqlserver 'Nightwing\Bludhaven'

Like most of the other cmdlets and functions, this is going to return more information than you need so lets use the pipe operator to just pull out some important details. If you don't remember the pipe operator, you can read about it in my first post on this subject

get-sqlserver -sqlserver 'Nightwing' |Select-Object Version, Edition, Platform, LoginMode, ServiceAccount

Here is the result:

Using a basic code structure you could wrap a loop around that command to iterate through a list of servers and get the same information from each of them. You could take the output of that information and store it in an Excel sheet or a database. Here is a basic script to iterate through a list of servers stored in a variable.

$mysqlserver = ('Nightwing','Nightwing\bludhaven')
$mysqlserver | foreach {
    get-SQLServer -sqlserver $_ | select-object Version, edition |Format-Table

Here is the result:

I feel I need to explain the script for those of you who do not have much programming experience. The first line
$mysqlserver = ('Nightwing','Nightwing\bludhaven')
declares a variable named $mysqlserver and assigns it an array with two strings. The second line
$mysqlserver | foreach {
takes that array and pipes it to the foreach command, creating a loop that executes once for each item in the array. The next line
prints out the literal string value from the array that was passed to it (in the first loop it is the string 'Nightwing'. In Powershell $_ is the reserved variable for the current value in the pipe. For more information on variables you can read this article at . The fourth line
get-SQLServer -sqlserver $_ | select-object Version, edition |Format-Table
gets two items of SQL Server Information for the server name that is stored in the $_ variable and then outputs it in table format. The last line
closes the foreach loop causing it to move on to the next string stored in the array.

Getting OS Information

It would also be good to get some information about the Windows server that SQL Server resides on. For this we aren't going to use the SQLPSX modules but instead use the WMI capabilities of PowerShell. Getting information using WMI  is built in to PowerShell, so we don't need to import any modules, instead we use the Get-WMIObject cmdlet. To use this cmdlet we are going to need two parameters: the name of the WMI class and the name of the server (Again this assumes you have the proper permissions with your current Windows login to get this information).

get-wmiobject -class 'win32_operatingsystem' -ComputerName 'nightwing'

This returns:

Wait a minute!! That isn't much info at all. Here is a trick with some PowerShell cmdlets, they don't show you all the possible info by default. If you want to see all the data returned you can pipe the output to Select-Object *

get-wmiobject -class 'win32_operatingsystem' -ComputerName 'nightwing' |select-object *

Run this on your own system to see everything you can get back. Just like we did with the get-sqlserver cmdlet we can wrap a loop around this to iterate through a list of servers.

Getting Database Information

Now that we have gathered some information about the SQL Server Instance Level and the OS that it resides on, let's take a look at how to get information on the databases that reside on the server. For this we are going to be using SQLPSX again so we will need to import the module.

Import-Module SQLMaint

Next we are going to use the Get-SQLDatabase function from the SQLPSX modules. In this example I am just going to be pulling the name of the database, you will see why later.

Get-SqlDatabase -sqlserver 'nightwing' |select-object name


Do you notice a problem here? Those are just the user databases, we are not getting any info on the system databases. Let's try a different cmdlet: Get-SqlSysDatabases.

Get-SqlSysDatabases -sqlserver 'nightwing'


Now we see them all, but we aren't getting much information by default. I know what you are thinking, pipe the result into select-object * to get the rest of the data. Give it a shot, I'll wait here.

Get-SqlSysDatabases -sqlserver 'nightwing' | select-object *

Did you try it? Not much extra info is there.
Now try the same thing with the Get-SqlDatabase command. Again, I'll wait.

Get-SqlDatabase -sqlserver 'nightwing' |select-object *

Did you see all the info you got that time! Pretty cool. Now if only there was a way to combine the two commands so you could get all the information on all the databases. We can do it by using a pipe. The trick is that first we have to store the data in an array and use a loop for each database.

$mydatabases = Get-SqlsysDatabases -sqlserver nightwing
$mydatabases | foreach {
    $mydb = $
    Get-SqlDatabase -sqlserver nightwing -dbname $mydb

Like the last loop we did we are creating an array, only this time instead of an array of strings we are creating an array of objects. Each element of the array contains the entire list of properties for a database and there are as many elements as there are databases. This might be confusing to those not familiar with Object Oriented Programming but just remember that you don't have to understand it completely to be able to use it. After we create our $mydatabases array variable
$mydatabases = Get-SqlsysDatabases -sqlserver nightwing
we pipe it into a for each loop that will execute once for each database.
$mydatabases | foreach {
Next we create a local variable and assign it the name property of our object (think of it like when we did the Select-Object name before).
$mydb = $
Now that we have the database name stored in a variable, we can pass it as an optional parameter to the Get-SqlDatabase command to get information just about that database.
Get-SqlDatabase -sqlserver nightwing -dbname $mydb
Then we close the loop so it can start over for the next database in our list.
Lets format the script so it just pulls out a few choice tidbits of information on each database

$mydatabases = Get-SqlsysDatabases -sqlserver nightwing
$mydatabases | foreach {
    $mydb = $
    Get-SqlDatabase -sqlserver nightwing -dbname $mydb |
    select-object Name,Createdate,lastbackupdate,size,dataspaceusage

Go ahead and take a look at all the properties that this function returns to see the wealth of information that PowerShell puts right at your fingertips.

Links and References

Download SQLPSX from Codeplex
Variables Arrays and Hashes from
Technet article on PowerShell and WMI

Sum Up

Once again I ended up writing way more that I thought I would. Hopefully you stuck with it and got all the way down here. I probably could have split this into three posts and gone a bit more in depth with each section but I am eager to move forward to show you how to use all of this information to help make your job as a DBA easier. Next week I am going to get into using PowerShell to do the basics of t-sql: Select, Insert and Update. See you then!

Hasta Luego,

Wednesday, September 26, 2012

PowerShell and SQL - SQLPSX

2019 update: SQLPSX was great seven years ago but nobody is actively working on it. If you are interested in using PowerShell with SQL, check out this is an active project on GitHub that just released version 1.0

This is part two in my PowerShell and SQL series. If you missed part one, you can use the PowerShell link under labels on the right to get to it. Last week I went over PowerShell Basics, what it is and how to use it. I included some examples to show you how you can use PowerShell to get SQL related event logs and store them in a file. This week I am going to introduce the SQL Server PowerShell Extensions.

You can download the modules for SQLPSX from the codeplex website:

SQLPSX is a library of modules that can be used to directly talk to SQL server. You can get server and database information, read from and write to a SQL database and even perform most tasks a DBA performs on a regular basis. Now you can do this without using SQLPSX by creating your own connections to a SQL server but I am going to show you why you shouldn't bother reinventing the wheel.


Once you download the file and run it, the SQLPSX modules will be added to your modules sub-directory of your Windows PowerShell folder (usually located under My Documents). In order to use the modules you have to import them into PowerShell.

NOTE: loaded modules are session based, this means that when you close the PowerShell window and get back in, the modules will not be loaded anymore. You can have them auto-load when PowerShell opens by adding an entry to your PowerShell profile. If you are interested here is an article at How-to-Geek:

You load the module into powershell using the import-module cmdlet, but wait, do you know what the names of the modules are? After you have installed SQLPSX open up powershell and type this command:

Get-Module -ListAvailable

You will get a result that looks like this:

There are thirteen modules in the SQLPSX installation, you can see the listing on the SQLPSX website under documentation.

Now lets import one of the modules into our PowerShell Session. To do this we use the Import-Module cmdlet followed by the name of the module we want to import.

Import-Module SQLMaint

Next let's see what functions are available in this module. We can do this using the Get-Command cmdlet and passing it the -Module parameter with the name of the module we just imported.

Get-Command -Module SQLMaint

You should now get a result that looks like this:

The best thing to do next is to pick a function that you like and see what it does and what parameters it takes. Let's look at the Get-SqlData function using our buddy the Get-Help cmdlet

Get-Help Get-SqlData

This returns the name of the function, a synopsis, syntax and a description. Notice the last section, Remarks:

Look at that! You can get a more detailed description and even examples. This is pretty cool, right!

Play around by importing the other modules and checking out the functions. I like to use the SQLMaint and  Agent Modules but check out the other ones to see what is available. If you find something cool, post it in the comments!

Don't Reinvent The Wheel

Like I said before you can connect to SQL just fine without using SQLPSX and do everything you can with it but you will end up writing more code. Let's take a look at two examples. The first example will connect to my server and return a result set from a query:

Now lets do the same thing using SQLPSX:

Fourteen lines of code vs five lines of code. Pretty easy decision in my head. I have enough to do during the day without having to remember all that code just to run a query. If you used method one you would probably end up writing your own module and importing it in all your scripts but why bother when the resource is already available. I want to point out that SQLSPX isn't doing anything different, the functions that they give you use the same code that you can hand write, they have just already done all the leg work for you. If you want to see what is going on "behind the scenes" you can open up the SQLServer.psm1 file in your PowerShell modules directory in the SQLServer folder.

Links and References

Download SQLPSX from Codeplex
Learn More About SMO and PowerShell

Sum Up

That's it for this week. This lesson is important because I am going to use the SQLPSX modules in all of my later examples. Download it and play around and check in next week and we will get started on putting this all together to help simplify your life as a DBA

Hasta Luego,

Friday, September 21, 2012

PowerShell and SQL - Intro

Over the last couple of months I have been learning PowerShell. I picked up the language pretty quickly because of my past experience with Linux and Perl but it isn't a very complicated language and anyone should be able to learn it. In addition, starting in 2009 Microsoft has added PowerShell to it's CEC (common engineering criteria). The CEC is a set of engineering requirements that all Microsoft Server products are required to comply with. This means that all future versions of Microsoft Server Products will use PowerShell as the standard for automation. Any admin task you can do in the GUI (or in T-SQL) will be able to be done (and automated) using PowerShell. This makes the language very powerful for a DBA and I can foresee a day when it becomes a required skill on a DBA job description.

In this first post in my PowerShell series I am going to go over some PowerShell basics and in the next posts I will get into how to use it with SQL Server.

What is PowerShell?

PowerShell is a task automation framework. This means it is a way to perform tasks on a server (add a login, add a database, etc) and more importantly it is a way to automate them. PowerShell is both a command line and a scripting language. You can use the PowerShell CLI to perform tasks one at a time or you can write a script that will perform multiple tasks sequentially. You can even use Task Scheduler or and Agent job to kick off a PowerShell Script at a set time. Pretty Cool, Right?

Microsoft has learned from it's past mistakes (vbscript) so PowerShell is also secure. It uses a concept called Execution Policy to determine what scripts can run. At the strictest setting PowerShell will not run any scripts, only commands entered at the CLI. The next level down requires that all scripts be digitally signed either using a third-party certificate or a self-signed certificate. Here are the four Execution Policies

  • Restricted - No scripts can run. Only commands from the CLI
  • All Signed - All scripts must be digitally signed
  • RemoteSigned - Local unsigned scripts can run but any remote scripts must be digitally signed
  • Unrestricted - All scripts can be run. No digital signatures required

Working with PowerShell

PowerShell is installed by default on Windows 7 and later but it isn't in your Start Menu. To find it, use the 'Search programs and files' bar in the Start Menu, just search for powershell.exe for the CLI and powershell_ise.exe for the integrated scripting environment.


The basic commands used when working in PowerShell are called cmdlets. Cmdlets take the form [verb]-[noun]. Examples:
  • Get-Help - can be used with any other cmdlet to get information (like -help in cmd)
  • Get-Command - Returns a list of all cmdlets, functions and aliases available
  • Out-File - can be used to reroute output from the screen to a file
  • Get-SQLData - takes a server, db, and a query as parameters and returns the result
Most cmdlets take one or more parameters. You can use Get-Help to show you what parameters are available and which ones are required. Here is how I would check the Application Event Logs on my local machine and how I would check it on a remote machine (make sure you have the necessary permissions on the remote machine)
  • Get-Eventlog -logname 'appliation'
  • Get-Eventlog -logname 'application' -computername 'MyServer'


You can string cmdlets together using the | operator (it's called a pipe) to take the output from one cmdlet and feed it to the next. This allows you to string cmdlets together to do what you want to do. Here we are taking the Get-EventLog cmdlet and writing the output to a file
  • Get-EventLog -logname 'application' | Out-File 'C:\temp\applog.txt'


After working with cmdlets for a while you will find the need to combine a series of cmdlets together. The is where a script comes in. PowerShell scripts use the .ps1 extension. You can create it in your favorite text editor or use the built in Microsoft Scripting Environment, PowerShell ISE. The ISE in PowerShell v3 includes intellisense so I would definitely suggest upgrading if you haven't already. You can get it here:


Once you have written a few scripts you will notice that some scripts have the same blocks of code. Instead of rewriting it every time you can create a function. A function is like a mini-CmdLet, you can create it to take input parameters and specify default ones if they aren't given. Let's take our previous example and turn it into a function that takes the computer name as a parameter.
function get-MyEventlog($computername) {
    Get-EventLog -logname 'application' -computername "$computername" | Out-File 'C:\temp\applog.txt'


To create a function you first define it with the word 'function' then you give it a name and then specify input parameters if needed. Notice the entire function is enclosed in curly brackets, everything between the curly brackets is part of the function. Then once the function is written we can call it to get the Event Logs written to file. Now imagine that we fancied up the function so it just pulled Event Logs where the source was MSSQLSERVER.

function get-MyEventlog($computername) {
    Get-EventLog -logname 'application' -computername "$computername" | 
             where-object { $_.source = 'MSSQLSERVER' } | 
             Out-File 'C:\temp\applog.txt'


Now you can start to see some of the power of PowerShell. You could have it iterate through a series of computer names, get the SQL Event Logs and write them to a file. You could schedule the script to run nightly and dump the file to your desktop so that when you log in in the morning you can check the logs for all your servers from the previous day. You could even have the script write to a database and use SSRS to deliver a daily error report to your entire DBA team. 


The next level after functions is modules. You can create a module of various useful functions and make it available to multiple scripts. Module creation is a bit outside the scope of this PowerShell intro but if you are interested here is a link to a MSDN article: 

Some Common Gotchas

Here is a quick list of some common gotchas I found when I started working with PowerShell
  • Assignment vs Comparison Operators - In PowerShell '=' is an assignment operator, you do not use it to compare to values. '$var = 1' sets the value of the variable $var to 1, it does not check to see if $var is equal to one. To compare values you use -eq for equal so: '$var -eq 1' will check to see if the value contained in the variable $var is equal to one. Here is a link to an article that discusses the comparison operators and goes into some conditional logic:
  • Double vs Single Quotes - In PowerShell variable interpolation (or variable substitution) only happens if the variable is enclosed in double quotes. If the variable is enclosed in single quotes Powershell treats it as a string literal. For example look at this code:
    $myvar = "RutherfordTX"

    The first call (the one in single quotes) will return the string $myvar while the second one (double quotes) will return the value RutherfordTX

Links and References

PowerShell v3 download
Module Creation
Comparison Operators and Conditional Logic

Sum Up

Wow, that was a lot more than I thought I was going to write. I have quite a few ideas for the next couple of posts so expect me to get pretty regular for a while. Also I will be presenting at our local SQL User group, CACTUSS, here in Austin on October 15th and 16th on this topic so if you are in Austin drop on by.

Hasta Luego,

Wednesday, May 16, 2012

Beginning SQL #5 - Security

Welcome back to me! It's been 7 months since my last blog post. I fell into a well last October and have not had access to an internet connection until just this week. It was a difficult ordeal for me. I was fed through a garden hose they managed to snake down to me so I have been living off of oatmeal and water, I did get them to put a steak in a blender and feed it down to me but it clogged up the hose.

Anyway, last week was PASS SQL Rally in Dallas. I was lucky enough to get to go on the company dime and it was worth every penny. There were a bunch of great speakers and I learned a lot of good info that I can use at work. The only problem I had was deciding which speakers to go see. There were many sessions I wanted to see that were at the same time. I got some advice from a fellow CACTUSS member who suggested that if I had to make a choice to pick the speaker who wasn't local because chances are if they are local you can see them again at a local user group meeting or local SQL Saturday.

This week I am going to return to my Beginning SQL series and talk about SQL Server security. This is a topic where I am going to have to learn to practice what I preach. I have been known to take the quick and easy route when setting up security for applications that hit my SQL servers. The policy I should be following is the policy of most restrictive permissions. Don't give any user or app any more permissions that it needs.

Principals and Securables
The people or applications that are given access to your SQL Database are known as principals.  There are three different types of principals

  • Windows - This can be a windows user or group that is given access to to the server. The user or group can come from either Active Directory, in a domain environment, or the users and groups that are defined locally on the server.
  • SQL Server - These are users (called logins) or groups (called Roles) that are created locally within the SQL instance and are authenticated using SQL Server Security. These live at the SQL Instance Level.
  • Database - These are users or roles that are created within each Database. 
Windows and SQL Server principals (logins) only give you access to the server. You need to associate the login with a database user or role or a Server role. When you create a Login on the SQL Server you can choose either Windows authentication or SQL Server Authentication. Windows Authentication allows you to search either the local or domain directory to choose an already exisiting user/group. SQL Server authentication allows you to create a username password combination that only exists for that SQL Instance.
Below is the New Login dialog box:

Database principals are the users that you can use to assign actual permissions to databases and database objects. While logins live at the server level database principals live at the Database level. You can have different database users and roles for each of your databases on the server. This allows the granularity of access control that I mentioned earlier. There are three types of Principals at the Database level.

  • Database Users - Database users are the security level at which actual connections to the database and it's objects are made. You can create a Database user,  link it to a Server level login then assign it to Database Roles to give it the permissions needed to access the database.
  • Database Roles - There are a number of fixed database roles that exist in SQL but you can also create your own user-defined roles. Database roles (like Windows Security Groups) allow you to assign permissions to a group of people (database users) rather than having to give permissions to the database objects to each individual user. This allows for ease of administration, especially in a larger environment.
  • Application Roles - This allows you to create a security context for an application to connect to the SQL Server Database
Securables are the objects that you want to control access to. Objects in SQL server can be secured at three different levels.
  • Server - These are the objects that live at the Server level (like logins) that are granted access to at a Server level. You can only give access to server securables to server principals (not database users).
  • Database - These are the objects that live within a Database but are not at the next level (schema). These include: Database users, schemas, Database roles, etc.
  • Schema - These are the objects that live within a schema in a database. A schema is an added layer of security that allows you to group database objects into different security groups. These objects include tables, view and stored procedures.

Well, there you go the 50 foot view of principals and securables. Researching this piece really helped me understand the different levels of security in SQL server. If you want more detail on the subject I would suggest you pick up a copy of "Accelerated SQL Server 2008" which is available from Amazon in print or for the Kindle. Next time I will dive into the SQL server Agent. 

Hasta Luego,