Getting Started with SQL 2012 Clustering

I’ve been playing with SQL Clustering recently. Why? It’s cool! Here’s how to get it up and running quickly.

Overview

  1. Create an MSCS Failover Cluster with 2 nodes.
  2. Install the SQL Cluster role.
  3. Configure the SQL nodes.
  4. Install the MS DTC cluster role.
  5. Test MSDTC

Create an MSCS Failover Cluster

To do this, see my previous post Windows Server 2012 – Getting Started With Failover Clustering. Your cluster will need the following characteristics:

  • Quorum disk configured.
  • A free cluster disk for DTC use.
  • A free cluster disk for SQL use.

Install the SQL Cluster Role

Here are the steps:

Create the Service Accounts

#read-host must be done manually or it assumes that the lines following it are the input.
$cred = Read-Host "Account Password:" -AsSecureString
$namePrefix = "sqlc1-"
$accounts = @("ssa","ssde","ssrs","ssis")
$accounts | %{$newname = $namePrefix + $_; New-ADUser -name $newname -Enabled $true -AccountPassword $cred}

Install .Net 3.5

  1. Insert windows media.
  2. Run the following PowerShell code:
    Install-WindowsFeature net-framework-code -source D:\windows\sxs

Install SQL Node 1

Steps:

  1. Insert SQL media
  2. Run the install wizard.
  3. Choose Installation -> New SQL Server Failover Cluster Installation

Here are my install notes:

  • On the screen ‘Setup Support Rules’, you will likely see warnings for MSDTC, MSCS, and Windows Firewall. This is OK.
  • On the screen ‘Instance Configuration’, enter the desired netbios name of your cluster under ‘SQL Server Network Name’. Don’t worry about the instance root directory being on a non-cluster disk. The instance root that you see under ‘Instance Root Directory’ is not where the databases will actually be stored.
  • On the screen ‘Cluster Resource Group’, you will see a sheet with two red circles in the ‘qualified’ column. This is normal, just hit ‘next’.
  • On the screen ‘Cluster Disk Selection’, check the box next to the cluster disk that you’d like to use for the SQL database. We will need to leave at least 1 disk free for MSDTC, so don’t check the boxes next to all of the cluster disks. Leave one available.
  • On the screen ‘Cluster Network Configuration’, check the box for the cluster network you’d like to use. I recommend entering a static address. The cluster will auto-register its name with DNS, but if you want to use TCP/IP you will need to enter specific IP’s into SQL Server Configuration Manager on each node.
  • on the screen ‘Server Configuration’, enter the username\passwords for the service accounts created in a previous step. Leave the ‘startup type’ as the default for all services (most should be set to ‘manual’).

Verify that the SQL Role is online using Failover Cluster Manager. Then, verify that you can connect to the SQL Cluster Network Name using SQL Server Management Studio (from the node itself since we haven’t configured TCP). If it connects, then your first node is up. Congrats!

Install SQL Node 2

There is nothing substantially noteworthy about adding the second node. The installation wizard is streamlined and straightforward.

  1. Insert SQL media.
  2. Run the install wizard.
  3. Choose Installation -> Add Node to a SQL Server Failover Cluster

Configure TCP and Firewalls

On both nodes, do the following:

  1. Launch SQL Server Configuration Manager
  2. Navigate to SQL Server Network Configuration -> Protocols for MSSQLSERVER
  3. Right click TCP/IP and choose ‘Properties’.
  4. On the ‘IP Addresses’ tab, make sure that the IP addresses for the cluster nodes and the SQL Cluster network resource are entered and enabled.
  5. Close SSCM.
  6. Open Powershell and run the following commands to open the firewall. I recommend choosing a firewall rule more specific than the one I’m presenting here, but it will get you started.
    New-NetFirewallRule -DisplayName "MSSQL BROWSER UDP" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow -Profile Domain
    New-NetFirewallRule -DisplayName "MSSQL ENGINE TCP" -Direction Inbound -LocalPort 1433-1434 -Protocol TCP -Action Allow -Profile Domain
    

You should now be able to connect to the SQL Server Cluster network name from any domain computer. Pretty awesome.

Install the MS DTC Cluster

I didn’t know anything about the Microsoft Distributed Transaction Coordinator service until this SQL clustering adventure. Here’s some background information.

  1. (wikipedia) Microsoft Distributed Transaction Coordinator
  2. What exactly is MSDTC, any when do I need it?
  3. How to install a clustered SQL Server 2012 Instance – step-by-step – Part 4

Basically, it’s a service that handles transactions across multiple nodes. Some applications as well as SQL services require MSDTC. I’m choosing to cluster it even though I’m not absolutely sure it’s necessary. Here’s how to set it up.

First, copy the following PowerShell code. Scan through and replace any strings with the ones you’d like to use.

#Add DTC Role
Add-ClusterResource "sqlc1-dtc" -ResourceType "Distributed Transaction Coordinator" -Cluster cluster1 -Group "SQL Server (MSSQLSERVER)"

#DTC Depends on Network Name
##find the name of the network name resource with Get-ClusterResource
##example: $sqlNetworkName = "SQL Network Name (sqlc1)"
$sqlNetworkName = "Insert SQL Cluster Network Resource Name Here"
Add-ClusterResourceDependency "sqlc1-dtc" $sqlNetworkName -Cluster cluster1

#Add DTC Disk to DTC Group
##chose a cluster disk. 1GB freespace is fine. It just stores some logs.
Move-ClusterResource "Cluster Disk 2" -Cluster Cluster1 -Group "SQL Server (MSSQLSERVER)"

#DTC Depends on Disk
Add-ClusterResourceDependency "sqlc1-dtc" "Cluster Disk 2" -Cluster cluster1

#SQL Server Depends on DTC
Add-ClusterResourceDependency "SQL Server" "sqlc1-dtc" -Cluster cluster1

In order to get DTC working between nodes, I needed to open all firewall ports between the DTC nodes themselves, and between the DTC nodes and the application server. I’m sure there’s a better way to do this. Again, this will get you started.

In powershell, on each node:

#$IPAddresses = @("node1-IP","node2-IP","Test-Client-IP")
$IPAddresses = @("10.10.10.10","10.10.10.50","10.10.10.54")
New-NetFirewallRule -name clustering-rpc-ports-to-partners -remoteaddress $IPAddresses -displayname "Clustering - Allow RPC to Partners"

Finally, we need to enable network access for the DTC service. To do this, complete the following procedure:

  1. Logon to the node actively running the DTC role.
  2. Run the ‘Component Services’ snap-in from ‘Control Panel’ -> ‘Administrative Tools’.
  3. Navigate to ‘Component Services’ -> ‘Computers’ -> ‘My Computer’ -> ‘Distributed Transaction Coordinator’ -> ‘Clustered DTCs’.
  4. Right-click the DTC cluster and choose ‘Properties’.
  5. On the ‘Security’ Tab, choose the following checkboxes:
    [X] Network DTC Access
    [X] Allow Remote Clients
    [X] Allow Inbound
    [X] Allow Outbound
  6. Click ‘OK’, then restart the SQL failover cluster via the Failover Cluster Management tool.

Assuming all went well, you now have DTC configured. Let’s test it!

Testing the DTC Cluster

Download the following tools from Microsoft. Extract them to a temp directory of your choice on both cluster nodes, and on a test client.

  • DTCPing
  • DTCTester

Testing Basic Connectivity

Perform the following procedure to test DTC basic connectivity:

  1. Launch DTCPing on both DTC nodes.
  2. On node1, type the netbios name of node2 into the DTC ping utility and press ‘Start’.
  3. You should see a successful response.
  4. Try this from the test client also, after relaunching DTCping on the nodes.

If DTC ping fails, it’s probably a name resolution or network issue.

Testing Actual Transactions

