SQL Server Setup is the utility you use to perform key installation tasks for SQL Server. You use SQL Server Setup to create new instances of SQL Server. When you want to manage SQL Server components, you use Add Or Remove Programs. Tasks you can perform with these utilities include the following:
Creating new instances of SQL Server
Installing additional client components
Maintaining existing components
Rebuilding the SQL Server registry
Uninstalling SQL Server
You can install multiple instances of the SQL Server 2005 database engine on a single computer. Running multiple instances of the database engine is ideal when:
You need to support multiple test and development environments on a single large server.
You need to run multiple applications on a desktop and each application installs its own instance of the SQL Server 2005 engine.
You need to securely isolate the databases that are available on a single server.
In most other situations, however, you should not run multiple instances of the SQL Server 2005 database engine. Each instance of the SQL Server 2005 database engine has its own set of system and user databases. Each instance has separate SQL Server and SQL Server Agent services, and as applicable, separate Analysis Services and Report Server services as well. All other components and services are shared, and this adds to the overhead on the server due to management of the shared resources.
When you install SQL Server 2005, you have the option of installing a default instance of the SQL Server 2005 database engine or a named instance of the SQL Server 2005 database engine. In most cases, you will want to install the default instance first and then install additional named instances of the SQL Server database engine as necessary. There is no limit to the number of named instances that you can run on a single computer.
A default instance is identified by the name of the computer on which the SQL Server 2005 database engine is running; it does not have a separate instance name. Applications connect to the default instance by using the computer name in their requests. Only one default instance can run on any computer, and this default instance can be any version of SQL Server.
All instances of SQL Server other than the default instance are identified by the instance name that you set during installation. Applications connect to a named instance by specifying the computer name and the instance name in the format computer_nameinstance_name. Only the SQL Server 2000 and SQL Server 2005 database engines can run as named instances. Previous versions of SQL Server do not support named instances.
Note
When you run SQL Server 2005 Enterprise Edition, you can create multinode server clusters. Applications connect to the default instance on a SQL Server cluster by specifying the virtual server name. Applications connect to a named instance on a SQL Server cluster by specifying the virtual server name and the named instance in the format virtual_server_nameinstance_name.
The SQL Server 2005 installation process has changed considerably since SQL Server 2000. The installation process now requires Windows Installer 3.0 or later, which is included in Windows Server 2003 Service Pack 1 or later, as well as in Windows XP Professional Service Pack 2 or later. If you are installing SQL Server 2005 on a different operating system, you should download Windows Installer 3.0 from the Microsoft Download Center at www.microsoft.com/download.
Not only does using Windows Installer help streamline and stabilize the installation process, it also makes modification of installed components easier. You can:
Perform upgrades directly using the Installation Wizard.
Install additional components or instances by rerunning the Installation Wizard.
Maintain installed components using Add Or Remove Programs in Control Panel.
Resume a failed upgrade or installation using Add Or Remove Programs in Control Panel.
To install an instance of the SQL Server 2005 database engine, complete the following steps:
Log on to the server using an account with administrator privileges. Then insert the SQL Server 2005 CD-ROM into the CD-ROM drive.
If Autorun is enabled, the SQL Server 2005 Setup program should start automatically. Otherwise, double-click Splash.hta in the Servers folder of the CD-ROM.
Under Install, click Server Components, Tools, Books Online, And Samples. The End User License Agreement is displayed. Select I Accept The Licensing Terms And Conditions, and then click Next.
The first time you run the Installation Wizard, the SQL Server Component Update Wizard is started next to determine the status of required services and components. If there are required components as shown in Figure 2-1, click Install to begin the component installation, and then click Next when the installation process is completed.
Note
SQL Server Component Update Wizard checks the configuration and availability of components such as WMI, MSXML, IIS, Internet Explorer, and COM+. It also checks the configuration of the operating system, operating system service packs, installation permissions for the default install path, memory, and hardware.
When the SQL Server Installation Wizard starts, click Next. The wizard will then perform a system configuration check. Note any errors and take the necessary corrective actions before continuing. If there are no required corrective actions, click Next to proceed with the installation.
Real World
Occasionally you may need to reboot prior to continuing the installation process (see Figure 2-2). If you do so, the Installation Wizard will not restart automatically and you will need to repeat the installation process, starting with Step 1.
On the Registration Information page, enter your name, the company name, and the 25-character CD key. Click Next to continue.
On the Components To Install page, select the components to install. Select one or more of the following options and then click Next:
SQL Server. Allows you to install a SQL Server instance. You can also install SQL Server 2005 as part of a cluster. If a cluster is detected, the Virtual Server option is selected by default.
Analysis Server. Allows you to install an Analysis Server instance. You can also install Analysis Server as part of a cluster. If a cluster is detected, the Virtual Server option is selected by default.
Reporting Services. Allows you to configure the server as a Report Server. Report Servers require IIS and the .NET Framework 2.0 or later. You will also need to install a Simple Mail Transfer Protocol (SMTP) server for sending reports or know the name of your organization’s Exchange gateway.
Notification Services. Allows you to install the notification engine and components for generating and sending notifications.
Integration Services. Allows you to install SSIS for the purposes of ETL.
Workstation Components, Books Online and Development Tools. Allows you to install SQL Native Client components, documentation, and tools.
Note
If you click Advanced instead of selecting individual options, you can customize the set of components to include in the installation. As an example, you could choose to install only the data files for the SQL Server Database Services and not the replication or full-text search components. In this way, you could create a SQL Server database installation with only the core engine.
As shown in Figure 2-3, you must now determine the instance type to install. To install a default instance of SQL Server, select Default, and then click Next. Otherwise, select Named Instance, type the instance name in the field provided, and then click Next.
Note
You can install only one default instance on a computer. If a default instance already exists, you select Default Instance only if you want to upgrade the existing default instance. The instance name can be up to 16 characters in length and must follow the naming rules for nondelimited identifiers. If you type an invalid instance name, you will see an error message and you will have to change the instance name before you can continue.
On the Service Account page, determine how the SQL Server and SQL Server Agent services (and if appropriate, the Analysis Services and Report Server services) will run, as shown in Figure 2-4, and then click Next. You have the following options:
Customize the service accounts. If you select Customize For Each Service Account, you can configure each service account individually. Use the drop-down list to configure the settings for each service before continuing.
No customization of service accounts. You assign a built-in system account or a specific domain user account to all SQL Server services. If the server requires resources on the local server only, use the Local System account. Otherwise, use a Domain User account.
Configure services startup. By selecting a service, you can specify that the service should be started at the end of setup. SQL Server is selected by default. You can also select SQL Server Agent and SQL Browser.
Real World
Use a local system account when you are configuring a SQL Server database instance that will be isolated from other servers, one that will operate independently and not connect to other servers over the network. The permissible actions, of course, depend on the permissions granted to the Local System account. If interaction with other servers is required, rather than granting additional permissions to the Local System account, you should use Domain User accounts and grant the appropriate level of permissions to these accounts.
Although the SQL Server service does not require administrator account privileges, the SQL Server Agent service does require them in some cases. Specifically, if you create CmdExec and ActiveScript jobs that belong to someone other than a SQL Server administrator or if you use the AutoRestart feature, the SQL Server Agent service does require administrator privileges. Additionally, if you are configuring Reporting Services and the report server database is on a remote server, you should use a Domain User account.
Use the Authentication Mode page to configure the authentication settings. The SQL Server instance can run under Microsoft Windows authentication or Mixed Mode authentication. With Windows authentication, you use only Domain User accounts to authenticate connections to the SQL Server instance. With Mixed Mode authentication, users can access the SQL Server instance using Domain User accounts or SQL Server IDs. If you’ve selected mixed-mode authentication, enter a strong password for the sa account. Strong passwords use a mix of numbers, letters, and special characters to make them difficult to crack. Click Next.
On the Collation Settings page, define the sorting behavior for the server (see Figure 2-5). If you select Customize For Each Service Account, you can specify separate collation settings for SQL Server and Analysis Services. You would then use the drop-down list options to configure separate settings for SQL Server and Analysis Services before continuing.
Note
The default Collation Designator is the Microsoft Windows locale setting for the server, such as Latin1_General. Typically, you want to use the default locale setting. Binary and Case-Sensitive are the fastest sorting orders. If the sort order is set to Binary, the other options are not available. SQL Collations are used for compatibility with earlier versions of SQL Server and are not used for Analysis Services.
Caution
While you can change the collation settings on individual databases, you cannot change the collation settings on an existing SQL Server installation without rebuilding the master database. Rebuilding the master database detaches all other databases on the server, making them unusable. For more information about this process, see the section "Changing Collation and Rebuilding the Master Database" in Chapter 6.
If you are configuring Report Services, specify the virtual directories to use for Report Server and Report Manager, and then click Next. These directories can be accessed in a Web browser as follow:
For the default SQL Server instance, use http://ServerName/DirectoryName, where ServerName is the host name or Domain Name System (DNS) name of the server computer and DirectoryName is the name of the virtual directory for either the Report Server or the Report Manager, such as http://corprs17/reports.
For the default SQL Server instance, use http://ServerName/DirectoryName$InstanceName, where ServerName is the host name or DNS name of the server computer, DirectoryName is the name of the virtual directory for either the Report Server or the Report Manager, and InstanceName is the SQL Server instance to which you are connecting, such as http://corprs17/reports$webapp05.
If you are configuring Report Services, specify whether the report server instance should use the default configuration or not be configured at this time. Click Details to determine the default configuration values for the report server name, virtual directories and SSL settings. With the default configuration, the report server is installed on the SQL Server instance you are configuring and the names of various components reflect that instance name. So if you are installing a named SQL Server instance called CustData on EngDbSrv12, the default report server name would be ReportServer$CustData and the default virtual directories would be http://engdbsvr12/ReportServer$CustData and http://engdbsvr12/Reports$CustData respectively. If you don’t want to use the default configuration, you can install report server at this time and then later use the Reporting Services Configuration tool to configure the report server. Click Next to continue.
On the Error And Usage Report Settings page, choose whether to automatically report fatal error messages and feature usage data, and then click Next. Error information is sent over Secure HTTP (HTTPS) to Microsoft by default or to a designated corporate error reporting server if you have configured one in Active Directory Group Policy. When Feature Usage Reporting is configured, reports about component usage are generated and reported to Microsoft. The intent of these reports is to help Microsoft better understand how components and features are being used. This feature is also referred to as Customer Feedback Reporting.
Click Install to begin the installation process. The Setup Progress page tracks the components that are being installed and the progress of the installation. When Setup finishes, note the status of each installed component and check the setup log file if there are any problems. Click Next, and then click Finish to complete the installation process.
Real World
Notification Services are integrated with the Microsoft .NET Framework. This allows you to use managed code with Notification Services without having to register the Notification Services assembly. However, if you are using unmanaged code with Notification Services, you must register the Notification Services assembly.
At a command prompt, use the CD command to change to the .NET Framework directory for the current .NET Framework version. Then use the Assembly Registration tool (Regasm.exe) to register the Notification Services assembly (Microsoft.SqlServer.NotificationServices.dll). Type the following command:
regasm /codebase /tlb "SQLDirmicrosoft.sqlserver.notificationservices.dll"
where SQLDir is the full directory path to the SQL Server installation, such as:
regasm /codebase /tlb "%ProgramFiles%Microsoft SQL Server90NotificationServices9.0.242inmicrosoft.sqlserver.no tificationservices.dll"
SQL Server keeps track of those components you have installed and those you have not installed. If you ever want to add components and instances, you can do so by completing the following steps:
Log on to the server using an account with administrator privileges. Then insert the SQL Server 2005 CD-ROM into the CD-ROM drive.
If Autorun is enabled, the SQL Server 2005 Setup program should start automatically. Otherwise, double-click Splash.hta in the Servers folder of the CD-ROM.
Under Install, click Server Components, Tools, Books Online, And Samples. The End User License Agreement is displayed. Select I Accept The Licensing Terms And Conditions, and then click Next.
When the SQL Server Installation Wizard starts, click Next. The wizard will then perform a system configuration check. Note any errors and take the necessary corrective actions before continuing. If there are no required corrective actions, you can click Continue to proceed with the installation.
Setup will then search for installed components. On the Registration Information page, enter your name, the company name, and the 25-character CD key. Click Next to continue.
On the Components To Install page, select the additional components to install. Keep the following guidelines in mind:
If you already installed the SQL Server Database Services and have an existing instance of SQL Server, the Instance Name page will have an Installed Instances button. If you click this button, you can view the component configuration details for installed instances of SQL Server, Analysis Services, and Reporting Services.
If there is an existing default instance and you select Default Instance, Setup will assume that you want to upgrade the existing default instance. When you click Next, you will then have options to determine which related components are to be upgraded.
If there is an existing named instance and you select Named Instance and specify the instance name, Setup will assume that you want to upgrade that instance. When you click Next, you will then have options to determine which related components are to be upgraded.
You cannot use the Setup process to maintain existing components. If you want to maintain existing components, use Add Or Remove Programs in Control Panel.
In Add Or Remove Programs, each component of SQL Server 2005 is listed individually, as shown in Figure 2-6. The basic options are Change and Remove. Click Change to start the SQL Server 2005 Installation Wizard, which allows you to use Setup to modify installed subcomponents or remove the selected component entirely. Click Remove to bypass Setup and remove the selected component completely. If you want to maintain multiple components, you must select and work with each in turn.
To modify the configuration of a SQL Server component, follow these steps:
Select Microsoft SQL Server 2005 in Add or Remove Programs, and then click Change. When the SQL Server 2005 Maintenance Wizard starts, select the SQL Server instance to change or maintain and then click Next.
On the Feature Maintenance page, select the component you want to work with, such as Analysis Services or Database Engine and then click Next. This starts a system configuration check.
The SQL Server Installation wizard is started. Click Next to allow setup to perform a system configuration check. When the system configuration check is completed, note any issues and correct problems as necessary. Click Next.
Setup will then review the installed components. On the Change Or Remove Instance page, click Change.
On the Feature Selection page, double-click the entry for the component. This will expand the component details so you can see subcomponents. Click the icon for the subcomponent to specify its availability.
When you are finished modifying the component configuration, click Next, and then click Install.
Use Add Or Remove Programs in Control Panel to uninstall SQL Server or any of its components. You must uninstall each instance of the SQL Server database engine separately.
To uninstall an instance of SQL Server, complete these steps:
Select the SQL Server instance in Add Or Remove Programs, and then click Remove. This starts the SQL Server 2005 Uninstall wizard.
On the Component Selection page, select the instance and/or components to remove.
Click Next and then click Finish. The SQL Server 2005 Uninstall Wizard will remove the selected instances and/or components. If Setup requires access to the SQL Server CD-ROM, you will be prompted to insert the CD into the CD drive.
If you want to completely uninstall SQL Server 2005, use Add Or Remove Programs to uninstall all instances of SQL Server. Then uninstall the following components in this order: