Nice to Have Criteria for Local Databases
In my last blog post I discussed the essential criteria that I'll be applying for our review of local storage databases, VistaDB and db4o, to appear in our July issue. In this blog post, I'll talk about some less essential criteria.
For my local storage database, I want as much compatibility with my existing database engine as possible. I don't want to have to use a different version of SQL to work with the local database than I use with my central database -- that's just an accident waiting to happen. I'll eventually get confused and use the wrong syntax with one of the database engines and not discover my error until I attempt to execute my SQL statement. This is the reason that I'm less interested in a Jet database solution. For instance, doing Joins across databases in Jet requires a different syntax than I use with any other database engine.
Of course, if I can use LINQ (and Entity Framework, presumably) to access my local storage database then this issue goes away: I can leave it to LINQ to handle generating the appropriate SQL for whatever database I'm connected to. I assume that I'm going to have to manage connection strings, anyway, so managing the right LINQ connections will be similar. Even in this scenario, though, I need a local database that will support all the names I've given my tables and columns.
Overall, the more that the local database implements the functionality that I expect to find in my central database, the happier I will be. While having views and stored procedures isn't essential, having them means that I have the full range of tools that I use in creating applications available to me to support local processing. Stored procedures is another blow against Jet (unless I use the version that comes with Office 2010). The problem with stored procedures is that I'd probably have to rewrite them from whatever language I use on my central database (or learn a new stored procedure language).
Replication would also be nice to support synchronizing local storage with the central database because, otherwise, I have to write the code myself. However, expecting a replication process to work across the Internet through Web services is probably too much to ask for. On the 'ubiquitous' criteria that I mentioned in my last post, a solution that runs anywhere that .NET runs would be nice, though it's not unreasonable to expect all of my client to be running Windows. This is another problem with SQL Server and Jet, which are "Windows-only" products.
And, of course, I want as much integration with Visual Studio as possible. After all, it's the name of the magazine.
As I asked at the end of the blog posts that described the scenarios that I'll be testing for: Do these criteria sound sane to you? What criteria would you want to apply when looking at local databases?
Posted by Peter Vogel on 06/16/2010