In late July, Microsoft introduced SQL Server 2005 Express Edition, which will replace Microsoft SQL Server Desktop Engine (MSDE). For years after its original release, MSDE was an orphan—without a product manager to guide and protect it. But that's changed now that the SQL Server and Visual Studio teams have adopted the Express Edition. Now the product has a whole team of product and program managers. The new product uses the same T-SQL and database engine that SQL Server 2005 implements (just as MSDE did with SQL Server 2000) but is configured to serve fewer users.
The biggest differences between MSDE and the Express Edition are in installation and how the products limit scalability. You'll install and use the Express Edition's architecture like you do the Microsoft Access and Jet databases. As many of you know, MSDE was hard to install and deploy. It's easier to get SQL Server 2005 Express Edition onto your system and integrate it with your application's setup program. Note that you can't use the product in a production system, expose it on the Web, or use it outside the other End User License Agreement (EULA) restrictions (see the EULA for more details).
Some Things Change
Developers, architects, and IT managers have been talking about MSDE deployment, security, instancing, performance, and upgrade problems for years. There aren't solutions to all these problems, but let's look at how the new Express Edition addresses some of them.
Deployment. After you download SQL Server 2005 Express Edition, you can install it on a user system either with the interactive setup program or by running a command-line setup executable. By default, when you install Express Edition, the setup program creates a shared SQLEXPRESS instance. If an instance with that name already exists, you can abandon setup or choose another instance name. The idea behind this approach is to get applications that use the Express Edition to share a common instance—not create a new one. This instance sharing makes application configuration easier and reduces the memory and disk footprint on the user system. This departs from the MSDE strategy, in which Microsoft requires developers to create a dedicated instance.
Security. Because SQL Server 2005 Express Edition assumes you're using a common instance, you have only one sa password to worry about. Additionally, the sa account is accessible only when you install or configure Mixed Mode authentication, so you won't ever need to reveal the sa password to your customer. You'll have to provide an sa password when you install the Express Edition, but the password can be set to a random (hidden) value in the released versions. In the betas, the install already does this. Microsoft recommends that you use the default behavior and configure your Express instance to use Windows Integrated Security authentication, which grants the domain system administrator full sa access to the Express instance. This means that until the SQL Server 2005 tools can access Express, you won't be able to manage Express instances—including changing the sa password. You must be an administrator to perform maintenance, install databases, and perform simple operations such as changing the values in database tables. Of course, not everyone who uses the Express Edition must be an administrator because you can set up user logins that have appropriate permissions on the database. During the setup regimen, create a user or application login, then set appropriate permissions for the tables, views, functions, and stored procedures that your application needs.
Scalability and performance. Unlike MSDE, SQL Server 2005 Express Edition doesn't have a workload governor. I've rarely seen MSDE's governor slow the engine down, but dropping the governor has removed some confusion about the product's scalability. The Express Edition still limits scalability—the amount of work the engine can do in a given length of time. The new edition limits the buffer pool (where data and procedures are cached) to 1GB of RAM. As you add load to the Express instance, you'll (eventually) see your performance decrease, as the engine has to fetch more and more data from disk. Can the Express Edition support 1000 users? Yes, but only if the load that each user places on the Express instance isn't significant. Remember, 10 users can bog your instance down if you haven't written the application efficiently.
SQL Server 2005 Express Edition is limited to a single processor. You can't run threads on additional processors or utilize hyper-threaded processors. But whereas MSDE databases are limited to 2GB, Express database files can be 4GB, so you can store twice as much data. And the log file size still isn't limited.
Tools. Besides the new GUI setup, the Express Edition also has a new osql utility, Computer Manager (a Microsoft Management Console—MMC—snap-in), and SQLCMD command-line tools to help you manage your instance. In addition, Microsoft plans to have a new GUI tool—named SQL Server Express Manager—to perform the initial configuration and periodic maintenance on your Express Edition databases. The GUI tool, which you can download separately, is basically a lightweight version of Query Analyzer that you can use to help you write, test, and debug SQL queries. You won't be able to connect to Express using any other tools, such as Enterprise Manager or the SQL Server 2005 SQL Server Management Studio, in the beta—Microsoft is still working out the details of how this can be done in the released product. However, I expect the Express Edition to be accessible from any of the current tools by the time the product ships; I've been able to access Express from Visual Studio 2005.
Service packs and upgrades. You must use the Windows Installer installation package files to install the Express Edition. But you'll still have to update its SQL Server engine by using service packs as you do with MSDE. Microsoft is currently formulating a better strategy regarding service packs and upgrades in the hopes of avoiding the problems that those traditional venues create, such as not being able to install service packs post setup.
Some Things Remain the Same
Now that you know what's different about SQL Server 2005 Express Edition, let's see what hasn't changed. The Express Edition is still free. The product still supports subscriber replication and includes virtually all MSDE's features. The new Express Edition can't host SQL Server 2000 Reporting Services, but it can be a data source for a server hosted by SQL Server 2000 Standard Edition. By default, setup still disables the ability to expose the Express instance to the network—just as it is disabled in SQL Server 2000 (MSDE) Service Pack 3 (SP3).
MSDE developers are welcoming SQL Server 2005 Express Edition with open arms because they've been asking for its features for years. I think we'll see more and more Joint Engine Technology (JET)-based applications migrate to Express as the product's functionality matures between now and SQL Server 2005's ship date.
