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.