SQL Server 2008 Installation

SQL Server 2008 Installation

Install SQL Server 2008 R2

Contents

Introduction

This article describes the steps necessary to install a clean version of Microsoft SQL Server 2008 R2 on the Windows Server 2008 operating environment. Installing SQL Server is not a difficult task by any means, however, critical decisions will be discussed during the setup and configuration described in this guide that will have a great impact on your installation.

This guide will include instructions for installing the SQL Server Database Engine instance, Analysis Services, Reporting Services, Integration Services, as well as all supplementary sub-features like SQL Server Replication, Full-Text Search, Shared Components, and all of the tools that ship with SQL Server.

SQL Server 2008 R2 Editions

SQL Server 2008 R2 provides a variety of different editions of the product that are all tailed to meet the needs of different organizations and application requirements. In most cases, it will be apparent which edition of SQL Server you will need to install. For the purpose of this guide, I will be installing Microsoft SQL Server 2008 R2 Enterprise Edition.

While this guide provides step-by-step instructions for successfully installing SQL Server, it is by no means a substitute for the official SQL Server documentation from Microsoft. Microsoft's official SQL Server documentation site is http://msdn.microsoft.com/en-us/library/bb545450.aspx.

In addition to this guide, users should also consult the following Microsoft SQL Server documents to gain a full understanding of alternative configuration options, installation, and administration with SQL Server 2008.

• SQL Server Books Online - (SQL Server 2008 R2)
• Features Supported by the Editions of SQL Server 2008 R2
• SQL Server Developer Center
• SQL Server TechCenter
• Microsoft White Papers
• SQLCAT Articles

Hardware and Software Configuration

The following table lists the hardware and software I will be using for the SQL Server 2008 R2 installation described in this article.

1 The machine vmwindows1.development.info is a member of the DEVELOPMENT.INFO domain.

2 During the SQL Server 2008 R2 setup on the Database Engine Configuration page, the wizard allows you to specify the Data root directory which in this guide will be set to F:\MSSQL\. This setting is used by the wizard to configure the default directories for the various database files and transaction log files required for the instance (system databases, user databases, transaction logs, tempdb, and backups). All of the default directories chosen by the wizard are user-configurable during the setup process with the exception of the system databases. With the SQL Server instance ID in this guide set to MSSQLSERVER and the Data root directory set to F:\MSSQL\, the wizard will automatically specify a non-user configurable default directory for the system databases as F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data. After the install, I intend to move the system databasesto a more user user friendly directory such as F:\MSSQL\Data.

3 The DEVELOPMENT\SQLServerAdmin Active Directory user account, known in this guide as the SQL Server Administrator, is a member of the DEVELOPMENT.INFO Domain Users group and a member of the local Administrators group on the vmwindows1.development.info server. This account will be used to install and setup the SQL Server 2008 R2 software.

Installation Pre-Requisites

In this guide, I don't intend to provide an exhaustive list of pre-requisite tasks for installing SQL Server 2008 R2. I will, however, highlight several considerations that should be made which will provide for a successful installation.

Copy the Software Installation Files

It is recommended to copy the entire directory structure from the SQL Server 2008 R2 installation media to the C: drive of the machine you will be performing the installation on. Although the copy process can take a little time to complete, it does provide for the following advantages:

• It makes the installation and setup process significantly faster than running it from the CD/DVD installation media.
• It allows you to easily add or remove components at a later time without having to search for and locate the CD/DVD installation media.
• If the CD/DVD installation media is damaged for any reason and a file isn't accessible, you have the advantage of knowing now rather than halfway through the installation process.

The following is what my system looks like after copying the directory structure from the CD/DVD installation media to the local disk:

Minimum Hardware and Software Requirements

Whether you are installing SQL Server 2008 in a lab or for a critical production application, you must ensure that the hardware and software being used meets the minimum system requirements. Click here for a detailed overview of the hardware and software requirements for installing SQL Server 2008 R2.

SQL Server Service Accounts

All of the core components in SQL Server run as services. Depending on the components that you decide to install, SQL Server Setup installs the following services:

