Jeff Gilbert's Web blog at myITforum.com

This posting is provided "AS IS" with no warranties, and confers no rights :-)

January 2008 - Posts

Finding Installed Editions of SQL Server Using SMS 2003 Hardware Inventory

If you're running SMS 2003 SP2 (or earlier versions of SMS 2003) then inventorying SQL Server installations can be a little difficult. The information displayed in Add/Remove programs for SQL Server installations does not provide you with enough detail to determine the edition installed or even whether or not the computer only has the client tools installed-and definitely nothing about the licensing information associated with it.

Note: If you're running an SMS 2003 SP3 site (with Asset Intelligence installed) or a Configuration Manager 2007 site, then ignore this post, do not pass Go, and go directly to Asset Intelligence License Report 01A! Asset Intelligence reporting is designed just for situations like this. If you're running an SMS 2003 SP2 or below site, then after you see what you need to go through to get the same information that Asset Intelligence gets by default, it may just be enough to convince you to install SMS 2003 SP3 (with Asset Intelligence) or Configuration Manager!

I've been confounded by this for a while now, and have even managed to use SMS 2003 hardware inventory to determine which computers have the full database engine installation of SQL Server installed versus just the client tools, but determining the edition (Standard, Enterprise, Developer, etc...) has always eluded me. I just couldn't find this information in the registry or WMI of SQL Server computers. This doesn't mean it's not there necessarily, just that I couldn't find it.

Of course, the nice thing about working for Microsoft, is that help is only an e-mail away and I've done a lot of e-mailing the past few days Smile

Big thanks to Jim Bradbury (SMS SDK writer) who wrote the .sql and .vbs files!

Finding this information without Asset Intelligence is not easy, but it is possible. Here's a quick rundown of how to do it:

  1. Create a collection of computers that have SQL Server installed based on the Add/Remove programs information collected via standard SMS 2003 hardware inventory. Make sure your collection only contains computers running operating systems capable of installing the full database engine and not workstations that can only install the client components.
  2. Use software distribution or manually run a .sql script on all SQL Servers in the collection created in step 1.
  3. Modify the SMS 2003 SMS_def.mof file to inventory the information stored in the registry when the .sql from step 2 is used.
  4. Create a query or report to view the newly collected information.

Before I continue here, you should know that this procedure requires some serious disclaimers (notice how long this section is...considering SMS 2003 SP3 with Asset Intelligence yet?):

  • To run the .sql script, the account used requires rights to access the registry and the master database.
  • The .sql script used to determine the SQL Server installation information uses an undocumented extended stored procedure to write to the registry. This means that it is not officially supported and subject to not working at some point in the future-use at your own risk.
  • I've only done very limited testing of this procedure in my lab environment. If you're going to use this method, be sure to test it in your lab environment, and then go test it again, before introducing these changes in your production environment.
  • Using this method to write information to the registry is kind of like using a NOIDMIF file or any other scripted method that writes to the registry. You'll need to re-run this .sql script on the computers with SQL Server installed periodically to get the most current information. Also, if SQL Server is uninstalled, the registry entries created by running the .sql script will not be removed and you may end up with conflicting information based on the collections/reports created using this custom information versus the information returned via Add/Remove programs. To mitigate this, base the queries/reports for this information only on systems that show up in your SQL Server collection (based on Add/Remove programs information). You might also want to run another program first to delete the registry key information and only repopulate later using the .sql script if SQL Server is still installed. Of course, SQLCMD.EXE won't work if SQL Server is no longer installed, and this will help keep the registry free from unneeded entries. A quick .vbs to do this is provided here (rename the extension from .txt to .vbs before using it).

OK, on to the good stuff. This post is already becoming long so I'm not going to show the actual files here, I'll just describe them and give you links to view them. When viewing these as .txt files in a Web browser, the formatting leaves something to be desired. For best results, right-click the link and save them somewhere to view. All of the linked files have been renamed with a .txt extension so I could upload them and make them easier to read without them actually doing anything. To get the files with their 'real' extensions, use the link to save the entire .zip at the bottom of this post.

After you've created the collection of computers that report SQL Server installed via Add/Remove programs information obtained through hardware inventory, you need to somehow run the .sql script on them to populate the registry with the information we're after.

