Hi there.
I am Michael Otey.
I am a SolarWinds contributor and President of TECA Inc.
And in this presentation, we're going to talk about
managing SQL Server with PowerShell.
This is part one of a two part presentation.
What we are going to cover in part 1;
First we are going to look at importing
the SQL Server PowerShell Module.
This module is called sqlps.
We will kind of see what it does.
It allows you to navigate through a SQL server
using power shell commands.
Next, we'll look at Running PowerShell from
SQL Server Management Studio.
It has options where you can launch a powershell
directly from management studio.
Then we'll look at using some navigation using
the SQL Server PowerShell Provider.
You can basically use this provider to navigate through
SQL Server exactly like you might a file system.
So, it's quite interesting and it even allows you to
to drill down into the file and table level,
and you'll see that.
Next, we'll see some examples of using the
SQL Server PowerShell cmdlets.
There's a number of different cmdlets available
to help you manage SQL Server.
So first, before you begin managing SQL Server with
the PowerShell console, you need to import the sqlps module.
This command shows you how to do this.
You basically use the import module command.
This is a standard PowerShell command.
Give it the name of the module and that sqlps
and then you use the disabled name checking parameter
to go along with that.
That will prevent any errors from being displayed
as you use this module.
These errors basically come about because
of minor naming in
compatibilities
between the SQL Server and PowerShell.
So, using that will just prevent those errors
from being displayed.
So, it's basically a nicety.
There is no real incompatibility there.
If you try to use the different SQL Server commands,
like here we're using the directory command
to navigate into SQL Server with PowerShell,
and we haven't loaded that PowerShell module,
you'll get an error a lot like this that says,
"object not found."
But after you go ahead and import the module using
the import module command, then you execute
that same command.
In this case, doing a directory or navigating
to the default SQL Server instance.
You'll see that everything works fine.
You can also launch this PowerShell Command Shell from
SQL Server Management System [SSMS] itself.
To do that, just open up object explore,
right click your SQL Server instance,
and from the context menu, go down and select
Start PowerShell command.
This will launch a PowerShell window.
If you launch this window from a SQL Server,
you do not have to run the import module command,
because it's done implicitly for you.
Once you've done this, you can go ahead
and navigate that SQL server instant.
For example, in this screen , we've launched the SQL Server
PowerShell module then we can instantly run the DIR command,
which is a PowerShell command.
And we can see the different directories that are out there.
We can use the CD command, like change directory
to the databases.
You can see here, we are using PowerShell
and that PowerShell provider for SQL Server to navigate
through the different directories that are out there
for SQL Server.
When you go to navigate using the PowerShell Provider,
there are several things you can navigate.
The one that you are going to work with most would be
your database object which you can find
in the SQLSERVER:/SQL directory.
But, there are a lot of other objects
out there that you can manage as well.
There's Policy based management objects.
There's Registered server objects you can navigate through.
Utility objects.
You can navigate through Data-tier application objects,
Data collector objects, and even integration services
and Analysis Services.
So the PowerShell lets you go through
and navigate through a lot of different objects out there
in SQL Server, and I'll show you an example
of using that in just a second.
PowerShell also provides a number of cmdlets
that you can use to help manage them.
You have cmdlets that can create Availability Groups,
as well as add notes to them.
You have cmdlets that can preform backup and restores.
You can also execute commands and queries
using these cmdlets.
In this example, you can see I am using
the Invoke-Sql command cmdlet with a query parameter
passing a simple query to a SQL Server.
We will give you a demonstration of all that
in just a second.
So let's have a quick look at getting started
using SQL Server and PowerShell.
First, let's open up a PowerShell prompt.
Now, we haven't loaded the sqlps module in here yet,
so let's try a simple PowerShell command.
This is one of the navigation commands,
and we are going to do a directory of SQL Server
and we will look in the default instance.
So, as we hit that command, since the module isn't loaded,
we've got an error.
So, in order to load that sqlps module,
we are going to use the import module command,
like you see here.
Give it the module name, which is sqlps
and disable name checking.
And at this point, it's going to go ahead
and load in that sqlps module for us.
This takes just a second.
And there you have it.
The module is loaded in.
Now, let's rerun that command that we ran earlier.
And at this point, you can see we're now able to navigate
the SQL server instance using PowerShell.
You can also start PowerShell from
the SQL Server Management Studio as well.
So, let's pop over here and get a look at doing that.
In this case, we can go in, we can say, "Start PowerShell."
And, when we do that, you'll notice the prompt
is just a little bit different.
It's black instead of the normal default PowerShell blue.
But, once we do this, this automatically loads in
the sqlps module for us as you can see here.
And, we can preform our navigation commands.
And, you can see we can navigate much like
going into the file system.
So, if we wanted to go into the databases
of this SQL Server instance, we could type in
CD Databases dir and we can see the databases
that are there.
CD, let's go into
adventureworks 2012.
And, there we've navigated to there.
Let's take a look in there as we continue
to dig in a little deeper.
And, let's go into the tables.
And, do a directory of those.
And, you can see that we can basically drill down
into whatever we want to look at here.
Now, let's have a quick look at running a couple
of other commands here.
Some of the SQL Server cmdlets that we have available to us.
So, let's pop over here.
We are going to go ahead and run a couple of these cmdlets.
First, we are going to start off with
the Invoke SQL command.
So, you can see that we've run a simple invoke SQL command
running a simple query, selecting our version number.
So, that was simple enough.
We can run other commands too using invoke SQL commands.
For instance, we can invoke functions
and return the results like here.
The results of select date.
We can also run queries.
Select the data from different tables.
Let's go ahead and have a look at doing that.
Here we are using Invoke SQL command.
Passing it in the database we are going
to be using, adventureworks.
And then using the query parameter to give it
a query where we are selecting a couple of columns
from the human resources employees table.
And there you'll see.
That's the basics of using PowerShell and SQL Server.
In part 2, we'll dig into this in a little more detail.
And now, you see how to get started using PowerShell
to manage SQL Server.
In part 2, wer're going to dive into this deeper
and we'll show you some actual examples
of running various useful commands to manage SQL Server.
Thanks for watching!