X-Copy Database Options
Rob covered the reasons we chose Microsoft Access as one of our two supported databases for the first beta release of Graffiti.
So far, despite a little bit of query quirkiness (top, lack of sub-queries, and no support for named parameters) Access has been working really well. I have used Access on this site since the beginning and I have yet to see anything that would concern me. I think many people remember back to the ASP.Old days where Access to could really slow down under heavy usage. However, with ASP.Net caching, you really do not have to hit the database nearly as often.
Before choosing Access, we looked at Sqlite which probably would have worked, but I do not think we would have been any better off than using Access. The other obvious choice would be SQL Express, but do to security reasons, it is still DOA when it comes to shared hosting; which is really pathetic. And then there is also SQL Compact edition, which throws a NotSupportedException if you try to use it under IIS (again, Nice!). Even as I type this, I cannot fathom why SQL Express did not have shared hosting as it's number feature requirement. What a huge missed opportunity to make the power of SQL Server accessible to the masses.
There are many other database platforms out there, but the key factor we are looking for is 100% x-copy deployment. Database configuration and setup is really hard for non-geeks to grasp and can easily get in the way of a great user experience. We are still exploring some other options and I am trying to find some time to play with VistaDB which could be a viable alternative as well. Does anyone else have any other suggestions?
We are also going to document our database provider API. It is very simple, relies on code generation, and has no clue what is going on in the layer above. Hopefully with a little help from the community we can have providers for databases like MySQL and Sqlite. Speaking of Sqlite, if you are familiar with it and want to help flush out a provider for it, please contact me (scottw@telligent.com). I did start a provider for it, but never got around to finishing it.
Comments
Brandon LeBlanc on on 12.10.2007 at 2:20 AM
Hey Scott, I shot over an email a few days ago about possibly moving to Graffiti for my blog at www.brandonleblanc.com. Its currently running WordPress right now. I've got some questions just not sure they are appropriate for the topic of this post.
In regards to databases for blogs, how durable or solid is using Access as the database provider for a blog? What is the main difference using say MySQL or MSSQL for a blog versus Access?
- Brandon
paperino on on 12.10.2007 at 10:59 AM
Hi Scott,
I was looking to graffiti as a nice simple solution for a blog. Since you ask specific questions, here are some answers:
1) Access is great, but it not supported on 64 bit; moreover currently, give this issue docs.graffiticms.com/.../updateable-quer , it doesn't work nice with hosting platforms like GoDaddy
2) Sqlite sounds a good solution, I've seen ADO.Net 2.0 providers for it so you shouldn't need to write them. The problem is that since is native code it requires interop to work properly which *IIRC* doesn't work under medium trust. If you can make SqLite work under medium trust, bingo!
HTH
Scott Isaacs on on 12.10.2007 at 3:15 PM
Curious, did you guys consider object DBs like db4o? I'd be interested in seeing a DB provider for something like that, or at least hearing someone's experience with it.
George J. Capnias on on 12.10.2007 at 10:58 PM
Scott, have you considered to create a database provider based on Microsoft's Compact SQL v3.1 or Compact SQL v3.5? It matches the profile of x-copy deployment, it uses a fully managed provider and it is possible to be used in web applications.
Mischa Kroon on on 12.11.2007 at 8:19 AM
Firebird, might be a good option.
www.firebirdsql.org/.../index.html
Although this looks bad:
www.firebirdsql.org/.../how-to-use-embe
Scott Watermasysk on on 12.11.2007 at 9:46 AM
@George
We have looked into SQL Compact. It is hard coded to not work under the IIS process.
Scott Watermasysk on on 12.11.2007 at 9:47 AM
@Mischa
Did you notice the line at the end which said "Do not do this". :)
We are working on adding some addition database providers and we will keep firebird in mind.
Thanks!
Scott Watermasysk on on 12.11.2007 at 9:49 AM
@paperino
We are looking into the 64bit issue. It looks like the proper .dll's are on the server but they are not registered.
We are also still evaluating an different xcopy option or two.
Thanks.
Paperino on on 12.11.2007 at 12:01 PM
You can force SQL CE to run under IIS but you have to take the responsability for locking the file: blogs.msdn.com/.../sql-server-comp
The problem with SQL CE is the same with SqLite: no medium trust support, although with SqLite it's simpler to workaround (GACing the provider dll).
Paperino on on 12.11.2007 at 12:32 PM
I've looked into the limitations of firebird and both seem workaroundable. For the second you can probably change the native DLL to not lock the file if a certain parameter is passed. But I don't think it would run medium trust.
Jason Short on on 12.18.2007 at 4:59 PM
VistaDB is XCopy deployable, and you can use it in a medium or even low trust environment. It is not as fast as SQL CE, but it can go places SQL CE cannot, and supports Views, TSQL Procs, Triggers, CLR Procs, etc.
Scott Watermasysk on on 12.19.2007 at 7:54 AM
Hi Jason,
Yes, VistaDB is something we plan on using. See: graffiticms.com/.../vistadb-support
Thanks,
Scott
Chris Hoffman on on 12.21.2007 at 1:07 AM
Scott,
Does the support of VistaDB now mean that Graffiti will work with sites like GoDaddy?
Chris
Scott Watermasysk on on 12.21.2007 at 8:32 AM
Hi Chris,
Yes, VistaDB will now enable x-copy deployment on sites which run under medium trust.
Thanks,
Scott