Book Review: Cooking For Geeks

(I know this isn’t SQL-related, but I think this may be a great read for people with similar interests.)

This is not a cookbook. If you’re looking for a new collection of recipes, this is not the book for you. If you like shows like Good Eats or enjoy seeing how food is prepared and served, you’ll almost certainly love Cooking for Geeks. If you like experimenting in the kitchen and knowing why food turns out the way it does, pick up this book!

As a geek, I loved Jeff’s analogy: Recipes are code. Follow a recipe as written and you generally get good results. Forget the where clause and you could have unrecoverable errors. Introduce your own changes and you could get something great or you could get something horrible that requires a lot of cleanup. Recipes may have bugs or need corrections. Perhaps there’s more than one way to the same result. Oh, and don’t forget to comment your recipe. Otherwise you might not be able to recreate something fantastic.

Each chapter of Cooking for Geeks deals with different concepts, each with their own scientific background. Common utensils, ingredients, time/temperature, baking, additives (chemicals), and even some geeky fun with hardware or unusual cooking techniques – all are included in a way that not only gives some neat recipes, but the science behind the recipes.

To me, the most interesting parts were on baking and the chemical reactions that take place as heat is applied. It was great reading exactly why food turns out with all of its various nuances. That science got me thinking about ways to tweak the outcome of various recipes I follow and was just fun to read.

You can get your own copy of Cooking for Geeks direct from O’Reilly.

Disclaimer: I received an electronic review copy of this book, though I’d likely have wanted to read and review this anyway.

SSIS, OLEDB Data Sources, and Table Variables

I ran across an interesting problem when trying to tune some SSIS data source performance issues the other day. I had noticed that we were doing a pretty large select out of a table filtered on a handful of date-related columns. We then pulled in most of the data from the table. Well, SQL Server saw this query and apparently figured that since we needed most of the columns, a table scan would be the most efficient way to get that data.  Since in real life, we only need a very small percentage of those rows, I decided to rewrite the select to pull in the PK values first, then pull in the data based on that subset.

My first attempt was to use a Temp Table. Sadly, using a temp table does not work in SSIS Data Sources, even if you put that inside a stored procedure. Disappointed, but not deterred, I decided to use a Table Variable. I quickly made the changes and tested them with some values I knew would returned quite a few results. Great – no worries.  I was still using a stored procedure at the time and used some of our standard code to start it off so had no issues.

Ultimately, I didn’t want to write a bunch of stored procedures that I would need to maintain outside of SSIS just for this particular load.  We had the code already inside of the OLE DB Sources, just not optimized as much as I’d like. I started pulling out the old queries, adding a table variable, populating it with the necessary PK values, the using that to filter out the main data set.  Save, deploy, run, see major speed increase.

Then, I looked at the actual insert/update counts. Yes, it ran much faster, but in the process, it didn’t pick up any results.  I was pretty confused because the step was successful, the code ran as expected through SSMS, there were no errors or warnings. I re-verified that running that same code got results in SSMS; it did.  I started doing some searching to see what might be happening and found this post by Dustin Ryan.

In short, it seems that if you have row counts being returned (default behavior), you return a number of rows affected when you insert the table variable, which results in a “Done in Proc” type message, which in turn tells SSIS that the data flow is done and moves on to the next step.  The workaround?  Add SET NOCOUNT ON; to the beginning of your OLEDB Source query (or make sure this is in your stored proc – we tend to put that in our procs by default).

Once again, thanks to Dustin Ryan for his post on this topic. There’s a good possibility I’d still be struggling with the root cause of this without that assistance.

Review: Windows PowerShell 2.0 Best Practices - Introduction

 

 

A while back I received a review copy of Windows PowerShell 2.0 Best Practices by Ed Wilson (blog | twitter) and the Windows PowerShell Teams at Microsoft. Having finally found the time to sit down and really digest the information within, I thought I’d do a multi-part review of the book.  Ed Wilson has compiled an excellent resource not only to show off what PowerShell 2.0 can do, but also to provide practical examples and several useful tips for best practices not only in using PowerShell, but in writing reusable and efficient code.

This book is broken down into 5 major sections:

• Introduction
• Planning
• Designing
• Testing and Deploying
• Optimizing

I want to give a quick overview of what’s contained in each chapter, though I’m not going to include the code from the book. You may be able to get it from the main page for the book, but ideally I think this book is well worth buying. You get a PDF file of the book and all of the code included in the book as well. If you plan to do a lot of PowerShell scripting, this book is well worth the money.  I will say that this book is not designed to teach PowerShell.  Ed Wilson really does expect that readers have a working knowledge of PowerShell. He builds on that knowledge to show how to write PowerShell scripts with these best practices in mind.

Chapter 1 contains an overview of PowerShell 2.0. Ed explains some of the differences between PowerShell 1.0 and 2.0, focusing on the new remoting capabilities, WMI enhancements, changes to the way some cmdlets work, and why they have changed the original behavior. He touches on some of the differences between VBScript and PowerShell and why PowerShell might be a better choice than VBScript, despite people’s familiarity with the latter. One of the key advantages of PowerShell over VBScript is that PowerShell was designed to be run from a prompt or as a script. VBScript must be run through something that calls the script.  This command-line interaction allows people to work with the script in a much more interactive way and even use native Windows commands with which people are already familiar.  Ed closes the chapter by reminding readers of the minimum requirements for PowerShell 2.0, informing readers from where they may obtain the latest version of PowerShell and suggesting where PowerShell 2.0 should be installed (pretty much everywhere).

Chapter 2 is where readers become more familiar with PowerShell 2.0 capabilities. We’ve already read about the promise of PowerShell 2.0, but now we can start to see it in action. Ed starts by introducing readers to the PowerShell 2.0 interactive command line. The first command shows users running processes inside the PowerShell environment.  Ed then shows some of the easiest cmdlets to run and remember, followed by the “most important” cmdlets: Get-Help, Get-Command, and Get-Member.  I agree that these three cmdlets are key. You can always find out more about what commands are available, how to run them, and what properties are available.  Ed then lists several useful cmdlets to enhance filtering, grouping, sorting, and such.  Chapter 2 ends with a good introduction to WMI, Remoting, Services, and the Event Log from a PowerShell perspective.

Chapter 3 concentrates on Active Directory. Ed reminds us that what we typically call “AD” is really a legacy term now and is actually “Active Directory Service Interfaces” or ADSI. We tend to think of AD in terms of users and domains, but it contains much more now than it used to. Ed jumps right in by showing readers how to reference ADSI Users, Groups, and Computers with some quick examples of how to reference each within PowerShell.  There’s then a quick “Inside Track” by James Turner showing how PowerShell can be used to automate some common administrative tasks around user accounts. James tells us to use the right tool for the job as well. While the LDAP functionality allows more authentication methods, the WinNT functionality allows more object types and more familiar syntax.  The bulk of Chapter 3 really concentrates on building a functional script to create and update Users within ADSI. The reader is guided from the more simple scripts to query data through reading a file to automate the creation and updating of user accounts. We also get to see several ways to accomplish this task to introduce the reader to reading from a connection to Excel.

Chapter 4 builds on what we’ve already learned from Chapter 3 to work within ADSI.  Ed starts by helping us become familiar with the AD schema through PowerShell. As always, Ed actually puts to use the “Best Practices” that he wants his readers to learn. His first script includes a full Help example and several functions, followed by a breakdown of each function. I found it helpful to look through the original script while Ed explains the details so I could keep track of how each function fit into the bigger picture.

Ed then demonstrates how to query AD for information, going beyond merely listing the results – this is PowerShell after all and the results can be full objects. He gives a pretty good list of ADSI-related properties, shows how to display results that list results, then moves on to true queries – ADO and OLEDB queries. Of course, his overall goal is to really use PowerShell effectively. ADO and OLEDB are useful, but we also want to be efficient. Therefore, the reader is introduced to the DirectorySearcher class and PowerShell 2.0’s [ADSISearcher] to simplify calling DirectorySearcher. Along the way, we see the “FindOne()” method to limit the results to a single row. That is useful to see what data we can expect.  Some familiarity with ADSI can really help when going through these chapters.

We’re led from these queries to actually managing user accounts. First, the reader needs to understand ADSI User Account Control values. He then shows using the Bitwise AND operation to compare the various bits set and determine properties of the various user accounts. Ed shows how to find and display disabled user accounts in a short script using bitwise AND and color coding. Readers are then shown how to move objects within AD without too much code. The final example details how to find missing values within AD. These are the values that are expected, but not always completed.  Once again, I found it very useful to see the whole script while Ed explains each step of the script.  Be sure to include the backtick if you want to type these scripts yourself and try them.

 

Conclusion

The Introductory section of Windows PowerShell 2.0 Best Practices is well-written and serves its purpose of getting the readers ready to move through the rest of the book. If I have any complaints about this section it’s that it uses ADSI pretty heavily. For people who don’t work directly with ADSI on a regular basis, a lot of the examples are harder to follow. While still possible to see the best practices for PowerShell, it’s easy to get lost in trying to figure out what Ed is doing with ADSI.  The Planning section in chapters 5-8 seem to be less focused on a specific set of functionality and much more on how to plan and use PowerShell effectively.  I plan to post a review of those chapters in the near future.

 

I’d appreciate feedback on this review. Too lengthy? Too short? Let me know what’s good and what’s not good. I’d like to write a useful and honest review for those considering this book.

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.