Microsoft SQL Server Express 2005 (MSDE)

Friday Apr 1st 2005 by Don Schlichting
Share:

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.

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.





Install Express Manager


Microsoft is also releasing a free management tool called SQL Server Express Manager (XM). The February 2005 CTP is available at http://www.microsoft.com/downloads/details.aspx?FamilyId=67079BB3-4FD4-4638-B923-A13741179B98&displaylang=en . The tool is similar to Query Analyzer, but is located inside an Enterprise Manager style MMC shell. To install Express Manager, go to Add Remove programs, point to the download and follow the prompts.





Management


After installation, there should be a Microsoft SQL Server 2005 CTP group located under Start, Programs. Under Configuration Tools, click the "SQL Server Surface Area Configuration" icon. This tool allows configuration of the SQL service and protocols. In addition, security sensitive options such as xp_cmdshell, SQL mail, and CLR integration can be configured from here. From the main screen, select "Surface Area Configuration for Services and Protocols." The new SQL Express installation should be listed. Drill down to Service, and click start if it is not already running. By default, only Shared Memory will be running. If TCP/IP or Pipes are needed, click the Network Protocols link, located beneath the Service.





Now that the service is running, the first thing to notice, or not notice, is that the old SQL 2000 Mgr icon that usually appeared in the lower right task bar showing the green or red running status of SQL is no longer included.

Another option for configuring services and protocols is to use the new SQL Computer Manager, also located under the Microsoft SQL Server 2005 CTP group. This tool uses familiar MMC style selects.

Express Manger

The new graphical Express Manager can also be found on the Start menu. To connect, enter ".\SQLEXPRESS" as the Server Instance and select your security preference. For new database creation, right click Database, and select New Database. A New Database wizard should appear. Alternatively, use the Query Editor to enter TSQL directly, just like Query Analyzer in SQL 2000.

Conclusion

SQL Server Express is an excellent fit for stand-alone and embedded applications. If you are currently using Access or Dbase, SQL Server Express may be a very good replacement. Many Access type applications use ODBC and pass query strings for database access, leading to poor performance and virtually no scale up or out path. By converting to SQL Server Express, the distribution cost is still $0, because SQL Express if free, but now you will have the full range of capabilities of SQL Server.

» See All Articles by Columnist Don Schlichting

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved