« Buy a PC Without an OS, get a visit from MSFT? | Main | Programming against XML... »

April 06, 2006

SQL Server Everywhere: The Access Killer?

Are any developers really using Access any more? Absolutely. I would say it's the tool of choice for many ISV applications and Web sites because it doesn't require the installation of any "Server" product. It's a file based database, with an in-proc engine, making deployment very simple (you don't have to install the app as Admin), and performance is pertty good. But now, Microsoft is announcing a new version of SQL Server - SQL Server Everywhere.

What is SQL Server Everywhere? According to this blog, it's going to be a release of SQL Mobile.

Why should you care? Just looking at SQL Mobile today, there are a number of advantages over Access:

  1. There is no "Server" to install. The database engine is just a handful of .NET assemblies that you can easilly deploy with your application.

  2. The database is connected to using a file path, just like an Access database.

  3. System.Transactions supports SQL Mobile, but not Access. This gives you a super simple way to wrap operations in a transaction.

  4. It's fast. My testing is showing SQL Mobile to be faster than Access. Anywhere from 1.1x to 4x depending on the operation.

  5. It's better supported. Things like identity columns work with SQL Mobile. The don't really work with Access.

The only down side right now is the admin tools. Access makes it really easy to create tables, and set up relationships between tables. From right inside of Visual Studio 2005, you can create SQL Mobile databases, but there are a few limitations. The default database max size is 128MB, even though SQL Mobile is capable of having 4GB databases. It's also a pain to set up relationships between tables right now. We'll see what comes in the promised CTP.

Posted on April 6, 2006 at 11:00 AM | Permalink

Comments

I agree with this whole article. However, I'd like to mention a couple other "down sides":

1. SQL Mobile only allows multiple connections from the same computer. This is an arbitrary limit. I know that this engine is not meant for thousands of simultaneous users, but I'd love to see the limit raised to two computers (ie. husband/wife sharing a database at same time on home network).

2. I develop an ISV application, and one of the features that some of my power-users love is that my app can generate a new Access database with linked tables referring back to my app database. This allows them to write their own queries and reports. There is currently not a way to do this with SQL Mobile being the app database.

Posted by: Troy Wolbrink | May 3, 2006 11:55:14 AM

Try Sybase SQLAnywhere former Watcon SQL it can be a desktop or a server database. It is really amazing. Sometime I wonder why the developer community ignores better tools and sticks with inferior products. In this particular case my only guess is the huge power of Microsoft and the inferior business skill of SYBASE. By the way Microsoft once obligated SYBASE to change the name of its DBMS from SQLServer to SYBASE Enterprise... and now again MS is naming one of it product again the same as a Sybase product. Who will keep the right to call it product SQLAnywhere???!!!

Posted by: Frank | Jan 7, 2007 7:29:59 AM

The comments to this entry are closed.