Recommended database to use with .NET MVC 2?

Question

I'm getting into MVC and is wonderful, however, still need to decide what dabatase system to use. For many years my options have been:

1) MS SQL. For complex web applications. Example: A shopping cart or CMS.

2) MS Access. For smaller and simple ones. Example: a small product catalog, blog or news system.

I don't want to keep using Access, however, using SQL means using SQL Express if you don't want to pay more (my clients will not want) in SQL database hosting. But using SQL Express (when hosting supports it) get some some connection problems when many connections are opened (from your app and others hosted apps in the same pool).

I want to use LINQ, thats why now I'm forced to use MS SQL (express) in order to use LINQ2SQL.

Any suggestion on what database rather that MS Access or SQL Express can be used that doesn't require more hosting expenses? Otherwise I have to try Entities + MS Access. Thanks for your help.

Answer 1

Like so many questions asked on SO, this one is like "how long is a piece of string?" You can't say really say what's appropriate except if you consider the environment.

For a read-only website, Jet/ACE can perform quite splendidly if you know what you're doing. Michael Kaplan's http://trigeminal.com is backed by a Jet database, but it's read-only. Back in its heyday, MichKa used to say he was getting 100K pageviews per day (or some number that was outrageously high, so far as I was concerned!).

But with a read/write site, you wouldn't be able to support nearly as many users.

That doesn't mean you could use Jet/ACE as the back end. If you have five users max, and it's an Intraweb app, you can be just fine with it.

I wouldn't want to use it myself, but it's not going to be a problem with user populations that are appropriate for the Jet/ACE database engine to begin with, and particularly if you manage your connections properly. Jet/ACE works better with a single shared connection than it does with constant opening and closing of connections (because of the overhead of creating and recreating the LDB file), so you have to code against it differently than you would with your standard server-based database.

Also, Michael Kaplan used to point out that via ADO/OLEDB, Jet was threadsafe, but via DAO, it was not. Use of ADO/OLEDB made it marginally acceptable as a back end for a web site, though I would tend not to choose it for anything other than a site that was very small or disposable, or where there were no options that were not outrageously expensive.

But the key point is that there is no blanket statement that can be made here -- whether it will work reliably depends entirely on the specifics of the environment in which you're using it.

Answer 2

For most small to medium websites, I would definitely use SQL Express. Its free and within its remit, works just as well as SQL Server full version. We run innumerable websites with SQL Express.

Regarding Access, Access is no joke. It just depends how you use it. If I put @mattimus on a horse and told him to lasso a 450 kg cow (something I did on a daily basis as a kid), he too would be a joke. It's horses for courses, isn't it?

To dispell the Access misconceptions, that are based on ignorance and false snobbery, see: Who Access really is (one very versatile cookie for a start)

Just don't use Access for a website. You don't need to. SQL Express is all you need.

Answer 3

You can use pretty much any database that you want with MVC. As other have suggested stay away from Access for web development.

SQL Server including the Express edition is a good candidate for small web sites. You can also look into MySQL and other cheap/free relational databases.

On an unrelated note, mi familia es de Merida Yucatan. :)

Answer 4

Access is completely unacceptable for any website that expects to handle more than one user at a time.

http://www.15seconds.com/Issue/010514.htm

What are your requirements for the databse? Do you need it to be relational? How many simultaneous users are you expecting for your website?

I would either use MySQL or SQL Server Express.

Perhaps you can post the details of your SQL Server problem. We may be able to find a fix for it.

Answer 5

Access is a joke. You should go with MSSQL, MySQL, or Oracle. I like MSSQL personally. Access is not designed to handle large operations and is ridiculously slow.