Deciding between SQL Express, JET and SQL Everywhere
This is one of the most hotly debated topics around so expect quite a few opinions. My TechEd talk (which might be posted online on the Microsoft site sometime in the near future) discusses where SQL Server Express Edition fits in relation to JET/Access and the newest DBMS system SQL Server Everywhere (SQL Ev). Yes, there are a lot of choices and each has its place.
A number of factors should be considered when you're trying to decide which approach to take. These include (but are not limited to):
· How do you intend to deploy the database? Is it to be included with the other files deployed with the application? The new One-touch deployment schemes in Visual Studio can make this easy (or a nightmare). SQL Ev is especially easy to pass around in email or by any means.
· Do you need to deploy it at all? Can it simply be accessed over the local area network? In this case it’s best to choose a service-oriented approach like SQL Server Express or one of the “paid” SKUs.
· When you use SQL Server you either need to run setup on the target system (as an administrator) or access it as a network service. SQL Ev does not have this limitation if you imbed its DLLs in your application.
· Is the system connected to a network? Is this connection likely to be broken as when deploying to a smart phone, PDA or a wireless network? In this case, hard-connection strategies simply aren’t reliable enough so you’ll want to consider using a replication-based architecture.
· Do you plan to access the database over the web? In this case, you’ll need SQL Express visible to the IIS server—SQL Ev is not suitable for this job (and is intentionally disabled in this scenario). Generally, we approach this problem by writing an ASP application or a Web Service that accesses data to be passed back as HTML or XML (or if you’re clever, using remoting).
· Do you or can you create the database on the fly? In some cases it’s just as easy to create the database on the fly—this can be as simple as 1 line of code (in SQL Ev) or take an entire routine to accomplish.
· How much data do you expect to manage? If it’s over 2GB, you can’t use JET/Access and if it’s over 4GB you’ll need a paid SQL Server SKU or some third-party DBMS.
· How many users do you expect to share the database? There are lots of applications that simply need a local data store—with a single user (the application). SQL Ev is perfect for this. It’s light, fast and does not run as a service so it has no residual overhead. If you expect to support dozens of users, you’ll do fine with SQL Server Express (as a shared service).
· Does the application run on a workstation along with other applications? In this case the application needs to be “well-behaved” and not consume every bit of RAM in sight. SQL Ev has the smallest footprint of all (about 1.7mb). SQL Server consumes a lot more RAM but does yield it when requested. Because SQL Server runs as a service, it constantly consumes CPU time—even when not servicing queries. This means you might want to add code to stop it when it’s no longer needed. Note that when you use the “User Instance” approach with SQL Server Express, it actually is running two instances of SQL Server.
· Does you application need to scale up to or also connect to another DBMS? In this case you need to consider SQL Express as it supports all of the same programming interfaces as the paid SKUs. This means when it comes time to upscale, you can simply attach the database to the larger SQL Server. SQL Ev uses a simple subset of the basic relational database constructs—for example, it does not support Views, triggers or stored procedures.
· Is the DBMS supported or just obsolete? Microsoft is trying to move developers away from JET/Access databases (not Access the product) so I can’t recommend it for any business applications. (Okay, there are a dozen more reasons why I don’t support JET but we don’t need to go there again).
· Is the DBMS secure (enough) for your requirements? Security should probably be at the top of this list. Each DBMS implements (or attempts to implement) some form of security. Only SQL Ev can be totally encrypted which means you can (relatively) safely pass its database file around without worrying that it contains malicious code—it does not have the ability to contain executable code of any kind). SQL Express’ security model really hinges around “physical” security. That is, it depends on the system that hosts the DBMS to be kept secure. This means you’ll want to encrypt the SSAN and credit-card data columns as well as any other sensitive information kept in the database.
I just finished my new book (expected to ship this summer) that details these issues (and many more). Keep an eye on www.hitchhikerguides.net for details.