Disk information via PowerShell & WMI

Posted December 12, 2008 by grayburn
Categories: Admin, Powershell

Sorry for taking so much time off in between posts. Lot’s of stuff happening. So, without further waiting, here we go – the long-promised Disk Info via PowerShell & WMI.

Note, there is a filter being applied to Get-WMIObject ["Drive Type = 3"] is a local disk drive.

##Create ServerList.txt file:
sqlcmd /Lc > F:/temp/ServerList.txt

#Populate $Server_List
$Server_List = Get-Content  "F:\Temp\ServerList.txt"

##Loop through $Server_List & print disks with (% FreeSpace) < X:
#
foreach ($row in $Server_List)
{
	$colItems = Get-WmiObject -computername $row -class Win32_LogicalDisk -filter "DriveType = 3"

	Write-Host "Checking: "$row "...."
	Write-Host

	foreach ($objItem in $colItems)
	{
		[decimal]$Size = [math]::Round( (($objItem.Size) / 1.00gb) ,2) # cast to [decimal] or [long] due to 
		[decimal]$FreeSpace = [math]::Round( (($objItem.FreeSpace) / 1.00gb) ,2) # PSH inability to cope
		[decimal]$Percent = [math]::Round( (($FreeSpace / $Size) * 100.00) ,2) # with UInt64 types coming at it.

		#if ($Percent -le 90)
			#{
				Write-Host  "	Mount Point: " $objItem.DeviceID
				Write-Host  "	Volume Name: " $objItem.VolumeName
				Write-Host  "		Total Size: " $Size "GB"
				Write-Host  "		Free Space: " $FreeSpace "GB"
				Write-Host
				Write-Host	"			Percent Free: " $Percent"%"
				Write-Host
			#}
				}
	Write-Host
	Write-Host
}

Three scripts of mine at SQLServerCentral.com

Posted June 12, 2008 by grayburn
Categories: Powershell, SMO, SQL

Since I can’t publish them myself for thirty days (I did agree to the sites rules about re-publishing)…

Delete files older than N-days via T-SQL:

Script

Powershell “unload and archive table” script:

Script

SQL Server “Quick n dirty” Restore Generator:

Script

SMO & Connection Pooling (and how to avoid it).

Posted June 12, 2008 by grayburn
Categories: Powershell, SMO, SQL

First, I’d like to apologise for not being as active the last week or so. I caught some very nasty food poisining from a local restaraunt chain. I won’t give names but thier initials start with “Chipotles”. ;)

I’d like to discuss connection pooling with SMO…

In my process’ that I’ll be demonstrating with, I am performing a serial operation on a list of servers. So each script usually has two connections, one to the ‘main’ db server that gathers the list of servers to query. And connections to the servers in the list. I’ve found, through rudimentary testing that when doing serial operations like this, it’s more efficient for the computer running the script to not use pooled connections. The connection pooling will create about 25% more network connections and leave them for the pool on the server to manage. I was able to reduce the number of open connections by controlling the connections to the servers manually.

So, let’s demo how to control our connections explicitly using the last script as our example:

[System.Reflection.Assembly]::LoadWithPartialName(”Microsoft.SqlServer.SMO”) | Out-Null
$SrvName = “YourServerName”
$DBName = “master”

$SrvObj = New-Object (”Microsoft.SqlServer.Management.SMO.Server”) $SrvName
$SrvObj.ConnectionContext.NonPooledConnection = “True” # Sulu, you have the Com.
$SrvObj.ConnectionContext.Connect() # Connect to the dbserver.
$DBObj = $SrvObj.Databases[$DBName]

$Query = “SELECT name FROM dbo.sysdatabases”

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)

$SrvObj.ConnectionContext.Disconnect()

foreach ($Row IN $Results.Tables[0].Rows)
{ Write-Host $Row.Name }

Note: These lines will actually connect to the database, so make sure that you have the ConnectionContext.NonPooledConnection set right after you create your $SrvObj object.

$DBObj = $SrvObj.Databases[$DBName]
$Query = “Select name FROM dbo.sysdatabases”

Next up is a WMI based disk query that records information from the remote servers to a permanant table in the ‘main’ dbserver. I see this asked for in the forums all the time, and it’s a perfect segue into some more complex usage of Powershell & SMO.

SMO server based connection vs. “classic” — Take two.

Posted May 28, 2008 by grayburn
Categories: Powershell, SMO, SQL

It was pointed out to me today by the lead dev at work, that I can remove two more lines from the script.

I have to confess that I am rather new to object-oriented types of programming. PowerShell is really my first venture into the OO world via .NET. DataSet/Adapters are still a bit of a stumbling block for me, but I think I’ve made it over a hurdle today. I must thank my developer at work again for his help and information.

Since in my scripts, I’m returning only one dataset for a query, I don’t need to name the table, and can go to it directly as such..


# Read table from db.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$SrvName = "YourServerName"
$DBName = "master"

$SrvObj = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $SrvName
$DBObj = $SrvObj.Databases[$DBName]

$Query = "SELECT name FROM dbo.sysdatabases"

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)

foreach ($Row IN $Results.Tables[0].Rows)
{ Write-Host $Row.Name }

Isn’t that nice? :)

SMO server based connection vs. “classic”

Posted May 27, 2008 by grayburn
Categories: Powershell, SMO, SQL

