It’s that time of month, and I’m the host this month. I wrote the invitation last week and now its’ time to answer. I’m actually using an example from the past that I was reminded of. That was the basis for the invitation as well.
SQL Slammer
I don’t know how many of you remember SQL Slammer, but it hit my company, JD Edwards, over a weekend. I was away on holiday, coming home Sunday night and got called into the office that night.
One of the challenges with JD Edwards was that we used MSDE extensively. It was a part of some of our products, and developers had it on various machines, lots of multi-instances, and in all sorts of development servers. The worm crippled our network.
We also had non-standard installs, so when we got a patch from Microsoft, it didn’t work because we weren’t in the c:Program Files… that they expected.
I had to some some fancy dynamic stuff to get the patch to work. First, we used some queries in SMS (Systems Management Server) to find all the places where we had MSDE and SQL Server services. This wasn’t too hard, and I had a list of hosts and instances from here. Now the hard part.
I needed to query all these instances and find out where things were installed, as well as get some patch information back. The SQL itself wasn’t too hard, but connecting to and querying all these machines wasn’t simple. This was the pre-PowerShell era. and VBScript wasn’t as easy, or bulletproof, to write.
Excel to the Rescue
I’d used Excel to help with this type of thing in the past. I would put in some data in a column in this case, the hosts and instances. Then I’d add the same value in other columns, like SQLCMD. Then I would concat these together to make a string I could run. I also included T-SQL code in here, as I’d be querying various tables inside the engine.
I also had an output part of the command, so that when I copied the contents of my final column, I had hundreds of SQLCMD command calls that would query all our instances and return data in a way that we could use to run the patch.
Double dynamic code!
I put these in a batch file, ran them, and then we had results that could be used in a similar process with the MS patch to patch all machines.
A long 2-3 day of getting systems patched and slowly turning our network back on.