BetaBoxDiscover SQL Server 2005 Express Edition

Brian A. Randell

Prerelease info in this article is subject to change.

One of the most promising updates to a Microsoft product this year is SQL Server™ 2005 (formerly known by its code-name "Yukon"). SQL Server 2005 will come in a variety of versions, supporting many different types of deployments from the desktop to the enterprise datacenter. At this point, Microsoft has announced two SKUs—the Developer Edition, and the Express Edition—and the pricing for the Express Edition. The price, you ask? Free.

So if it's free, it won't be very interesting, right? Wrong. SQL Server 2005 Express Edition is full of features for the single user, workgroup, and the enterprise. See the "Express Features" box.

SQL Server 2005 Express Edition has been designed to be a core component of many different types of solutions. The desktop user gets a reliable, enterprise-grade data store that has easy-to-use GUI tools. The workgroup gets shared access to data with unrestricted concurrent access and industrial-strength data safety. Even the enterprise benefits from having a component that can integrate into a myriad of applications as well as be a reliable data store for administrative data.

Personal, Workgroup, and Enterprise Component

Express Features

  • No workload governor
  • Can have up to 50 instances
  • Can make use of up to 1GB of RAM
  • 4GB database size limit
  • GUI tools as a separate download
  • .NET Framework 2.0 is required
  • Supports Windows 2000 Service Pack 4, Windows XP Service Pack 1, Windows Server 2003 (including Web Edition)

As a personal and workgroup database, SQL Server 2005 Express Edition is a natural choice for a relational database store. The removal of the workload governor from previous versions, known as the Microsoft® Database Engine (MSDE), allows concurrent database operations without any performance penalty. In addition, each machine can have up to 50 distinct SQL Server 2005 Express Edition instances installed with each one supporting thousands of individual databases. This eliminates worries about ISV applications and your own internal apps impacting each other during installation, use, or removal.

As an enterprise component, SQL 2005 Express Edition plays well when integrated into large-scale solutions that include the full version of SQL Server 2005. SQL Server 2005 Express Edition uses the same core SQL Server engine, which includes the query optimizer, advanced locking, and transaction support. This provides your developers with access to all of the existing data management and manipulation features in SQL Server 2005 such as views, stored procedures, and triggers.

In addition, features specific to the 2005 version, including integration with the common language runtime (CLR) and a new XML data type, provide a two-fold advantage. First, applications built on top of SQL Server Express don't need specialized data access code for the desktop and workgroups and a different set for line-of-business applications. Second, applications that push the limits of SQL Server 2005 Express Edition can be transparently upgraded to the full-blown versions of SQL Server 2005.

The ability to integrate as part of enterprise solutions is particularly evident in two features of SQL Server 2005 Express Edition—replication and Service Broker. As a subscriber, SQL Server 2005 Express Edition supports both transactional and merge replication, both long-time SQL Server features. Transactional replication allows you to have synchronized copies of data via a publisher-subscriber model. As changes are made to the publisher, they are propagated transactionally to the subscribers, ensuring data integrity. In addition, filters can be applied to allow you to send only certain sets of data to specific subscribers. This can reduce the load on your main servers, especially for read-only operations.

Merge replication is useful when you want multiple subscribers to be able to update data at various times and have these updates propagated back to publishers as well as other subscribers. It's handy in many scenarios in which you want to allow satellite locations to work with data at any time and have their changes synchronized with publishers and other subscribers.

Service Broker, a new feature being introduced with the release of SQL Server 2005, lets you write asynchronous, queued applications without using an external product such as Microsoft Message Queuing (MSMQ). The need is easy to understand. Many database-centric applications try to do too much in a single transaction or operation. Take for example a typical e-commerce transaction that runs when a customer buys a book online. If you wrote a list of all the business processes that have to occur from the time the user clicks submit until the book is actually shipped, your list could include well over 20 tasks. The problem is that often almost all 20 or more operations are attempted in the period of time between the click of the submit button and the return of the response page. In years past, one way this problem was solved was by using technologies such as MSMQ and SQL Server 2000 all wrapped in a distributed transaction. In such a solution, only operations that have to take place in real time—creating the order number, verifying payment—are performed in real time. All other operations take place asynchronously via queues and databases.

An alternative to this solution is to build the transactional messaging process and run it all inside the database using Service Broker to manage it. By doing this you can increase transaction throughput, store data and messages in a central location (providing centralized management and data recovery), and have a uniform programming model. SQL Server 2005 Express Edition can participate in Service Broker conversations, making it a cost-effective technology when you're already using SQL Server 2005 for your primary database needs. (For more information about Service Broker, see Roger Wolter's article at A First Look at SQL Server 2005 Service Broker).

Management

From a manageability standpoint, SQL Server 2005 Express Edition is like any other version of SQL Server 2005. When deployed, a local copy of the SQL Computer Manager is installed. This new tool allows you to manage all types of SQL Server installations. And for the first time, Microsoft is going to ship a GUI tool to let you create databases and their objects, execute queries, and back up and restore databases. The tool is called Express Manager (XM) and it will be an optional component of SQL Server 2005 Express Edition installation.

While GUI tools are nice, if you have hundreds or even thousands of installations, you'll be happy to know that SQL Server 2005 Express Edition is a good command-line citizen. First there's SQLCMD, which is designed to not only replace its predecessor, OSQL, but to go beyond it. Extensions to the T-SQL language allow SQLCMD to perform a variety of tasks including database backup. In addition, SQL Server 2005 Express Edition is easily controlled from COM scripting languages like VBScript via the new exposed management interfaces—SQL Management Objects (SMO).

Express Edition Resources

According to Microsoft, their GUI tools are being built on top of SMO. So whatever they can do, you can do. In addition, don't forget that SQL Express can be a data store for your own administrative applications. You could easily write a script that uses Windows® Management Instrumentation (WMI) to gather machine information on a daily basis, store the data in a SQL Server 2005 Express Edition database using ActiveX® Data Objects (ADO), and then run reports against the data over time. If you need more power, the new Visual Studio® 2005 Express Editions, such as Visual Basic® 2005 Express, provide full-blown development environments, at a very affordable cost and size, to build more complicated management applications that require a GUI or compiled binaries.

Deployment and Servicing

Management is one thing, deployment and servicing is another. This is another area that has received a lot of attention from Microsoft. Installation is through Microsoft Installer (MSI) files—no more merge modules that prevent you from servicing using Microsoft updates. SQL Server 2005 Express Edition is designed to support updates via Windows Update to make it easier to get updates out to a large audience.

Getting your databases installed is easy too. Not only does SQL Server 2005 Express Edition support traditional connection strings, but a new option, called AttachDBFileName, will auto-attach and register a database if it has not already been used. This significantly reduces deployment headaches and enables XCopy deployment of applications and their databases.

SQL Server 2005 Express Edition is a free, easy to use and administer database engine. At its core is SQL Server 2005—an industrial strength, enterprise-capable Windows data platform.

Brian A. Randell is a senior consultant with MCW Technologies, a Microsoft Certified Partner specializing in custom apps development with .NET, SQL Server, and Office. He can be contacted via his blog at www.sqljunkies.com/weblog/brianr.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.