So I started out on a quest: a quest for the overview of installed SQL Server instances on my machine. With previous versions we had to crawl the tangled forest of the Registry to get this information. Knowing that with SQL Server 2005 there is now a WMI namespace that can be queried, and with this sample I could easily enumerate my 2005 instances (both full and express versions). It was all there in the ROOT\Microsoft\SqlServer\ComputerManagement namespace!
Encouraged by the simplicity of getting instances through WMI I moved on to install the latest SQL Server 2008 bits. I wanted to try out the Express CTP, downloaded and installed it, and ran my code again.
…no sign of the 2008 instance. Thinking that they surely haven’t ripped out WMI support in 2008 I needed a tool to browse the WMI namespace to find out what was going on. Luckily, the PowerShell Guy have made a nice PowerShell script that explores the WMI namespaces in a nice and graphical UI.
But the script requires PowerShell V2 so now I had to go on a side-quest, a quest to get the WMI Explorer running. Installing PowerShell V2 CTP on Windows XP requires installing Windows Remote Management, the Microsoft implementation of WS-Management protocol. For the record, WRM is built into Windows Server 2008 (and probably Vista too) but needs to be installed separately on Windows Server 2003 and Windows XP. And yes, I had to throw out PowerShell 1.0 (what happened to side-by-side installations?) The quirk with removing PS 1.0 is that in Add/Remove Programs, remember to Show Updates in order to see the “Windows PowerShell(TM) 1.0” entry under “Windows XP – Software Updates”.
The rest went well, and I’m now back on track browsing the WMI namespace. And lo and behold, they have indeed changed the WMI schema with SQL Server 2008. The location is now ROOT\Microsoft\SqlServer\ComputerManagement10.
But this wasn’t the treasure I set out to find! Instead of crawling the registry we now have to crawl through WMI. First, I will have to query the ROOT\Microsoft\SqlServer\__NAMESPACE in order to discover what ComputerManagementXX namespaces are installed. Okey, so it is a bit easier than the registry anyway. When I go into the ComputerManagement10 namespace and look up instances of the SqlService class, I get a nice list of all SQL Server instances, both 2005 and 2008.
Lessons learned? Microsoft warned us that “the Registry will change”. It seems that the same goes for WMI.
Update: couple this with strongly typed classes for accessing WMI for extreme simplicity 