Perform the following procedure to test actual DTC transactions:

  1. On node1, naviagte to Control Panel -> Administrative Tools -> ODBC Connectors (32-bit).
  2. Create a new System DSN pointing at the netbios name of the SQL cluster.
  3. Enable mixed-mode authentication on the SQL cluster via SQL Server Management Studio.
  4. Create a new SQL account on the SQL cluster named ‘test’. Give it full permissions on the databases and cluster via SQL Server Management Studio.
  5. Run DTCTester.exe with the following syntax:
    dtctester.exe <dsn-name> <user> <password>
    example: dtctester.exe sqlc1-dsn test myPassword

You can perform the same procedure from a test client instead of a node, however there are additional steps required.

On a test client, perform the following steps before running DTCTester.exe

  1. Install DTC with the following powershell command:
    DTCInstall
  2. Navigate to Control Panel -> Administrative Tools -> Component Services.
  3. Navigate to ‘Component Services’ -> ‘Computers’ -> ‘My Computer’ -> ‘Distributed Transaction Coordinator’ -> ‘Clustered DTCs’.
  4. Right-click the DTC cluster and choose ‘Properties’.
  5. On the ‘Security’ Tab, choose the following checkboxes:
    [X] Network DTC Access
    [X] Allow Inbound
    [X] Allow Outbound

If dtctester.exe fails, follow the chart at this page: Troubleshooting MSDTC Communication Checklist.

Now, you can create the DSN on the test client and run dtctester.exe

Whew, and now you’ve got a working SQL cluster with DTC. Not the easiest thing ever. Easier than getting vPro running at least ;).

Advertisements

VDR Backup Failures

I’ve spent the last month dealing with VDR backup failures. Sometime around vSphere 4.1 VDR stated failing on my domain controllers, then my SQL servers, then all windows servers in general. The linux servers and any other VM’s backing up without application quiescing never skipped a beat. Here’s the dreaded error message from the VDR appliance:

Failed to create snapshot for specops-cmd, error -3960 ( cannot quiesce virtual machine)

or from the vCenter server itself:

Cannot create a quiesced snapshot because the create snapshot operation exceeded the time limit for holding off I/O in the frozen virtual machine.

DAMNED LIES! That wasn’t the real problem.

After a whole lot of testing, about 12 emails, 2 webex sessions, and 5 phone-calls to support, I narrowed the problem down to the following multiple causes. Any of the following will cause this error message on a Server 2008 R2 VM backing up with the VMWare Tools VSS driver.

  1. Independent Disks
  2. iSCSI Connections with MS iSCSI
  3. Running an older version of vmware tools — note that vCenter itself will tell you the tools are ‘OK’ unless you’re a major build behind. My systems were running b257589, and most of the VDR issues were resolved when I did an in-place upgrade to b299420.
  4. “Missing” drives in Disk Management. I had one system that kept adding ‘missing drives’. The key was that on boot, I’d get the message “VMWare Customization in Progress”. Apparently it never cleared sysprep out of the boot list. See the fix for that at my wiki here: vSphere on JP Wiki. Search for ‘customization’.
  5. There is a problem with the way VMWare Tools calls for a VSS Snapshot on systems running Active Directory (the NTDS writer). The writer will show “non-retryable error” after a backup attempt. This is a known issue slated to be fixed in the next release of VMWare tools. For now, just create a text file in “C:\programdata\vmware\vmware tools” called vmbackup.conf with 1 line: “NTDS” without the quotes. This disables the NTDS writer, but it’s better than Disk.EnableUUID=false because the rest of your system will still be application-quiesced.

I hope this helps someone avoids the work of systematically ruling out the other 20+ suspected causes :).

References:

Optimizing SQL Server 2008 Standard Installation

Note: Updated to fix a couple path errors problems that I noticed. Sorry!

Note: Updated to include slipstreaming both a SP and a CU.

There’s a couple things which can be done to optimize SQL Server 2008 Standard 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, “SQL Data”
    Size: however large your database will be)
  • E Drive, GPT,  “SQL Logs”
    Size: typically 20% the size of the SQL Data volume

Getting all the pieces

Gather\download the following items:

  1. SQL Server 2008 R1 x64 Media
  2. Check for the version numbers of the latest Service Pack and Cumulative Update here:
    SQL Server Version Database
    NOTE: At the time of this post the latest version was Service Pack 2 with CU1.
  3. Search microsoft.com and download the latest service pack and cumulative update.
    Here’s a link to SQL 2008 R1 SP2.
  4. Make sure you extract the cumulative update file right away, since it’s locked the time-limited password sent to your email address.

