Slipstream SQL Server 2008 R2 Service Pack 2

One of the new features added to Microsoft SQL Server 2008 and R2 is the ability to slipstream a service pack into the installation. This streamlines the installation, and reduces human error. Now that SQL Server 2008 R2 SP2 is officially out, here’s how to slipstream SQL Server 2008 R2 Service Pack 2. I’ve updated the instructions from the MSDN SQL 2008 R2 SP1 blog article to reflect SP2 filenames.

1. Download SQL Server 2008 R2 SP2 here. Note that you need to download all three architectures (IA64, x64, x86) even if you never plan on using them.

  • SQLServer2008R2SP2-KB2630458-IA64-ENU.exe
  • SQLServer2008R2SP2-KB2630458-x64-ENU.exe
  • SQLServer2008R2SP2-KB2630458-x86-ENU.exe

2. Copy your original SQL Server 2008 R2 media to a folder (e.g. SQL2008R2_SP2), I used the E drive so my path was E:SQL2008R2_SP2.

3. You now need to extract the packages, using the path to where you copied the original source media.

SQLServer2008R2SP2-KB2630458-IA64-ENU.exe /x:e:\SQL2008R2_SP2SP
SQLServer2008R2SP2-KB2630458-x64-ENU.exe /x:e:\SQL2008R2_SP2SP
SQLServer2008R2SP2-KB2630458-x86-ENU.exe /x:e:\SQL2008R2_SP2SP

4. Replace the original setup.exe with the SP2 version:

robocopy E:\SQL2008R2_SP2SP E:\SQL2008R2_SP2 Setup.exe

5. Copy all of the files, excluding folders and the Microsoft.SQL.Chainer.PackageData.dll file to overwrite the non-SP2 files.

robocopy E:\SQL2008R2_SP2\SP\x86 E:\SQL2008R2_SP2\x86 /XF Microsoft.SQL.Chainer.PackageData.dll
robocopy E:\SQL2008R2_SP2\SP\x64 E:\SQL2008R2_SP2\x64 /XF Microsoft.SQL.Chainer.PackageData.dll 
robocopy E:\SQL2008R2_SP2\SP\ia64 E:\SQL2008R2_SP2\ia64 /XF Microsoft.SQL.Chainer.PackageData.dll

6. To tell the SQL server installer where to find the service pack files you need to update all three DefaultSetup.ini files. They are located under E:\SQL2008R2_SP2x86, x64 and ia64. Add the following line to the end of each file:


7. Your SP2 slipstream media is now complete. Run Setup.exe like you normally would and enjoy an integrated installation of SQL Server 2008 R2 with SP2.

Update: You may get an installation error if installing on a 64-bit server. You may need to rename the sqlncli.msi file in SP1033_ENU_LPx64setupx64 to sqlncli_amd64.msi.

DBI328: Building the Fastest SQL Servers

Brent Ozar, Microsoft Certified Solutions Master (MSCM),

This was a REALLY great session that was both practical and filled with great technical details and good take away information. #1 takeway is “TempDB is like a public toilet: You never know what’s in there.” LOL Clearly Brent Ozar knows his stuff, and has a ton of resources on his web site. This session was focused on building the fastest SQL server possible, and making it easy. If you are DBA or run SQL in your environment (and who doesn’t if you are a MS shop), even if you don’t need blazing speed, this session had a lot of good sizing and performance tips. Below are several links to additional resources on his site:

SQL Server Setup Checklist
SQL Perfmon Counters
SQL Virtualization Best Practices
SQL IO Performance Testing
SQL 2012 Data warehouse reference design

