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.