THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Version Checks

Patch time is upon us! Patch time is a great time to take stock of your whole environment, find those new SQL instances that you didn't know you had, or the retired ones you don't need to worry about any more. Here are some housekeeping tips:

Simple SQL Server Engine Version Checks

To see where you might need patches, here's a simple way to check all the known instances in your environment:

  1. Open SQL Server Management Studio 2008. (If you don't have it, I highly recommend getting it - many new and useful features even if you are still running 2005 or even 2000 -- gasp! -- servers.)
  2. If you don't have them, create server registrations for all your known instances, perhaps using a folder structure if you manage many servers. I have folders for dev, test, preproduction and production SQL instances across my whole environment.
  3. Right click a folder of server registrations and choose New Query. This opens a multi-server query.
  4. Run this to gather version information from all the registered instances in one go:
SELECT SERVERPROPERTY( 'ProductVersion' ) AS ProductVersion
, SERVERPROPERTY( 'ProductLevel' ) AS ProductLevel
, SERVERPROPERTY( 'Edition' ) AS Edition
, SERVERPROPERTY( 'EngineEdition' ) AS EngineEdition
, ISNULL( SERVERPROPERTY( 'InstanceName' ), 'DEFAULT' ) AS InstanceName
, SERVERPROPERTY( 'ComputerNamePhysicalNetbios' ) AS ComputerNamePhysicalNetbios
, SERVERPROPERTY( 'MachineName' ) AS MachineName

Yeah, but where are those OTHER SQL Services?

Most environments are sprawly these days, and you probably have some SQL Server services out there that maybe you didn't even know you had. This is a problem both from a licensing standpoint and also in terms of backup and DR. Here I have found either PowerShell or C# can be a huge help. A simple PowerShell script listing services from WMI will reveal all the SQL Services including SSIS, Analysis Services, Reporting Services and so on (this is a now popular technique, not my invention):

>$serverlist = "SERVER1","SERVER2","SERVER3","SERVER4"

>$serverlist | foreach { 
   get-wmiobject win32_service -computername $_ 
   | where { $_.displayname -like "SQL*" } 
   | select systemname, name, displayname, startmode, state, status 
} | Format-Table -AutoSize

If you want to get fancy, the server list input can come from a text file, and the output can be directed to a CSV, or straight into a SQL Server table. Personally I keep a set of SharePoint custom lists for my team containing all the machines, instances, clusters, and databases in our environment, and update those lists periodically with some basic environment-wide queries like these.

Alas, version information for SQL Services other than the Engine is slightly more difficult to collect, but can be fetched out of the registry if necessary.

Happy Patching!

Published Wednesday, October 14, 2009 3:41 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



silk said:^Eescort.html^Eescort.html^Eescort.html^Eescort.html

February 9, 2019 8:48 AM

Leave a Comment


This Blog


Privacy Statement