It's strange how there are often many facets of a product that you can go
entire years without using or caring about, then life changes and its time to
dig into something new. All the more fun (well, interesting perhaps) when it's a
new product and there isn't a lot published on the subject. As you may now we
here at SQLServerCentral.com have opened a
Orlando and I manage the day to day training operations. Part of those
operations is to rebuild the machines after each class and generally to install
SQL Server 2005 - the topic of this article.
First, a little background. When we first set up the office we knew that we
would need to rebuild the machines at times, and that the easier it was the
better. We debated alternatives for applying the OS and settled on
Installation Services (RIS), a free service built into Windows servers. If
you've never seen it in action, using it is pretty easy. You boot directly from
a network card, supply credentials, then you can see a list of available images.
Select an image and then move on to another task. A basic Win XP Pro install
with SP2 installs in 30-35 minutes on our network. If you want to learn RIS I'd
recommend setting aside a day. It might go faster, might not.
In terms of strategy we thought we had two options; use one base image and
then software ala cart after installing the image, or customize images so that
it's all done at once (for example, we might have an XP2 with SQL 2000, XP2 with
SQL 2005, etc). The latter option seemed painful if we wanted to make a core
change, such as adding an application to all of our images, but would probably
be faster when we needed to rebuild machines. Figuring that we can always change
later, we went with the simple plan - one image.
For the first classes I used RIS to install the image, each time tweaking it
a bit as we found things we wanted changed or added. I would then run through
the SQL 2005 setup wizard to manually set the options, start it, move to the
next machine (nine total), and once done go back to work for an hour or so. Then
I'd return and run a batch file that applied SP1. I think it probably takes 45
minutes or so to get the SQL 2005 install going manually on nine machines. Not a
huge amount of time, but over the course of a year it would add up - and some
weeks I just really wish I had that 45 minutes back!
I did a quick search and didn't find a lot of non MS material, the most
interesting one I did find was
this one from Devx. I
don't want to steal the authors thunder, but the quick recap is that there is a
way to capture your install settings to a file but you have to edit that file
before you can use it for an unattended install. I'm tempted to rant about MS, but we'll
save that for another day.
There are two different options for unattended install supported by SQL 2005.
You can pass a ton of parameters on the command line, or you can provide a INI
file that has the parameters in it. If you look in the Servers folder on the SQL
2005 disk you'll see template.ini, which contains most of the information you'll
need to build your own INI. I thought having stuff in the INI looked easier to
work with so I started work. I'm providing a link here to the
template.ini in case you don't have media available.
What I wanted to do was install everything. We teach classes across a
wide range of features, easier to just install them all, including the
Adventureworks database and sample code. When you glance at the INI it looks
like it will be easy, just set ADDLOCAL=All and life is good. Not quite it
seems. I ended up installing SQL 2005 about 5 times to get it the way I wanted, each
time trying to fix something that was missing. I won't claim that the following
file is the right way to do it or the best way, but it somehow does get
everything installed when you're doing a new install of a default instance.
;Note: Semi-colons are used to comment out a line. The ADDLOCAL line below must be one very long line. ;Feature names are case sensitive and there can be no space after the comma. ADDLOCAL=SQL_Engine,SQL_Data_Files,SQL_Replication,SQL_FullText,Analysis_Server,AnalysisDataFiles, RS_Server,RS_Web_Interface,Notification_Services,NS_Engine,NS_Client,SQL_DTS,Client_Components, Connectivity,SQL_Tools90,SQL_WarehouseDevWorkbench,SDK,SQLXML,Tools_Legacy,SQL_Documentation, SQL_BooksOnline,SQL_DatabaseSamples,SQL_AdventureWorksSamples,SQL_AdventureWorksDWSamples, SQL_AdventureWorksASSamples,SQL_Samples USERNAME=Your User Name COMPANYNAME=Your Company Name PIDKEY=AAAAABBBBBCCCCCDDDDDEEEEE INSTANCENAME=MSSQLSERVER SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS ASCOLLATION=SQL_Latin1_General_CP1_CI_AS sQLACCOUNT="NT AUTHORITY\SYSTEM" AGTACCOUNT="NT AUTHORITY\SYSTEM" ASACCOUNT="NT AUTHORITY\SYSTEM" RSACCOUNT="NT AUTHORITY\SYSTEM" SQLBROWSERACCOUNT="NT AUTHORITY\SYSTEM" SAMPLEDATABASESERVER=. RSSQLLOCAL=1 RSCONFIGURATION=Default |
The things I was missing initially were the Adventureworks and Reporting
Services database, plus the Reporting Services virtual directory. The last three
lines above corrected that. Without them the files get copied to your drive, but
not attached/installed as needed for you to use them immediately. You'll note
that I opted to use the System account rather than a domain account, for
training this is usually good enough and we have domain service accounts
prepared that can be used if needed.
Once you've got your INI built, you'll need a batch file containing the
following (which assumes you are in the folder containing setup.exe AND it
contains your INI, you can fully qualify both if needed):
start /wait setup.exe /settings your.ini /qn
This will run a quiet install with no dialogs and if things work, you'll be
ready to go. If things do go wrong, sometimes you can find some helpful
information in C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files, where each install attempt will have a series of files
starting with SQLSetup0001, SQLSetup0002, etc. If you're using the INI I
provided above, verify that the SQL Service is running and that you can see the
AdventureWorks, AdventureWorksDW, ReportServer, and ReportServerTempDB
databases. Then confirm that Reporting Services is fully installed by opening
http://localhost/reports in a browser.
If you're trying to get a different feature set and struggling to get it
right, use the technique suggested in the external link I mentioned earlier that
allows you to 'record' all your settings. Then you can look through that to
figure out what you need (or don't need) in your INI. One note that may help
you, you typically only need to provide parameters that don't have defaults or
where you want to override the defaults.
If you've made it this far it's easy to do the rest! The next step is to
install SP1. No reason not to at this point and you want the fixes. If you've
installed full text search (which I have), SP1 requires it be stopped first.
That means we need three lines in our SP1 batch file:
net stop msftesql SQLServer2005SP1-KB913090-x86-ENU.exe /ALLINSTANCES /QUIET net start msftesql
The first line stops the full text search service, the second line applies
SP1 to all instances (you can change that if you need to) with no
prompts, and the third line of course restarts the full text search.
Our last step is to actually install the samples. I know that sounds
confusing, but selecting sample code in the install just puts the MSI containing
the samples on your drive. You have to run the package to actually get the
source code and supporting files out where you can use them. Luckily, you only
need one line to do that:
msiexec /i "C:\Program Files\Microsoft SQL Server\90\Tools\Samples\SQLServerSamples.msi"
/qn
You could easily put all of that into a single batch file. Run it, then walk
away. I'm installing from a network share on a 100m switched network, seems to
take at least an hour to install and apply the service pack. This article was
based on the the Developer edition, depending on what you install not all of the
features listed in the INI above may be available. Even if you're not installing
SQL 2005 on enough servers to need an unattended install you may have enough
developers that they will find this setup useful. If you've built other
variations of INI's or command lines for unattended, I hope you'll post them in
the discussion forum attached to this article - you may save someone else some
time and headache.