• Database Engine
• SQL Server Agent
• Analysis Services
• Reporting Services
• Integration Services
• Full Text Search
• Server Browser

During the SQL Server 2008 R2 setup process, you will be asked to assign a low-privilege login account for each of the SQL Server services. You can assign the same login account to all SQL Server services, or you can configure each service account individually. In this guide, I will create a separate Active Directory login account for each of the SQL Server services (see below). In addition, I'll create a dedicated Active Directory login account that will be used to install and setup the SQL Server software (known in this guide as the SQL Server Administrator). These login accounts will need to be created prior to installing SQL Server.

It is not mandatory that service accounts or the SQL Server Administrator be created as Active Directory users. It is fully acceptable to use local user accounts or in some cases, built-in Windows accounts.

From the Active Directory Domain Services (AD DS) machine, bring up the Active Directory Users and Computers application and select the Action / New / User menu option. Create the Active Directory login accounts by supplying the Full Name and User Logon Name. After the user is created, the login account will be a member of the Domain Users group.

Repeat the same procedure to create the service accounts listed in the following table (if you are not using Active Directory, these can be setup as local user accounts):

Create SQL Server Service Accounts in Active Directory

Purpose	Full Name	User Logon Name	Member Of
SQL Server Service Account	SQL Server Service - (Agent)	SQLServerAgent	Domain Users
SQL Server Service Account	SQL Server Service - (Database Engine)	SQLServerEngine	Domain Users
SQL Server Service Account	SQL Server Service - (Analysis Services)	SQLServerAnalysis	Domain Users
SQL Server Service Account	SQL Server Service - (Reporting Services)	SQLServerReporting	Domain Users
SQL Server Service Account	SQL Server Service - (Integration Services)	SQLServerIntegration	Domain Users

Next, create a dedicated Active Directory login account for the SQL Server Administrator that will be used to install and setup the SQL Server software(or a local account if you are not using Active Directory). The Active Directory login account will be named DEVELOPMENT\SQLServerAdmin and will be a member of the Domain Users group as well as the local Administrators group of the database machine (vmwindows1.development.info).

After creating the SQL Server Administrator login account, add it to the local Administrators group for the database machine. This task can be performed from the AD DS server if the database machine has been added to the domain or it could be done from the database machine itself while logged in as an Administrator. Bring up the Active Directory Users and Computers application from the AD DS machine and click on the Computersfolder under the domain.

Right-click on the database machine (vmwindows1.development.info) and click Manage.

The Computer Management window will be displayed. Under Computer Management / System Tools / Local Users and Groups, click on theGroups folder.

Double-click the Administrators group and add the SQL Server Administrator login account.

Click OK and verify that the SQL Server Administrator login account has been added to the local Administrators group for the database machine.

When complete, your screen should look similar to the following:

Click here for a more in-depth look at SQL Server 2008 service account privileges.

Reboot Machine

It's a good idea to reboot the machine prior to starting the SQL Server installation process. This ensures that you do not have any pending reboot requests which could block the SQL Server installation and setup process.

Install SQL Server 2008 R2

Once all of the pre-requisites have been met, it's time to start the SQL Server 2008 R2 installation.

1. Start SQL Server 2008 R2 Setup

Log in to the database machine with administrative privileges and double-click on the setup.exe file to start the SQL Server 2008 R2 installation process. For the purpose of this guide, I will be performing the SQL Server installation as the domain user DEVELOPMENT\SQLServerAdmin which is a member of the local Administrators group on the server:

1. Install the .Net Framework 3.5

The .Net Framework 3.5 SP1 (also referred to as .NET Framework 3.5.1) is a prerequisite for SQL Server 2008 R2. If the required .Net Framework is not already installed, then the following message will appear when you start the SQL Server 2008 R2 installation:

Click on OK to start the .Net Framework setup process. The wizard will extract files from the package and then load the installation components. This may take a minute or two to complete.

After the installation components are loaded, the wizard displays the .Net Framework Setup screen. Read over the license agreement. If you agree with the license agreement, click Install to continue.

The setup will then download the .Net Framework package from the Microsoft website and install it on your machine.

After the .Net Framework package has been successfully installed, the following will appear. Click Exit to continue.

Once the .Net Framework is installed, the SQL Server 2008 R2 setup will commence:

1. SQL Server Installation Center

On the SQL Server Installation Center landing page, first select the Installation page, and then click the New installation or add features to an existing installation option.

1. Setup Support Rules

The wizard will run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click OK to continue with the installation.

1. Product Key

On the Product Key page, specify a free edition of SQL Server such as Evaluation or Express Edition or provide a SQL Server 25-character product key to validate this instance of SQL Server 2008 R2, and then click Next to continue.

1. License Terms

Read through the Microsoft Software License Terms. If you agree with the license agreement, choose I accept the license terms, and then click Next to continue.

1. Setup Support Files

On the Setup Support Files page, the wizard will indicate whether or not any additional setup support files are required for the SQL Server installation. If additional files are required, review the status of the files required, and then click Install.

1. Setup Support Rules

The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Nextto continue.

1. Setup Role

On the Setup Role page, select the SQL Server Feature Installation option which will allow you to individually select which feature components to install.

1. Feature Selection

On the Feature Selection page, choose which features to install. For the purpose of this guide, I will be installing the SQL Server Database Engine instance, Analysis Services, Reporting Services, Integration Services, as well as all supplementary sub-features like SQL Server Replication, Full-Text Search, Shared Components, and all of the tools that ship with SQL Server.

In addition to selecting which features to install, set the directory that should be used to install SQL Server shared features. For this guide, I will be installing the shared features and the SQL Server software (next screen) to the C:\Program Files\Microsoft SQL Server\directory (the default).

1. Installation Rules

The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Nextto continue.

1. Instance Configuration

On the Instance Configuration page, specify whether to create a Default Instance, which is MSSQLSERVER, or a Named Instance of SQL Server. Note that each instance ID name must be unique and there can only be one default instance per SQL Server system. In this guide I will be configuring a Default Instance.

On the same screen, enter the Instance Root Directory where the SQL Server 2008 R2 software should to be installed. By default, the instance root directory is C:\Program Files\Microsoft SQL Server\. For this guide, I will be using the default instance root directory to install the SQL Server software.

1. Disk Space Requirements

On the Disk Space Requirements page, review the disk space requirements for the SQL Server components and features you selected for your installation, and then click Next to continue.

1. Server Configuration

On the Server Configuration page, specify the low-privilege login account name and password for the appropriate service accounts that you created in the Server Service Accounts|SQL Server Service Accounts]] section to this guide. Next, specify whether services should start automatically, are started manually, or are disabled. Note that the default startup type for the SQL Server Agent service is manual which means when the machine is rebooted, the service does not start up and any jobs that the agent handles will not run. If you intend to execute jobs and automate administrative tasks, it is recommended to change the SQL Server Agent service to start automatically. I also prefer to change the SQL Server Browser service to start automatically. Finally, review the collation settings for the Database Engine (and Analysis Service if it was selected for install), and then click Next to continue.

1. Database Engine Configuration

The Database Engine Configuration page consists of three tabs — Account Provisioning, Data Directories, and FILESTREAM.

On the first tab, Account Provisioning, specify the Authentication Mode which consists of either Windows Authentication Mode or Mixed Mode (SQL Server authentication and Windows authentication). With Windows authentication, you can only authenticate connections to a SQL Server instance using Windows accounts (Active Directory user accounts, local user accounts, or groups). With Mixed Mode authentication, users can authenticate to a SQL Server instance using SQL Server accounts or Domain / Local User accounts. If you select Mixed Mode authentication, enter and confirm a strong password for the built-in SQL Server system administrator (sa) account. Although Microsoft highly recommends Windows-only authentication, many third party applications cannot authenticate with Windows and rely on SQL Server logins to operate correctly. In cases like this, enabling Mixed Mode authentication is needed.

