Sql Server, WMI and PowerShell

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 Smile




Multicore and virtualization simplifies server infrastructure

Even a small company like mine have a need for many servers. Web servers, file servers, domain controllers, source code repositories… and then there are test and research servers with various operating systems. And most of these must be monitored and backed up regularly. A lot of boxes consuming both space and power.

We are now moving into an era where multicore computers can fit the budget of small companies. Dual core have been around for some time and now we’re seeing
quad-core. Both in servers and workstation machines. To alleviate our “space, power and maintenance” problem, lets put multicore technology to good use.

So recently we acquired a server with two quad-core processors. Easily
the most powerful computer I ever was able to carry around the office
without a forklift. Using virtualization we can now consolidate all our servers into a farm of virtual machines running on this single server. Some instant benefits:

  • The ability to add, remove and restore servers at will without having to acquire new hardware or reinstall existing machines comes as an added bonus.
  • One, fast, redundant disk system is there for all machines to use and backup becomes a breeze.
  • Shared power and network redundancy using one of the many server housing services out there. Renting 2U rack space is cheap these days…

For those of you that love numbers, here’s some machine specs:

  • Machine: DELL PE2950
  • Processors: twin quad-core Xeon E5335 2.0GHz/2x4MB 1333FSB
  • Disk: five 73GB 2.5in hard drives configured as RAID5. These are SAS (Serial Attached SCSI ) disks spinning at 15000 rpm.
  • Network: dual Gigabit network cards
  • Power: dual power supplies
  • Memory: 4 GB 667MHz FBD

…and even some pictures. You can see my Qtek 8500 smartphone on the top for some degree of size comparison Smile


The next installment of the XNA Game Studio is close at hand!

Announced just yesterday, XNA Game Studio 2.0 is soon to be released. This will be a lot of fun, especially the network support. Like others, I’m curious about wether it will cost extra to use those network api’s. I would hope not, since we’re already paying for the XNA Creators Club membership.

What is also interesting is that the G studio will no longer be tied to the Express edition of Visual Studio. And since urban legend says that Visual Studio 2008 will be released this November, Game Studio 2.0 will hopefully be compatible with 2008.