Data Management

How do I… install SQL Server 2005 Express Edition and SQL Server Management Studio Express?

With the release of SQL Server 2005 Express Edition and the SQL Server Management Studio Express, Microsoft has entered the small, free database market with a splash trumping even their own Microsoft SQL Desktop Edition. Although it is free, it is still important for you to install this application correctly, Scott Lowe shows you how.

With the release of SQL Server 2005 Express Edition and the SQL Server Management Studio Express, Microsoft has entered the small, free database market with a splash trumping even their own Microsoft SQL Desktop Edition. SQL Server Management Studio Express -- a full-featured management tool comparable to the legacy SQL Server Enterprise Manager -- makes this entry-level database software usable and formidable for running small businesses, small Web sites, and more.

To get SQL Server 2005 Express Edition install on your server, follow these steps.


Click this tag search to find other How Do I… articles and downloads.

Step 1: Download SQL Server 2005 Express Edition

The SQL Server 2005 Express Edition download page has three separate downloads for you to consider.

Table A -- Decide which features you need for your installation


Version

Feature

SQL Server 2005 Express Edition SP1

SQL Server 2005 Express Edition with Advanced Services SP1

SQL Server 2005 Express Edition Toolkit SP1

Database engine

X

X

 

Client software

X

X

X

Full-text search

 

X

 

Reporting Services

 

X

 

Management Studio Express

 

X

X

Step 2: Identify system requirements

SQL Server 2005 Express Edition does not have significant system overhead requirements, particularly given the overpowered nature of many of today's newest servers. The minimum system requirements for the various SQL Server 2005 versions are:

Table B -- System and software requirements for SQL Server 2005 Express Edition


Version

 

Feature  

SQL Server 2005 Express Edition SP1

SQL Server 2005 Express Edition with Advanced Services SP1

SQL Server 2005 Express Edition Toolkit SP1

RAM (minimum)

192 MB

512 MB

512 MB

RAM (recommended)

512 MB

1 GB

1 GB

Drive space

600 MB

Processor (minimum)

600 MHz

Processor (recommended)

1 GHz

IIS 5 or higher

No

Yes

No

Operating systems supported

Windows Server 2003 SP1, Windows Server 2003 Enterprise Edition SP1,

Windows Server 2003 Datacenter Edition SP1, Windows Server 2003 Web Edition SP1, Windows Small Business Server 2003 Standard Edition SP1, Windows Small Business Server 2003 Premium Edition SP1, Windows XP Professional SP2, Windows XP Home Edition SP2, Windows XP Tablet Edition SP2, Windows XP Media Edition SP2, Windows 2000 Professional Edition SP4, Windows 2000 Server Edition SP4, Windows 2000 Advanced Edition SP4, Windows 2000 Datacenter Server Edition SP4

Software prerequisites

.NET Framework 2.0 & MSXML 6

Other requirements

Server joined to your Active Directory domain.

I highly recommend that you don't use one of your existing servers for this purpose. If you don't have any reasonable spare hardware, consider using VMware Server or Virtual Server 2005 R2 and creating a virtual machine. Both products are free, and great for creating test beds. SQL Server 2005 requires the .NET Framework 2.0, which breaks some applications, so keeping the database separate is a good idea.

Step 3: Install database software prerequisites

I mentioned above that there are a number of software prerequisites for SQL Server 2005 Express Edition. Before you can install the database software, you need to take the necessary steps to get these items installed.

Install the following items in order.

Internet Information Server 5 or higher


If your Windows server does not have IIS installed, go to Start | Control Panel | Add or Remove Programs | Add/Remove Windows Components.

.NET Framework 2.0


Download the .NET Framework 2.0 (x86) from here. After downloading, execute dotnetfx.exe and follow the instructions to complete the installation. I'd show a screenshot, but there's really not a whole lot to see!

MSXML6


Download MSXML6 from here. Execute msxml6.msi. This is a quick installation.

Step 4: Create a SQL Server service account

From a security perspective, it's best to run SQL Server as a normal everyday user. When possible, don't use the built-in service accounts on your SQL Server as this practice does not limit your server's attack surface as much as using an unprivileged user account.

