Art of the DBA Rotating Header Image

March, 2013:

Getting Tooled

This week Tom LaRock(@sqlrockstar) tweeted a question, followed by a full on blog post and survey, asking folks if they installed client tools on their servers.  My answer was pretty blunt:

 

This got wrapped up in a larger discussion about whether or not installing client tools is appropriate, with some strong (and not necessarily wrong) opinions on either side.  I confess I didn’t get involved, mostly because I find it hard to have a serious discussion in 140 character snippets.  So now I’ll blog about it! 

I’m the kind of DBA that is a “jerk”.  I say no a lot and prefer, in production, not to take any more action than absolutely necessary until it’s proven that the action will do no harm.  I don’t get off on it nor do I enjoy giving people the hand.  I’m just…..careful.  We’ve all been burned and it’s my responsibility to make sure that we’re protecting the company’s assets, both data and the systems that data lives on. 

The problem with client tools is they can create avenues for danger.  For example, if I install SQL Server Management Studio and the Sysinternals tools, I’ve created a way for a local administrator on that server to log in to my SQL Server as an administrator, even if it wasn’t my intention to grant him that access.  This can be extremely useful (such as a situation where you do lose your SQL admin logins), but there’s inherent risk there.  Other tools can create similar risk, so my view is to try and reduce this risk by minimizing client tool installs. 

Another problem with client tool installs is that client tools take resources away from SQL Server (or other processes that the server is hosting up).  I know, I know…we live in an age where RAM and CPU are plentiful, but I still get protective of my stuff.  These are MY toys and, since I’m a jerk, I hate sharing.  By restricting client tool installs, I proactively prevent this sort of sharing and keep folks out of my sandbox. 

Thirdly, by putting client tools on to server, I provide a crutch for those who feel they have to do all their work directly on the server.  This is a bad practice, even if you have resources.  You’re not just taking stuff away from SQL, things you could also seriously damage something without even intending to.  Accidentally shut down the box?  Easy.  Delete or corrupt critical files?  Piece of cake.  I often think of working on the server as playin around in the middle of a minefield and why put a tool I need in the middle of that minefield?  If the tool isn’t installed, there’s no reason to log in to a server, so it doesn’t even become an issue.

This is one of many reasons I’m so high on Server 2012 Core.  By the very lack of its GUI, a lot of people will shy away from tools.  And the tools are there, but let’s be honest with each other here:  Most of us Windows folks love our dialog boxes and ‘Ok’ buttons, our drop ‘n drag paired with right clicking.  Command line interfaces are an anathema and we will avoid them as a preference.

 I get it, though.  There are plenty of valid reasons why you would install those tools.  It can speed up troubleshooting and there are certain things that can only be done from the machine itself.  Plus, if your box is secure, you can reduce the risk of having those tools out there.  I would argue that, even with the box secure, minimizing your client tool installs will reduce your risk even further.

I challenge folks to really, REALLY ask themselves: Do you really need use those client tools on the server?  In most cases, you probably don’t.  And if you don’t need to use them on the server, then why are you installing them in the first place?

Slipstreamin’ Away

It’s nothing new: I love anything that makes less work for me.  One thing that has been a great contributor to this has been slipstream installs.  While that’s a bit of a fancy name, basically it’s an install of SQL Server where the service packs and cumulative updates are rolled up into one install package.  This saves the tedious chore of constant install after install to get a new SQL Server installation up to a patch level consistent with the rest of your enterprise.

Now, the problem with slipstream installs is that they’re a pain in the butt to create, at least previous to SQL 2012.  Not difficult, but tedious.  You have to copy some files, merge some directories, and edit some config files.  It’s nothing that can’t be done, but very repetitive.  To boot, if something isn’t copied or configured right, the whole package won’t work and you won’t know until you attempt the install (and it fails).

Enter Powershell.

Building a slipstream is no different than any other task that we should automate.  After all, we’re doing the same actions every time, the only thing that might change are the files we use to build our slipstream.  What I’ve done is put together a script that does all this for me.  As with any script, there are a few rules to how you use it:

  • The script was designed for building SQL 2008 and 2008 R2 slipstreams.  I tried it with 2005, but that process is different enough that this script won’t work.
  • RTMSource and output are the two mandatory parameters.  The SPFiles and CUFiles parameters will be skipped if left null.  Basically, if you are making just a SQL 2008 R2 SP2 slipstream, you would only need to supply the service pack files.
  • The script expects the base install (the RTM media) to be un-extracted and the service packs or cumulative updates in their .exe form.  The script will extract the additional files from the .exe into the slipstream package.
  • I built the script to work along my organizational thought process, which means I put each set of files in their own subdirectory.  This shouldn’t be a big deal, but be aware that if you point the script at a location, it won’t just pull out the files it needs, it will try and use every .exe file in the subdirectory.

