Search results matching tags 'PowerShell' and 'Scripts'

PowerShell for the DBA: Search the Windows Event Logs for Errors

BuckWoody — Wed, 04 Aug 2010 13:43:30 GMT

This is a very simple script - but it's one I run each morning. It searches the Windows System Event Log for an error condition. You can replace "System" here with "Application" or "Security", or any of the other logs that are created on your Windows Server. This is run at the server, since I have each server check itself and make a file of the results - then I swing by and pick up the files each morning with another script that builds a web page.

# Parse for errors
Get-eventlog System | Where-Object { $_.entryType -eq "error" }

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

PowerShell and Extended Properties

BuckWoody — Tue, 20 Jul 2010 12:43:33 GMT

I use Extended Properties on databases and their objects all the time. They are a great way to include information about the object – I use them for versioning the database, detailing what a column is used for and so on. They can be a little tricky to set, but it’s really not bad once you learn how. Ken Simmons, a SQL Server MVP has a fantastic article here that explains more: http://cybersql.blogspot.com/2010/07/extended-property-awesomeness.html

Although it’s not as simple as I would like to set the properties using PowerShell, it’s really trivial to read them. I do this to make system documentation in an HTML page. Open a SQL Server 2008 Powershell provider (sqlps.exe) and CD to any “directory” of a database or tables, views, whatever. Then you can just type this:

DIR | select-object –property name, Extendedproperties

As always, do this on a test system.

Script Disclaimer, for people who need to be told this sort of thing:

Create and Track Your Own License Keys with PowerShell

BuckWoody — Thu, 15 Apr 2010 13:10:45 GMT

SQL Server used to have cool little tool that would let you track your licenses. Microsoft didn’t use it to limit your system or anything, it was just a place on the server where you could put that this system used this license key. I miss those days – we don’t track that any more, and I want to make sure I’m up to date on my licensing, so I made my own.

Now, there are a LOT of ways you could do this. You could add an extended property in SQL Server, add a table to a tracking database, use a text file, track it somewhere else, whatever. This is just the route I chose; if you want to use some other method, feel free. Just sharing here.

Warning Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall the operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

And this is REALLY important. I include a disclaimer at the end of my scripts, but in this case you’re modifying your registry, and that could be EXTREMELY dangerous – only do this on a test server – and I’m just showing you how I did mine. It isn’t an endorsement or anything like that, and this is a “Buck Woody” thing, NOT a Microsoft thing. See this link first, and then you can read on.

OK, here’s my script:

# Track your own licenses

# Write a New Key to be the License Location

mkdir HKCU:\SOFTWARE\Buck

# Write the variables - one sets the type, the other sets the number, and the last one holds the key

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseType" -value "Processor"

# Notice the Dword value here - this one is a number so it needs that. Keep this on one line!

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseNumber" -propertytype DWord -value 4

New-ItemProperty HKCU:\SOFTWARE\Buck -name "SQLServerLicenseKey" -value "ABCD1234"

# Read them all

$LicenseKey = Get-Item HKCU:\Software\Buck

$Licenses = Get-ItemProperty $LicenseKey.PSPath

foreach ($License in $LicenseKey.Property) { $License + "=" + $Licenses.$License }

Script Disclaimer, for people who need to be told this sort of thing:

List SQL Server Instances using the Registry

BuckWoody — Wed, 31 Mar 2010 13:41:24 GMT

I read this interesting article on using PowerShell and the registry, and thought I would modify his information a bit to list the SQL Server Instances on a box. The interesting thing about listing instances this was is that you can touch remote machines, find the instances when they are off and so on. Anyway, here’s the scriptlet I used to find the Instances on my system:

$MachineName = '.'

$reg = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey('LocalMachine', $MachineName)

$regKey= $reg.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL" )

$regkey.GetValueNames()

You can read more of his article to find out the reason for the remote registry call and so forth – there are also security implications here for being able to read the registry.

Script Disclaimer, for people who need to be told this sort of thing:

Open the SQL Server Error Log with PowerShell

BuckWoody — Mon, 29 Mar 2010 13:35:52 GMT

Using the Server Management Objects (SMO) library, you don’t even need to have the SQL Server 2008 PowerShell Provider to read the SQL Server Error Logs – in fact, you can use regular old everyday PowerShell. Keep in mind you will need the SMO libraries – which can be installed separately or by installing the Client Tools from the SQL Server install media. You could search for errors, store a result as a variable, or act on the returned values in some other way.

Replace the Machine Name with your server and Instance Name with your instance, but leave the quotes, to make this work on your system:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$machineName = "UNIVAC"
$instanceName = "Production"
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$machineName\$instanceName"
$sqlServer.ReadErrorLog()

Want to search for something specific, like the word “Error”? Replace the last line with this:

$sqlServer.ReadErrorLog() | where {$_.Text -like "Error*"}

Script Disclaimer, for people who need to be told this sort of thing:

PowerShell: Read Excel to Create Inserts