Ok, I said I was going to show an easier way than the ‘classic’ way of connecting to a database server. You’ll be amazed at how much less code there actually is (or needs) for an SMO based connection.

Fire up your trusty PowerShell IDE (mine happens to be PowerGUI made by the folks over at Quest) and paste the following into a script…


# Read table from db.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$SrvName = "YourServerName"
$DBName = "master"

$SrvObj = New-Object ("Microsoft.SqlServer.Management.SMO.Server") $SrvName
$DBObj = $SrvObj.Databases[$DBName]

$Query = "SELECT name FROM dbo.sysdatabases"

# Execute $Query and return a dataset
$Results = $DBObj.ExecuteWithResults($Query)

$Results.Tables[0].TableName = "DBList"
$DBList = $Results.Tables["DBList"]

foreach ($Row IN $DBList.Rows)
{ Write-Host $Row.Name }

So simple and much easier to work with than the ‘classic’ connection method. Next time around, I’ll show you how to retrieve a list of items, iterate over them and insert data back into a ‘master’ server/db.

Powershell – Read list & get service information.

Posted May 23, 2008 by grayburn
Categories: Admin, Powershell, SQL

Tags:

This is a PowerShell script that will use SQLCMD to generate a list of SQL Servers on your network, piping the reults into a file.  A “classic” database connection is made and the list is processed using a PSH foreach loop calling Get-WMI in order to retrieve SQL Service information via WMI. As the service information is retrieved for each server in the file (that we can connect to) it is stored in an object and inserted into a table in the database specified in the connection string. The table is fairly simple to construct, but is included at the base of the script, commented out.

This script will prompt for network credentials at startup.  This is neccessary in my enviroment as we have different accounts for higher level access.  So we end up passing credentials quite a bit.  This isn’t really as big of an issue as you might think.  By using a scheduler service we can specify accounts to runas, or take advantage of proxies on SQL Agent cmdshell jobs…

I think this example shows a decent example of using a database to store results from an object.

As I go along, I’ve got more to show, especially regarding my comment about the ‘classic’ database connection.  This is just a quick little something to show how you can iterate a list of servers, get some good information about thier services and store it permanantly in a database for central management and reporting. 


##
## ServiceInfo.ps1
##
## G. Rayburn 05/08/2007
##
## Generate list of SQL Servers on the network and
## iterate through them to determine service information.
##
## The script will insert into a table called Server_Info
## on the server specified in the connection string.
##

##Create ServerList.txt file:
sqlcmd /Lc > ServerList.txt

##Connect to DBServer:
$sql_conn = New-Object system.Data.SqlClient.SqlConnection
$sql_Conn.connectionstring = "server=;database=Admin;trusted_connection=true"
$sql_conn.open()

#Get network credentials & populate $servers
$cred=Get-Credential
$servers = Get-Content "ServerList.txt"

##Loop through $servers and insert into table.
foreach ($server in $servers)
{
write-host "Checking: " $server"...."
Write-Host

$colItems = Get-WmiObject -computername $server -cred $cred Win32_Service -filter "Name LIKE '%SQL%'"

foreach ($objItem in $colItems)
{
$SystemName = $server
$DisplayName = $objItem.DisplayName
$StartName = $objItem.StartName
$StartMode = $objItem.StartMode
$State = $objItem.State

$insert_query = "INSERT INTO dbo.Server_Info
(SystemName, DisplayName, StartName, StartMode, State)
VALUES ('$SystemName', '$DisplayName', '$StartName', '$StartMode', '$State')"

$execute_query = New-Object System.Data.SqlClient.SqlCommand
$execute_query.connection = $sql_conn
$execute_query.commandtext = $insert_query
$execute_query.executenonquery() | out-null
}
}
$sql_conn.close()
 

CREATE TABLE dbo.Server_Info
( RecID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
, SystemName sysname
, DisplayName varchar(128)
, StartName varchar(128)
, StartMode varchar(64)
, [State] varchar(32) )
GO

 

** I apologize for the formatting. I will have to see if I can figure out the CSS and how to expand the size of the post column.
 

 

 

Introductions?

Posted May 22, 2008 by grayburn
Categories: Uncategorized

I guess I should start off with some form of introduction….

My name’s Gordon Rayburn.   I’m a senior level database administrator using Microsoft SQL Server technologies.  It’s nice to be able to say technologies these days, instead of “Yeah, I’m the guy who runs the database servers”.  I do have a tendancy to introduce myself or position as a High-Tech Janitor though.  It usually gets a giggle out of other folks like myself.  I’ve been in the I.T. business for eleven years now, primarily as a DBA.  I am currently employed by a leading student lending institution working with some very cool technolgies and hardware.

When I’m not working I’m usually riding a motorcycle.  Either in the canyons of Southern California or at one of the racetracks in the area.  I have a couple of sportbikes and a supermotard that gets ridden almost daily.

Now that the formalities are out of the way…let’s get down to business.  This is mainly going to be a technical blog; as I need some place to park my code that I would like other people to be able to use/review/comment.  I intend on using this blog to get some things out there that I don’t think some people realize we (as DBA’s) are doing with technology.

First up is going to be some PowerShell stuff with a smattering of SMO that I’ve been working on the last few months.  There will also be some T-SQL stuff relating to tracing, backups, fragmentation and other administrative things that every good DBA should know or at least learn about.

MCP end of file.


Follow

Get every new post delivered to your Inbox.