Jeff Gilbert's Web blog at myITforum.com

This posting is provided "AS IS" with no warranties, and confers no rights :-)
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

 

 

Published Friday, October 31, 2008 2:01 AM by jgilbert

Comments

# re: Deploying SQL Server 2005 with SP2 Using an OSD Task Sequence (Part 1)@ Sunday, December 14, 2008 2:46 PM

Hey Jeff,

YOU ROCK, DUDE!! Thanks for blogging this...you always go "above and beyond" the call of duty!!

I wanted to say that I think this would be a great addition to this particular blog entry - www.devx.com/.../1763

tmartin1994