BuckWoody — Thu, 18 Mar 2010 12:09:36 GMT

I’m writing a series of articles on how to migrate “departmental” data into SQL Server. I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data.

One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and output statements that would insert the data using a stored procedure. Of course, you could re-write this as INSERT statements, out to a text file for bcp, or even use a database connection in the script to move the data directly from Excel into SQL Server.

This snippet won’t run on your system, of course – it assumes a Microsoft Office Excel 2007 spreadsheet located at c:\temp called VendorList.xlsx. It looks for a tab in that spreadsheet called Vendors. The statement that does the writing just uses one column: Vendor Code. Here’s the breakdown of what I’m doing:

In the first block, I connect to Microsoft Office Excel. That connection string is specific to Excel 2007, so if you need a different version you’ll need to look that up.

In the second block I set up a selection from the entire spreadsheet based on that tab. Note that if you’re only after certain data you shouldn’t get the whole spreadsheet – that’s just good practice.

In the next block I create the text I want, inserting the Vendor Code field as I go.

Finally I close the connection.

Enjoy!

$ExcelConnection= New-Object -com "ADODB.Connection"
$ExcelFile="c:\temp\VendorList.xlsx"
$ExcelConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;`
Data Source=$ExcelFile;Extended Properties=Excel 12.0;")

$strQuery="Select * from [Vendors$]"
$ExcelRecordSet=$ExcelConnection.Execute($strQuery)

do {
Write-Host "EXEC sp_InsertVendors '" $ExcelRecordSet.Fields.Item("Vendor Code").Value "'"
$ExcelRecordSet.MoveNext()}
Until ($ExcelRecordSet.EOF)

$ExcelConnection.Close()

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

PowerShell Version Two – Get Continuous Perf Counters

BuckWoody — Thu, 25 Feb 2010 14:32:52 GMT

In version 2.0 of PowerShell, you can now use a direct command-let (get-Counter) to get at the Performance Monitor counters. For instance, to show the current value of the Processor Percent Time, use this command:

Get-Counter '\Processor(*)\% Processor Time'

The interesting part of get-Counter is that you can add a parameter at the end to keep sampling:

Get-Counter '\Processor(*)\% Processor Time' - Continuous

Which is very useful if you’re doing performance troubleshooting like I am this morning. And yes, you can use this to get at SQL Server counters as well – to see the entire set of counters, use this command:

Get-Counter –listSet * | Select-Object -ExpandProperty Paths

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

Add-Content and Out-File are not for performance

Linchi Shea — Mon, 04 Jan 2010 21:31:00 GMT

When you write Powershell scripts and need to write a text file, you have a number of ways to accomplish that. The often suggested approach is to use cmdlets Add-Content or Out-File. Well, this is not news.

But some may not notice that these cmdlets are not there for performance. Sure, they are convenient. But if you need to write to a file many times, you may want to consider something else. To demonstrate the point, try the following script, which tests the performance of writing to a text file 10,000 times:

# Measure Add-Content

$t = get-date;

for ($a=1; $a -le 10000; $a++)

{

add-content -path "c:\junk\test1.txt" -value "Hello";

}

$s = $(get-date).subtract($t).TotalMilliseconds;

write-output ("Add-Content: " + $s);

# Measure Out-File

$t = get-date;

for ($a=1; $a -le 10000; $a++)

{

"Hello" | out-file -filepath "c:\junk\test2.txt" -append

}

$s = $(get-date).subtract($t).TotalMilliseconds;

write-output ("Out-File: " + $s);

# Measure StreamWriter

$t = get-date;

$sw = New-Object System.IO.StreamWriter "c:\junk\test3.txt";

for ($a=1; $a -le 10000; $a++)

{

$sw.WriteLine("Hello")

}

$sw.Close();

$s = $(get-date).subtract($t).TotalMilliseconds;

write-output ("StreamWriter: " + $s);

The output from this script is similar to what I obtained from my laptop and shown below:

Add-Content: 34015.8427

Out-File: 37781.4918

StreamWriter: 781.255

From running this scripts multiple times on my laptop, using StreamWriter was consistently about 4 ~ 5 times faster than either Add-Content or Out-File. This should not really be surprising because each time you call Add-Content or Out-File, you open and close the file, and that is expensive.

Performance wise, using Add-Content or Out-File is similar to using StreamWriter in the following way (i.e. including opening and closing the file inside the loop):

# Measure StreamWriter, open/close the file for every write

$t = get-date;

for ($a=1; $a -le 10000; $a++)

{

$sw = New-Object -typename System.IO.StreamWriter("c:\junk\test4.txt", "true");

$sw.WriteLine("Hello")

$sw.Close();

}

$s = $(get-date).subtract($t).TotalMilliseconds;

write-output ("StreamWriter2:" + $s);

And if you run the above script, you should find its performance very similar to that of Add-Content or that of Out-File.

So what is the takeaway? Well, do not use Add-Content or Out-File if performance matters such as when you write a massive number of lines to a file.