Powershell for Database Developers

I recently presented for the SQLPASS AppDev Virtual Chapter on "Powershell for Database Developers".  I promised that I'd get the files used in the demos up to my blog shortly so wanted to make good on that promise. First, some of these depend on having either SQLPSX or the SQL 2008 Snap-in loaded in your Powershell environment. Some of those demos won't work unless that's set up. I think I've got some posts on that already, but if you need help with it, leave me a comment or shoot me a message.

I know that the SQL ISE portions of my demo require SQLPSX. Once again, I highly recommend that you download and install these modules for your use. You don't necessarily need them in your startup profile, but they are useful if you work with SQL Server.

I'd also mentioned Red-Gate in my presentation. Here's one link for more details on Doing Schema Compares.

You can access all of my files used for this presentation on Skydrive.

Powershell: Writing Text Files

I was recently trying to generate a bunch of data that I needed to pass through one of our in-house DLLs in order to use some custom encryption algorithms. Thanks to the wonderful Powershell community, I found several examples on using custom DLL methods. However, I ultimately wanted this data to end up in SQL Server. My first attempt used an “Invoke-SQLCmd” Cmdlet from the SQLPS snap-in. However, generating several million rows took a while to insert. I knew that I didn’t want to queue all of this data in memory, so was inserting one row at a time. I stopped this process after a while because it was too time-consuming.

My next attempt was to push these values to a text file using a variable pointing to a file and then using add-content to write each line to the file.  This took longer than doing the SQL inserts one at a time.

I remembered coming across an article by Linchi Shea titled Add-Content and Out-File are not for performance. After finding the link to that article, I found that he recommends using System.IO.StreamWriter to write out files in the most performant manner. I definitely recommend that you check out his article for his summary and examples. For my part, I can say that it took me the better part of 6 hours to generate and populate 10 million rows. I just generated 20 million into a file in < 15 minutes!

I’m including a mockup of my code below in case anyone is interested. I’m definitely not a Powershell expert so as always, be careful if you copy any of this code.  My first attempt at directly inserting into SQL Server worked, but threw an error at about 3.5 million rows. As the time was already pretty lengthy by then, I stopped the process and moved on to writing the text file, resuming where I’d left off. That ran for hours until I finally killed it and looked for a better way.  The uncommented code is what finally ended up working in a timely manner.

I started by loading my DLL. I called it’s “EncryptValue” function (against just the integer – that’s a longer story) and populated both out to a table. There are several reasons for doing this, but this gave me a good working set for a test project and definitely lays some groundwork for the future.  If anyone has any ideas on how I can speed this up, feel free to leave me some comments. If I find some, I’ll post an update.

 

[Reflection.Assembly]::LoadFile("C:\MyDLL.dll")

# First attempt – insert directly into SQL Server.
#foreach ($i IN 1..10000000) { invoke-sqlcmd –query '
#("insert dbo.MyTable VALUES(" + "$i" + ",'" + [MyDLLNamespace]::EncryptValue("$i") + "')") -serverinstance "localhost" }

#Attempt #2 – Write to file with add-content
#$file = New-Item -type File "C:\Values.txt"
#foreach ($i IN 1..10000000) { add-content $file ("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file = New-Object System.IO.StreamWriter "E:\Values.txt";

foreach ($i IN 10000001..30000000) { $file.Writeline("$i" + "," + [MyDLLNamespace]::EncryptValue("$i") )  }

$file.close();
            

Powershell Links – 2010-03-06

Here are some of the more interesting Powershell links I came across recently, along with some thoughts about them.

Powershell, String Encryption, and GPG – SQLServerCentral.com brought this one to my attention. Chad Miller discusses working with Oracle through Powershell, and the subject of encrypting the connection strings came up. There are times that we need to connect using something other than Windows authentication. Storing an encrypted connection string makes a lot of sense in those cases. I don’t normally work with Oracle, but the idea of passing and saving encrypted connection strings makes a lot of sense. I do a lot of work against SQL Servers that require SQL Logins so plan to investigate this more thoroughly in the near future.

Powershell Cheatsheet – If you don’t follow the #Powershell hashtag on Twitter, you may have missed out on this useful set of Powershell tips and tricks, including a way to set your current location to a UNC path. This is cool because it’s something you cannot do with a standard command prompt without mapping a drive. There are other tips here that make this well worth bookmarking if you are getting started with Powershell.

The T-SQL Hammer – This is taken from Chad Miller’s blog, but is a great reminder that just because your typical DBA knows T-SQL and thinks in T-SQL does not make that the best solution for everything. If you’re wondering why a DBA might want to learn Powershell, give this a read.