Create a domain account named "SQLExpressUser" for this purpose. If you're joined to a domain -- which you should be -- use Active Directory Users and Computers. If you're just testing locally, use Computer Management to add the account. Make sure to also assign a strong password to the account.

I have also configured this user account so that the password does not expire. In addition, anyone logging in as this user -- no one should be doing that though -- will not be able to change the password.

Step 5: Install SQL Server 2005 Express Edition

For this article, I am installing SQL Server 2005 Express Edition with Advanced Services SP1. The name of the downloaded file is SQLEXPR_ADV.EXE. Double-click this file to start the product installation. The contents of the file are extracted and the installation begins.

After you start the installation, you have to agree to the typical End User License Agreement after which you're presented with a screen that shows you what prerequisites you need to get the express edition on your system. The installer will handle the installation of these prerequisites for you. Click the Install button to install these items. Once the items are installed, click the Next button.

Figure A

The new Native Client and the Setup Support Files need to be installed.

Once the prerequisites are in place the SQL Server installation wizard will start. The first screen you're shown provides you with a list of items that may or may not give you trouble if you continue the installation. In Figure B below, you can see that my test system has a warning regarding the minimum hardware requirements. I am installing SQL Server 2005 Express Edition into a VMware Server-based virtual machine and the installer doesn't seem to like that I have allocated only 768MB RAM to the virtual machine when it really wants 1GB. However, this warning is not a show-stopper, so I'll continue. Note that the installer provides you with a complete status report to make it easy for you to determine what needs to be done. Click Next to continue.

Figure B

If you have any serious problems, correct them before you continue.

After that, you have to provide your name and, optionally, your company's name. I have also unchecked the box marked "Hide advanced configuration options" so you can get a look at these options during the installation.

Figure C

Provide registration details for your server installation.

Next, you get to choose which features you want to install. Even though the Express Edition is free, it includes many of the features found in the Standard and Enterprise editions. I've decided to install everything except replication. Note that I've opted also to install the Management Studio Express. This is an outstanding utility provided by Microsoft and it replaces Query Analyzer and Enterprise Manager. I highly recommend you install this tool. I'm installing into the default location, C:\Program Files\Microsoft SQL Server. If you want to install to a different location, click the Browse button to choose this new location.

Figure D

Decide which features you want to install. The only one you absolutely need is the Database Services option, but the others are very useful.

Instances were introduced in SQL Server 2000 and provide a way for you to more seamlessly serve multiple databases from a single SQL Server. SQL Server 2005 Express Edition supports up to 16 named instances while the Enterprise Edition supports up to 50. For my installation, I'm installing SQL Server 2005 Express Edition as the default database instance on my server.

Figure E

Choose your instance.

Service accounts are used in order to better protect your SQL Server -- and the rest of your network -- in the event of a security breach. If you run your SQL Server under an administrative account, you run the risk of additional damage should your server be compromised. Therefore, run SQL Server 2005 Express Edition under a user account with few, if any, rights.

Figure F

Use the account you created before as the service account for SQL Server 2005.

SQL Server can authenticate users in two ways: (1) directly against Active Directory (Windows Authentication Mode); (2) against its own database (Mixed Mode, since Windows Authentication is also supported). From a security perspective, Windows Authentication mode is highly preferred. First off, you have only a single user database -- Active Directory -- to worry about.

However, Windows Authentication Mode is not always appropriate. For example, I've had better luck scripting against a Mixed Mode SQL Server versus a Windows Authentication Mode SQL Server. If you choose Mixed Mode (which you should) you will also need to provide a password for the SQL Server 'sa' user. Make sure that you assign a strong password if you take this route.

Figure G

Choose the mode under which you want to authenticate SQL Server users.

The next step of the installation involves selecting a collation method, which defines the way that your SQL Server will sort data. If you need to maintain backward compatibility with older versions of SQL Server, you should choose SQL Collations. If, however, you do not need to worry about backward compatibility, you should choose the Latin1_General collation method, unless, of course, you're using a different language.

Figure H

Choose your collation method.

SQL Server 2005 Express provides a new feature: The ability to run sub-instances of the product under a normal user account. User instances are useful in situations in which users are logged into Windows under a least-privileged user account. Using user instances, the user can still have SQL system administrator privileges to their SQL "sandbox", but the rest of the system is protected since the user's primary account does not have rights to make global changes. Databases housed under user instances support only a single connection and higher-end features such as replication are not supported.

If you want to support user instances in your installation, make sure the checkbox next to "Enable User Instances" is selected.

Figure I

Decide whether you want to enable support for user instances.

Introduced as an add-on for SQL Server 2000, an enhanced version of Reporting Services is included with SQL Server 2005. For your small SQL Server 2005 Express Edition installation, I recommend that you install this feature, but don't worry about customizing the configuration. The default configuration places the tool at http://{your server name}/ReportServer. The Report Manager tool is placed at http://{your server name}/Reports.

If you decide to use SQL Server 2005 and Reporting Services in a production environment, make sure to obtain an SSL certificate for use with the Reporting Services site, which is housed in IIS. I'll provide another "How Do I" article that explains how to install a certificate in IIS.

Figure J

Reporting Services is a welcome addition to SQL Server.

Microsoft has included an error reporting tool in other applications for quite some time. This feature is now optionally included in SQL Server 2005 Express. Further, Microsoft wants you to send them anonymous usage data about your installation. Neither option is enabled by default. Personally, I like the error reporting service and the only way it will improve is by adding data. I don't like sending usage data, however. While it's supposedly confidential, it doesn't directly benefit my installation.

Figure K

Decide what information you want to send to Microsoft.

These are all of your options for SQL Server 2005 Express Edition. Continue on to install the product using these selections.

Figure L

The installation's progress.

In some cases, your installation may fail with errors reported for the SQL Native Client and the SQL Server Database Services. In addition, the Workstation Components selection may indicate a failure. If this happens to you, it may be because you formerly had a conflicting SQL Server service on the machine and the Native Client previously installed is creating a problem. If this is the case for you, follow these steps:

  1. Change to the directory in which you saved the downloaded SQL Server Express 2005 installer.
  2. Extract the contents of the installer to a new directory: SQLEXPR_ADV.EXE /x:c:\sqltmp. If you are using the download that does not contains the advanced services, this command is SQLEXPR.EXE /x:c:\sqltmp instead.
  3. Change to C:\sqltmp\setup.
  4. Execute "sqlncli.msi"
  5. Choose the "Uninstall" option.
  6. Reboot your server.
  7. Run the SQL Server Express 2005 installer again. It should be successful this time around.

Installed and ready

At this point, SQL Server 2005 Express Edition is installed and can be managed using the SQL Server Management Studio Express tool that was installed along with the database server. To access this tool go to Start | All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio Express.

In my next article, I will show you how to manage your new database installation using the Surface Area Configuration tool and the Management Studio Express.

29 comments
mehboob.attar
mehboob.attar

realy thanku you techrepublic this site very helpfull for user and admin s post some all 2008 server all configaration regards tech team

lykes
lykes

why it's always error every time i tried to download the SQL server?

shivrajsinh
shivrajsinh

while installing the last step my installing then my Pc is restarted....

kheak2010
kheak2010

thanks, this is useful for me to try my labs ,it's like an example for me!

ErnestoVm
ErnestoVm

SQL Server Database Service interrompido com SSL n?o COnfi?vel?????????????

abhayjhaa
abhayjhaa

I am unable to install Windows Vista Home Premium edition. Windows Vista supports SQL Server 2005 or not. Plz, reply.

nrogers
nrogers

What an insane fuck up!!! After all was done, and SQL Express rebooted the machine,...The machine would'nt get past the Login stage, black screen, so we get the Xp Disc out, reboot and "R" to repair and now it goes into perpetual boot and reboot. MS and SQl Express (the SP3 for Xp Home)(wish they read this) are absolutely fucking useless.. Consider how many posts are out there re installing IIS on XP Home and the woes and other issues. Theres a banana republic company alive and well in the USA. Am I pissed,...? The entire business , our livelihood resides on that machine. Emails, Docs, Forms, clients, payments etc and no way to get to them now. Not that MS would give a shit. bunch of dooses (means in English) "Criminally Stupid Vaginas"