Next, provision a SQL Server Administrator that will have administrative permissions on the Database Engine by either selecting the option Add Current User or using the Add button to specify other SQL Server administrator accounts.

Select the second tab, Data Directories, to specify the default database directories associated with the installation of this SQL Server instance. Depending on the number of disks available on the database server, it is highly recommended to place the System Databases, User Databases, User Databases Logs, TempDB Data, TempDB Log and Backup directory on different volumes other than the default. At a minimum, it is a best practice to store database log files on a separate disk volume then database data files. Performance can be significantly improved as log file I/O's are sequential and database data file I/O's are mostly random. It is also recommended to keep TempDB related data and log files on their own volume to further avoid I/O contention and improve performance.

Notice that the wizard allows you to specify the Data Root Directory which in this guide will be set to F:\MSSQL\. By default, all sub-directories build off the Data Root Directory for the various database files and transaction log files required for the instance. All of the default directories chosen by the wizard are user-configurable during the setup process with the exception of the system databases. With the SQL Server instance ID used in this guide set to MSSQLSERVER and the Data Root Directory set to F:\MSSQL\, the wizard will automatically specify a non-user configurable default directory for the system databases as F:\MSSQL\MSSQL10_50.MSSQLSERVER\MSSQL\Data. After the install, I intend to move the system databases to a more user user friendly directory such as F:\MSSQL\Data.

Finally, click on the FILESTREAM tab if you would like to enable FILESTREAM for the setup of this SQL Server instance. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

Use the following options to enable FILESTREAM during the setup of this SQL Server instance.

• Enable FILESTREAM for Transact-SQL access

Select to enable FILESTREAM for Transact-SQL access. This control must be checked before the other control options will be available.

• Enable FILESTREAM for file I/O streaming access

Select to enable Win32 streaming access for FILESTREAM.

• Windows share name

Use this control to enter the name of the Windows share in which the FILESTREAM data will be stored.

• Allow remote clients to have streaming access to FILESTREAM data

Select this control to allow remote clients to access this FILESTREAM data on this server.

Because many databases do not store unstructured data and given the restrictions imposed on using FILESTREAM, many organizations will not make use of this feature. It is advisable to leave FILESTREAM disabled unless you intend to use it. If you decide to use FILESTREAM after SQL Server has already been installed, it can be easily enabled using either SQL Server Configuration Manager or by running the following Transact-SQL code from Query Editor:

EXEC sp_configure filestream_access_level, 2 RECONFIGURE

1. Analysis Services Configuration

If you selected to install an Analysis Services instance, then the Analysis Services Configuration page will be displayed with two tabs — Account Provisioning and Data Directories.

On the first tab, Account Provisioning, specify users that have administrative permissions for Analysis Services by either selecting the optionAdd Current User or using the Add button to specify other SQL Server administrator accounts.

Next, click the Data Directories tab to specify the directories for data, transaction logs, tempdb, and backups for the Analysis Services instance.

1. Reporting Services Configuration

If you selected to install a Reporting Services instance, then the Reporting Services Configuration page will be displayed.

On the Reporting Services Configuration page, specify options that determine how the Report Server instance will be installed. You can specify Native mode or SharePoint integrated mode with a default configuration or you can install but not configure the report server.

If you select Native mode default configuration, the Report Server will be available as soon as the SQL Server setup is finished.

It is important to note that with a default configuration, the Report Server is installed on the SQL Server instance currently being configured which means that the names used for various components will reflect the instance name. If you do not want to use the default configuration, select the option to Install, but do not configure the report server and then use the Reporting Services Configuration Manager application after installing SQL Server to manually configure the Report Server.

1. Error Reporting

Use the Error Reporting page of the SQL Server Installation Wizard to enable feature error reporting functionality for SQL Server. By enabling this option, any error information from SQL Server can be sent over secure HTTP (HTTPS) to Microsoft by default or to a designated corporate error reporting server if you have one configured in Active Directory Group Policy. This option can be safely skipped if you do not wish to share any of this information.

1. Installation Configuration Rules