To run the .sql script on SQL Servers, you need to use SQLCMD.EXE. Since I've named the .sql script SQLInfo.sql, the command to use is: SQLCMD -i SQLInfo.sql. If you're running this locally via a command prompt, ensure that the account you're using can write to the registry and has rights to the master database. When run, the .sql script does a few nifty things:

  • Creates some variables
  • Runs SQL Server SELECT queries to get the information we're after into those variables
  • Uses the undocumented extended stored procedure xp_regwrite* to write the information to the registry
  • NULLs out the variables at the end to clean up

* This extended stored procedure is not officially documented and therefore not officially supported. Use at your own risk.

Once you've got the information in the registry, next up is an SMS_def.mof hardware inventory modification using the Registry Property Provider to read those keys and get them in the database. To see this information, you can create queries based on it or reports to view the information.  When viewed in Resource Explorer, the information looks like so:

To determine the version of SQL Server running using the version value, check out this link. The license type value can be PER_SEAT, PER_PROCESSOR, or DISABLED. The number of licenses is the number of client licenses registered, processors licensed for that instance, or NULL if licensing is disabled.

Of course, this is a long way to go, and not a very fun way to get there, when trying to inventory SQL Server installations...and to be honest, if I was an SMS 2003 SP2 admin I'd be much more inclined to just install SMS 2003 SP3 with Asset Intelligence or even make the move to Configuration Manager 2007 to get this information the easy way.

Happy inventorying!

~Jeff

If you want to get all of the files I've referenced here, you can download them all in one .zip file here

Posted Sunday, January 27, 2008 9:38 PM by jgilbert | 2 comment(s)

Dude, where’s my SMS_SiteSystemToSQLConnection group?

A fairly common question I seem to keep running across from SMS 2003 admins that have upgraded to Configuration Manager is: "What happened to the SMS_SiteSystemToSQLConnection group on my Configuration Manager 2007 site database server?"

Although this is documented in the Configuration Manager documentation library in the What's New in Security for Configuration Manager topic, I figured that I would blog this to get the word out a little more.

In case you're wondering what that topic says, here's the applicable bit:

The SMS_SiteSystemToSQLConnection group is no longer needed because database access is controlled by SQL Server roles that are automatically created during Configuration Manager 2007 Setup. For more information, see About the Database Roles for Configuration Manager at http://technet.microsoft.com/en-us/library/bb632943.aspx.

So there you go...question answered Big Smile 

OK, you probably want a little more background information I'm guessing.

First, the history of the group. When SMS 2003 sites are installed, the SMS_SiteSystemToSQLConnection_<site code> group is created to manage the required permissions for site systems to access the site database to support advanced security. Site systems like management points, server locator points, and reporting points are thereafter added to this group when applicable-even if they're installed locally on the site server computer. You'll still see database roles installed on SQL 2005 instances hosting SMS 2003 site databases because database roles are a SQL 2005 feature, but you won't see SMS-related database roles as they're not used.

Now you see it (before upgrade from SMS 2003):

There's the connection group

As stated earlier, Configuration Manager installations do not use the SMS_SiteSystemToSQLConnection_<site code> group so you won't find that group on the SQL Server hosting a Configuration Manager site database. Instead, SQL Server 2005 database roles are used to control site system access to the site database. All of the required database roles are created, and assigned the required permissions, during Configuration Manager primary site setup. Modifying the roles created by Configuration Manager 2007 and the permissions assigned to those roles is not supported

As new site systems are configured for the site that require access to the site database, site component manager automatically adds the computer account to the required database access roles for you-as long as you're using the computer$ for the site system to connect to the site database. If you will use a database connection account to allow a management point, PXE service point, or server locator point to connect to the site database, then you need to manually add the database connection account to the appropriate site database role.

Now you don't (after upgrading to Configuration Manager 2007):

Connection group not there!

For a picture of what these database roles look like (before and after upgrading to Configuration Manager) check out the picture at the bottom of my Upgrading SQL for SMS Sites post.

For more information about Configuration Manager database roles, see About the Database Roles for Configuration Manager.

Hope this helps,

 ~Jeff

 

Posted Thursday, January 17, 2008 10:42 PM by jgilbert | with no comments

Some great Configuration Manager Internet-based client support information

