Would Building An Application Using A Sql Server Database File (mdf) Be A Terrible Idea?

- 1 answer

I'm working on a side project that would be a simple web application to maintain a list of classes and their upcoming schedules.

I would really like to use Linq to SQL for this project, but unfortunately the server environment I'm developing for only has MySql available. I've dabbled briefly with Subsonic but it just doesn't get the job done.

The database requirements for this application aren't that great, though, so I'm curious if using an MDF file in App_Data would be a workable solution. Otherwise, it appears I'm going to have to hand-code sql queries which I want to avoid at all costs.



I've long since completed the project which prompted this question, but recently I've had another project come along with very minor data requirements, so I spent some more time experimenting with this.

I had assumed that Sql Server Express required licensing fees to deploy, but this is not in fact the case. According to Microsoft's website, you are free to use it with certain restrictions:

  • Maximum database size: 4 GB
  • Maximum memory used: 1 GB
  • Maximum CPUs used: 1 (complete procs, not cores)

Sql Server Compact is a bad idea for web applications because it requires a hack to make it work, and it isn't built for the concurrent access you'd need for the web. But if your application can fit within the modest limitations of Sql Server Express, it works pretty well. And since it speaks regular T-SQL like its larger siblings, you can use Linq to SQL with it.

I hear that Linq to Sql support is now in the Mono trunk for the 2.6 release, so L2S' tight-coupling to Sql Server will likely be a moot point in the near future. I will either end up porting my code to use Mono's superior Linq to Sql implementation on the db of my choice, or go another route entirely (SubSonic has improved by leaps and bounds since I last tried it). But for the time being, Sql Server Express is a valid choice for very small database-driven apps.