SQL Database Dilemmaby satman
SQL databases for Light Developers

Ive spent the last day tinkering with various SQL offerings trying to find something that I can use as an impoverished developer with DiscountASP.NET hosting without having to fork out extra cash.  My current hosting is a Windows 2008 Server with .NET 4 for ASP.NET framework web applications, development is in VS2010, while recently dabbling in WebMatrix 2. 

MS SQL 2008 R2
  • The daddy of databases, Integration with VS2010 is as good as it gets.
  • most expensive option to host, also to have it as a test rig is not really an option.
  • SQL Management Studio to manage database engine is pretty good
SQL EXPRESS 2008
  • Free offering from MS, so good for freelance developers
  • Integrates well with VS2010, great for Entity Framework models
  • DASP does not support SQLExpress, but its .mdf files can be attached to MS SQL db via control panel
MS ACCESS 2007
  • quick easy portable mdb files for easy portability but much maligned for its web hosting failures
  • integrates ok with VS2010 Server Explorer, although needed a bunch of providers when used to provide membership services for a web forms website. Editing within MSAccess itself is the best way to develop the database
  • I quite like using MS Access as its free to host and supports Inline Parameterised Queries which show up as Stored Procedures in VS2010 SE. Prefer this approach to put as much SQL in the DB rather than in code.
SQL Compact Edition 4.0
  • lightweight standalone offering which should work as an .sdf file deployed
  • Requires VS2010 SP1 and 
  • Webmatrix likes this ok, but VS2010 deployment requires the relevant framework files to be copied over into the bin folder on DASP for it to work
  • No way to store queries that I can see, only Tables which doesn't suit the way I prefer to code yet but working with the EF Model might allow for better design.
  • SQL Management Studio only supports SQLCE 3.5 files, not 4.0 which is a big let down, all manipulation needs to be done within VS2010 but a nice tookit (SQL Server Compact Toolbox) helps.
  • Supports EF Data models which are quite good for data classes and it works with MVC 3 deployments
MySQL
  • Another lightweight offering which DASP charges half the price of MS SQL to host
  • ideal for Wordpress type deployement, WebMatrix 2 has it as a deployable app in its gallery
  • no EF integration or management within VS2010 except for the Server Explorer, but can be used as a membership service with the Connector/.NET addon
  • still deciding if I want to use MySQL, its lack of EF Modelling in VS2010 deters me somewhat as its something I want to persue.
I cant really decide which database to opt for, I'm reluctant to fork out $10 a month just to have a playground and I'm not quite sure If I can use SQLExpress .mdf files to develop and just attach them to MS SQL on publish. MS Access is convenient but not really practical in a multiuser environment, although this blog site I wrote is currently using it for membership and story, comments and data quite happily. I was quite excited by SQLCE4 but I its lack of SP or queries puts me off.

I could be wrong on a few points here but if anything changes I will post a comment

1 Comments #128 Wednesday, November 9, 2011 02:04 AM Filed in Technology
Submitted by Web Applications Development
Thanks for this brilliant article I really like it...Great info!
posted Thursday, November 24, 2011 05:38 AM 120.28.91.168: Linkback http://www.mindblowcreatives.com
Name (Required)
Email (Required)
URI(will display as a linkback)
Comment (Required)
128 (no script tags allowed)

Custom Blog Engine Developed by Satman