One of
Microsoft’s most long-awaited releases, SQL Server 2005 has finally hit the market and brought with
it significant new features and changes from previous versions.

In
this article, I will go over a sample deployment of SQL Server 2005. The
installation procedure has changed quite a bit from previous versions, and this
will hopefully help you find the potential pitfalls before you undertake your
own deployment.

This
series on SQL Server 2005 includes:

  • An
    examination of what’s new in SQL Server 2005
  • Go through a sample SQL Server 2005 deployment (current
    document)
  • Provide you with a tutorial on managing and using
    Reporting Services in SQL Server 2005
  • Provide a tutorial with tips for the SQL Server 2000
    admin making the leap to SQL Server 2005, including a sample upgrade
    installation.

Note: For this article, I will be using
the Enterprise edition of SQL Server 2005; performing a new installation. I’m
running the Enterprise Edition of Windows Server 2003 R2.

SQL Server 2005 installation

When
you first put the SQL Server 2005 CD or DVD into your server, you’re provided
with an introductory window (Figure A)
that outlines the product and what you need in order to be able to run it.

Figure A
The SQL Server 2005 introduction screen. Note
that, even when you use the single DVD for installation, you still get the
“Disc 1 of 2” text at the top of this window.

To get
the installation underway, under the Install heading, choose the “Server components, tools, Books Online, and
samples” option. The first screen that pops up is the end user license
agreement for the product. You need to check the box “I accept the licensing terms and conditions” and click the
Next button to continue. I’m not going to show the license screen. You’re probably
pretty familiar with these.

The
next screen (Figure B) outlines the
prerequisites that the installer will take care of for you. The .NET
Framework 2.0 is key here. If you decide to
install SQL Server 2005 with other applications on the same server, make sure
that they can use this framework. I have seen cases where the 2.0 version of
the .NET Framework created problems with applications that expected an older
version. Click the Install button to install these items. When the installation
of these items is complete, you can click the Next button.

Figure B
These items are required by SQL Server 2005.

After
the prerequisites install on your system, the SQL Server 2005 installation
wizard starts. The first task completed by the wizard is a scan of your system
to make sure it meets the minimum requirements for SQL Server 2005. In Figure C below, you will see that there
were two areas of concern noted by the system check. The first is the minimum
hardware requirement. This is showing up because I’ve assigned only 384MB of
RAM to the virtual machine running SQL Server 2005 on my system, which can be
easily fixed. The second, IIS Feature Requirement, is a little more serious. A
few SQL Server 2005 services, such as Reporting Services, require IIS. So, before I move
forward, I’m going to quit the installation of SQL Server 2005 and install IIS
on my server. After that, I’ll continue with the installation.

Figure C
The system check reveals shortcomings so you can address them before you
install, thus avoiding potential problems.

The
next step of the installation (Figure D)
is pretty easy. Provide your name, company name and product key. Click Next to
continue.

Figure D
This is pretty typical stuff.

This
is where things start to get meaty. On this screen, you need to pick which
components you want to install along with SQL Server 2005. If this is your
first server, you really should pick the SQL Server Database Services option.
In the interest of completeness, I’m installing all of the services, but will
not go over failover clusters in this article. If you want to choose options on
a more granular basis, or change the default installation path (C:\Program
Files\Microsoft SQL Server), click the Advanced button, which opens up a more
familiar feature selection window. Figure
E below shows you the Component
Selection screen. In Figure F, I’ve
provided you with a look at the Advanced screen. In either window, click Next to continue with the
installation.

Figure E
The main component selection screen for SQL Server 2005.

Figure F
This is the advanced feature selection window that provides you with more
control over the installation.

SQL
Server 2000 introduced the concept of named instances to SQL Server. You can
choose to upgrade an existing named instance (Figure G), or you can install SQL Server 2005 with a default
instance. For this example, I am using the Default
option.

Figure G
Choose your instance.

As
always, the SQL Server services need to log on to the system using specific
credentials. You can choose to have all SQL services share the same credentials
or you can provide each service with its own login, which I recommend for
larger installations. However, for this sample article, I’m going to allow all
services to share a single account, and will use the built-in Local System
account.

This
screen also allows you to choose which services will start after installation
completes. You can see from the screenshot in Figure H which services the installer chooses by default.

Figure H
Choose the service account to use for each SQL service.

On the
next screen of the installation (Figure
I), you’re asked if you want to use Windows Authentication of Mixed Mode
Authentication, which allows you to use SQL Server accounts. Unless you have
some reason not to, just use Windows Authentication Mode. The maintenance of
user accounts is easier, and it’s one less potential security hole for your
company. If you do need to use Mixed Mode, provide the installer with the
password you want to use for the system administrator (sa)
account.

Figure I
Choose your authentication mode.

Collations
are groups of different character sets and sort orders that make SQL Server
work for different regions and languages. The SQL Server 2005 installer
provides you with the option to choose SQL
Collations, which provides you with backward compatibility with previous
versions of SQL Server. However, if you’ve opted to install Analysis Services, SQL collations can’t
be used for this service.

Microsoft
recommends that you use Windows collations rather than SQL collations, unless
you have backward compatibility issues to address. Windows collations provide
you with consistent string comparisons for both Unicode and non-Unicode text.

For
this example installation, I’m going to use the Latin1_general collation designator (Figure J) and sort order for both SQL and the Analysis
services.

Figure J
If you have backward compatibility issues, use the SQL collations option
instead.

If you
chose to install Reporting Services,
you have two installation options on the next screen (Figures K and L). First, you can install Reporting Services with default options, or you can choose to
install without configuring it. I’m going to install Reporting Services with the default options.

Figure K
Click the Details button for information about the installation options.

Figure L
These are the default options for Reporting Services.

A new
option in SQL Server 2005 allows you to share errors and usage information with
Microsoft. The disclaimer (Figure M)
indicates that personal information may incidentally be sent to Microsoft but
won’t be used for anything. It’s up to you to decide whether or not you believe
it.

Figure M
Do you want to share error and usage information with Microsoft?

The
last screen before SQL Server 2005 actually starts installing, the Ready to Install screen (Figure N), provides you with a short
overview of what you’re installing. I do wish that some of the options that we
selected during the wizard would show here, too, such as collation selection.
Click the Install button to commence installation.

Figure N
The summary screen is pretty bare.

The Setup Progress window keeps you apprised
of exactly where the installer is with regard to each individual component.

Figure O
The Setup Progress window is a nice addition.

Overview

This
was a fairly simple overview of the SQL Server 2005 installation process and is
not designed to show every possible installation scenario, but rather to give
you a look at the overall process to help you plan your own installation.

In my
next article in this series, I’m going to delve deeper into one of the
components I just installed: Reporting
Services.