Jeff Gilbert's Web blog at myITforum.com

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

October 2008 - Posts

Deploying SQL Server 2005 with SP2 Using an OSD Task Sequence (Part 2)

The first thing that you need to do before deploying SQL Server 2005 and SQL Server 2005 SP2 using a task sequence is to create the packages and programs that the task sequence will use. If you haven't done that yet, go read part 1 of this post!

After the required packages and programs are created, and successfully distributed to distribution points, we can get started. I'm going to break this post down into the following sections:

  • Creating the task sequence
  • Advertising the task sequence
  • Monitoring the task sequence actions
  • Verifying the install did what it was supposed to

Creating the task sequence

The package source is just the SQL Server 2005 RTM installation media. The interesting bits are all in the program properties.

  1. Navigate to the Task Sequences node in the Configuration Manager console (System Center Configuration Manager \ Computer Management \ Operating System Deployment\Task Sequences). Right-click Task Sequences and select New\Task Sequence to start the New Task Sequence Wizard.
    1. Select the Create a new custom task sequence option.
    2. Type a name for the task sequence, give it a comment if you want to, and click Next—don't select a Boot image (I named mine Install SQL Server 2005 w/SP2).
    3. Click Next on Summary page, let the wizard finish, and then click Close on the Confirmation Page. The new task sequence should appear in the results pane.
  2. Right-click the task sequence you just created and click Edit.
  3. In the Task Sequence Editor, click Add and select New Group. Give the group a name (I named mine Install SQL Server 2005 w/SP2).
  4. Highlight the top group name and click Add\New Group. Give this subgroup a name (I named mine Install SQL Server 2005).
  5. Highlight that group name, and then click Add\General\Install Software. You should see an install software task with a red X now (it will remain red X'd until you select a package and program to run).
    1. Name the Install Software task (I named mine Installing SQL Server 2005).
    2. Leave Install a single application selected. Click the Browse button and select the package that you want to use and then select the program that you want to deploy (this is the package/program that installs SQL 2005 RTM for me). The red X should now be a green check mark.
  6. Highlight the install software task and click Add\General\Restart Computer.
  7. Highlight the top group name again and click Add\New Group. Give this subgroup a name (I named mine Install SQL Server 2005 SP2). Drag the new group underneath the previously created task sequence group.
  8. Highlight that group name, and then click Add\General\Install Software.
    1. Name the Install Software task (I named mine Installing SQL Server 2005 SP2).
    2. Leave Install a single application selected. Click the Browse button and select the package that you want to use and then select the program that you want to deploy (this is the package/program that installs SQL 2005 SP2 for me). The red X should now be a green check mark.
  9. Highlight the install software task and click Add\General\Restart Computer.

If all goes well, you should now see something like this screen shot in the Task Sequence Editor Window and you can save and close it:

 
 

Advertising the task sequence

Of course, task sequences do no good if you don't advertise them to clients.

  1. Right-click the newly created task sequence and select Advertise to start the New Advertisement Wizard.
  2. Ensure the correct task sequence is selected and click Browse to select the collection that you want to advertise this task sequence to. If you don't want this advertisement to include subcollections (I don't), then de-select the Include members of subcollections option.
  3. On the Schedule page, configure the schedule that you want this advertisement to use. Because I'm doing this in a lab and I want it to install right now (or as close to it as possible), I create a mandatory assignment time for as soon as possible. I also check the options to Ignore maintenance windows when running this program and Allow system restart outside of maintenance window.
  4. On the Distribution Points page, you need to select an option for how the package contents will be accessed. Because I like to ensure that all required files are present before beginning major software installations to avoid transient network issues during installation that could cause the install to fail, the two options I'm looking at are: Download all contents locally before starting task sequence and Download content locally by running task sequence. From those two available choices, I'm going to pick Download content locally by running task sequence and here's why:

When you configure an advertisement to Download all contents locally before starting task sequence, the content is stored here in the client cache directory—and they are not deleted after the task sequence finishes:

When you configure the task sequence advertisement to Download content locally by running task sequence, the content is stored in a temporary task sequence directory—and they are deleted when the task sequence finishes:

Technically, the install will run successfully using files from either location (and most likely even if you run it from a distribution point), but the problem I have with using the client's local cache directory is that the source files are not removed after installation. Even though I'm using a setup.ini file located on a hidden share and not located with the source files in this instance, you might not always do that and a savvy user could get ahold of those cached SQL Server setup files, proceed to run amok on the network, and then I've got a bunch of unauthorized SQL installations to account for. Besides that, it just takes up a bunch of hard drive space on clients unnecessarily (over a gigabyte in this case for SQL Server 2005 and SP2).

  1. On the Interaction page, select Show the task sequence progress.
  2. Finish out the wizard to complete the advertisement process.

Now, you can just wait until the targeted computer retrieves the machine policy that contains the advertisement information for the task sequence to kick off, but I'm not that patient so I manually initiate a machine policy retrieval & evaluation cycle to get the party started.

Monitoring the task sequence actions

There are a couple of ways to monitor the task sequence as it goes through the motions that you have configured for it. You can use the Configuration Manager console and reports to view the status of the task sequence execution or you can watch the progress from a targeted machine…or both I suppose. I'll walk you through each of these methods next.

Monitoring task sequence execution from the Configuration Manager console

As soon as you advertise the task sequence to a collection, the Configuration Manager console OSD home page updates and displays the status of the new task sequence advertisement:

As the task sequence execution proceeds, clients send in task execution status messages, and the home page refreshes, you can follow the status in the console using the color coded chart:

If you don't want to wait, or you want to see an individual computer's task sequence execution progress, you can view Web reports that detail the progress by clicking the task sequence name in the console. There are a total of three reports that drill down and display more detailed information about the task sequence status:

Status summary of a specific task sequence advertisement. This report shows the status summary of all resources that have been targeted by an advertisement.

Drilling down by clicking an execution state value you get:

All system resources for a specific task sequence advertisement in a specific state. This report will show a list of computers that are targeted by the specific task sequence advertisement and are currently in the specified state.

Click a computer name for that particular computer's status gets you:

History – Specific task sequence advertisements run on a specific computer. This report shows the status for each step of a task sequence. If no record is returned, it means the task sequence has not yet started.

The last one is the one that I like. It displays detailed information about each step taken during the task sequence execution for a particular computer. I like to open this report for a test computer and watch the actual task sequence in action by refreshing the page every now and then. Of course, nothing beats watching the task sequence execution at an actual computer that is running it though.

Monitoring task sequence execution from the client computer

After a client computer receives the task sequence notification policy it displays the notification in the system tray to alert you that it is going to run (I set a mandatory installation time of as soon as possible for this example):

You can follow the progress of these initial stages by watching the client's execmgr.log which hands off logging to the <install dir>\CCM\Logs\SMSTSlog\smsts.log file. Anyway, clicking the notification gives you the option to run the task sequence or wait for the countdown to complete. When the task sequence starts, installation progress messages are displayed on the screen.

 

The install software task (to install SQL Server 2005):


The restart computer task that comes after the install software task in the task sequence:

 

After the computer restarts, the task sequence waits for the Configuration Manager client to reinitialize and then continues on its merry way:

 

 


The install software task to install SQL Server 2005 SP2:

 

 

The restart computer task that comes after the install software task in the task sequence and we're all done:

 

Verifying the task sequence did what it was supposed to

The OSD home page gives you a good overall view of the task sequence status and by viewing the Web reports you can even more detailed information about whether or not the task sequence was successful. This part is just verifying that the task sequence did what it was supposed to do—install SQL Server 2005 and then SQL Server 2005 SP2.

On the targeted computer, open up SQL Server Management Studio (it is there now right?!), right-click the server name and click Properties. The General page of the server properties should look something like this:

 

 

Here you can verify some of those setup.ini file settings that we told SQL setup to use, and ensure that SP2 was successfully installed—the version number should be 9.00.3042.00. If it says something like 9.00.1399.06 (SQL Server 2005 RTM) then you know the service pack installation didn't stick. Here's a handy link while I'm thinking about it: How to identify your SQL Server version and edition.

You should also check the SQL Server setup and service pack setup summary logs. If you used my .ini file, then these logs are at the following locations respectively:

  • %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt
  • %Program Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Summary.txt.

And that is that. This is just one example of how to use task sequences to install software outside of normal OSD tasks, but I hope it has been helpful and gets you thinking of other ways that you can use this powerful new feature of Configuration Manager. Happy task sequencing!!!

~Jeff

 

 

Posted Friday, October 31, 2008 2:33 AM by jgilbert | with no comments

Deploying SQL Server 2005 with SP2 Using an OSD Task Sequence (Part 1)

One of the coolest features (I think) of Configuration Manager is the ability to deploy software using task sequences, but because task sequences are generally associated with operating system deployment, a lot of people never really use them unless they're deploying new operating systems. Because of that, I thought I'd write up a few posts on how to use task sequences to install software, when not deploying operating system images, to kind of highlight the things you can do with it.

If you're not familiar with task sequences, then you should probably go check this link out first: About Task Sequences

One important paragraph in that topic that relates to this post is:

Although task sequences are essential for operating system deployment, they can also play a vital role in other Configuration Manager 2007 tasks. The power of task sequences lies in their flexibility and administrators can use these to configure client settings, distribute software, update drivers, edit user states, and perform other tasks independent of operating system deployment.

I install Configuration Manager site hierarchies all the time in my lab, and one of the prerequisites for installing a Configuration Manager 2007 primary site is a supported version of SQL Server to host the site database. Installing SQL Server 2005, and then SP2 on top of that, usually takes up a good bit of time and requires a few reboots so I thought this would be a good example to use. I also do most of my installations using scripted installation methods and finding a SQL setup.ini file to use to support Configuration Manager specific requirements was also hard for me to do so I've included that in this post as well.

One thing to take note of here is that task sequences that install software run normal Configuration Manager software distribution package programs and that's what the remainder of this post is about—creating the required packages and programs to install SQL Server 2005 and SQL Server 2005 SP2. These packages and programs can be advertised outside of OSD task sequences and will run just fine. Of course, you're probably now wondering why I'm writing a whole post about creating packages and programs and then using a task sequence to deploy them instead of normal software distribution. Here are a couple of reasons why:

  • The installation process is fully automated—including rebooting the computer. Chaining software distribution installations that require the computer to reboot is just generally not fun. Task sequences handle planned reboots with ease and ensure that all files are installed correctly with no files from a previous installation still requiring a reboot.
  • The entire task sequence installation is easily tracked. Each step along the way is viewable in Web reports and the overall status of the task sequence is displayed in the operating system deployment home page in the console.
  • The package source files are not leftover on the client machine after the task sequence runs (this depends on an advertisement setting that I talk about in part 2).
  • …and probably more things that I can't think of right now, but the above reasons are enough for me anyway.

The first thing that you need to do before deploying SQL Server 2005 and SQL Server 2005 SP2 using a task sequence is to create the packages and programs that the task sequence will use. This part is fairly straightforward software distribution stuff so I'm not going to go into much detail except to point out the important parts.

NOTE: Before using these programs to install software using a task sequence, ensure that you have tested them running as normally advertised programs. When programs run as part of a task sequence, user interaction is generally not possible. This means that if an error or warning dialog pops-up that you can't see, the task sequence will fail without completing the installation, and you'll have a hard time figuring out why!

  

Creating the SQL 2005 Package

The package source is just the files from the SQL Server 2005 RTM installation media that I've stored in a server share on my site server computer. The interesting bits are all in the program properties.

General Tab

First, there's the command line that I'm using that requires some explanation:

Servers\setup.exe /settings \\siteserver\sql$\SetupSQL.ini /qb

The actual setup.exe file is located in the Server subdirectory of the installation source files and I'm using the SQL Server setup /settings command line option to specify a setup.ini file for SQL Setup to use when during installation. Because you have to specify a complete path to the .ini file, and determining what that path will be at runtime can be difficult (especially when deploying through a task sequence), I've decided to use a hidden share on the site server to host the .ini file. That way, I always know where the .ini file is, and I can change it as required from a central location later without having to change the package source files. Another good reason to do this is because you don't want just anyone finding the source files and also getting the installation product key at the same time unless you like a lot of unplanned SQL installationsJ.

About the .ini file settings that I'm using

I looked around for a example of a SQL Server setup.ini file and couldn't find one that completely fit my requirements for installing SQL to support Configuration Manager so I made my own. I decided to just install everything that I thought I might ever need all the way up to Configuration Manager 2007 R2 installations.

