SQL Server Express 2005 vs. JET

1.

Commentary

SQL Server Express 2005 vs. JET
      by Michael Otey, [email protected]

If you’re a Microsoft developer who’s deciding between database back ends for a small-scale Web or Windows application, chances are your decision boils down to Microsoft SQL Server 2005 Express Edition or Microsoft Office Access 2003. Sure, there are open-source alternatives such as MySQL or IBM Cloudscape, but if you’re using Microsoft development technologies such as Microsoft Visual Basic (VB) or Microsoft Visual C# 2005 Express Edition, then you know you'll find more support and useful examples with the Microsoft database products. You could also opt for Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0, but that would be like going to the grocery store and looking for the expired milk--MSDE’s shelf life is limited. Therefore, the best database choices for Microsoft developers are SQL Server Express and Access.

I’ve been a SQL Server developer and author for years, so you'd be excused for thinking my take on this is skewed (and that might actually be the case), but as a consultant I’ve implemented many solutions using Access, so I have a good feel for what it can do. The real comparison isn’t between SQL Server Express and Access, it's between SQL Server Express and Microsoft Jet 4.0 Database Engine (JET), the database engine that Access uses. Access isn’t a free product: It's part of the Microsoft Office suite. Access provides basic database functionality, a front end, and reporting tools that let you take advantage of its database capabilities. For example, Access can generate database entry forms and also includes a powerful, easy-to-use report writer. The JET engine provides the underlying technology. But whereas Access requires an Office license, you can freely distribute JET technology with Office and Visual Studio 6.0 applications.

Although SQL Server Express and Access are both database engines, the differences between these two technologies are more than skin deep--there are profound differences between the two database technologies' implementation and functionality. Let’s compare some of the key features to help you choose between JET and SQL Server Express.

Price

The cost aspect is basically a wash. Distributing either JET or SQL Server Express with your application is free. Score: 1 for SQL Server Express and 1 for JET.

Footprint

JET has a clear advantage in terms of its footprint: It's very lightweight and distributing the database is as easy as copying the .mdb file along with your application. SQL Server Express is a heavier weight product; you need to install and run SQL Server Express as a service. Score: SQL Server Express 1 and JET 2.

Scalability

The difference in footprint is a contributing factor to a difference in scalability. In this area, there’s really no contest: SQL Server Express scales far better than JET. SQL Server Express is connection-based and can scale up to the system’s hardware limits. The JET database is file-based, and concurrent connections run into file-locking problems. Although JET implementations are great for single users, they quickly run out of steam for multiple connections. Microsoft recommends no more than 20 users per JET implementation (5 to 10 users is optimal). Score: SQL Server Express 2 and JET 2.

Compatibility

SQL Server 2005 is the standard by which you need to judge the compatibility of Microsoft database products, and in this area SQL Server Express has the advantage over JET. SQL Server Express shares the same core database engine as the SQL Server family, so moving a database from SQL Server Express to another SQL Server system is as easy as detaching and reattaching the database--no code changes are required. To move a database from JET to SQL Server (or SQL Sever Express) you must make code and database changes. Score: SQL Server Express 3 and JET 2.

JET is a great lightweight database that’s well suited for single-user implementations, but because SQL Server Express is based on SQL Server technology, it has an important advantage of being easily scalable for multi-user applications, not to mention advantages in security and manageability.

2.

SQL Server Express Jump Start

Loading SQL Server 2005 Express with Data
      by Michael Otey, [email protected]

After you’ve downloaded and installed SQL Server 2005 Express, the best place to start learning how to work with data is with one of the Microsoft sample databases. The most popular example databases are AdventureWorks for SQL Server 2005 and Northwind and pubs for SQL Server 2000. AdventureWorks makes use of the newest SQL Server 2005 features and data types, but the Northwind and pubs examples use only the data types that were available in the previous release.

You can download the installation file for the AdventureWorks sample database from the following link. Double-click the .msi file version to run the installation program.
      http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

Surprisingly, after you install the AdventureWorks .msi file, the sample installer installs only the database data (.mdf) and log (.ldf) files. Before you can use the database, you must manually attach these files to your SQL Server Express database. Fortunately, this process is easy: Use Query Editor (from SQL Server Management Studio) to attach the AdventureWorks database, or use the built-in SQLCMD tool. To use SQLCMD to attach AdventureWorks, run the following command:

SQLCMD -E -q "sp_attach_db AdventureWorks, 'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDataAdventureWorks_Data.mdf', 'C:Program FilesMicrosoft
SQL ServerMSSQL.1MSSQLDataAdventureWorks_Log.ldf'"

If you want to install the sample pubs and Northwind databases, double-click the .msi version of the file from the following link:
      http://www.microsoft.com/downloads/details.aspx?familyid=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

This installs two T-SQL scripts that you must run to build the databases. Again, you can use either the built-in SQLCMD command line tool or you can use Query Editor from SQL Server Management Studio. The follow two command examples let you use SQLCMD to install the sample databases to the local system:

SQLCMD -E -i "C:SQL Server 2000 Sample Databasesinstpubs.sql"
SQLCMD -E -i "C:SQL Server 2000 Sample Databasesinstnwnd.sql"

3.

Check It Out

Product Highlight: Microsoft is Providing a Free Tool Called SSEUtil

Are you looking for a lightweight and easy-to-use tool to help you manage SQL Server Express 2005, but you don’t want to wait for the SQL Server Management Studio for Express? Well, look no further.

Microsoft is providing a free SQL Server Express Utility—SSEUtil. This downloadable tool lets you list all installed instances of SQL Server Express; connect to an instance; and create, attach, and detach databases as well as execute SQL statements. Check out SSEUtil at:
      http://www.microsoft.com/downloads/details.aspx?familyid=FA87E828-173F-472E-A85C-27ED01CF6B02&displaylang=en

7.

New Product

Simplify Reporting Services Reports
      by Blake Eno, [email protected]

Designing and delivering SQL Server Reporting Services reports (RDL files) has become easier with SoftArtisans OfficeWriter 3.5. Users can now design and deliver their RDL files inside Microsoft Excel and Microsoft Word through the OfficeWriter Designer, a distributed client-side toolbar that’s integrated in your Word documents and Excel spreadsheets. You can disperse the toolbar among all your users, and once it’s deployed, anyone can create and publish reports directly to the Reporting Services server without leaving Microsoft Office. The toolbar lets you query a data source and add merge fields and repeat blocks. Once you’ve designed your report, you can save it to your hard drive, view it instantly, or publish it to the server for distribution. A free trial version of OfficeWriter 3.5 is available. For more information, contact SoftArtisans at 877-763-8278 or [email protected].
      http://www.softartisans.com