Getting Started with Microsoft SQL Server 2008 R2

Microsoft SQL Server 2008 R2 Administration Cookbook

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system

Introduction

Microsoft SQL Server 2008 has opened up a new dimension within data platforms and SQL Server 2008 R2 has been developed on the areas of core Database Platform and rich Business Intelligence. On the core database environment, SQL Server 2008 R2 advances the new enhancements as a primary goal of scalability and availability for highly transactional applications on enterprise-wide networks. On Business Intelligence platforms, the new features that are elevated include Master Data Management (MDM), StreamInsight, PowerPivot for Excel 2010, and Report Builder 3.0. The SQL Server 2008 R2 Installation Center includes system configuration checker rules to ensure the deployment and installation completes successfully. Further, the SQL Server setup support files will help to reduce the software footprint for installation of multiple SQL instances.

This article begins with SQL Server 2008 R2 version's new features and enhancements, and adding the service pack features using Slipstream technology. Then an explanation towards how best the master data services can help in designing and adopting key solutions, working with data-tier applications to integrate development into deployment, and an explanation of how best the federated servers enhancement can help to design highly scalable applications for data platforms.

Adding SQL Server 2008 R2 Service Pack features using Slipstream technology

The success of any project relies upon the simpler methods of implementation and a process to reduce the complexity in testing to ensure a successful outcome. This can be applied directly to the process of SQL Server 2008 R2 installation that involves some downtime, such as the reboot of servers. This is where the Slipstream process allows other changes to the databases or database server. This method offers the extension of flexibility to upgrade the process as an easier part, if there are minimal changes to only those required for the upgrade process. The following recipe is prepared to enable you to get to know Slipstream.

Slipstream is the process of combining all the latest patch packages into the initial installation. The major advantage of this process is time, and the capability to include all the setup files along with service pack and hotfixes. The single-click deployment of Slipstream helps us to merge the original source media with updates in memory and then install the update files to enable multiple deployments of SQL Server 2008 R2.

Getting Ready

In order to begin adding features of SQL Server using Slipstream, you need to ensure you have the following in place:

• .NET Framework 3.5 Service Pack 1: It helps improvements in the area of data platform, such as ADO.NET Entity Framework, ADO.NET data services, and support for new features of SQL Server 2008 version onwards.
  You can download .NET Framework 3.5 Service Pack 1 from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ab99342f-5d1a-413d-8319-81da479ab0d7&displaylang=en.
• Windows Installer 4.5: It helps the application installation and configuration service for Windows, which works as an embedded chainer to add packages to a multiple package transaction. The major advantage of this feature enables an update to add or change custom action, so that the custom action is called when an update is uninstalled.
  You can download Windows Installer 4.5 redistributable package from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=5A58B56F-60B6-4412-95B9-54D056D6F9F4.
• SQL Server setup support files: It installs SQL Server Native Client that contains SQL OLEDB provider and SQL ODBC driver as a native dynamic link library (DLL) supporting applications using native code APIs to SQL Server.

How to do it...

Slipstream is a built-in ability of the Windows operating system and since the release of SQL Server 2008 Service Pack 1, it is included.

The best practice is to use Slipstream Service Pack as an independent process for Service pack installation, Cumulative Update patching, and Hotfix patching. The key step to Slipstream success is to ensure the following steps are succeeded:

1. The prerequisite steps (mentioned in the earlier sections) are completed.
2. In case of multiple language instances of SQL Server, we need to ensure that we download the correct service pack language from http://www.microsoft.com/downloads/en/ that suits the instance.
3. The Service Pack files are independent to each platform to download, such as X86 for 32-bit, X64 for 64-bit, and IA64 for Itanium platform.

To perform the Slipstream Service Pack process, you need to complete the following steps:

1. Create two folders on the local server: SQL2K8R2_FullSP and SQL2K8R2SP.
2. Obtain the original SQL Server 2008 R2 setup source media and copy to SQL2K8R2_FullSP folder.
3. Download the Service Pack1 from Microsoft Downloads site to save in SQL2K8R2SP folder, as per the platform architecture:
   • SQLServer2008SP1-KB968369-IA64-ENU.exe
   • SQLServer2008SP1-KB968369-x64-ENU.exe
   • SQLServer2008SP1-KB968369-x86-ENU.exe

4. Extract the package file using Windows Explorer or using a command prompt operation, as shown in the following screenshot:

   

5. In case the platform consists of multiple SQL instances with different architectures, for instance SQL Server 2008 R2 Enterprise Edition 64-bit as a default instance and SQL Server 2008 R2 Standard Edition as a named instance, then make sure you download the relevant architecture file http://www.microsoft.com/downloads/en/ as stated previously and extract to relevant folders.
6. This is the first checkpoint to proceed further and the key to ensuring the original setup media is updated correctly.
7. Copy the executable and localized resource file from the extracted location to the original source media location using robocopy utility, which is available from Windows Server 2008 onwards:

   