Other than that, here’s the script:

<#
.SYNOPSIS
   Builds a SQL Server slipstream install.
.DESCRIPTION
   This script will take the various components of a SQL Server isntall (RTM binaries, SPs, CUs) and assemble
	 them to create a slipstream install.  To use this script, you will need the unextracted RTM binaries (.iso
	 or DVD media) and the executables for the SP and CUs you want to use.
	 Mike Fal (htp://www.mikefal.net) 2-28-2013
.PARAMETER <paramName>
   RTMSource - File path for the RTM source files
	 SPSource - File path for the Service Pack executables (if no value is passed, no SP will be added)
	 CUSource - File path for the Cumulative update executables (if no value is passed, no CU will be added)
	 output - filepath where slipstream will be written, directory will be created if it doesn't exist.
.EXAMPLE
   .\Build-Slipstream.ps1 -RTMSource "C:\Users\mfal\Downloads\en_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522665"
	 												-SPSource "C:\Users\mfal\Downloads\2008R2SP1"
													-CUSource "C:\Users\mfal\Downloads\2008R2SP1CU2"
													-output "C:\SQL2008SP1CU2_Slipstream"
#>

param([parameter(Mandatory=$true)][string] $RTMSource,
			[parameter(Mandatory=$false)][string] $SPSource,
      [parameter(Mandatory=$false)][string] $CUSource,
      [parameter(Mandatory=$true)][string] $output)

#function to extract and copy SPs and CUs
function Modify-Source{
	param($TYPE="PCU",$WORKDIR,$PATCHDIR)
	$PATCHFILES=get-childitem "$PATCHDIR\*" -include *.exe
	if (!(test-path("$WORKDIR\$TYPE"))) {mkdir $WORKDIR\$TYPE }
	if (!(test-path("$WORKDIR\$TYPE"))) {Throw "Error: Patch file destination not available"}

	set-location $PATCHDIR

	foreach($FILE in $PATCHFILES)
		{

			iex "./$($FILE.basename)$($FILE.extension) /x:""$WORKDIR\$TYPE"" /q"
			"Waiting for $FILE to extract..."
			while (@(Get-Process $FILE.Name.Replace(".exe","") -ErrorAction SilentlyContinue).Count -ne 0)
			{
			Start-Sleep 1
			}
		}
  "Copying $TYPE files..."
	robocopy "$WORKDIR\$TYPE" "$WORKDIR" setup.exe /ndl /nfl /njh /njs
	robocopy "$WORKDIR\$TYPE" "$WORKDIR" setup.rll /ndl /nfl /njh /njs

if (test-path("$WORKDIR\$TYPE\x86")) {robocopy "$WORKDIR\$TYPE\x86" "$WORKDIR\x86" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}
if (test-path("$WORKDIR\$TYPE\x64")) {robocopy "$WORKDIR\$TYPE\x64" "$WORKDIR\x64" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}
if (test-path("$WORKDIR\$TYPE\ia64")) {robocopy "$WORKDIR\$TYPE\ia64" "$WORKDIR\ia64" /XF Microsoft.SQL.Chainer.PackageData.dll /ndl /nfl}

set-location $WORKDIR
}

#Test path locations for validity

if (!(test-path($RTMSource + "\setup.exe"))) {Throw "SQL RTM Source does not exist!"}
if (!(test-path($SPSource)) -and !($SPSource -eq $null)) {Throw "Invalid Service Pack file location!"}
if (!(test-path($CUSource)) -and !($CUSource -eq $null)) {Throw "Invalid Cumulative Update file location!"}
if (!(test-path($output))) {mkdir $output}
if (!(test-path($output))) {Throw "Unable to create build directory!"}

#begin merge process, RTM
"Copying RTM..."
robocopy "$RTMSource" "$output" /s /ndl /nfl
$startloc=pwd
set-location $output

Copy-Item ".\x86\DefaultSetup.ini" ".\"

#merge Service Pack
if(!($SPSource -eq $null))
{
	Modify-Source "PCU" $output $SPSource
	&quot;<code>n</code>nPCUSOURCE=<code>&quot;.\PCU</code>&quot; <code>n&quot; |Out-File &quot;DefaultSetup.ini&quot; -Append
}

#merge Cumulative Update
if(!($CUSource -eq $null))
{
	Modify-Source &quot;CU&quot; $output $CUSource
	&quot;</code>n<code>nCUSOURCE=</code>&quot;.\CU<code>&quot; </code>n&quot; |Out-File &quot;DefaultSetup.ini&quot; -Append
}

#cleanup
copy-item &quot;defaultsetup.ini&quot; .\ia64 -force
copy-item &quot;defaultsetup.ini&quot; .\x64 -force
copy-item &quot;defaultsetup.ini&quot; .\x86 -force

Set-Location $startloc