Preparation

  1. Create C:\workingtemp, and copy the SQL installation media to a new subfolder named “SQLFULL_ENU”.
  2. Copy the Service Pack download file here.
  3. Unzip the SP file to its own folder.
  4. Unzip the CU to it’s own folder.
  5. If you’re using SP2, the folder structure should look like this:
    C:\workingtemp\SQLFULL_ENU\setup.exe
    C:\workingtemp\SQLServer2008SP2-KB2285068-x64-ENU\setup.exe
    C:\workingtemp\SQLServer2008-KB2289254-x64\setup.exe

Slipstreaming the Service Pack and CU

References:

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

  1. Create a new folder
    C:\workingtemp\SQL2008SP2CU1 (unless you’re on a different SP\CU).
  2. Copy the contents of C:\workingtemp\SQLFULL_ENU into C:\workingtemp\SQL2008SP2CU1\.
  3. Next, create another new folder and copy the service pack contents to it:
    C:\workingtemp\SQL2008SP2CU1\SP
  4. Next, create a new folder and copy the cumulative update contents to it:
    C:\workingtemp\SQL2008SP2CU1\CU

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

  • C:\workingtemp
    • \SQL2008SP2CU1
      • setup.exe
      • \x64
      • other supporting files and folders
      • \SP
        • setup.exe
        • \x64
        • other supporting files and folders
      • \CU
        • setup.exe
        • \x64
        • other supporting files and folders

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

  1. Copy all of the files, but not the folders, and not the one file called “Microsoft.SQL.Chainer.PackageData.dll” in C:\workingtemp\SQL2008SP2CU1\SP\x64 to C:\workingtemp\SQL2008SP2CU1\x64. Here’s a robocopy command which will do this for you.
    robocopy C:\workingtemp\SQL2008SP2CU1\SP\x64 C:\workingtemp\SQL2008SP2CU1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
  2. Copy setup.exe and setup.rll from C\workingtemp\SQL2008SP2\SP to C:\workingtemp\SQL2008SP2.
  3. Copy all of the files, but not the folders, and not the one file called “Microsoft.SQL.Chainer.PackageData.dll” in C:\workingtemp\SQL2008SP2CU1\CU\x64 to C:\workingtemp\SQL2008SP2CU1\x64. Here’s a robocopy command which will do this for you.
    robocopy C:\workingtemp\SQL2008SP2CU1\CU\x64 C:\workingtemp\SQL2008SP2CU1\x64 /XF Microsoft.SQL.Chainer.PackageData.dll
  4. Copy setup.exe and setup.rll from C\workingtemp\SQL2008SP2CU1\CU to C:\workingtemp\SQL2008SP2CU1.

Creating the Configuration File

