Avoid deployment pitfalls Microsoft's SQL Server 2005

by Scott Lowe MCSE | Jan 11, 2006 4:14:00 PM

Tags: Databases, Enterprise software, Microsoft Corp., Scott Lowe MCSE, Microsoft SQL Server 2005, Microsoft SQL Server

4 comment(s)

Takeaway: This tutorial shows you how to properly install Microsoft's SQL Server 2005, avoiding the pitfalls of an unsuccessful deployment.

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.

4 comment(s)