Installing and Configuring SQL Express
Author: jason
Date: 2007-05-03 15:59:56
Category: Technical

Every now and then I have the need for a database and let's face the facts: 1. Microsoft Access doesn't cut the mustard 2. Management is scared of anything not Microsoft (like mysql). That pretty much means digging around for an old version of SQL 7 or 2000 and hoping the CD/product key works or using a "lite" version of MSSQL.

Fortunately Microsoft has released a successor to MSDE (Microsoft SQL Server Desktop Engine) titled SQL Server 2005 Express Edition. Microsoft bills SQL Express as: "free to download, free to redistribute, free to embed, easy to use/manage, and includes powerful features."

Downsides
Limited enterprise features (no high availability or business intelligence)
Runs on only one CPU (can be installed on multiprocessor machines)
One GB RAM for the buffer pool
4 GB max database sizes

Upsides
Small in size and free!
36.5 MB, Microsoft SQL Server 2005 Express Edition (SQLEXPR32.EXE)
43.1 MB, SQL Server Management Studio Express (SQLServer2005_SSMSEE.msi)

Basic Requirements
.NET Framework 2.0
Windows Installer 3.1
Lastest service pack for the OS

SQL Express Install
Double-click the install file (SQLEXPR32.EXE)
The setup will start extracting files
Accept the end user license agreement
On the "Installing Prerequisites" screen click Install
Ensure everything was installed correctly click Next
On the "Welcome to Microsoft SQL Server Installation Wizard" screen click Next
Review the "System Configuration Check" screen. If there are items in error you will need to correct them before you can continue
Click Next




On the "Registration Information" screen fill in the Name and Company sections. Uncheck "Hide advanced configuration options" click Next
On the "Feature Selection" screen you can change the default install location or click Next
On the "Instance Name" screen change the instance to default and click Next




On the "Service Account" screen you can specify what account will be starting SQL or click Next




On the "Authentication Mode" screen you can select what authentication mode to use (I like mixed mode) or click Next




On the "Collation Settings" screen you can define the sorting behavior or click Next




On the "Configuration Options" screen you can configure user and administrator accounts (I normally check "Add user to the SQL Server Administration role" box) or click Next




On the "Error and Usage Report Settings" you can elect to send Microsoft some statistics or just click Next
On the "Ready to Install" screen click Install
On the "Setup Progress" screen review the components that were installed click Next




On the "Completing Microsoft SQL 2005 Setup" screen click Finish
SQL Expres 2005 is installed, now on to the management tools

Management Tools Install
Double-click the install file (SQLServer2005_SSMSEE.msi)
On the "Microsoft SQL Server Management Studio Express Setup" screen click Next
Accept the license agreement, click Next
On the "Registration Information" screen fill in the Name and Company sections click Next
On the "Feature Selection" screen you can't really change the install path click Next
On the "Ready to Install" screen click Install
On the "Completing the Microsoft SQL Server Management Studio Express Setup" screen click Finish

Protocol Configuration
You will have to enable the specific protocols you need to access SQL Express from scripts or programs.
Open the SQL Server Configuration Tool (Start - All Programs - Microsoft SQL Server 2005 - Configuration Tools)
Expand "SQL Server Network Configration"
Highlight "Protocols for SQLEXPRESS"
Here you can enable: Shared Memory, Named Pipes, TCP/IP, and VIA. Shared Memory is enabled by default. TCP/IP is only needed if access to the server is needed across the network. I normally enable Named Pipes.




The SQL service will need to be restarted if a protocal is enabled/disabled
Highlight SQL Server 2005 Services
Right-click SQL Server (SQLEXPRESS) on the right side
Click Restart

Open SQL Express
To open SQL Server Management Studio Express go to Start - All Programs - Microsoft SQL Server 2005
On the "Connect to Server" screen click Connect
If you are unable to connect ensure the authentication is setup correctly and that the SQL service is running




The Management Studio is similar to the Enterprise Manager MMC for SQL
Here you can make new databases, tables, users, etc...




References
Microsoft SQL Express Homepage
SQL Express Overview
SQL Queries
SQL Express Downloads
SQL Express System Requirements



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009