Installing a SQL Server 2005 instance

Installing and configuring a SQL Server instance can be a challenging process. Installing a SQL Server 2005 instance is an entirely new learning opportunity for DBAs and developers upgrading to the latest version. Follow this step-by-step guide and experience a glitch-free installation.

To most DBAs and developers, installing and configuring a new SQL Server is exciting because it brings on a whole new set of challenges and opportunities. The installation is just the first of many new learning opportunities with the latest version of SQL Server. In this guide we'll walk through the steps to properly install a SQL Server 2005 instance and the current service pack (SP1) to get you started.

Key installation considerations

Although most of the SQL Server installations use the default parameters this may be a simple process, but not understanding the installation parameters could lead to confusion or future security vulnerabilities. As such, below I've listed some of the key considerations for you to refer to when installing SQL Server:

  • Only install the necessary SQL Server components to limit the number of services. This will also limit the potential for overlooking a critical patch because you didn't realize the SQL Server necessary components.

  • For the SQL Server service accounts, be sure to select an account with the appropriate privileges in the domain. Do not just select a domain administrator to run the SQL Server service account. Leverage the principal of least privileges and only assign the needed rights to the account. Along the same lines, be sure to assign a complex password to the SQL Server service account. For both of these items, be sure to select the correct parameters when you reach Step 12.
  • At Step 13, the authentication mode is selected. The two options are Windows Authentication Mode or Mixed Mode (Windows and SQL Server authentication). With the Windows Authentication Mode only Windows accounts can be granted login rights in SQL Server, where as with the Mixed Mode both Windows and SQL Server accounts can be granted login rights to SQL Server.
  • The SQL Server authentication mode from Step 13 in the process carries over to step 14 in the installation process. This is when the sa password is assigned when Mixed Mode Authentication is selected. As is the case with the service account, be sure to use a strong password or pass phrase and properly protect the password.
  • The next key consideration during the SQL Server installation process is the collation setting. With SQL Server, both Windows and SQL Server collations are available. These collations should be determined based on the application language support needs. It is also wise to review these configurations across the current SQL Servers in your environment to ensure they meet the specific application needs and/or are consistent among the SQL Servers.
  • The final consideration is to review all output from the installation to ensure the process was successful. Be sure to validate the output prior to releasing the SQL Server to your environment.

Installing a SQL Server 2005 Instance

  • Home: Introduction
  • Step 1: Begin your SQL Server instance installation
  • Step 2: Choose SQL Server installation components
  • Step 3: Specify account, authentication mode and settings
  • Step 4: Click 'Install' and review your summary log
  • Step 5: Install SQL Server Service Pack 1

Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. He is the author of the Rational Guide to IT Project Management. Jeremy is also the Performance Tuning expert. Ask him a question here.

Dig Deeper on Microsoft SQL Server 2005