The .ini file I'm using is here (rename the extension from .txt to .ini before using), but specifically, the .ini file does this:

  • I'm not specifying an installation key as the media that I'm using doesn't require it (not that I'd show you the key I used anyway!)
  • Installs SQL components to their default locations. I'm just doing this in a lab so I've commented out the installation directory options in the .ini file. If you want to change where the SQL bits are installed, just uncomment (take out the ;) and enter the locations you want to use.
  • The ADDLOCAL line tells SQL to install:
    • SQL database components
    • SQL replication bits (in case I want to play with SQL replication later)
    • Reporting Server components (for SQL reporting services integration with R2)
    • Client components so I can manage the SQL installation locally
    • SQL tools and books online (of course you want the technical documentation right?)
  • Use the default instance for the installation (MSSQLSERVER)
  • Run all of the SQL services under local system (not a SQL Server best practice, but this is for a lab installation and I don't want to deal with setting an SPN for the account every time I use this .ini to setup SQL). I also set all the services to autostart.
  • Sets the collation of the installation to SQLCOLLATION=Latin1_General_CI_AS.
  • Configures the reporting services installation to the default configuration.
  • Sets the installation to use TCP/IP communication and not named pipes.
  • Ops out of error and SQM reporting for the instance.

Another option for this program command line could be an actual SQL Server setup command line like the one below (this is just an example command line and does not do what the .ini file I'm using does—this would all need to be on one line too):

.\Servers\setup.exe /qb INSTANCENAME=MSSQLSERVER ADDLOCAL=ALL SQLACCOUNT=""NT AUTHORITY\SYSTEM"" AGTACCOUNT=""NT AUTHORITY\SYSTEM"" SQLBROWSERACCOUNT=""NT AUTHORITY\SYSTEM"" RSACCOUNT=""NT AUTHORITY\SYSTEM"" ASACCOUNT=""NT AUTHORITY\SYSTEM"" SQLAUTOSTART=1 AGTAUTOSTART=1 ASAUTOSTART=1 RSAUTOSTART=1

Of course, that won't all fit in the command line text box in the program properties, so you'd have to write a .bat or .vbs or some other wrapper to pass the command line. Besides, setup .ini files are more TechSexy than setup command line options J

Note: If you're interested in customizing your own .ini file to use for SQL Server setup, you could take a look at the template.ini file that ships with the SQL Server setup files in the .\Servers\ directory. This template also gives command line examples and additional information about .ini file settings that I haven't talked about.

Requirements Tab

I always set the Maximum allowed run time (minutes) option to Unknown. This might cause the installation to run over maintenance windows, but if I'm going to deploy SQL Server I don't want it timing out on me and I should probably have a pretty good idea of which servers I'm sending this installation package to so I know what to expect.

Environment Tab

I set the program to: run whether or not a user is logged on, run with administrative rights, and run with UNC name.

Advanced Tab

I'm not going to advertise this program because I'm only going to deploy it through a task sequence, so I enabled the Allow this program to be installed from the Install Software Task sequence without being advertised option.

  

Creating the SQL 2005 Service Pack 2 Package

The package source is just the SQL Server SP2 installation file stored on the site server computer. This package is much simpler than the actual SQL Server installation package, but I'll review what I'm doing to install SP2 here.

General Tab

The command line I use for the actual SP2 installation is just a normal command line install of SP2: SQLServer2005SP2-KB921896-X86-ENU.exe /quiet /basic /allinstances.

Requirements Tab

I always set the Maximum allowed run time (minutes) option to Unknown for service pack installations so it won't time out on me. Again, this might cause the installation to run over maintenance windows, but I'm OK with that.

Environment Tab

I set the program to: run whether or not a user is logged on, run with administrative rights, and run with UNC name.

Advanced Tab

I'm not going to advertise this program because I'm only going to deploy it through a task sequence, so I enabled the Allow this program to be installed from the Install Software Task sequence without being advertised option.

So at this point, the required packages and programs are created to install SQL Server 2005 and SP2. Now you need to make that sure that you get the packages successfully to a distribution point that you'll use to get the bits during the actual task sequence steps.

While those packages are being copied to distribution points, we can get started working on the actual task sequence that will be used to install SQL Server 2005 and SP2... but you'll have to wait for me to finish Part 2 of this post to see how to do that first!

~Jeff

 

 

Posted Friday, October 31, 2008 2:01 AM by jgilbert | 1 comment(s)

Getting Required Updates on Non-Internet Connected SUPs (Part 2)

[BEGIN EDIT]

I've discovered something interesting that might make the remainder of this post moot.

When I installed WSUS for the SUP on the disconnected network and used the WSUS local internal database I could not use the download software updates wizard to obtain updates from the local Wsuscontent directory on the SUP (thus this post below). However, after writing the below post I did some additional testing and during WSUS installation for the disconnected SUP if I configured WSUS to use the SQL instance hosting the site database, and then approved required updates in the Internet-connected WSUS server (which downloaded them to the local wsuscontent dir on the Internet-connected WSUS), and then copied the wsuscontent dir to the WSUS installation on the disconnected network I COULD use the wsuscontent directory as the local source to download required updates. 

I'm going to leave the remainder of this post here because it has some other useful information like running scripts to directly query the site database for information, but the bottom line is this:

From my additional testing after writing the below post, it seems that if you use the SQL instance to host the WSUS database on the disconnected SUP, you do not need to go through all of the effort below and can just download updates locally from a synchronized wsuscontent dir containing the updates as downloaded by WSUS.

[END EDIT]

 

 

Before you get to this point:

  • You need to have successfully synchronized your active SUP with the latest and greatest software updates metadata from an Internet-connected WSUS Server (see: this post).
  • Enabled the software updates client agent for the site.
  • Have clients that have run software updates scans and reported in updates as required.
  • Optionally, but recommended, created the v_RequiredUpdates view according to this post.

Now that you know which updates are required (part 1), you have to figure out how to get them on to the non-Internet connected software update point so you can deploy them. To do this, you can use a script to query directly against the site database. This example uses a .vbs script to write the required update source url properties stored in the site database for required updates to a .txt file. Then, on an an Internet-connected computer, that .txt file is used as a source file for another script that reads the .txt file and downloads all the updates listed in the file. After the updates are downloaded, they can then be copied somehow back to the non-Internet connected site and used as a local source to download software updates.

I'm running the query directly against the site database because the source url property is stored in a site database table and not a view. That makes it difficult to query using standard WMI scripts that go through the SMS Provider for information. I didn't want to change the site database any more than necessary so I figured that this would be the way to go, plus it's a great example of how to do it.

Note: I didn't write this script completely on my own, but instead modified one that I found here: http://www.technowledgebase.com/2007/06/12/vbscript-how-to-create-an-ado-connection-and-run-a-query/

I'm not going to post the entire script here (runSQLQuery.txt is located here for download—you'll need to modify it a little bit and rename the extension from .txt to .vbs before trying to use it), but here's what it does in a nutshell:

  • Connects to the site database using integrated security
  • Runs a query to find the source url property for required updates that haven't been downloaded yet (runs the query created in part 1)
  • Writes the source url properties to a .txt file called SourceURLs.txt
  • Displays a pop-up message box to tell you how many update source urls were written to the text file.

The only part of the script that you must change is the source database and server entries in the connection object line. Otherwise, the script will run 'as is', but if you want to change the default behavior up a little, there are really only two other lines that you will need to modify (if you choose to):

Required change(s):

  1. objCN.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=SMS_XYZ;Data Source=SiteServer"
    The default security settings will connect to the site database using integrated security. If you want to use a username & password to connect to SQL, then add in this after Persist Security Info=False: User ID=<account>;Password=<password>; and take out the Integrated Security part.
    You also need to change the site database name (Initial Catalog), and the site database server name (Data Source) to match your requirements.

Optional changes:

  1. strSQL="SELECT DISTINCT dbo.v_RequiredUpdates.SourceUrl FROM dbo.v_RequiredUpdates"
    If you didn't create the view, you can just comment out the line about querying the view and uncomment the full SQL query. In case you missed it from part 1, here it is: (watch for word wrap, this must all be on one line when used in the script):

    strSQL="SELECT DISTINCT TOP (100) PERCENT dbo.CI_ContentFiles.SourceURL FROM dbo.v_Update_ComplianceStatusAll INNER JOIN dbo.v_UpdateContents ON dbo.v_Update_ComplianceStatusAll.CI_ID = dbo.v_UpdateContents.CI_ID INNER JOIN dbo.v_UpdateInfo ON dbo.v_UpdateContents.CI_ID = dbo.v_UpdateInfo.CI_ID INNER JOIN dbo.CI_ContentFiles ON dbo.v_UpdateContents.Content_ID = dbo.CI_ContentFiles.Content_ID WHERE (dbo.v_Update_ComplianceStatusAll.Status = 2) AND (dbo.v_UpdateContents.ContentLocales = 'English' OR dbo.v_UpdateContents.ContentLocales = ' ') AND (dbo.v_UpdateInfo.IsExpired = 'False') AND (dbo.v_UpdateContents.ContentProvisioned = 0)"

  2. strOutputFile = "SourceURLs.txt"
    By default, the script will write the download location for required updates to a .txt file named SourceURLs.txt and that file name is referenced when using the download script. If you want to use a different text file name for some reason, then just change the strOutputFile value to whatever you want to call it. You'll have to remember to use the new file name for the download script too.

OK, all that out of the way, just double-click the .vbs and it should reward you with a bunch of required updates source urls written to a text file in the same directory that the script was run from and display a message similar to:

Note: Open SourceURLs.txt and backspace the last line so there is no empty line at the end. If you don't do that the download script still works correctly, but throws an error at the end when it sees the empty line.

Now it is time to take that text file (SourceURLs.txt), go to a computer with an Internet connection, and run script number two. The second script is the one that does all the hard work for you. It reads the source urls text file and downloads the updates into the same directory the script was run from. Just in case you wanted to test this, I've included a sample SourceURLs.txt file with three update download locations here.

This script came from a few hours of me banging my head against the wall trying to figure out how to do it properly and then realizing that the other side of the wall was literally the office of one of our talented SMS SDK writers…problem solved. The download script doesn't require any modifications at all other than some error logging that I didn't add into the script and it can be found here.

This time you need to open a command prompt and use the following command: cscript downloadUpdates.vbs SourceURLs.txt.

If all goes to plan after running the script, you should see something like the below screen shot and the folder the script was run in should now contain all of the downloaded updates:

 

 

Now you just need to somehow get that directory full of source files to the non-Internet connected site, share it out, and then go through your normal ConfigMgr software updates processes. When it comes time to deploy those updates, just select the option to download source files from a location on the local network and point the wizard to your local source files share:

 

 

Some random last thoughts about these posts that I've neglected to mention so far:

  • Why do we need these scripts? Because you can't just synchronize a WSUS Server and then sync that WSUS Server's downloaded update content with the WSUS Server hosting the SUP site role…well, you can, but you won't be able to download updates using the WSUS content files for distribution via ConfigMgr because the software updates download wizard won't recurse through the various update directories that the WSUS Server creates when it stores downloaded updates. Same goes for using a different SUP server's package source directory (another script on the back burner for me).
  • This technique will download all updates that clients have determined that they require through software updates scanning. You might not want to deploy every update that a client has found it requires so this might end up just using up hard drive space on your server with a bunch of unnecessary updates. Of course, you could just delete the original source directory contents after you create the deployment package, but the next time you run through this process those same updates will reappear as they're still required and haven't been downloaded yet. I figured it was better to have all the updates there just in case you ever ended up needing them than to have to go through this process all the time for smaller groups of updates. That being said, another option is to only download required updates that haven't been downloaded yet that exist in defined update lists. I'm working on that, but it has taken me a while to get to this point and to be honest I really need to get back to my day job J
  • These scripts are really more of an example and they'll work 'as is', but you might want to refine them as needed later on.

I hope you've found these posts (or at least some part of them) helpful and this technique gets your disconnected SUPs sync'd, and your clients patched!

~Jeff

Posted Wednesday, October 29, 2008 6:08 AM by jgilbert | with no comments

Getting Required Updates on Non-Internet Connected SUPs (Part 1)

Before you get to this point:

  • You need to have successfully synchronized your active SUP with the latest and greatest software updates metadata and the WSUSContent directory from an Internet-connected WSUS Server (see: this post).
  • Enabled the software updates client agent for the site.
  • Have clients that have run software updates scans and reported in updates as required.

Now that you know which updates are required, you have to figure out how to get them on to the non-Internet connected software update point so you can deploy them. The first part of all of this (part 1 of this post) is to query the site database to find all the required updates that you want to download. After you get that far, you'll need to use some handy .vbs scripts to actually do all the hard work and download the updates from a remote computer with an Internet connection—that will be part 2.

Technically, you don't have to create this view, but it makes the syntax of the script to come easier with the added bonus that you can use the view to create a Web report to view more information about the required updates other than just their download location.

NOTE: If you use this view to create a Web report, don't forget to grant the webreport_approle application role the required select permissions!

Here's the query that I'm using for the view and an explanation of what I'm actually querying on. Basically, what I'm after are required updates that haven't been downloaded yet and aren't expired (we can't deploy expired updates with ConfigMgr). If you create this view, make sure that you call it v_RequiredUpdates because that's what the script in the next post will query on:

 

SELECT DISTINCT
     TOP (100) PERCENT dbo.v_UpdateInfo.DatePosted, dbo.v_UpdateInfo.BulletinID, dbo.v_UpdateInfo.ArticleID, dbo.v_UpdateInfo.Title,

                                     dbo.CI_ContentFiles.SourceURL, dbo.v_UpdateContents.ContentLocales,

                                    dbo.v_UpdateInfo.IsExpired, dbo.v_UpdateContents.ContentProvisioned

FROM                           dbo.v_Update_ComplianceStatusAll INNER JOIN

                                    dbo.v_UpdateContents ON dbo.v_Update_ComplianceStatusAll.CI_ID = dbo.v_UpdateContents.CI_ID INNER JOIN

                                    dbo.v_UpdateInfo ON dbo.v_UpdateContents.CI_ID = dbo.v_UpdateInfo.CI_ID INNER JOIN

                                    dbo.CI_ContentFiles ON dbo.v_UpdateContents.Content_ID = dbo.CI_ContentFiles.Content_ID

WHERE                       (dbo.v_Update_ComplianceStatusAll.Status = 2) AND (dbo.v_UpdateContents.ContentLocales = 'English' OR

                                   dbo.v_UpdateContents.ContentLocales = ' ') AND (dbo.v_UpdateInfo.IsExpired = 'False')

                                   AND (dbo.v_UpdateContents.ContentProvisioned = 0)

ORDER BY                  dbo.v_UpdateInfo.ArticleID DESC

 

Here's what the query does (WHERE statements):

  • Finds all the updates that are displayed as required (dbo.v_Update_ComplianceStatusAll.Status=2)

    (and then)

  • Finds all updates that are either English, or have no language-specific content locale:
    • (dbo.v_UpdateContents.ContentLocales = 'English' OR dbo.v_UpdateContents.ContentLocales = ' ')
    • You'll need to manually add in any additional languages that you want to download updates for here.

    (and then)

  • Finds all updates that are not expired, because you can't download/deploy expired updates (dbo.v_UpdateInfo.IsExpired = 'False')


    (and then)

  • Finds all of those updates that have not been downloaded yet ((dbo.v_UpdateContents.ContentProvisioned = 0)

          (and then)

  • Orders the resulting update information by article ID (ORDER BY dbo.v_UpdateInfo.ArticleID DESC).

 

Here are some .sql files that you can use to make all of this easier. I've had to rename their file extensions from .sql to .txt to upload them, but you'll need to right-click and download them anyway because the database to use needs to be changed to your site database name. After modifying the database to use in the files, change the file extension back to .sql before running them:

  • If you don't really want to go into SQL to create the view, here is a little .sql that will create it the easy way (make sure you change the USE line at the top to use your site database!): Create_v_RequiredUpdates.sql. This one is pretty long so I'm not going to post the contents of it here; you can see what is in there using Notepad.exe though.
  • If you just want to run the query real fast to see what it's supposed to get, run this: View_v_RequiredUpdates.sql. Below are the contents of that .sql:

     

/*** Use this .sql to view the query results AFTER you create the v_RequiredUpdates view ***/
/*** Change the USE and FROM lines to use your site database! ***/

USE SMS_XYZ

SELECT [DatePosted]

,[BulletinID]

,[ArticleID]

,[Title]

,[SourceURL]

,[ContentLocales]

FROM [SMS_XYZ].[dbo].[v_RequiredUpdates]

 

Now that you've got the view returning a list of required updates, the next post will show you how to use that view to actually download them.

~Jeff

 

Posted Tuesday, October 28, 2008 8:24 PM by jgilbert | with no comments

Synchronizing Non-Internet Connected Software Update Points

Because the highest level active software update point for a Configuration Manager site hierarchy must synchronize with Microsoft Update, it can be a little difficult to get this working in non-Internet connected sites. This post explains how to get the latest software updates scan metadata imported into the site database to enable software updates functionality for non-Internet connected Configuration Manager sites and site hierarchies.

This process is already documented in the Configuration Manager documentation library, but I figured that I'd blog the steps that I took as another resource for you. You should probably check out the 'official' version first: How to Synchronize Updates Using Export and Import at: http://technet.microsoft.com/en-us/library/bb680473.aspx .

Note: I'm using WSUS 3.0 SP1 because SP1 is required for Configuration Manager 2007 SP1 sites.

To get started, you'll need to have the x86 version of WSUS 3.0 SP1 (WSUSSetup_30SP1_x86.exe) installed on a computer that has Internet access. This is because the tool used to export and import the metadata only works on the x86 version. Clients don't need to connect to this computer and it can even be a VM. We just need to get the WSUS catalog synchronized with Microsoft Update so we can transfer the scan metadata back to the SUP in the non-Internet connected site. WSUS installation is fairly straightforward so I won't go into it in much detail here. Just be sure that you install both WSUS and the administration console and configure the WSUS Server to store updates locally (we won't actually download any updates on this server, but the update EULAs need to be stored locally in the WsusContent directory so we can transfer them later).

If you don't already have it/haven't installed it yet, to get WSUS 3.0 SP1, head to the Windows Server Update Services 3.0 SP1 download page at: http://www.microsoft.com/downloads/details.aspx?FamilyId=F87B4C5E-4161-48AF-9FF8-A96993C688DF&displaylang=en.

Tip: You don't need to install WSUS 3.0 before installing SP1. Just run the SP1 install and you'll have everything you need.

For the WSUS installation that will synchronize with Microsoft Update, you'll need to install both WSUS and the administration console. For the WSUS installation that will be co-located with the non-Internet connected software update point (if it's not on the site server computer), you only need to install WSUS. The WSUS administration console bits need to be installed on the non-Internet connected site server so we can configure the WSUS settings, but that's a standard software update point prerequisite so I'm guessing you already knew that. If you didn't, the prerequisites for Configuration Manager software update point installation are located in this topic Prerequisites for Software Updates at: http://technet.microsoft.com/en-us/library/bb680712.aspx .

I'm going to break down the following steps and call the WSUS Server installation connected to the Internet the WSUS export server and the WSUS installation supporting the software update point site system for the non-Internet connected site the WSUS import server.

On the WSUS export server:

  1. Install WSUS 3.0 SP1 (WSUS and administration console), open the administration console, click Options, and start the WSUS Server Configuration Wizard. Most of the pages are fairly straightforward, but here they are as well as some things to keep in mind while configuring the WSUS installation:

    1. Decide if you'll participate in the Microsoft Update Improvement Program.
    2. Choose and upstream server to synchronize updates from (you'll want to synchronize with Microsoft Updates).
    3. Configure a proxy server if necessary.
    4. On the Connect to Upstream Server page, click Start Connecting. This downloads the types of updates available, the products that can be updated and available languages. This is going to take a couple of minutes.
    5. On the Choose Languages page, select the languages that you want to download updates for and click Next (it probably doesn't matter what is selected here as we won't use WSUS to download the updates anyway).
    6. On the Choose Products page, select the products that you want to synchronize updates for with Microsoft Update. These should be the products that you want to be able to patch your clients for in the non-Internet connected site later (ie Exchange Server 2007, Forefront Client Security, Windows Server 2008, etc…).
    7. On the Choose Classifications page, select the update classifications that you want to synchronize with Microsoft Update. Once again, these should be the classifications that you want to be able to patch your clients for in the non-Internet connected site later (ie Critical Updates, Security Updates, Updates, etc…).
    8. On the Configure Sync Schedule page, select a sync schedule option. You can either synchronize on a schedule or manually.
    9. On the Finished page, select the Begin initial synchronization option. Click Next and then Finish to begin synchronization. Some quick notes about this process:
    • Grab a Snickers® it's going to be a while before the initial synchronization completes. I found it to be somewhere between around 1.5 to 2 hours.
    • The WSUS database file sizes will increase during this process. The initial database files were 20.1MB and 2 MB for the SUSDB.mdf and SUSDB_log.ldf database files respectively. After synchronization, the database file sizes were 710MB and 61.9MB.
  2. Verify the WSUS installation has completed synchronizing successfully by watching the SoftwareDistribution.log log file (%Program Files%\Update Services\LogFiles\) or you can also check the Synchronizations node of the WSUS administration console.
  3. Next, you need to export software update metadata using the wsusutil.exe utility.
    1. Open a command prompt and navigate to the %Program Files%\Update Services\Tools directory.
    2. Run the following command to export the software updates scan metadata:
      wsusutil export <drive letter>:\export.cab <drive letter>:\export.xml. You don't need to name them export.<extension>, it's just something that I do. You can name them whatever you want, but ensure that you save the export as a .cab. I also save the log file as an .xml because it makes it easier to read later.
    3. This takes about 15 minutes or so. In my little lab installation, the exported files were around 8MB for the export.cab and around 4MB for the export.xml. You won't see much interesting going on, but your command prompt should now display:
      Updates are being exported. Please do not stop this program.
      When it is finished, you'll see this message in the command prompt window: All updates are successfully exported.

On the WSUS import server:

  1. After the metadata export has completed, find some way to copy the export files to WSUS installation that is going to host the software update point for the non-Internet connected site. By this point I'm assuming you already have WSUS 3.0 SP1 and a software update point site system configured for the site. If so, skip to 3, if not go to 2 J
  2. If you're installing WSUS on a soon-to-be software update point site system computer that is not on the site server, you need to install WSUS (no admin console) on the software update point computer and the administration console on the site server computer. However, if you're installing WSUS on the site server computer, you'll need to install the full installation including the Administration Console. If you do need to install the console, do just that—install it, but don't configure it. During installation when the Console Configuration wizard starts—click Cancel. When installing WSUS, take note of these settings:
    1. Do not synchronize from Microsoft Update and do not create WSUS reporting events.
    2. Do not schedule synchronization (we'll do it manually).
    3. Don't worry about the classifications and products settings (you can only configure those if you're sync'ing with Microsoft Update, so we'll handle what metadata we get from the online WSUS Server export files.
    4. Select the languages that you want to download (probably doesn't matter because we'll have to manually download these later).
    5. Finish the WSUS installation wizard.
    6. Install a software update point site system role on the newly installed WSUS Server and review SUP setup logs to ensure it is installed and WSUS is configured successfully: SUPSetup.log and WSUSCtrl.log log files in the ConfigMgr logs directory.
  3. Navigate to the Software Updates node in the Configuration Manager console. Refresh the software updates feature home page and you shouldn't see any software updates information.
  4. Copy the WsusContent directory contents from the export server to the import server's WsusContent directory (C:\WSUS\WsusContent by default).
  5. Import software update metadata using the wsusutil.exe utility so that software updates home page won't be so boring.
    1. Open a command prompt and navigate to the %Program Files%\Update Services\Tools directory.
    2. Run the following command to import the software updates scan metadata:
      wsusutil import <path>\export.cab <path>\import.xml. Of course, you'll need to know where you copied the .cab file that was exported from the WSUS export server at this point.
    3. The import process takes about 15 minutes or so. You still won't see much interesting going on, but during the import process your command prompt should display the following until the import process has completed:
      Updates are being imported. Please do not stop this program.
      When it is finished, you'll see this message in the command prompt window: All updates are successfully imported.
  6. After the import process completes, go back to the Configuration Manager console, expand Software Updates, right-click Update Repository and select Run Synchronization. Some notes about this process:
  • Grab another Snickers® it's going to be a while again. This is the point where the WSUS software update metadata information is being imported into the site database. To view the progress of the import process, you can watch the wsyncmgr.log log file. This log file tells you how many updates (metadata) were imported and even how long it took—this process took 3 hours and 19 minutes in my lab!
  • Another disk space consideration: my lab's site database was 223MB before synchronizing metadata. After synchronizing the updates that were exported from the WSUS export server (remember the 8MB export.cab file?), the site database was 891MB!

Refresh Update Repository and Viola! the updates are listed according to the WSUS metadata settings you configured on the WSUS export server for products (ie Exchange Server 2007, Forefront Client Security, Windows Server 2008, etc…) and categories (ie Critical Updates, Security Updates, Updates, etc…) earlier.

Of course, none of those updates display as required by clients until you have clients, with the software updates client agent enabled, complete the software update scan cycle, but that's another post J

~Jeff

Posted Sunday, October 19, 2008 4:09 AM by jgilbert | with no comments

Setting Up Multicast in Configuration Manager 2007 R2

Steve Rachui is at it again. This time he's explaining how to setup R2's OSD multicasting feature. It's a great post and contains some very important information that you should be aware of if you're going to implement multicasting--definately a must read.

Steve's post is here: http://blogs.msdn.com/steverac/archive/2008/10/19/setting-up-multicasting-in-sccm.aspx 

~Jeff

Posted Sunday, October 19, 2008 3:14 AM by jgilbert | with no comments

Client is Installed Flag Explained

Every now and then you might see someone asking about an SMS or ConfigMgr client being physically installed at the client computer, but not appearing as installed in the admin console. This invariably results in someone else pointing them to their site maintenance tasks configuration—which is generally the culprit. I was doing some things in my lab today so I figured that I'd post a basic description of the behavior.

When a client is installed, the site database is updated with the new client's information. This update also 'sets the client is installed flag' as you see mentioned a lot. What that means is that Client=1 is set in the database for the system. Client=1 means the client is installed and Client=0 means that the client is not installed (you can see this property when viewing a discovered resource's properties by double-clicking the record in the admin console).

Even if you manually uninstall the client software from a computer, the installed flag for the computer is not updated in the site database. When that happens, even though the client is not installed, the site database thinks it is (Client bit still says 1). That's why you might sometimes see clients as installed in the console when you know that the client really isn't installed. This can lead you on all sorts of goose chases trying to troubleshoot client health issues that don't really exist. To ensure that doesn't happen, you need to enable the Clear Install Flag site maintenance task. When that task runs, it looks for client systems that have not talked to the site (sent discovery information) during the client rediscovery period that you've set for the task. By default when the task is enabled, the installed flag is cleared for clients (sets Client=0) that haven't sent in discovery data in over 21 days.

Of course, enabling the Clear Install Flag task can also cause problems of its own. Enabling this task sometimes causes clients that you know are installed, to be displayed as not installed in the console. In other words, the client is installed, but the database has Client=0 for the resource. In the console, these computers show up as installed No, but Approved Yes? If the hardware inventory client agent has been enabled for the site, then checking Resource Explorer for one of these computers also shows that they've sent in hardware inventory reports recently (ConfigMgr clients do not generate DDRs when hardware inventory runs). So now you see a computer resource that says it's not installed, is approved, and has sent in recent hardware inventory information. This can lead to understandable confusion and therein lies the motivation for me to write this post—this scenario occurs if the Clear Install Flag maintenance task has been enabled, but the Heartbeat Discovery method has not been enabled for the site or it has been configured incorrectly.

So, pretending that I haven't already given away the cause, some plausible actions you might take to correct this situation would be to push the client bits out to the system using the client push installation wizard and selecting the option to always install or repair to reinstall the client. This will work initially because once installed, the client will send in a DDR to tell the site that it has happily installed and is functioning. Problem solved you think, but then the next time you look at the client resource, it is back to Client=0. Argh. Continuing with that logic, you might go to that computer, open the client applet and initiate the Discovery Data Collection Cycle, the client will then send in a heartbeat DDR and the resource record is back to Client=1. Great, but eventually the client will be back to Client=0 and you're back where you started from. If your client rediscovery period is set to the default (21 days), then this process could become a lengthy one and affect many clients. Add to the mix that when the site-wide Client Push Installation method is enabled, it will attempt to install the client on any systems assigned to the site that have the Client=0 bit set. If you're in this situation, you might see systems with this issue and then, seemingly at random, it's a whole different set of systems later.

To avoid all of this: If you enable the Clear Install Flag site maintenance task, then make sure that you also enable the Heartbeat Discovery method and schedule the Heartbeat Discovery method's recurrence interval to be shorter than the client rediscovery period for the Clear Install Flag task. This will cause clients to send in heartbeat discovery records to update the database and let the Clear Install Flag task know about their continued existence. If the Clear Install Flag task runs and the site hasn't received a heartbeat DDR from an installed client during the client rediscovery period defined for the task, it will set the client installed bit to 0 and we all know what happens after that occurs.

Bonus tip: Sometimes the name of the Clear Install Flag task throws people, but if you watch the smsdbmon.log (the log file that records scheduled maintenance task actions) then you'll see that in the log this task is called: clear undiscovered clients. So it's probably easiest to think of that task as 'the task that clears the installed flag for clients that haven't been rediscovered'. Add 'by heartbeat discovery' to that mental definition and you're in business.

Hope this helps,

~Jeff

Posted Saturday, October 18, 2008 11:19 PM by jgilbert | 2 comment(s)