When a PowerShell module becomes a crutch

As I’ve mentioned, I’ve a big fan of SQLPS.  However, as this inventory system I’ve built has slowly started to become used by more people than myself, it has become more important that I ensure the data is always available.  Currently, all the PowerShell scripts that I have in place to inventory our various systems make use of SQLPS to first delete all data in a table, then insert a fresh inventory each night.  A problem occurred recently while I was making an update to one of the scripts when I forgot to enable the $debug line, causing the script to wipe out the table and refresh inventory while my boss was trying to look at it.  Oops.

The solution?  I’m going to have to stop using SQLPS.  A much more elegant way to go about getting fresh data into the server is to make use of SQL temp tables, then once the temp table is fully built, quickly drop the current table and replace it with your freshly built temp tables data.  The problem with SQLPS is that each time you run Invoke-SqlCmd a new connection to the SQL server is opened and closed, which would cause any temp tables to be dropped from memory.  So, in order to keep the connection to SQL persistent while the script runs, I’m going to have to make my own .NET connection objects and leave them open until the script is done.  I finished up most of the groundwork for the Active Directory inventory script this afternoon and will post the updated version tomorrow once I get it nicely commented up.

This entry was posted in Musing and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s