The wizard will again run the system validation tool to perform a System Configuration Check which looks for conditions on the computer that might block the setup of SQL Server. On the Setup Support Rules page, review the output from the System Configuration Check to ensure all tests associated with the operation passed without any failures or warnings. Click the Show details button (as shown below) or View detailed report to review a standard or comprehensive report of the System Configuration Check. If no failures or warnings are reported then click Nextto continue.

1. Ready to Install

On the Ready to Install page, review the various configuration options you selected during the setup wizard. If you need to make any changes to the installation settings, click the Back button. If the installation settings are correct, click the Install button to start the SQL Server 2008 R2 installation.

1. Installation Progress

The Installation Progress page will track the progress of the components as they are being installed as well as the progress of the entire SQL Server installation.

1. Complete

The installation and setup of SQL Server 2008 R2 is finally complete! The Complete page provides a link to a summary log file of the installation. This log contains important information about the SQL Server setup and should be saved for future reference. Click the Close button to exit from the SQL Server R2 Setup screen.

The SQL Server R2 Setup screen will close and you will be returned to the SQL Server Installation Center landing page. Click on the Closebutton () in the top right of the screen to exit the SQL Server installer.

Post-Installation Steps

This section contains a short list of post-installation checks which are useful to ensure SQL Server 2008 R2 was installed correctly and connections can be made to the various SQL Server components.

SQL Server Services

One of the first checks should be to verify that all required SQL Server services have been started.

Start SQL Server Management Studio

Next, start SQL Server Management Studio. From the Windows Start menu, select Start / All Programs / Microsoft SQL Server 2008 R2 / SQL Server Management Studio.

After authenticating to the new SQL Server instance, run a simple query. Right-click on the new SQL Server instance and select New Query.

Enter the following query and hit F5 to execute:

Access SQL Server Instance from the Network

Check that the new SQL Server instance can be accessed from the network. From another SQL Server on the same domain, run SQLCMD -L.

If you can't see the new SQL Server in this list or the server list is empty, check the following:

• SQL Server Browser Service

Check that the SQL Server Browser service is started on the machine where you have just installed SQL Server.

• Windows Firewall

Check that Windows Firewall is disabled.

• HideInstance Network Protocol Option

Check that the HideInstance network protocol option is set to No. On the machine running the new SQL Server instance, open SQL Server Configuration Manager, expand SQL Server Network Configuration, and right-click the Protocols for <Instance ID> node and clickProperties. If HideInstance is set to Yes, this instance will be hidden from sqlcmd -L.

Next, log on to the new instance from another SQL Server on the same domain using SQLCMD.EXE.

SQL Server Agent

Check that the SQL Server Agent is running (if you enabled the service to start automatically). You should see a green arrow next to the SQL Server Agent node.

Test Reports Server

If you selected to install a Services Configuration|Reporting Services instance]] and setup a native mode default configuration, verify that the Report Server and Report Manager is functioning correctly. The virtual directories to use for the Report Server and Report Manager can be accessed from a Web browser as follows:

• SQL Server Default Instance

For a default SQL Server instance, use http://ServerName/DirectoryName where ServerName is the host name or DNS name for the machine and DirectoryName is the name of the virtual directory for either the Report Server (ReportServer) or Report Manager (Reports). For example:

http://vmwindows1/ReportServer

http://vmwindows1/Reports

• SQL Server Named Instance

For a named SQL Server instance, use http://ServerName/DirectoryName$InstanceName where ServerName is the host name or DNS name for the machine, DirectoryName is the name of the virtual directory for either the Report Server (ReportServer) or Report Manager (Reports), and InstanceName is the SQL Server instance name. For example:

http://vmwindows1/ReportServer$WebApps01

http://vmwindows1/Reports$WebApps01

Click here for instructions on how to authenticate to the Report Server and resolve any permission errors you may receive while accessing the Reporting Services.

Install AdventureWorks Sample Databases

Starting with SQL Server 2005, the AdventureWorks sample databases are not installed by default due to security concerns. Users are now required to download and manually install the sample databases after successfully setting up SQL Server.