Microsoft SQL Server Express 2005 (MSDE)

Introduction

SQL Express 2005 is a new, FREE, Microsoft SQL Server lightweight
edition. This new edition is the upgrade to Microsoft’s SQL Server 2000
Desktop Edition (MSDE). Targeted to developers of "simple"
applications, it includes a fully functional SQL Server database engine and a Query
Analyzer type management tool. And best of all, it is FREE to use and
redistribute.

What is a "Simple" application?

Microsoft repeatedly uses the phrases "simple
applications", "lightweight," and "mobile or web
applications" when describing the target uses for SQL Server Express
2005. However, the generous restrictions of Express make it an ideal database
for many applications. Express is limited to 1 CPU, 1 GB RAM, and 4 GB Max
database size. There is NO workload governor (See July article SQL
Sever 2000 MDSE
for a complete description of the workload governor).
Meaning SQL will not slow, or restrict its response times due to licensing
constraints. SQL Server Express can be installed on a machine with multiple
CPUs and RAM in excess of 1 GB, but the database engine will limit scheduler threads
to one, meaning only one CPU will be used. Similarly, the buffer pool, where
data pages are stored, will only use 1 GB of RAM, regardless of any additional
memory available. Enterprise features are not supported on SQL Express.
Missing are Analysis Services, Reporting Services, DTS, and Notification
Services. Aside from these restrictions, the SQL Server Express database
engine is the same one found in the other SQL products. Triggers, cursors,
views, stored procedures, CLR, XML, and TSQL are all supported the same way
they are in any other version of SQL Server.

Some Microsoft documentation describes SQL Express as a tool
for hobbyists and non-database experts. Don’t let these descriptions dissuade
your use of SQL Express. It is a full featured robust SQL Server engine. Although
SQL Express does ship with a tool similar to Query Analyzer, I feel hobbyists
familiar with Microsoft Access, or File Maker Pro will not be able to make the
transition. SQL Express is still SQL Server. For DBAs, this is a very good
thing. The familiar methods, functions, and concepts from other versions of
SQL Server carry over without change to SQL Express.

Requirements

SQL Express is supported on all versions of Windows 2000,
and Windows XP Pro. A Pentium III 550 with 256 MB of RAM and 405 MB of disk
space is needed. In addition, the Microsoft .NET Framework 2.0 is required.
It can be downloaded free of charge from Microsoft at http://www.microsoft.com/downloads/details.aspx?FamilyID=b7adc595-717c-4ef7-817b-bdefd6947019&DisplayLang=en
. This article was written with the February 2005 Community Technology Preview
of SQL Server 2005 Express, which can be downloaded at http://www.microsoft.com/downloads/details.aspx?FamilyId=08215D3F-9A1D-483F-8E21-A2EE19936899&displaylang=en
.

SQL Installation

First, install the Net Framework following the defaults.
Next, install SQL Server Express by double clicking the downloaded file. The
CTP setup should begin. After several screens, the install will come to the
System Configuration Check. Verify that the status of all actions is Success.

At the Feature Selection screen, select the Client
Components to be installed to the hard drive. Then the SDK and tools will be
available.

On the next screen, Authentication, select "Windows
Authentication Mode" if only Windows users will have permission to SQL
Express. If you need SQL users defined as well as Windows users, select "Mixed
Mode" and specify a password for sa.

Continue through the next screens to complete the
installation. An instance called SQL Express will be created by default.
Installations can also be accomplished by using an unattended install script.
For ISVs, SQL Express now supports Application XCopy, allowing SQL Express to
treat databases like files. Therefore, a database can be moved, copied, or
emailed along with an application.

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles