Installing SQL Server 2005
Microsoft SQL Server 2005 Setup is the tool for installation of all SQL Server components. The setup tool provides the functionality to install a new instance of SQL Server, upgrade an existing instance, or add a component to an existing instance.
The installation steps are consistent for all SQL Server components. We start by describing the installation steps for SQL Server Database Engine, and for other components we refer to the common steps described for the Database Engine.
Installing SQL Server Database Engine
Here are the steps for installing the Database Engine component:
Log on to the computer that will be hosting the SQL Server instance with a user account that has local administration privileges.
If you have a SQL Server DVD, insert the DVD in the DVD drive and let Autoplay launch the splash screen. If Autoplay does not run, you can manually launch it by running splash.hta
on the DVD. On the splash screen (see Figure 2-1
), click the Run the SQL Server Installation Wizard
Figure 2-1. The main screen of SQL Server 2005 DVD provides a set of installation and preparation options.
If you have downloaded or copied the SQL Server installation files, navigate to the Servers
folder and run Setup.exe
. This launches the Microsoft SQL Server Setup Wizard
, as illustrated in Figure 2-2
Figure 2-2. The SQL Server 2005 Setup Wizard guides the user through the preinstallation options, such as the licensing terms and conditions.
Read the end user license agreement. If you agree with these terms and conditions, select I accept the licensing terms and conditions
and click on Next
The SQL Server 2005 Setup program itself requires installation of three components, as illustrated in Figure 2-3
. These components are
Microsoft .NET Framework Version 2.0
Microsoft SQL Server Native Client
Microsoft SQL Server 2005 Setup Support Files
Figure 2-3. The SQL Server 2005 Setup program requires installation of three components.
To proceed with the installation of these components, click on Install.
SQL Server 2005 has a dependency on .NET Framework 2.0, which takes a while to install on most computers. Although the SQL Server Setup wizard installs .NET Framework 2.0 before the installation process, you can preinstall this prerequisite before installing SQL Server 2005 to speed up the installation process.
You should be aware that if any of the prerequisite components have already been installed, that component does not appear in the list. In addition, the setup program does not remove these components if the installation fails or it is cancelled. If you want to remove these components after a failure, use Add or Remove Programs
in Control Panel
After the setup prerequisites are installed, the setup program launches the SQL Server Installation Wizard, as illustrated in Figure 2-4
. Click on Next
to proceed with the installation.
Figure 2-4. The SQL Server 2005 Installation Wizard guides the user through the installation options.
On the next screen (see Figure 2-5
), System Configuration Check (SCC) examines the computer against a predefined set of conditions that would prevent the installation wizard from doing a successful installation. SCC retrieves the status of each condition and compares it to the required status. If a condition is not met, it provides an advisory message next to the item. For a complete list of check items, conditions, and remedies, see the "Check Parameters for the System Configuration Checker" topic in Books Online. Click on Next
to proceed with the installation.
Figure 2-5. SQL Server Installation Wizard examines the system settings and existence of dependent components to ensure a successful installation.
On the Registration Information
screen (see Figure 2-6
), enter your username and optionally your company. Depending on the edition of SQL Server that you are installing, you may have to enter the product key. You can look up the product key from the sticker on the SQL Server 2005 DVD packaging. The product key needs to be entered only for the first installation.
During the subsequent installations, the product key appears automatically on this screen and it cannot be changed.
Figure 2-6. SQL Server Registration Information screen.
To install an instance of SQL Server Database Engine, follow the instructions for installing the installation Wizard. Click on Next.
On the Component to Install
screen, select SQL Server Database Services
(see Figure 2-7
Figure 2-7. SQL Server Installation Wizard provides the option to install Database Engine along with other components.
On the Instance Name
screen (see Figure 2-8
), select Default instance
. If you are installing a named instance, enter the name of the named instance. Click on Next
Figure 2-8. You can install the default instance or a named instance of SQL Server 2005.
The installation wizard examines the specified default or named instance and performs different tasks depending on the following conditions:
If the specified instance does not exist, it creates the instance and installs the selected components in that instance. In this scenario, where SQL Server Database Services was just selected, the installation wizard installs just the Database Engine component. But if we had selected additional components such as Analysis Services, it would have installed both Database Engine and Analysis Services in the specified instance.
If the instance already exists and it is a SQL Server 7.0 or SQL Server 2000 instance, the installation wizard upgrades the selected components to SQL Server 2005 components. We discuss this further in the upgrade section in this chapter.
If the specified instance is a SQL Server 2005 instance and the selected components do not exist in the specified instance, the installation wizard installs and adds the selected components to the instance.
If the specified instance is a SQL Server 2005 instance and the selected components already exist in the specified instance, the installation wizard tries to upgrade the editions of selected components. For example, if the selected instance already has a Database Engine Workgroup Edition and you are running the installation wizard from a SQL Server Enterprise Edition, the installation wizard upgrades the Database Engine component to Enterprise Edition.
The next screen of the wizard, titled Service Account
(see Figure 2-9
) provides the functionality to specify credentials for SQL Server services and their startup behaviors. SQL Server services typically run with the same security credentials, but if you want to specify a different credential for each of the services, use the option Customize for each service account
. SQL Server services can also run by using one of the security credentials of the local computer accounts. As depicted in Figure 2-10
, you can choose the option Use the built-in System account
to specify Local System
, Network Service
or Local Service.
To run the services with a specific domain credentials, select the option Use a domain user account
and enter its username, password, and domain on this screen.
Figure 2-9. SQL Server Installation Wizard provides the option to assign service accounts.
Figure 2-10. SQL Server service accounts can be one of the built-in system accounts.
We recommend that you do not use any of the built-in accounts for the SQL Server services because they may be too powerful and prone to security threats. Instead, use a local user or a domain user account with the lowest possible privileges. For more information and best practices, see "Security Considerations for a SQL Server Installation" in Books Online.
After installation, you can change the service accounts by using SQL Server Configuration Manager as described in Chapter 3.
You can specify the startup behavior of SQL Server services after the installation and when Windows is started by using the Start services at the end of setup
options on the bottom of the screen (refer to Figure 2-9
). You should know that SQL Server Agent Service has a dependency on SQL Server Database Engine service; therefore, SQL Server Agent cannot be started unless SQL Server Database Engine is already started.
Although the installation wizard displays only three services for each instance of Database Engine, it creates two additional services named SQL Server Full-Text Search and SQL Server VSS Writer, which are not displayed on the installation wizard. During installation, the wizard assigns the same credential as the SQL Server service to the SQL Server Full-Text Search service, and the built-in local System credential to the SQL Server VSS Writer service.
SQL Server Database Engine can use SQL Server Authentication or Windows Authentication to authenticate client connections to the server. In Windows Authentication mode, SQL Server applies users' Windows credentials without requiring users to provide a username or password. In SQL Server Authentication mode, users must provide a login and password, which are stored in the database engine. The option for Database Engine to use Windows Authentication Mode
or Mixed Mode
(Windows Authentication plus SQL Server Authentication) can be specified during the installation of the engine. We recommend that you use Windows Authentication Mode
because it is more secure than Mixed Mode
. New in SQL Server 2005, you can enable enforcement of strong passwords and password expiration, and more importantly configure your server with Kerberos protocol encryption, which is essential for implementing a secure system. These topics are thoroughly described in SQL Server Books Online.
On the Authentication Mode
page of the installation wizard (see Figure 2-11
), you have the option to specify Windows Authentication Mode
or Mixed Mode
. If you choose Mixed Mode
, you must also provide a password for the sa (system administrator) account. Once again, we want to reemphasize that you choose Windows Authentication Mode
, but if you must use mixed mode, you should specify a strong sa password for the sa account. For additional information, see "Strong Password Guidelines" in SQL Server Books Online.
Figure 2-11. SQL Server Database Engine security options during installation process.
After installation, you can change the authentication mode by using the Server Properties dialog in SQL Server Management Studio.
The next page of the installation wizard (see Figure 2-12
), titled Collation Settings
, provides the functionality to specify the default Database Engine collation settings. Generally, you do not need to modify the default value for the collation. The installation wizard suggests the most appropriate collation based on the system locale for your computer. You need to change the Collation designator and sort order
only if the collation setting for this instance of SQL Server must match the collation of another instance of SQL Server. You can also change the SQL Collation
to use a collation that is compatible with previous versions of SQL Server (perhaps as part of an upgrade scenario).
Figure 2-12. Setting SQL Server collation during the installation process.
SQL Server 2005 provides the functionality to send information about feature usage and serious system errors to Microsoft. This information helps the SQL Server team to improve the product by fixing the most common bugs, and it gives them a better understanding of product usage scenarios. The Error and Usage Report Settings
page of the installation wizard (see Figure 2-13
) gives the option to opt in or out of each of these error and usage reporting options.
Figure 2-13. SQL Server Installation Wizard provides the option to opt in or out of error and usage reporting for all its components.
The usage reporting option is applicable to the following components and tools:
After installation, you can change or fine-tune the error and usage reporting settings by using the Error and Usage Report Settings tool, as described in Chapter 3.
Up until this point, the installation wizard has been gathering configuration information about the installation options, and aside from the setup prerequisites, it has not installed any files or Registry keys. The Ready to Install
(see Figure 2-14
) page displays the components that it is about to install. Click on Back
if you want to go to the previous pages of the wizard to review or change your selections. Click on Install
to proceed with the actual installation.
Figure 2-14. After you specify the installation option, the installation wizard provides a report of the components that will be installed.
While the wizard is busy with the installation process, the Setup Progress
screen (see Figure 2-15
) displays the progress and status of the installation. You can click on the status link for each component to view its installation log. Clicking on Cancel
is not a good idea because it cancels the installation of only those components that are incomplete; not the ones that have already been installed.
Figure 2-15. Progress and status reporting during the installation process.
When the installation is completed, the wizard displays the completion page, as illustrated in Figure 2-16
. The page provides a link to the installation summary log, where you can view information about errors that may have occurred during the installation. It is good practice to always view the log after installation. To learn about reading logs, search for the topic "How to Read a SQL Server 2005 Setup Log File" in SQL Server Books Online.
Figure 2-16. Final screen of SQL Server Installation Wizard provides a link to the installation log.
Installing Analysis Services
Installation of Analysis Services is similar to the installation of Database Engine, except on the Components to Install screen of the installation wizard, you choose the Analysis Services option, as illustrated in Figure 2-17. Analysis Services can be installed as a standalone component or in conjunction with other components.
Figure 2-17. SQL Server Installation Wizard provides the option to install an instance of Analysis Services.
Analysis Services can be installed in the same instance as other components, such as Database Engine, or in a separate instance; the instance can be the default or a named instance. New in SQL Server 2005, you can install multiple instances of Analysis Services on the same computer.
Installing Reporting Services
Installation of Reporting Services is similar to the installation of Database Engine, except that on the Components to Install page of the installation wizard you choose the Reporting Services option, as illustrated in Figure 2-18. Reporting Services can be installed as a standalone component or in conjunction with other components in the same instance.
Figure 2-18. SQL Server Installation Wizard provides the option to install an instance of Reporting Services.
On the Components to Install page, you may find the Reporting Services option disabled. This is because the installation wizard could not find Internet Information Services (IIS) on your computer. To install IIS, cancel the SQL Server Installation Wizard, launch Control Panel, open Add/Remove Program, open Add/Remove Windows Component, select Internet Information System and click Next (see Figure 2-19).
Figure 2-19. Reporting Services requires installation of Internet Information Services (IIS).
Installing Integration Services
Installation of Integration Services is similar to the installation of Database Engine, except that on the Components to Install screen of the installation wizard you choose the Integration Services option, as illustrated in Figure 2-20. The installation Wizard does not prompt you to specify an instance because Integration Services is not an instance-aware component. Integration Services can be installed as a standalone component or in conjunction with other components in the same instance. There is no user interface in the installation wizard to assign a service account to the Integration Services service. The installation wizard automatically assigns the built-in Network Service account to the service. You can change the Integration Services service account in SQL Server Configuration Manager after the installation, as described in Chapter 3.
Figure 2-20. SQL Server Installation Wizard provides the option to install Integration Services.
The legacy Data Transformation Services (DTS) runtime utilities are installed with the legacy components under Workstation components.
Installing Notification Services
To install Notification Services, select the Notification Services option on the Component to Install screen of the installation wizard as depicted in Figure 2-21. However, the installation wizard does not really install Notification Services; it copies the necessary files for creation of a Notification Services instance. To create a Notification Services instance, you need to use SQL Server Management Studio, as described in Chapter 15. Therefore, it is a good idea to install the workstation components when you install Notification Services.
Figure 2-21. SQL Server Installation Wizard provides the option to install the files for Notification Services.
Installing SQL Server Tools and Workstation Components
As illustrated in Figure 2-22, you can select the Workstation components, Books Online and development tools option on the Components to Install screen of the installation wizard to install SQL Server tools and client components. This includes the following:
SQL Server Management Studio, SQL Server Configuration Manager, SQL Server Profiler, Database Engine Tuning Advisor, and Replication Monitor
Business Intelligence Development Studio
Connectivity Components: Network libraries for DB-Library, OLE-DB for OLAP, ODBC, ADODB, and ADOMD+
Software development kits
SQLXML Client Features
Legacy Components: Data Transformation Services (DTS) runtime utilities, SQL-DMO
SQL Server Books Online
Figure 2-22. SQL Server Installation Wizard provides the option to install workstation components, which include tools and Books Online.
To limit the installed component just to tools or other components, you can click on Advanced and use the menu option Entire feature will be unavailable to exclude the unwanted components, as depicted in Figure 2-23.
Figure 2-23. You can exclude installation of the unwanted client components.
Installing SQL Server Books Online
SQL Server Books Online is a comprehensive set of product documentation that helps you better understand the SQL Server features and functionality. Books Online is filled with feature explanations, examples, and best practices that makes it a DBA's best friend. We highly recommend that you install Books Online as part of every SQL Server installation.
To install SQL Server Books Online, select the Workstation components, Books Online and development tools option on the Components to Install screen of the installation wizard. This option installs a number of other components that you may not need. If you need to just install Books Online, on the Components to Install page of the installation wizard do not select any components and click on Advanced. On the Feature Selection dialog, expand Documentation, Samples, and Sample Databases, click on SQL Server Books Online, and select the Will be installed on local hard drive menu option as illustrated in Figure 2-24.
Figure 2-24. SQL Server Installation Wizard provides the option to install Books Online.
Installing Sample Databases, Sample Codes, and Sample Applications
SQL Server 2005 Database Engine has a new set of sample databases:
AdventureWorks New Online Transaction Processing (OLTP) sample database for Database Engine.
AdventureWorksDW New data warehouse sample database for Database Engine.
Adventure Works DW New Analysis Services sample database.
These sample databases are not installed by default. However, you have the option of installing them during the installation of a new instance, or copying the database files to attach them to an existing instance. For both options, you start by clicking on Advanced in the Components to Install screen of the installation wizard. Next, click on the Feature Selection dialog, expand Documentation, Samples, and Sample Databases, click on Sample Databases and select the Will be installed on local hard drive menu option, as illustrated in Figure 2-25. Click Next.
Figure 2-25. SQL Server Installation Wizard provides the option to install the sample databases.
On the next page (see Figure 2-26), the installation wizard prompts you for sample database installation options: You can choose to either copy the sample database files to your computer or attach the database to an existing instance of Database Engine.
Figure 2-26. Sample databases can be installed and attached to a new or existing SQL Server Database Engine instance.
Installing SQL Server 2005 on a Computer with a Previous Version of SQL Server
As we stated before, installing a SQL Server 2005 in an exiting instance that has a SQL Server 7.0 or SQL Server 2000 component upgrades the components. To install SQL Server 2005 components next to a SQL Server 7.0 or SQL Server 2000 installation, SQL Server 2005 must be installed in a different instance. This is true for all components of SQL Server 2005. If the previous version of SQL Server is installed as a default instance, SQL Server 2005 must be installed as a named instance.
Adding or Removing SQL Server Components from an Existing Instance
To add a SQL Server component, you can rerun the installation wizard as previously described for SQL Server Database Engine. To remove a component, open Add/Remove Programs in Control Panel, select Microsoft SQL Server 2005, and click Remove. This launches the SQL Server 2005 Uninstall Wizard displayed in Figure 2-27. The wizard provides the functionality to remove all or a selection of SQL Server components. To remove all SQL Server components, select instance components and then the Common Components option in the wizard.
Figure 2-27. SQL Server Uninstall Wizard provides the option to remove a selection of instances and components.
After an installation of SQL Server, we suggest you make a checklist of items to ensure setup has performed a successful installation and the system is prepared for your application.
To verify a successful installation of SQL Server components, try to start the service by using the SQL Server Configuration Manager tool described in Chapter 3.
You may also need to enable some of the features that are disabled on a new installation of SQL Server. By default, SQL Server disables some features and components (such as remote connections) to minimize the security risk and reduce the exposed surface area of SQL Server. If your application is leveraging any of the disabled features, you need to enable these features by using the Surface Area Configuration tool accessible from the Start menu. For more information about the Surface Area Configuration tool, see Chapter 3.