Microsoft Access or SQL Server Express

Thursday, August 28 2008 -

We've had a couple of people ask us why Graffiti uses Microsoft Access instead of SQL Express. I wanted to explain the decision and also point out that Graffiti will run just fine on SQL Express as well as SQL Server 2000 or SQL Server 2005. With our commercial edition you'll actually be able to choose between Access or SQL Server.

We decided to use Microsoft Access instead of SQL Express because we know that the majority of customers that would deploy Graffiti would do so on servers that were hosting hundreds, if not thousands, of other sites.

As Scott Guthrie states, "SQL Express is is good for dedicated hosting scenarios, and for scenarios where you have a small number (4-10) customers on a box (where each can get their own instance of SQL Express)" he continues "When you have 100-1000 customers on a box (massive shared hosting), having a separate instance of SQL Express for each customer doesn't scale. That is where/why you'd want to use SQL Server -- and carve up a separate database for each customer."

http://weblogs.asp.net/scottgu/archive/2005/10/15/427581.aspx#428941

A lot of people in the Microsoft developer community have been really disappointed that SQL Express didn't live up to its expectations: a realistic replacement for scenarios where a simple light-weight copy-and-deploy database was required. Believe me, we'd much rather use SQL Server!

The other challenge is that many hosting companies still charge a premium for SQL Server storage. From our perspective it was more important to keep the following barriers to entry as low as possible:

  1. Hosting costs at as low a cost as possible. Hosting companies typically don't charge for use of Access databases.
  2. Install and setup as simple as possible. Access allows the install process to literally be as simple as getting the files on the server.

Hope that helps!

8 comment(s) so far

Justin Rudd wrote on Friday, December 07 2007

What about SQL Server Compact edition? www.microsoft.com/.../default.mspx does it work with that?

Josh Stodola wrote on Friday, December 07 2007

I know I mentioned a little about this on your last post, but have you guys considered/are you guys considering using XML as a method of storage?

James Avery wrote on Friday, December 07 2007

Sqlite is a good option as well.

-James

Rob Howard wrote on Friday, December 07 2007

I'm probably going to get flamed for saying this, but for this type of data (structured & relational) XML is a bad choice.

Here is why:

Over time there is lots and lots of data created. For content intense sites how that data is related to other pieces of content (especially in a blog or cms) is hard to pre-define (think tags, categories, etc.).

With a relational database it is easy to define what the foundation is going to look like and optimize how data gets in and out.

With XML its just not that simple. For example, do you create multiple stand-alone XML documents for each piece of content and then create additional XML index documents? What happens when items get moved from one index (such as a category) into another. You're definitely going to have to do a delete from one file and an add to another file.

Doing I/O like that in a site where you're also trying to actively read files isn't impossible, but it can be impractical.

You also get into problems with search. Unless you are building a separate index running a search for a particular keyword is next to impossible (that is unless you are public facing and can rely on Google, etc).

I don't have anything against XML. I just think using it as a relational store isn't something that is really easy to do without a lot of heavy lifting --- databases are built to do this and make things infinitely simpler.

In fact, I think what is more common (although we don't do this) is to use a relational DB to store XML docs.

Al Pascual wrote on Friday, December 07 2007

Access does not scale that well neither!

Rob Howard wrote on Friday, December 07 2007

@Al Pascual

Correct, and we don't expect the database to be what enabled Graffiti to scale.

Just to be clear: Graffiti isn't about big scale (that would be Community Server).

Graffiti is about getting up-and-running quickly and providing a great foundation that a site can use to run. Scale would mean going to multiple servers to handle massive load =)

A database (ignore that it's Access) does provide tools to organize information such that it's easily retrieved in the correct order.

Our use of a database is primarily to: (1) organize (2) manage and (3) relate how information fits together. Data is retrieved from the database and is put into the cache.

Thomas Freudenberg wrote on Monday, December 10 2007

Access isn't that bad. However, Vista x64 does not have suitable drivers, which is a real bummer.

Josh Stodola wrote on Wednesday, December 12 2007

Of course XML will not be very optimal, that goes without saying. As Al pointed out, Access is not optimal neither, but you still made it available for those who cannot or choose not to use SQL server. Some people might want to use XML for very odd reasons that you would never think of. It's a very flexible format; viewing/modifying the data does not require anything other than Notepad. People could have an infinite number of reasons as to how they could benefit from this. The fact is that the only thing XML requires is hard disk I/O permissions. Any web server can handle that standalone.

Hanselman's blog is powered by XML. If you ask me, there's plenty of "heavy lifting" going on behind the scenes there considering the amount of posts he has made and the amount of comments he has received. It seems to handle it just fine.

Post your comment

Comment