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.
For an introduction to SQL Server 2005 you may want to see our previous article here.
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 installationWhen 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. |
Do you need help with SQL Server? 
1
doug gorman - 19/01/06
why did you write this? does sql server 2005 not ship with this same info, or is this easier to understand? or?...
» Report offensive content
2
TCBrown - 28/02/06
if you want to help people avoid pitfalls you should mention that if they choose windows authentication the setup program randomly generate an sa password and they should change it to something they know as soon as the installation is done, in case of emergency need.
» Report offensive content
3
Harbhajan Biran - 10/10/06
Would you install IIS on a SQL server cluster build?
» Report offensive content
4
Jennilyn - 06/06/07
how can I install if I already download the Microsoft SQL 2005?
» Report offensive content
5
sql master - 10/08/07
the article is good.
very useful and really a practical approach....
» Report offensive content
6
Scott - 15/08/07
I don't get it. This is the absolute basics / obvious stuff. What pitfalls have we avoided other than perhaps mentioning dot-net compatibility risks? It would be really nice to know how to work around any of the components failing to install....as they all seem to do a some point depending on unique env constraints.
» Report offensive content
7
Scott - 15/08/07
I don't get it. This is the absolute basics / obvious stuff. What pitfalls have we avoided other than perhaps mentioning dot-net compatibility risks? It would be really nice to know how to work around any of the components failing to install....as they all seem to do a some point depending on unique env constraints.
» Report offensive content
8
Scott - 15/08/07
I don't get it. This is the absolute basics / obvious stuff. What pitfalls have we avoided other than perhaps mentioning dot-net compatibility risks? It would be really nice to know how to work around any of the components failing to install....as they all seem to do a some point depending on unique env constraints.
» Report offensive content
9
Scott - 15/08/07
I don't get it. This is the absolute basics / obvious stuff. What pitfalls have we avoided other than perhaps mentioning dot-net compatibility risks? It would be really nice to know how to work around any of the components failing to install....as they all seem to do a some point depending on unique env constraints.
» Report offensive content
10
David Fisher - 15/08/07
This simply shows the successful installation process, which one sees first-hand when installing the program. It offers nothing useful. Why not address the common errors that the installer makes, such as stopping dead when it purports to find a conflicting existing installation that is not actually there? Only when problems occur will the user require assistance beyond what appears in the installer itself.
» Report offensive content
11
George - 16/08/07
I was hoping for an explanation on the difference between 'Local System', Local Service', 'Network...', etc., drop down box options.
» Report offensive content
12
George - 16/08/07
I was hoping for an explanation on the difference between 'Local System', Local Service', 'Network...', etc., drop down box options.
» Report offensive content
13
George - 16/08/07
I was hoping for an explanation on the difference between 'Local System', Local Service', 'Network...', etc., drop down box options.
» Report offensive content
14
SqlDba - 16/08/07
Complete waste of my time
» Report offensive content
15
Joshua Perry - 16/08/07
Wow! I didn't know how to click next that many times! This is a totally worthless article and the title is very misleading.
You didn't even hit some common pitfalls since you used the service account instead of a domain account, you didn't remember to reset the sa password since you used windows authentication, and you installed reporting services without IIS. You also installed all the services on the same server instead of putting reporting services on a web server and analysis services on it's own server and you didn't set up MSDTC so that integration services would work properly. What about changing the default ports and configuring a static port for integration services since most of the world blocks dynamic ports on their firewalls. Those are some common pitfalls.
» Report offensive content
16
SqlDBA2 - 16/08/07
Nothing useful. But at least the author can claim he's "published" on the web now.
» Report offensive content
17
mcullet2 - 16/08/07
Hi,
Followed this to the letter - problem is SQL Server 2005 doesn't seem to play fair with Visual Studio Pro 2005. I cannot get the default website to work at all. MS will help so long as you shell out several hundred dollars to fix what was broken.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
I'm just starting out - would truly love to be able to get this to work (and understand why it is not working) so I can move onto interesting things.
Stuck :(
Any suggestions?
Kindest regards,
Mike
18
Gaz - 20/08/07
Well this doesn't help with anything really. You are just showing a default install, pressing ok and next lots of times and letting it install.
No pitfalls here.
Wasted article.
Give me the 5 minutes of my life back that I wasted while reading it!!
» Report offensive content
19
Lee P - 27/12/07
The comment Joshua made was more useful than the article it's self
» Report offensive content
20
priya - 21/01/08
Thank you...your article was very helpful...
» Report offensive content
21
Patrick - 01/02/08
What a terrible article.Thanks for going over teh default install you idiot.
» Report offensive content
22
astonished - 11/08/08
Amazing article, you are a guru..
» Report offensive content
23
Nils - 19/08/08
very useful stuff, especially thats about collations. Didnt know that !
» Report offensive content
24
Elysa - 19/08/08
Hi - Thanks for the documentation. Microsoft seems intent to obfuscate its software...what was once simple and obvious is now completely counter-intuitive and confounding. Installing this software is no different as it is there is no obivous choice for "Install Microsift SQL Server"...DER! If you don't want all the crap, you are left to ' guess' at which option to choose. It is SO ABSURD!
We just installed SQL Standard using your guide, and guess what.... No SQL Server Management Studio installed. FFS. Would love to throttle who ever designed this interface.
» Report offensive content