Powershell Help Brower – This script uses Primal Forms and Powershell to build a Treeview Help browser for Powershell. I tested this out and the only entry that seemed to give me fits was trying to get help for Get-ChildItem. That constantly threw an error for me. However, the rest was very nice – an easy way to browse the available commands and see the help for them.

As with anything else you find on the Internet, don’t just copy these scripts and run them. Check them out carefully and be sure you know what’s going to happen. (Someday I’m just gonna copy Buck Woody’s disclaimer for scripts and give attribution. :)  )

DDL Schema Change Auditing on SQL Server 2005 / 2008

I’ve run across this before so wanted to write up a solution to keeping track of schema changes we’ve used. We trust our team to make DDL changes, additions, drops in order to get their job done. We also trust them to save those scripts into source control as they are run so we can use them when we release software. However, there are times that people make changes and forget to save the script or just think that it’s a temporary change to test and forget about it. With that in mind, we decided to take advantage of SQL Server 2005’s DDL triggers.
First, we created a database called [Audit] on our development server.
Next, we created a table to store the logs.

USE Audit

GO

CREATE TABLE [dbo].[DDL_Audit](

[DDL_Audit_ID] [int] IDENTITY(1,1) NOT NULL,

[Event_Type] [varchar](100) NULL,

[Database_Name] [varchar](100) NULL,

[SchemaName] [varchar](100) NULL,

[ObjectName] [varchar](100) NULL,

[ObjectType] [varchar](100) NULL,

[EventDate] [datetime] NULL,

[SystemUser] [varchar](100) NULL,

[CurrentUser] [varchar](100) NULL,

[OriginalUser] [varchar](100) NULL,

[EventDataText] [varchar](max) NULL

) ON [PRIMARY]


GO

GRANT INSERT ON DDL_Audit TO public

GO

Note in this example, I granted INSERT permissions to public to avoid needing to give our team any other access to that database. I didn’t want them to read/write rows in that table if I could avoid it. After that, I ran a script in SSMS using Text output to step through all of our databases and generate the trigger create code.

sp_msforeachdb 'SELECT ''use ?

GO

SET ANSI_PADDING ON

GO

SET QUOTED_IDENTIFIER ON

GO



CREATE TRIGGER trg_DDL_Monitor_Change
ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

SET NOCOUNT ON

SET ANSI_PADDING ON


declare @EventType varchar(100)

declare @SchemaName varchar(100)

declare @DatabaseName varchar(100)

declare @ObjectName varchar(100)

declare @ObjectType varchar(100)

DECLARE @EventDataText VARCHAR(MAX)


SELECT 

@EventType = EVENTDATA().value(''''(/EVENT_INSTANCE/EventType)[1]'''',''''nvarchar(max)'''')  

,@DatabaseName = EVENTDATA().value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''',''''nvarchar(max)'''')  

,@SchemaName = EVENTDATA().value(''''(/EVENT_INSTANCE/SchemaName)[1]'''',''''nvarchar(max)'''')  

,@ObjectName = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectName)[1]'''',''''nvarchar(max)'''')

,@ObjectType = EVENTDATA().value(''''(/EVENT_INSTANCE/ObjectType)[1]'''',''''nvarchar(max)'''')   

,@EventDataText = EVENTDATA().value(''''(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]'''',''''nvarchar(max)'''')


insert into Audit.dbo.DDL_Audit (Event_Type, Database_Name, SchemaName, ObjectName, ObjectType

, EventDate, SystemUser, CurrentUser, OriginalUser, EventDataText)

select @EventType, @DatabaseName, @SchemaName, @ObjectName, @ObjectType

, getdate(), SUSER_SNAME(), CURRENT_USER, ORIGINAL_LOGIN()

, @EventDataText

GO

'''

We took the results of that script and removed any databases that did not matter for purposes of tracking changes. We ran that and created the appropriate triggers on all of the databases. That left us with the notification messages. We handled this through a SQL Server Agent job using Database Mail.

IF EXISTS (SELECT 'x' FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (service account list here)

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101))

BEGIN

DECLARE @email_from nvarchar(100)

, @email_address nvarchar(200) 

, @TheSubject nvarchar(255)


SELECT @Email_Address = 'myemail@myemail.com'

SET @email_from = 'DevServer@server.com'
select @email_address as 'To:' ,  @email_from as 'From:'


set @TheSubject = 'Recent Schema changes on ' + RTRIM(@@SERVERNAME)

DECLARE @tableHTML  NVARCHAR(MAX) ;


SET @tableHTML =

N'<H1>DevDB Schema Change</H1>' +

N'<table border="1">' +

N'<tr><th>Database_Name</th><th>SchemaName</th>' +

N'<th>ObjectName</th><th>Event_Type</th><th>ObjectType</th>' +

N'<th>EventDate</th><th>SystemUser</th><th>CurrentUser</th><th>OriginalUser</th><th>EventDataText</th></tr>' +

CAST ( ( SELECT td = Database_Name,       '',

td = SchemaName, '',

td = ObjectName, '',

td = Event_Type, '',

td = ObjectType, '',

td = EventDate, '',

td = SystemUser, '',

td = CurrentUser, '',

td = OriginalUser, '',

td = EventDataText

FROM Audit.dbo.DDL_Audit

WHERE Event_Type NOT LIKE '%statist%'

AND SystemUser NOT IN (serviceaccount)

AND EventDataText not like '%ALTER%INDEX%REBUILD%'

AND EventDate >= convert(varchar(10),DATEADD(dd, -1, GETDATE()),101)

ORDER BY Database_Name, ObjectType, ObjectName, EventDate, Event_Type

FOR XML PATH('tr'), TYPE 

) AS NVARCHAR(MAX) ) +

N'</table>' ;


EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Default' ,

@recipients=@email_address,

@subject = @TheSubject,

@body = @tableHTML,

@body_format = 'HTML' ;


END

We scheduled this code in a job step that runs daily at 10am. Any changes in that time frame were then sent to a team of people on a daily basis. If no changes were made, no email was sent. Hopefully this will give someone some ideas on one way to audit changes to their DBs. I make no claims that this is the best way, but it worked well for us.

You can download the script file containing all of this code using the link below.

Powershell Community Extensions

While with the time and knowledge we can write a lot of the things we’ll use in Powershell, it’s often not worth it to re-invent the wheel. There’s a group of Powershell coders who have put together the Powershell Community Extensions – a set of Powershell aliases, functions, and other useful code to make working with Powershell a little easier.

For example, there’s a built-in function to split a string. Functions and DLLs are included to read and write ZIP files. There are even some Cmdlets written to allow easy reading and writing from the clipboard or MSMQ. While not all of this will be immediately useful, it’s definitely worth having around.

Installation should be pretty straightforward. Make sure all instances of Powershell are closed. The recommended install is performed using the MSI file. If you want the latest version, you’ll have to unzip the file into your Documents\Windows Powershell\Modules folder.  After that, you can import the module using “Import-Module PSCX”.  There is supposed to be a way to customize what you actually import as well so you don’t load items you’ll never use. The details should be on the Powershell Community Extensions site.

 

While I’m at it, I’d like to give props to John D. Cook who has written a short pamphlet called Day 1 With Powershell which lists a lot of little things that he wished he’d known before starting. There is some good, basic information in it, including configuration, some basics behind Powershell decisions (like = vs –eq), and some pointers to other Powershell sites. I’d recommend it if you’re just getting started with Powershell as it could give you some pointers on how to proceed past the basics.

Powershell – Comparison Operators

I’m writing this one so I remember these operators. Powershell doesn’t use standard operators such as =, <, <>, !=, etc. These operators are used in the following manner.

Operator	Conventional Operator
-eq	=
-ne	<> or !=
-gt	>
-ge	>=
-lt	<
-le	<=
-contains	e.g., 1,2,3 –contains 1 is true
-notcontains	e.g., 1,2,3 –notcontains 1 is false
-not !	e.g., –not ($a –eq $b) Logical Not operator
-and	Logical AND e.g., ($age –ge 21) –and ($gender –eq “M”)
-or	Logical OR e.g., ($age –le 21) –or ($InSchool –eq $true)

 

I think this is one of the areas that is going to trip me up quite a bit. In Powershell, “=” is always used to assign.  “<” and “>” are used for redirection. The names make sense to me in a way, but I can tell I’ll be spending some time with these before I’m completely comfortable with this concept.

 

The above are the base comparisons.  If you prefix them with “i” the operator becomes case-insensitive.  If you prefix an operator with “c”, the operator will be case-sensitive.  Thus we would get the following:

  "johnson" -eq "Johnson" #True

  "johnson" -ieq "Johnson" #True

  "johnson" -ceq "Johnson" #False

Where-Object

This is a useful Cmdlet to filter out results from the pipeline, but this should also be used with care. The initial object will still contain everything with which it started. The results will then be filtered. If you have a way to filter these results prior to passing them through the pipeline, you’ll often have better performance. The following will find all instances of Firefox running on the machine:

  Get-Process | where-object {$_.name -eq 'firefox'}

We could then pass this through the pipeline to operate on that specific object. Where-Object can be abbreviated with a “?”.

 

If, ElseIf, Else

This is a pretty basic statement for Programming and seems to make sense in the way it was implemented in Powershell.

If (condition) {#do something}

ElseIf (next condition) {#do something else}


Else (final condition) {#final action}

As in most uses of If, the process will work through until it finds a condition that evaluates to $true. Once it hits that, it will execute the command(s) inside the corresponding curly braces and exit out of the If.

 

Switch

Sometimes it doesn’t make sense to write out a bunch of IfElse lines to keep checking conditions. Switch can help shorten that. A simple example would be something like this.

$Value = 15
Switch($Value)
{
  1 {"Value is 1"}
  2 {"Value is 2"}
  15 {"Value is 15"}
  default {"Value is $_"}
}

Powershell interprets these in order and will evaluate each one in turn. The default line tells Powershell what to do if no match is found.  If you want Powershell to stop evaluating when it finds a match, add a ;break inside the {} following the matching condition. Otherwise, it could hit the same matching condition and evaluate each one it finds. This may be desirable at some times when you want to act on all matching statements. Other times you may want to short-circuit the process.

Another advantage of the Switch statement is that you can evaluate expressions instead of just the values.  In the example above, the behavior behind the scenes is performing {$_ –eq 1} and {$_ –eq 2}, etc for the lines in the switch. You can substitute your own expressions in here easily to change the evaluations.

Switch supports character comparisons (defaulting to case-insensitive equals), but can do other types by default with a parameter on the Switch statement (e.g., –regex, –case, –wildcard, etc.)

The biggest drawback for Switch is that it would be considered a “blocking” component in the pipeline. It needs to wait for all results before it operates. If you just want to do some filtering on the results, you would be better off using Where-Object or filtering up front.

Powershell – Quite note on Objects

I’m sure this comes as no surprise to anyone who’s dabbled in Powershell, but just about everything in Powershell has an Object underneath. The results we see on screen come from those objects and it’s only when those results are formatted, output, captured, etc that they cease to be an object. This drives much of the power in Powershell. You can set a variable to the contents of an object or part of an object and use that later.

Typically objects will have properties and methods. Properties define what an object “is” – e.g., Red, Small, Named, etc. Methods define what an object can do – e.g., Write, Read, Slice, Mix, etc. There are quite a few internal methods and properties that are common across the internal Powershell objects. You can find these by piping the object to the “Get-Member” cmdlet.

  $host | Get-Member

You can add your own properties and methods to objects easily by calling the Add-Member Cmdlet. The most direct way to do this seems to be by piping the object to the Add-Member Cmdlet.

  $host | Add-Member –Member NoteProperty –Name PropertyName –Value PropertyValue

As Powershell is based on MS technologies, it makes sense that you can use the .NET Framework to work with objects. I will admit that I’m not as familiar with the wide variety of .NET objects so will not try to go into detail.  For one example, here’s a way to call the .NET framework to look up a DNS name by IP address.

  [system.Net.Dns]::GetHostByAddress("207.46.19.190")

 

Perhaps a more useful class would be System.Environment, first by querying its Static members:

  [System.Environment] | Get-Member -Static

You can see that there are a lot of members that are useful. If we choose one and don’t enter valid parameter data, we can even get useful error messages at times.  For example, the following will return an error with valid parameters in the message:

  [system.environment]::GetFolderPath("z")

We can tell by the error message, that some valid parameters would be Desktop, Programs, MyDocuments, Home, etc. Entering a valid parameter in this case will then return the path stored in the Environment for that variable.

 

You can even define your own objects using the New-Object Cmdlet. With no parameters, this creates an empty object. You can create objects of various types by passing the type as the first parameter to the New-Object cmdlet. If you set a variable to an object, you need to be careful about type casting.  Setting a variable to a string containing a date will not necessarily cause that variable to be an object of type System.DateTime. It will most likely be a System.String.  You may need to explicitly cast the object as the type you desire or force the value to be of the type you desire in order to cast the object as the correct/desired type.

 

You can call on COM objects, .NET objects, Powershell objects, Assemblies, and probably more than I’ve listed here as long as you know the name or way to call those objects. I’m still learning this are of Powershell, but hope to expand on this in future posts.