fisho70
fisho70

You're the man!! Thank you

icemancse
icemancse

I developed a program in Visual c# using sql server 2005 express edition as database.Now I want use this program in a different machine.How do connect with database file. Please help

raju.scadaglobal
raju.scadaglobal

how do i install sql server 2005 on server side can any one help

laseraman18
laseraman18

thanks this helped me a lot installing MS server 2005

0ceanpearl
0ceanpearl

GREAT WORK MAN THANK YOU SO MUCH

vincent_jos
vincent_jos

My sql installer CD is giving error when installing on INTEGRATED SERVICE, when i abort the installation it continues installing all other components except Int. Ser. What i have to do for this, is this tie the CD Problem? Please help.........

hkhanzadeh
hkhanzadeh

Hi, I have online server with Valid IP address and I have installed SQL 2000 enterprise with SP4 and Installed SQL 2005 enterprise edition, with different instance name , on windows 2003 Standard with SP2. Now tow sql server up and running but I have a problem: I can connect to SQL2000 database's from remote client but I can't connect to SQL 2005 from remote clients, what should I do to can connect to SQL2000 and 2005 from remote client? Thanks

pujarahardik2005
pujarahardik2005

Hello Friends, I have just created the Instance named the SAPDB and its completely working with the Windows Authentication with Local 'Administrator' User, and when i used to login with the SQL Server Authentication with 'sa' User then its giving me error like "Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)' Please help me to solve out. thanks & regards hardik

raketla
raketla

very helpful for new one's.thanks a lotttt.bye

rabaigy
rabaigy

I have successfully installed SQL Express. Now I want to add SMS functionality to it. Do you know any tool, that can do this free of charge? The one I found at http://www.ozekisms.com/ seems to do the job, but it quite expensive.

kghosh_99in
kghosh_99in

When we install sql server 2005 Express Edition, the authentication should be Sql server not window authentication. how we can change to sql server

etaylor8252
etaylor8252

Hi, I am an Oracle DBA, and am planning to learn DBA skills for Microsoft SQL Server 2005. I want to install "SQL Server 2005 Express Edition", because it is free; do you know if it is sufficient enough to do HANDS-ON-TRAINING, to learn DBA skills? I would appreciate your insight. Thank You!

id804097
id804097

This is a fantastic step by step guide. You even mentioned, as a final note, about the errors on native client, which helped me. Thank you Joseph

uvraja
uvraja

this article is not good please clear full contant send me

mehboob.attar
mehboob.attar

hello sir you need to purchage a licence copy and all installation ,configuration detailes in that make sure you purchease before wich version would you like to intsall thanks and regards mehboob

raju.scadaglobal
raju.scadaglobal

how to install SQL SERVER 2005 on server side can any one help me

anilm001
anilm001

Hi Hardik, Its very simple, Launch SQL Server Management Studio Express and login using windows authentication. Now right click on your server instance and under the Security properties choose SQL Server and Windows Mode. Now Restart the instance, you can now login using your SA Login Final note: Always try to use Windows Authentication as it is considered a best practice. Kind Regards Anil Mahadev Senior SQL Server DBA http://anilm001.myfreewebs.net

anilm001
anilm001

Hi, It's very simple. During the Security Dialog of the installation, choose Mixed Authentication Mode and enter a STRONG SA PWD. or after installation perform the steps outlined below. Launch SQL Server Management Studio Express and login using windows authentication. Now right click on your server instance and under the Security properties choose SQL Server and Windows Mode. Now Restart the instance, you can now login using your SA Login Final note: Always try to use Windows Authentication as it is considered a best practice. Kind Regards Anil Mahadev Senior SQL Server DBA http://anilm001.myfreewebs.net

mehboob.attar
mehboob.attar

dear sir there is third party component not supported or otherwise some oparating system file currepted or not supported file system check wether it having problems