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.
PRESENTATION
DEMO FILES
Regular blog posts will resume next week.
Hasta Luego,
RutherfordTX
Monday, October 29, 2012
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
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.
Import-Module sqlmaint
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.
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:
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,
RutherfordTX
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.
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
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,
RutherfordTX
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.
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.
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 PowerShellPro.com . 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.
get-wmiobject -class 'win32_operatingsystem' -ComputerName 'nightwing'
This returns:
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
Result:
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'
Result:
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 = $_.name
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 = $_.name
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 = $_.name
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.
Variables Arrays and Hashes from PowerShellPro.com
Technet article on PowerShell and WMI
Hasta Luego,
RutherfordTX
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 PowerShellPro.com . 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
Result:
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'
Result:
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 = $_.name
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 = $_.name
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 = $_.name
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 CodeplexVariables Arrays and Hashes from PowerShellPro.com
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,
RutherfordTX
Subscribe to:
Posts (Atom)