8. Copy all the files except the module program file that is executed by various programs and applications in Windows operating systems.

   

9. It is important to ensure the correct architecture files are copied, such X64 and X86 related files.
10. In addition to the initial checkpoint, this additional checkpoint is required in order to ensure the correct path is specified that will be picked up by Slipstream during the setup of SQL Server 2008 R2 and Service Pack installation.
11. The defaultsetup.ini is the key to guide the Slipstream process to install the RTM version and Service Pack files. The file can be located within the SQL2K8R2_FullSP folder as per the architecture.
12. From Windows Explorer, go to the SQL2K8R2_FullSP folder and open the defaultsetp.ini file to add the correct path for the PCUSOURCE parameter.

    

Unlock access to the largest independent learning library in Tech for FREE!
Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of.
Renews at $19.99/month. Cancel anytime
START FREE TRIAL
13. The file can be located from the SQL Server setup folder location for the processor, for instance, the 32-bit platform the file is available from servernamedirectorySQL Server 2008 R2X86 folder.
14. The previous screenshot represents the file existence within the server, to ensure that the matching SQL Server Product ID (license key) is supplied.
15. There is more attached to the process if the file does not exist, there is no harm to the Slipstream process, the file can be created at the original folder defined in the following steps.
16. It is essential that the license key (product ID) and PCUSource information is included as follows:

    ;SQLSERVER2008 Configuration File
    [SQLSERVER2008]
    PID="??"
    PCUSOURCE=??

    
    

17. Now, the PCUSOURCE value should consist of the full path of Service pack files that are copied during the initial step, the entry should be as follows:

    add PCUSOURCE="{Full path}PCU".

    
    

18. The full path must include the absolute path to the PCU folder, for instance, if the setup files exist in local folder the path must be as follows:

    <drivename>SQLServer2008R2_FullSP

    
    

19. If that folder is shared out, then the full path must be:

    MyServerSQLServer2008_FullSP1

    
    

20. The final step of this Slipstream process is to execute the setup.exe from SQL2K8R2_FullSP folder.

How it works...

The Slipstream steps and installation process are a two-fold movement. Slipstream uses the Remote Installation Services (RIS) technology of Windows Server services to allow configuration management to be automated. The RIS process is capable of downloading the required files or images from the specific path to complete the installation process.

The SQL Server 2008 R2 setup runs a pre-check before preceding the installation. The System Configuration Check (SCC) application scans the computer where the SQL Server will be installed. The SCC checks for a set of conditions that prevent a successful installation of SQL Server services.

Before the setup starts the SQL Server installation wizard, the SCC executes as a background process and retrieves the status of each item. It then compares the result with the required conditions and provides guidance for the removal of blocking issues.

The SQL Server Setup validates your computer configuration using a System Configuration Checker (SCC) before the Setup operation completes using a set of check-parameters that will help to resolve the blocking issues. The sample list of check-parameters is as follows:

The following are some of the additional checks that SCC performs to determine if the SQL Server editions in an in-place upgrade path are valid:

• Checks the system databases for features that are not supported in the SQL Server edition to which you are upgrading
• Checks that neither SQL Server 7.0 nor SQL Server 7.0 OLAP Services is installed on the server

SQL Server 2008 or higher versions are not supported on the server that has SQL Server 7.0.

• Checks all user databases for features that are not supported by the SQL Server edition
• Checks if the SQL Server service can be restarted
• Checks that the SQL Server service is not set to Disabled
• Checks if the selected instance of SQL Server meets the upgrade matrix requirements
• Checks if SQL Server Analysis Services is being upgraded to a valid edition
• SCC checks if the edition of the selected instance of SQL Server is supported for 'Allowable Upgrade Paths'

There's more...

As the prerequisite process of Slipstream is completed, we need to ensure that the installation of SQL Server 2008 R2, Service Pack, and Hotfixes patches are applied with the setup steps. To confirm the workflow process is followed correctly from the folder SQL2K8R2_FullSP, double-click on setup.exe file to continue the installation of RTM version, Service Pack, and required hotfix patches.

While continuing the setup at the Installation Rules screen, the SCC rule checks for Update Setup Media Language Compatibility value, which should be passed in order to proceed, as shown in the following screenshot:

If you have failed to see the update setup media language rule, then the same information can be obtained once the installation process is completed. The complete steps and final result of setup are logged as a text file under the folder: C:Program FilesMicrosoft SQL Server100Setup BootstrapLog. The log file is saved as Summary_<MachineName>_Date_Time.txt, for example, 'Summary_DBiASSQA_20100708_200214.txt'.