Optimized SQL Server 2008 R2 Express Installation

There’s a couple things which can be done to optimize SQL Server 2008 R2 Express during installation. This post makes the assumption that you have a dedicated windows server 2008 R2 domain member server in the English (1033) localization.

First, create 2 extra volumes on your server:

  • D Drive, GPT, 14GB, “SQL Data”
  • E Drive, GPT, 5GB, “SQL Logs”

Getting all the pieces

Download the following items

  1. SQL Server 2008 R2 Express x64 with Management Tools
  2. Check for and download the latest Cumulative Update here: SQL Server Release Blog. As of 12/1/2010, it’s CU4. You can also check SQL Version Database.
    1. When you visit the KB page for CU4, you want to download the file named “SQLServer2008R2_RTM_CU4_2345451_10_50_1746_x64”.
    2. Make sure you extract the hotfix right away, since it’s locked the time-limited password sent to your email address.
  3. 7Zip or another good zip program.

Preparation

  1. Create C:\workingtemp, and copy the SQL Express and CU download files there.
  2. Unzip each of the 2 files to their own folders.
  3. If you’re using CU4, the folder structure should look like this:
    C:\workingtemp\SQLEXPRWT_x64_ENU\setup.exe
    C:\workingtemp\SQLServer2008R2-KB2345451-x64\setup.exe

Slipstreaming the Cumulative Update

References:

Now that you have everything extracted and ready, let’s slipstream the CU.

  • Create a new folder
    C:\workingtemp\SQL2008CU4 (unless you’re on a different CU).
  • Copy the contents of C:\workingtemp\SQLEXPRWT_x64_ENU into C:\workingtemp\SQL2008CU4.
  • Next, create another new folder
    C:\workingtemp\SQL2008CU4\CU
  • Copy the contents of C:\workingtemp\SQLServer2008R2-KB2345451-x64 into C:\workingtemp\SQL2008CU4\CU. You don’t need to copy all of the localization files if you don’t want to. In my case, the only localization folder I needed was ‘1033_enu_lp’.

Once this is done, your directory structure should look like this:

  • C:\workingtemp
    • \SQL2008CU4
      • setup.exe
      • supporting files and folders
      • \CU
        • setup.exe
        • supporting files and folders

Now, we need to overwrite a couple of the root setup files with ones from the CU.

  • All of the files, but not the folders, and not the one file called “Microsoft.SQL.Chainer.PackageData.dll” in C:\workingtemp\SQL2008R2CU4\CU\x64, should be copied to C:\workingtemp\SQL2008R2Cu4\x64. Here’s a robocopy command which will do this for you.
    • robocopy C:\workingtemp\SQL2008R2CU4\CU\x64 C:\workingtemp\SQL2008R2CU4\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
  • NOTE: The article listed as a reference says to overwrite setup.exe in the root setup directory with the setup.exe in the CU directory. I intentionally left this step out because replacing the file causes my setup installation to crash–not sure what’s wrong there, but I’ve found that setup should still slipstream and install correctly with the original setup.exe file in place.

Creating the Configuration File

References:

One more thing before installing SQL–we need to create the configuration file. Open a text editor, and copy\paste the following code block, then save it as C:\workingtemp\SQL2008R2CU4\configurationFile.ini. Make sure to change the SQLSYSADMINACCOUNTS parameter.


;SQLSERVER2008 Configuration File
[SQLSERVER2008]
CUSOURCE=".\CU"
IAcceptSQLServerLicenseTerms="True"
INSTANCEID="MSSQLSERVER"
ACTION="Install"
FEATURES=SQLENGINE,SSMS,SNAC_SDK
HELP="False"
INDICATEPROGRESS="False"
QUIET="False"
QUIETSIMPLE="True"
X86="False"
ROLE="AllFeatures_WithDefaults"
ENU="True"
;UIMODE="Normal" -- not supported when using quietsimple="true"
ERRORREPORTING="True"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEDIR="D:\SQLData"
SQMREPORTING="True"
INSTANCENAME="MSSQLSERVER"
AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
AGTSVCSTARTUPTYPE="Automatic"
ISSVCSTARTUPTYPE="Automatic"
ISSVCACCOUNT="NT AUTHORITY\NetworkService"
ASSVCSTARTUPTYPE="Automatic"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="Data"
ASLOGDIR="Log"
ASBACKUPDIR="Backup"
ASTEMPDIR="Temp"
ASCONFIGDIR="Config"
ASPROVIDERMSOLAP="1"
FARMADMINPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="True"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
SQLSYSADMINACCOUNTS="DOMAIN\YourUsername" "DOMAIN\SQL Admins"
SQLUSERDBLOGDIR="E:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\Data"
SQLTEMPDBLOGDIR="E:\SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\Data"
ADDCURRENTUSERASSQLADMIN="True"
TCPENABLED="0"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Disabled"
RSSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
RSSVCSTARTUPTYPE="Automatic"
RSINSTALLMODE="FilesOnlyMode"

Installation

Now, Install SQL.

References:

  1. Install the .Net framework.
    1. Open powershell as administrator and run the following command:
      Import-Module ServerManager; Add-WindowsFeature net-framework-core
    2. Go to control panel -> Windows Updates, install Microsoft Update, and “check for updates”. Then, install them all and reboot. This should update you to .NET 4.
  2. In powershell:
    1. cd C:\workingtemp\SQL2008R2CU4
    2. setup.exe /CONFIGURATIONFILE="C:\workingtemp\SQL2008R2CU4\configurationFile.ini"
    3. wait forever, and setup should finish!

Verification

To make sure CU4 actually installed, we can run a file version discovery.

  1. Run setup.exe with no flags, then in the GUI click “Tools” then “Installed SQL Server features discovery report”.
  2. The report should output results similar to the image below. If you installed CU4 correctly, the version number will show  “10.50.1746.0”
  3. Compare the version number listed with the table at this website: SQL Server Version Database .
  4. Login to the freshly installed SQL Server through “SQL Server Management Studio”. Everything should work!

To-Do \ Wishlist

  • Document installing a slipstreamed SQL Server 2008 R1 with both a CU and an SP.
  • Document enabling Named Pipes and TCP\IP
  • Document capturing a SQL Server Express configuration file.
  • Document backing up and restoring a SQL Server with DPM
  • SQL Server Express and Sysprep (and a vmware template)
  • Using WaspTime Professional with a SQL Server Express installation.
  • Dumping regular SQL backups to disk (for crude backup systems that are crash-consistent only).
Advertisements

2 thoughts on “Optimized SQL Server 2008 R2 Express Installation

  1. Pingback: Creating a vSphere Template – Windows Server 2008 R2 with SQL Server 2008 R2 « windowsmasher

  2. Pingback: Installing Dell OpenManage IT Assistant | Windows Stuff That Your Dreams Dreamed Of

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s