A free fully functional database is yours with Microsoft's SQL Server Express

by Joshua Hoskins | Feb 23, 2006 8:40:00 PM

Tags: Databases, Joshua Hoskins, Log Shipping, Microsoft Corp., full-text search, Microsoft SQL Server, database

Takeaway: SQL Server Express is available free from Microsoft. This document examines the requirements and the limitations of this free database application.

With the release of SQL Server 2005 Express Edition and the Community Technical Preview release of SQL Server Management Studio Express, Microsoft has released a fully functional database system free of charge. There are, of course, limitations to both of these systems, but they offer a perfect jumping-on point for either those wishing to learn the basics of SQL Server or those who need a small-scale database that offers a clear upgrade path as the database expands.

SQL Server 2005 Express Edition does have requirements and limitations. Its requirements are fairly light:

• Windows 2000 Service Pack 4; Windows Server 2003 Service Pack 1; Windows XP Service Pack 2
• Computer with Intel or compatible Pentium III 600 MHz or faster processor (1 GHz or faster is recommended.)
• Minimum of 192 MB of RAM (512 MB or more is recommended.)
• 525 MB of available hard disk space
• Microsoft .NET Framework 2.0

The limitations of each application are what make them free when compared to the other versions of SQL Server 2005.

• Limited to 1 CPU
• Can only use 1 GB of RAM
• Limited a Maximum Database size of 4GB
• Log Shipping is not supported
• Full-Text Search is not an option
• SQL Server Agent Scheduling service is not available

Also, several higher end features, such as the Tuning Advisor and Failover Clustering, are only available in Standard or Enterprise Edition, not in the Express or the Workgroup Edition. Also, the enterprise management tools are not included with SQL Express, but SQL Server Management Studio Express is available.

The installation is fairly straight forward. Download the installer here. Once the system installs needed components it will check to make sure you meet the necessary requirements.

Figure A

System check

This screen will tell you if you meet the requirements. If you do not meet them, the Report button will give you details on how to complete the steps necessary for the installation to continue.

The next installation screen will ask for your name and company. Do not blindly click past this screen, as on the bottom is a check box for Hide advanced configuration options. This box is checked by default.

Figure B

Registration

If you need any of the advance options, uncheck this box.

If you selected to see the advanced options you will be presented with this screen:

Figure C

Advanced

If you need the advanced options (Replication, Connectivity Components, Software Development Kit) select them to be installed on this screen. If you are using this as a full scale database rollout, you will probably want the Replication and Connectivity Components installed at a minimum.

Following this you will have the option of installing SQL Server 2005 Express Edition as either the default instance name (local PC name) or as a named instance.

Figure D

Instance name

A named instance will be connected to as %PCName%\instancename, while the default instance will just be connected to as %PCName%.

After installing SQL Server, I recommend installing SQL Server Management Studio Express. This is a graphical management tool for SQL Server Express. This is a large improvement over MSDE (the SQL 2000 free database engine), as only command line was available to manage it. When you first start SQL S.M.S.E. you will be prompted to log into a SQL Server. Enter the name of your SQL Server (as defined above), and your login information.

Figure E

Server management

After selecting this, you will be greeted with the main screen of this tool.

Figure F

Main screen

From here you can manage your databases, you security logins, replication subscriptions, view you SQL Server logs, query your server, and many other management options.

To create a database, all you need to do is right click on the databases folder, and select new database.

Figure G

New database

From here you set the name of your database, the location and number of your database files, database options, and filegroup information if you choose to use them. Once you click OK, the database files will be created and your database initialized.

After your database is created you can query your database directly from this tool. To do so, right click on the database and select new query. You will be granted with a query window on the left side of the screen.

Figure H

Query

From here, you can type in your query in the left pane. When you are ready to execute your query you can either push the Execute button, or press F5 on your keyboard. Your query results will be returned in the bottom of your screen.

Figure I

Results

From the Security folder, you can define logins and permissions on your server. You can map logins to individual SQL accounts or Active Directory accounts and groups. You define on each login what rights on the server side, and in each database the login has. You create a login by right clicking on the Security | Logins folder and selecting New Login.

Figure J

Login

From here you can browse your Active Directory for accounts, select server side roles if desired, map users to specific databases, define security on database objects, and other features for the account.

Lastly you can view your SQL Server logs from with in the tool. By selecting Management | SQL Server Logs you can just view your current SQL log file, but your last several historical logs.

Figure K

Logs

Microsoft has released a great product with SQL Server 2005 Express Edition. While not quite as robust as the other SQL Server 2005 Editions it may well become the optimal solution for not just applications that will use its data engine, but as an entire RDBMS system for small companies or limited rollouts.

Print/View all Posts Comments on this article

Does the Express Edition interest you? Mark W. Kaelin | 02/23/06

It's not fully functional Tony Hopkinson | 02/23/06

MySQL Ed Woychowsky | 02/24/06

MySQL - Awesome, professional & free sfox@... | 02/27/06

Even worse, it is missing... Tampa Hillbilly | 02/27/06

Even worse, it is missing... martyconnelly | 03/06/06

Another butchered database from Microsoft - NO WAY! oz_ollie | 02/27/06

Not Sure! salonah | 02/28/06

slower, bigger more cumbersome sfox@... | 02/28/06

MS Access CoolbeansG | 03/06/06

It doesn't Tony Hopkinson | 03/06/06

Very limiting. scott365 | 03/06/06

Wanna` BET !!! Tampa Hillbilly | 03/06/06

900 Meg Tony Hopkinson | 03/07/06

Hard to switch from MySQL scott365 | 03/01/06

Hard to switch from MySQL martyconnelly | 03/06/06

That just bites... scott365 | 03/06/06

Joshua, better Jaqui | 02/23/06

Search for PERSONAL bl4nks@... | 02/27/06