If you're interested in Configuration Manager native mode sites supporting Internet-based clients, you should definitely give this post a good read I think. It's an awesome post from Carol (our native mode/Internet-based client support writer (among other things)) on our writing team's blog:

 http://blogs.technet.com/wemd_ua_-_sms_writing_team/archive/2008/01/15/tips-tricks-hints-for-native-mode-and-internet-based-client-management-part-1-of-3.aspx  

 

~Jeff 

 

Posted Wednesday, January 16, 2008 3:31 AM by jgilbert | with no comments

How to format an external drive without losing the information stored on it

I generally format new USB drives NTFS as soon as I get them because for some reason they all still come formatted Fat32. You don't really notice this until you try to copy something large to them and then you get an error saying the file is too large for the file system.

I tried to copy a large virtual machine hard drive to one that I have that is already full of stuff today (I'm creating images for an MMS lab that will hopefully be approved!) and I got that error....#$%^&

I didn't want to format the entire drive and lose all of the information already on it, so I had to dig into my bag of tricks and use the DOS command Convert. This is actually a pretty fast process and saves the data already present on the drive-which is a plus. I converted a 80GB drive to NTFS in around five minutes.

To use this command, you just open a command prompt (if you're running Windows Vista you'll need to open the command prompt running as administrator) and convert away. You'll be prompted for the volume label of the drive to convert, so you need to know that as well.

It's a fairly simple process to convert the drive from FAT32 to NTFS using the CONVERT command and its syntax is shown below:

DOS Convert command

 

I think this is equivalent to the quick format option, I'm not positive, but I'll be reformatting this drive "the hard way" later just to make sure everything is copasetic in the future.

Posted Friday, January 11, 2008 6:40 PM by jgilbert | with no comments

Filed under:

How to check the WMI repository before rebuilding it

I've had some WMI repository repair tidbits stashed away in my e-mail that I was going to blog about sooner or later. I'm not sure if today is sooner or later, but I did have to go looking through my old e-mails to find these steps for a lab machine today, and figured that was a sign I should finally post this information to the blog. If you suspect that the WMI repository has been corrupted, before throwing in the towel and blowing it away you can/should try to repair it 

I couldn't remember where I got these command lines so I had to go look them up again! Looks like if the repository can't be repaired, it will be recreated anyway: http://www.microsoft.com/technet/scriptcenter/topics/help/wmi.mspx

Of course, you could use the WMI Diagnosis Utility to check for WMI corruption, but I'm a pretty impatient person at heart and find that this is just faster for me. The steps when using these commands go kind of like this--check it, fix it and worst case replace it.

Anyway, to check the WMI repository for errors on a Server 2003 SP1 or SP2 systems, run the following command from command prompt:

rundll32 wbemupgd, CheckWMISetup 

Once you've run that command, check the setup.log file located at: %windir%\System32\Wbem\Logs\Setup.log.  Check for entries from today's date.  If you find none, then WMI is probably OK and you should continue troubleshooting somewhere else.  If however, you see an error message from today saying that it can't find a namespace then yes, your WMI repository does have issues and you should do the below:

rundll32 wbemupgd, RepairWMISetup

This will re-create your WMI repository minus any custom .mof additions that were implemented without the -autorecover switch.

For Windows XP SP2, use the following command to check for corruption, and repair if necessary:

rundll32 wbemgupgd, UpgradeRepository

For Windows XP SP1, the check and repair commands are below and function just like the Windows Server 2003, SP1 commands:

rundll32 wbemupgd, CheckWMISetup

and

rundll32 wbemupgd, RepairWMISetup

 

*******************************************************************************************************************************************************************************************

UPDATE: For Windows Vista and Windows 7:
(I found this info on the WMI team blog originally)

Note: You must perform this procedure from an elevated command prompt (right-click command prompt on the start menu and select 'run as administrator'. 

Type the following command:

winmgmt  /salvagerepository

The above command performs a consistency check on the WMI repository, and if an inconsistency is detected, rebuilds the repository. The content of the inconsistent repository is merged into the rebuilt repository, if it can be read.

*******************************************************************************************************************************************************************************************

Hope this helps,

~Jeff

Posted Friday, January 11, 2008 1:30 AM by jgilbert | 2 comment(s)

Filed under: