Ok so here is my problem. We are rolling out SQL Server 2005 in the
coming weeks. The server side will require me to update/upgrade/clean
install about 30 SQL Servers and process will be the topic for the next
blog entry. But now I must also deploy the SQL Server Management Studio
and related tools to over 40 programmers located in 3 different
countries and six time zones. Further to that fact, my programmers are
a surly bunch and don’t always follow the rules (sound familiar???). So
if I give them the disks, they will probably install everything and
then call me when it doesn’t work.
So how do I control what they are installing and do this without
killing myself in the process? Going around to each desktop is out of
the question, I am too busy to do this and I don’t have any lackeys to
do it. I don’t know about you but this screams for automation. In SQL
2000, we could create an ISS file and run the install. However, SQL
2005 made that a bit more difficult or did they? Actually, it is quite
hidden but still possible. To do a silent install we will create a
template.ini file and call the setup application and tell it to use
that template.
Ok, so now what do I want them to have? For our world it is pretty
basic. They need the SQL Server Management Studio, SQL Server
Integration Services, SQL Books Online, SQL Client Network Components,
and SQLXML. Nothing fancy here. While we have a few who use MS
Reporting Services, I can help them as they are in the same building as
me. We don’t use MS Notification Services and I don’t want them to
install the developer or personal editions on their local machines.
So now I can start to modify the TEMPLATE.INI file.
A shell file is provided by Microsoft and can be found on your install
disk #1 on the root. Copy this file to your local to modify. Better
safe than sorry. For my purposes, I will be creating two files—one for
server installs (SERVER_TEMPLATE.INI) and one for workstation installs (WORKSTATION_TEMPLATE.INI)
Just open the new file (WORKSTATION_TEMPLATE.INI)
with Notepad or your favorite text editor. It is pretty well documents
but I will just cover the sections that we are installing for our
client workstation install.
The first thing we need to modify is the USERNAME and COMPAMYNAME
parameters. I have left in Microsoft’s comments about the parameter but
in your final file you can easily remove the explanatory text and make
a very compact file. However, I would recommend leaving it. My memory
isn’t as good as it once was.
|
[Options]
;--------------------------------------------------------------------
; If USERNAME or COMPANYNAME are not specified, the default
; operating system username and company name values for
; the server are used.
; Note: If names contain spaces, surround the names with quotes.
USERNAME=YourUserNameHere
COMPANYNAME=YourCompanyNameHere
|
If you put a semi-colon in front of a line, that will remark the line out or tell the system to ignore that line.
Next you need to input your product key by inserting your key in the PIDKEY parameter.
|
; PIDKEY specifies the Product Identification Key.
; Usage: PIDKEY=ABCDE12345FGHIJ67890KLMNO (This is not an actual
; key value.)
; NOTE: PIDKEY is not required for SQL Server Express Edition.
; NOTE: Do not include "-" in the PIDKEY.
; NOTE: This is not a real key. It is faked; you need to enter your own key here.
PIDKEY=B4H74BJX3P26RX2J8TTBH9RMJ
|
When entering your key, don’t put in the hyphens (-).
Next, we need to tell it where we want the files installed by specifying the INSTALLSQLDIR. For me the default location works very well 99% of the time.
|
; INSTALLSQLDIR specifies the location for the instance specific binary files.
; Default location is: %ProgramFiles%\Microsoft SQL Server\
; To use the default path, do not specify the following parameter.
; NOTE: INSTALLSQLDIR is REQUIRED for clustered installations and
; must point to a local drive for which the drive letter exists on
; all nodes in the cluster definition.
; NOTE: If there is a space in a specified path, surround the path with quotes
; and end every path with a '\'.
INSTALLSQLDIR=C:\Program Files\Microsoft SQL Server\
|
Now we get to the meat of the system. Next we tell the install program what tools and utilities we want installed. This is the ADDLOCAL key.
|
; ADDLOCAL specifies which components to install. If ADDLOCAL is not
; specified, setup will fail.
; To install all components specify 'ADDLOCAL=ALL' on the command line.
; ADDLOCAL Rules:
; Features are case sensitive.
; To use ADDLOCAL, provide a comma-delimited list with no spaces of the
; features to install.
; Selecting a parent feature only installs the parent feature, not the parent and the child.
; For example: SQL_Engine is the parent feature, SQL_Data_Files is the child feature.
; Installing the child feature automatically installs the parent feature.
; Removing the parent feature removes the parent and all child features. See BOL for
; more information on this topic.
ADDLOCAL=SQL_DTS,Client_Components,Connectivity,SQL_Tools90, SQL_WarehouseDevWorkbench,SQLXML,SQL_Documentation,SQL_BooksOnline
|
Note: ADDLOCAL should exist all on one line.
The next two sections I added for those rogues who may have already
downloaded and installed the Developer or SQL Express Edition. It is
our company policy to not do this but since they are administrators on
their local machines there is nothing preventing them from that other
than the honor code. In your general template file, you probably won’t
need these if you are just installing the tools. So you could remark
them out.
|
To use the *AUTOSTART features, specify 1 to start automatically or 0 to
; start manually.
; NOTE: If you decide to start SQL Agent automatically, then SQL is also
; started automatically.
; If *AUTOSTART is not specified on the command line, startup is set
; to manual.
SQLBROWSERAUTOSTART=0
SQLAUTOSTART=0
AGTAUTOSTART=0
ASAUTOSTART=0
RSAUTOSTART=0
;--------------------------------------------------------------------
; To use Mixed mode authentication, use SECURITYMODE=SQL.
; If SECURITYMODE=SQL is not specified, then Windows Authentication only
; will be used by default.
; If you use SQL Authentication, you are REQUIRED to provide a strong
; system administrator (SA) password.
; If you use Windows Authentication, the SA account will be disabled.
; To set SA password specify SAPWD.
; Again these are fake passwords, you should enter your own here.
SECURITYMODE=SQL
SAPWD=98PoB3g$$#
|
The next section determines how the workstation will communicate
with the SQL Server. For us, we prefer to use TCP/IP and NetBIOS
disabled.
|
; The DISABLENETWORKPROTOCOLS switch is used to disable network
; protocol for SQL Server instance.
; Set DISABLENETWORKPROTOCOLS = 0; for Shared Memory= On,
; Named Pipe= On, TCP= On
; Set DISABLENETWORKPROTOCOLS = 1; for Shared Memory= On,
; Named Pipe=Off (Local Only), TCP= Off
; Set DISABLENETWORKPROTOCOLS = 2; for Shared Memory= On,
; Named Pipe=Off (Local Only), TCP= On
; Note: DISABLENETWORKPROTOCOLS if not specified has the
; following defaults.
; Default value for SQL Evaluation/Developer:
; DISABLENETWORKPROTOCOLS =1
; Default value for Enterprise/Std/Workgroup:
; DISABLENETWORKPROTOCOLS =2
DISABLENETWORKPROTOCOLS=2 |
I am sure you are wondering what Shared Memory is all about because
it is always on. From Books Online: Shared memory is the simplest
protocol to use and has no configurable settings. Because clients using
the shared memory protocol can only connect to a SQL Server instance
running on the same computer, it is not useful for most database
activity.
Now I understand why Microsoft wants the next but I always configure ERRORREPORTING and SQMREPORTING as disabled.
|
;--------------------------------------------------------------------
; If you enable ERRORREPORTING, SQL Server, SQL Server Agent
; and Analysis Server will be configured to automatically send
; a report to Microsoft if a fatal error occurs. Microsoft uses
; error reports to improve SQL Server functionality, and treats
; all information as confidential.
; To enable error reporting specify ERRORREPORTING=1
; By default ERRORREPORTING=0
ERRORREPORTING=0
;--------------------------------------------------------------------
; If you enable SQMREPORTING, SQL Server Products will be
; configured to automatically send periodic reports to Microsoft
; on feature usage. These reports are used to better understand
; how our customers use our products: which SQL services are installed,
; which features are being used, database deployment, use, and
; maintenance patterns, etc. This will help us determine how to improve
; reliability and how to better target resources in ; future offerings.
; To enable error reporting specify SQMREPORTING=1;
; By default SQMREPORTING=0
SQMREPORTING=0
|
So that is all we need for our WORKSTATION_TEMPLATE.INI. In its simplest form it would look like:
[Options]
USERNAME=YourUsername
COMPANYNAME=YourCompanyName
PIDKEY=B4H78YOURKEYHERE
INSTALLSQLDIR=C:\Program Files\Microsoft SQL Server\
ADDLOCAL=SQL_DTS,Client_Components,Connectivity,SQL_Tools90,
SQL_WarehouseDevWorkbench,SQLXML,SQL_Documentation,SQL_BooksOnline
SQLBROWSERAUTOSTART=0
SQLAUTOSTART=0
AGTAUTOSTART=0
ASAUTOSTART=0
RSAUTOSTART=0
SECURITYMODE=SQL
SAPWD=YourSAPasswordHere
SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS
DISABLENETWORKPROTOCOLS=2
ERRORREPORTING=0
SQMREPORTING=0 |
NOTE: Again the ADDLOCAL line should be all on one line.
Now that our WORKSTATION_TEMPLATE.INI file is setup, we need to have the SQL installer use it.
To make life easy, I have copied the install disks to the network
along with my WORKSTATION_TEMPLATE.INI file which I put in the Disk1
directory.
Now, I map to the network drive and switch to that drive mapping and run the following command from a command line
| start /wait setup.exe /qb /settings Y:\2K5\StdEdition\Disk1\workstation_template.ini |
Actually, if I really wanted to make life easy, I would create a
batch file call install.bat and have it map the network drive, and
start the install program.
net use Y: /d
net use Y: \\msw2k3-san1\devarchive$
Y:
cd 2K5\StdEdition\Disk1
start /wait setup.exe /qb /settings Y:\2K5\StdEdition\Disk1\workstation_template.ini |
Then, I would have the network admin add that batch file to their
network login.bat file that we use each time they login into the
network.