References:

  1. How to: Install SQL Server 2008 from the Command Prompt

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\SQL2008SP2\configurationFile.ini. Make sure to change the SQLSYSADMINACCOUNTS parameter. For a list of possible parameters and what they mean see the referenced link above.

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PCUSOURCE=".\SP"
CUSOURCE=".\CU"
INSTANCEID="MSSQLSERVER"
ACTION="Install"
FEATURES=SQLENGINE,FULLTEXT,RS,CONN,BC,SDK,SSMS,ADV_SSMS,SNAC_SDK
HELP="False"
INDICATEPROGRESS="False"
QUIET="False"
QUIETSIMPLE="True"
X86="False"
MEDIASOURCE="C:\workingtemp\SQL2008SP2\"
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\SYSTEM"
AGTSVCSTARTUPTYPE="Automatic"
ISSVCSTARTUPTYPE="Automatic"
ISSVCACCOUNT="NT AUTHORITY\NetworkService"
ASSVCACCOUNT="NT AUTHORITY\SYSTEM"
ASSVCSTARTUPTYPE="Automatic"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="D:\SQLData\MSAS10.MSSQLSERVER\OLAP\Data"
ASLOGDIR="D:\SQLData\MSAS10.MSSQLSERVER\OLAP\Log"
ASBACKUPDIR="D:\SQLData\MSAS10.MSSQLSERVER\OLAP\Backup"
ASTEMPDIR="D:\SQLData\MSAS10.MSSQLSERVER\OLAP\Temp"
ASCONFIGDIR="D:\SQLData\MSAS10.MSSQLSERVER\OLAP\Config"
ASPROVIDERMSOLAP="1"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="False"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
SQLSYSADMINACCOUNTS="DOMAIN\YourUserName" "DOMAIN\SQL Admins"
SQLUSERDBLOGDIR="E:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data"
SQLTEMPDBLOGDIR="E:\SQLData\MSSQL10.MSSQLSERVER\MSSQL\Data"
ADDCURRENTUSERASSQLADMIN="False"
TCPENABLED="1"
NPENABLED="0"
BROWSERSVCSTARTUPTYPE="Automatic"
RSSVCACCOUNT="NT AUTHORITY\SYSTEM"
RSSVCSTARTUPTYPE="Automatic"
RSINSTALLMODE="DefaultNativeMode"
FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"

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\SQL2008SP2
    2. setup.exe /CONFIGURATIONFILE="C:\workingtemp\SQL2008SP2\configurationFile.ini"
    3. wait forever, and setup should finish

Verification

To make sure the SP and CU actually installed, you 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 SP2 with CU1 correctly, the version number will show  “10.00.4266”.
  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!

Creating a vSphere Template – Windows Server 2008 R2 with SQL Server 2008 R2

Configure Windows

First, Install Windows Server 2008 R2 and configure it to be an efficient template using the instructions on the following websites. Note that not everything is really necessary; use your own discretion.

Configuration Files

Next, make 2 configuration files. One is for the Image Preparation step, the other for Image Completion. The files given below assume that you’re using my optimized sql install instructions here: Optimized SQL Server Express R2 Installation. Make sure to change the “DOMAIN\SQL Admins” user in the completion image config file.

Shared Features Installation Config File:
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
IAcceptSQLServerLicenseTerms="True"
ACTION="Install"
FEATURES=SSMS,SNAC_SDK
HELP="False"
INDICATEPROGRESS="False"
QUIET="False"
QUIETSIMPLE="True"
X86="False"
ROLE="AllFeatures_WithDefaults"
ENU="True"
CUSOURCE="C:\workingtemp\SQL2008ExpressR2CU4\CU"
ERRORREPORTING="True"
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
SQMREPORTING="True"

Image Prep Config File:
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
CUSOURCE=".\CU"
QUIETSIMPLE=True
IAcceptSQLServerLicenseTerms="True"
ACTION="PrepareImage"
FEATURES=SQLENGINE
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
INSTANCEID="MSSQLSERVER"
INSTANCEDIR="D:\SQLData"

Image Completion Config File:
[SQLSERVER2008]
IAcceptSQLServerLicenseTerms="True"
INSTANCEID="MSSQLSERVER"
ACTION="CompleteImage"
HELP="False"
INDICATEPROGRESS="False"
QUIET="False"
QUIETSIMPLE="True"
ENU="True"
ERRORREPORTING="True"
SQMREPORTING="True"
INSTANCENAME="MSSQLSERVER"
AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
AGTSVCSTARTUPTYPE="Automatic"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="True"
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
SQLSVCACCOUNT="NT AUTHORITY\SYSTEM"
SQLSYSADMINACCOUNTS="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"

Prepare SQL

References:

Ok, so this is going to look almost exactly the same as a regular SQL installation. The key is modifying the configuration file. Use my blog post as a guide with the additional steps below.

Optimized SQL Server Express R2 Installation

  1. Once you complete all of the steps down to ‘Installation’, the first run of the installer should use the “Shared Features Installation Config File”. This will install your shared features.
  2. Next, run the installer with the “Prepare Image” configuration file you made above.
  3. After the template is deployed, you have to run the following command to complete SQL installation:
    setup.exe /configurationFile=<pathto_sqlconfig-completeImage.ini>

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).