He was flying through the slides and they were packed with content, so I didn’t get everything down. If his session is posted on Channel 9, check it out. His slides weren’t posted when I wrote this, so I can’t fill in the missing details. Even if you aren’t using the reference hardware in some of the links below (like HP and Dell) it still gives you great sizing and performance data you can translate to your own hardware.

  • How Microsoft Designs SQL Server Appliances
    • Systematically review thousands of SQL servers
    • Distill use cases down to a few common patterns
    • Choose HW components that are very likely to work great for those patterns
    • Publish an incredibly detailed test checklist to make sure the hardware is working as designed
  • Session Agenda
    • Define common SQL server use patterns
    • Understand the right hardware for a pattern
    • Recognize the server designs we can reuse
    • Learn how to test our own hardware
  • Define common SQL server use patterns
    • OLTP: Transactional Processing
      • How it’s accessed: Inserts
    • Data warehousing
      • Loaded in short windows overnight, then read-only with big reads through the day for reports
      • Just a few tables, but many historical records in each table, and often over 1TB of data
      • 10 years of sales history, stock prices, patient history, etc.
    • The real difference: OLTP (batch users requests per second) wants its to finish instantly, data warehouse can wait a bit longer (say 30 seconds)
  • The Right Hardware for Pattern OLTP
    • Hardware at it’s simplest: Memory 64GB, Drives 100GB, CPU
    • OLTP scenario: 50GB table
    • Right hardware for the fastest OLTP
      • Instant queries = cache all data in memory
      • Minimize data size (drop extra indexes)
      • Wide stripe data across all drives in the array (even log files)
    • Instant transactions = blazing fast log file
      • SSD RAID10 for multiple databases
      • Can get away with dedicated RAID10 magnetic for single DB
    • Avoid locking issues = blazing fast TempDB with RCSI
      • Read committed snapshot isolation
      • Bing: TempDB files SGAM contention – Multiple files for TempDB (1/8 to 1/4 the number of cores = 1 TempDB files); E.g. 16 cores = 4-8 data files
      • DO count hyperthreads as cores for this calculation
    • I didn’t say blazing fast data drives
      • Once the data is in cache, data drive speed rarely matters
        • SQL server restarts will mean slow performance
        • Aggressive monitor data size, memory size
        • When you run out, things get ugly fast
      • Bottom line: Cache the whole DB in memory and not much else matters
  • The Right Hardware for Pattern Data warehouse
    • Hardware at it’s simplest: 256GB memory, 1TB drives, CPU
    • Instant queries = Maximize memory size
    • Minimize data size (drop extra indexes, right-size fields)
    • See data warehouse links at the beginning of this article
    • Maximum CPU core consumption Rate (MCR) 200MB/Sec good rule of thumb
  • Reference Material
  • Test Storage Quickly with CrystalDiskMark
    • Pick 5 tests, 4000MB test file, drive letter
    • Only look at the sequential and 4K QD32 (queue depth) results
    • Sequential: Roughly akin to backups, large table scans
    • 4K QD32: Vaguely similar to active OLTP server or TempDB
    • MCR is most similar to Sequential read metric
  • Test Storage Slowly with SQLIO
    • See link at start of article for SQLIO tips from Brent
    • Lots of possible options collect the whole set
    • Use a test file larger than your SAN’s cache (say 20GB)
    • Don’t run on a live server
    • Only look at these numbers from the output: IOs/Sec and MBs/sec (MCR)
    • Test drives of different sizes but doesn’t need to test all drives
  • Your Goals
    • Test with CrystalDiskMark to get a quick idea
    • Try two simultaneous CrystalDiskMark tests against two different drive letters to see if your multipathing works
    • When that works, amp up to SQLIO and really push it
  • How to Reduce Storage Throughput Needs
    • Keep memory free for SQL server data caching
    • Merry-go-round scans with SQL server enterprise edition make a huge difference in storage performance and throughput
    • Give OS 10% of the total server memory, or 4GB, whichever is GREATER
  • Defined common SQL server use patterns
    • OLTP: I want the query to finish instantly
    • DW: I want the query to finish in 30 seconds
  • Very important to perform SQLIO performance baseline…EXTREMELY IMPORTANT
  • If you virtualize, only use one instance per VM.

    DBI317: Optimizing SQL in a Virtual Environment

    Denny Cherry, Independent Consultant ( @mrdenny)
    vExpert 2012, Microsoft Certified Master, MVP

    This session covered some helpful tips for virtualizing SQL server, be it on Hyper-V or VMware. Yes you can virtualize SQL and still get excellent performance. But there are some special considerations that you need to be aware of. Most of the tips apply to many applications, but some are SQL specific. The speaker’s slides and commentary were pretty high level, so this wasn’t quite as technical as I was expecting.

    Session summary:

    • High level Topics
      • Diagnosing Performance Problems
      • Balloon Memory Drivers
      • Memory deduplication options
      • Storage Configuration options
    • Diagnosing Performance Problems
      • Check host and Guest CPU numbers
      • Check host for CPU thrashing
      • Check host and guest for disk IO latency
      • On VMware check % Used % Rdy time
    • Balloon Memory Drivers
      • Only does something when the host is out of memory. Under normal conditions it does nothing.
      • Prevents host from paging physical memory to the host’s swap file
      • Should be enabled
      • Lock pages in memory within the SQL server config should be disabled unless enabled for a specific reason
    • Memory reservations
      • Recommended that it be set to a portion of the allocated memory (SQL server + some for OS)
    • Memory deduplication Options
      • Great for OS memory
      • Doesn’t work at all for SQL server
      • Doesn’t hurt performance, but don’t count on it to conserve host memory
    • Storage Configuration Options
      • IO is the same if the disks are physical or virtual
      • Use automatic tier adjusting technology if possible except for SQL logs (use RAID 10)
      • Keep OS, data, logs, tempdb on separate disks
      • Use 64K NTFS allocation size
      • Make sure partitions are aligned (default in Server 2008 and later)

      SQL 2008 R2 SP1 hits the streets

      In the midst of all the uproar about the vSphere 5.0 licensing changes, I missed the fact that Microsoft released SQL 2008 R2 Service Pack 1 yesterday. You can download it here. The master list of bug fixes can be seen here. The new features are listed below. For the full release notes, click here.

      • Dynamic Management Views for increased supportability. sys.dm_query_stats DMV is extended with additional columns to improve supportabilities over troubleshooting long-running queries. New DMVs and XEvents on select performance counters are introduced to monitor OS configurations and resource conditions related to the SQL Server instance.

      • ForceSeek for improved querying performance. Syntax for FORCESEEK index hint has been modified to take optional parameters allowing it to control the access method on the index even further. Using old style syntax for FORCESEEK remains unmodified and works as before. In addition to that, a new query hint, FORCESCAN has been added. It complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index. No changes to applications are necessary if you do not plan to use this new functionality.
      • Data-tier Application Component Framework (DAC Fx) for improved database upgrades. The new Data-tier Application (DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service for database schema management. The new in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC. A DAC is an entity that contains all of the database objects and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects. For more information, see Designing and Implementing Data-tier Applications.
      • Disk space control in PowerPivot for SharePoint. This update introduces two new configuration settings that let you determine how long cached data stays in the system. In the new Disk Cache section on the PowerPivot configuration page, you can specify how long an inactive database remains in memory before it is unloaded. You can also limit how long a cached file is kept on disk before it is deleted.
      • Support for 512e Drives. SQL Server now correctly detects and supports hard drives with the new 512e format. These drives report 512 byte logical sector sizes, but they are formatted internally using 4KB sectors. When SQL Server 2008 R2 SP1 is installed on Windows Server 2008 R2 or higher, we will correctly detect these drives and adjust automatically.

      SQL 2008 R2 Cumulative Update 8 Released

      Microsoft recently released SQL Server 2008 R2 cumulative update package 8 to the web for downloading. You can request the hotfix from this page. Also remember that the June 2011 monthly security patches included some for SQL Server (2005 through 2008 R2). You can find the whole bulletin list here. SQL security updates are fairly rare, so you might not have checked the bulletins this month.

      Enable SQL SSL with low-privileged service account

      One of the neat security features with SQL 2005 and later is the ability to use a SSL certificate to encrypt off-host SQL server communications over port 1433. Encrypting communications between your SQL server and your remote applications is strongly recommended. Do you really want credit card data, personal information or sensitive data traversing the network in clear text? Probably not. Yes you could use IPsec between your SQL server and applications, but that’s not for the faint of heart. But once you know the trick to making SSL work with SQL, it’s a no brainer.

      First, your computer needs a server authentication certificate with an allowed enhanced key usage of For simplicity the domains I manage have an auto-enroll policy that doles out machine certificates to all computers. If you don’t know how to create a machine certificate and don’t have auto-enrollment enabled, I’ll leave that as an exercise for the reader to explore. Assuming you have a valid server authentication certificate, we can continue.

      SQL best practices urge that the SQL database engine, and agent service, run under a non-privileged service accounts. Should your SQL server get compromised, it can help limit the damage to the underlying operating system. But if you pick a machine certificate within SQL Configuration manager to use, SQL will fail to start.

      The failure message, on Windows Server 2008 R2 with SQL Server 2008 R2 is: Event ID 26014, source MSSQLSERVER. Content of the error is:

      Unable to load user-specified certificate [Cert Hash(sha1) “A3B….913C”]. The server will not accept a connection. You should verify that the certificate is correctly installed. See “Configuring Certificate for Use by SSL” in Books Online.

      Hmm…why is that? Well the answer is simple, and the fix is simple as well. The non-privileged service account is attempting to read the private key for the selected certificate and is unable to do so. The solution is to enable the SQL database engine service account read access to the private machine certificate key.

      How do you do that? Glad you asked!

      1. Open a blank MMC and add the Certificates snap-in. Chose the Computer account.

      2. Open the Personal Certificates store and you need to see one certificate with the FQDN of your SQL server. If not, then no SSL for you!

      3. Right click on the certificate and select All Tasks, Manage Private Keys.

      4. Click Add and locate the SQL database engine service account. Change the permissions to Read only.

      5. Click OK.

      6. Open the SQL Server Configuration Manager and expand SQL Server Network Configuration. Open the properties of Protocols for MSSQLSERVER.

      7. Change Force Encryption to Yes. Click on the Certificate tab and from the drop down select the certificate that is listed. It should be the same one you changed the permissions on from the previous step.

      8. Close all windows and restart the SQL service. With any luck the SQL services will start.

      9. To verify SQL is using the PKI certificate, check the Windows Application log for event ID 26013. It should read something like:

      The certificate [Cert Hash(sha1) “A3B2…7913C”] was successfully loaded for encryption.

      And there you go! To recap, we assigned an existing machine certificate to the SQL service, changed the permissions on the private key, and restarted the SQL service. Pretty easy once you know the permissions trick.

      If you are really paranoid, whip out Network Monitor 3.4 (not Wireshark) and capture a trace during a connection attempt to the SQL server. Under the protocol column you should see a TLS session between the SQL server and your application server.

      I noticed that it appears the SQL client that comes with SQL Server 2008 R2 does NOT attempt to negotiate a TLS 1.2 session, like IIS 7.5 and IE8 on Windows 7 does as mentioned in my prior blog.

      SQL 2008 R2: Generate trusted TDE certificate

      As previously mentioned in my blog about SQL TDE (Transparent Data Encryption), the example script I gave just used a SQL self-signed certificate to encrypt the database. While this is fine for a demo, you should only used trusted certificates in a production environment.

      Getting a trusted certificated inserted into SQL 2008 R2 is easier than it sounds and took quite a bit of digging. BUT, there is a way and it’s not too terrible. Plus, this method can be used with commercial certificate authorities or an internal CA of your choice. It does not rely on a Microsoft CA, but works perfectly fine with one.

      1. Download OpenSSL 1.x and install it. Do not use v0.9.x releases as they won’t work.

      2. Open a command prompt and type: openssl.exe genrsa 2048 > private.key

      3. Type: openssl.exe req -new -key private.key > certificate.csr

      4. You will be prompted with a series of questions. Input data as you see fit, but pay attention to the “Common Name”. This will be the subject of the certificate.

      5. Open the certificate.csr file and submit it to your favorite certificate authority. It could be a commercial or internal CA. Save the resulting certificate as a DER (not BASE64) file, let’s say certificate.cer.

      6. Type: openssl rsa -in private.key –outform PVKpvk-strong -out private.pvk
      -You will be prompted to type a password to protect the private key. Remember the password.

      7. Open SQL Management studio and create a new query. Cut and paste the following query, adjusting the paths, filenames and password from step #6 you used. You can change “My_New_Certificate” to any name you wish. Probably best to use the common name you input during the certificate request.

      CREATE CERTIFICATE My_New_Certificate
      FROM FILE = ‘D:certificate.cer
      WITH PRIVATE KEY (FILE = ‘D:private.pvk‘,
      DECRYPTION BY PASSWORD = ‘MyPassword‘);

      8. Press the Execute button and you should get “command(s) completed successfully.” If not, triple check your paths, filenames, and password. The error messages are not helpful if you get it wrong.

      9. To verify the certificate was actually installed and to view all the other certificates, in SQL Server Studio execute this query:

      use master
      select * from sys.certificates

      And there you have it! You can now refer to back to my TDE blog post and change the sample script to use this new trusted certificate instead of the self-signed “RMSServerCert“. You should backup the two certificates you imported into SQL and delete all copies on the local hard disk. If you ever need to restore your encrypted database you MUST have these two certificate files…no data!

      Happy encrypting!

      SQL 2008 R2: Transparent Data Encryption (TDE) Example

      One of the new features in SQL Server 2008/R2 is Transparent Database Encryption, or TDE. TDE lets you encrypt any database, without having to change your application. This means you can fully encrypt databases and log files for SharePoint, RMS, or anything else you wish. For the ultimate in security you can use Microsoft Exensible Key Management (EKM) to use a hardware security module (HSM) to store the private keys off-host.

      The Thales nShield Connect is an excellent example of a FIPS 140-2 Level 3 and Common Criteria EAL4+ certified HSM that is compatible with SQL 2008 EKM and TDE. (Try saying that ten times fast!) Their SQL 2008 EKM brochure is here.

      But for mere mortals that want to encrypt a database or two and don’t want to spend $50,000 or more for a HSM, the script below shows you how to encrypt the three Active Directory Rights Mangement Services (AD RMS) databases. Just change the database names, and you are good to go. There’s nothing unique here about RMS; just an example I worked on today. The certificate used in this example is only a self-signed, so for better security I’d recommend you use a trusted certificate. I’ll save how to do that for a future post here!

      Just run the script in SQL Studio, and you should be good to go. Of course database encryption adds additional overhead, and you can kiss database compression good bye as well. So be careful what you encrypt and monitor database performance.

      /* Configures SQL 2008/R2 transparent database encryption.
      Version 1.1, 7 July 2010 */

      /* Configures master database encryption key and certificate */

      USE master;

      /* Encrypts RMS Configuration Database */

      USE DRMS_Config_RMS_Root_contoso_net_443
      ALTER DATABASE DRMS_Config_RMS_Root_contoso_net_443

      /* Encrypts RMS Directory Services Database */

      USE DRMS_DirectoryServices_RMS_Root_contoso_net_443
      ALTER DATABASE DRMS_DirectoryServices_RMS_Root_contoso_net_443

      /* Encrypts RMS Logging Database */

      USE DRMS_Logging_RMS_Root_contoso_net_443
      ALTER DATABASE DRMS_Logging_RMS_Root_contoso_net_443

      SQL 2008 R2: Temp Database Configuration (Part 6)

      One of the last things I do during a SQL installation is configure the temp databases. Temp databases are very important to some applications, as they are used as a scratch or buffer space. Other applications may not use them hardly at all, so it really depends on your environment.

      One rule of thumb I use for VMware environments is one TempDB for every vCPU presented to your SQL server. Since many virtualized SQL environments will have multiple processors, you want multiple TempDB files for SQL to use.

      Using SQL studio, you can run the script below to automatically add a second TempDB file, and also expand the default TempDB. Of course, adjust the size and growth parameters to fit your situation.

      If you want 8GB of TempDB space and have two vCPUs, then change the existing TempDB to 4GB and create a second that is also 4GB. You want the TempDBs all of equal size since SQL weights their usage based on their size.

      USE master;
      ALTER DATABASE tempdb
      ADD FILE
      NAME = tempdev2,
      FILENAME = ‘T:Microsoft SQL ServerMSSQLDatatempdb2.mdf‘,
      SIZE = 2048MB,
      FILEGROWTH = 512MB

      ALTER DATABASE tempdb
      ( NAME = tempdev,
      SIZE = 2048MB,
      FILEGROWTH = 512MB

      ALTER DATABASE tempdb
      NAME = templog,
      SIZE = 512MB,
      FILEGROWTH = 128MB

      SQL 2008 R2: Windows Firewall (Part 5)

      One of the best things Microsoft did with Windows Server 2008 and later is the built-in firewall. Unlike previous OS releases where the firewall was pretty much a joke, Microsoft started from scratch and came up with a very robust two-way firewall. SQL is one of the prime targets for hackers as databases can contain a plethora of juicy data like credit card numbers, social security numbers, and other personal data.

      As part of my standard SQL 2008 R2 installation I run a script which only allows inbound SQL requests from specific remote IP addresses. Requests from any other machine in the world will be dropped. Depending on what SQL services and features you are using, it is likely the script will need some tweaking. But the script below opens the basic SQL port (1433) and SQL browser port (1434). Reporting services, analysis services, etc. will need unique rules to allow them to function. Be sure to change the path to point to where your SQL binaries are installed.

      @echo off
      :: Configures Windows Server 2008/R2 firewall for SQL 2008 R2.
      :: Version 1.1, 5 July, 2010
      :: Requires one argument, the IP address of the remote server that requires SQL access.
      :: Usage: SQL-Firewall.cmd

      if [%1]==[] ; GOTO :ERROR

      Echo Configuring Windows Advanced Firewall for SQL to listen on IP %1

      netsh advfirewall firewall add rule name=”SQL Server (TCP-in)” dir=in action=allow protocol=TCP Profile=domain localport=1433 program=”D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnsqlservr.exe” description=”Allows inbound Microsoft SQL connections.” remoteip=%1

      netsh advfirewall firewall add rule name=”SQL Server Browser (TCP-in)” dir=in action=allow protocol=TCP Profile=domain localport=1434 program=”D:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinnBinnsqlservr.exe” description=”Allows inbound Microsoft SQL browser connections.” remoteip=%1

      Exit /B

      Echo Please specify IP address.