OT: What would you do if you where me (SQL Server or MS Access)?

rxelizondo posted on Thursday, October 19, 2006

We have been building a CSLA application that is targeted to the "regular Joe". Our definition of "regular Joe" suggests that a lot of these regular Joes will not have access to high speed internet.

 

This is bad news for us because our current download includes the .Net framework (20 megs) plus the application (20 megs). If you think about it, 40 megs is already quite a bit for someone using dialup.

 

Because of this, about a year ago, we decided that we were going to use SQL Server 2005 when the application was ran online and MS Access when the application was ran locally. Since MS Jet (Access) is already included on most PCs, we can spare our users from downloading the extra 40 or 50 megs needed for SQL Server 2005.

 

Anyway, here is my question:

 

Maintaining 2 databases is a real pain in the butt, further more, we all know that MS Access does not support stored procedure so we had to build a store procedure simulation class to help make everything work. After dealing with all this for about a year, I am thinking of ditching Access and force the user to download SQL Server 2005 whether they like it or not.

 

If the decision to continue to support the application with an Access database was up to you, what would you decide to do? Keep supporting Access to spare the user form downloading the extra 50 megs? Or make them suffer in the name of simplicity for the programmers? I appreciate your feedback.

 

Thanks.

 

 

figuerres replied on Thursday, October 19, 2006

rxelizondo:

We have been building a CSLA application that is targeted to the "regular Joe". Our definition of "regular Joe" suggests that a lot of these regular Joes will not have access to high speed internet.

 

This is bad news for us because our current download includes the .Net framework (20 megs) plus the application (20 megs). If you think about it, 40 megs is already quite a bit for someone using dialup.

 

Because of this, about a year ago, we decided that we were going to use SQL Server 2005 when the application was ran online and MS Access when the application was ran locally. Since MS Jet (Access) is already included on most PCs, we can spare our users from downloading the extra 40 or 50 megs needed for SQL Server 2005.

 

Anyway, here is my question:

 

Maintaining 2 databases is a real pain in the butt, further more, we all know that MS Access does not support stored procedure so we had to build a store procedure simulation class to help make everything work. After dealing with all this for about a year, I am thinking of ditching Access and force the user to download SQL Server 2005 whether they like it or not.

 

If the decision to continue to support the application with an Access database was up to you, what would you decide to do? Keep supporting Access to spare the user form downloading the extra 50 megs? Or make them suffer in the name of simplicity for the programmers? I appreciate your feedback.

 

Thanks.

 

 

 

I would:

1) ship the user a setup CD with .Net and the app with a click-once installer
    that would get them installed w/o the download and updates can happen when needed - clickonce will only download *NEW* files so it should not take long for small updates.

2) on the cd ship SQL EXPRESS

3) model the "OnLine/OffLine" use of the app and use the local SQL db like a cache
download static data and have the app always use the local static data first.
work out some logic to sync the data between local and remote.

MS Access is ok but I have seen files get corrupted bad sometimes!

also when the user is conneted use the .net background worker process component to have data transfer happen "under the hood" so that some data fills the local cache and things like new orders go out but the user can still use the app. show a thing like a sync-icon that blinks or flashes to showthem it's updating.

 

figuerres replied on Thursday, October 19, 2006

PS: sql express is free,

MS Access is not if the pc does not have MS Access installed then you may be up a creek.

also to go from Jet to .Net means OLEDB and com-interop that's not great IMHO - can make some things VERY SLOW.

rxelizondo replied on Thursday, October 19, 2006

Thanks,

I **REALLY** want to ditch the MS Access database, I never liked it in the first place. I know that using SQL Server 2005 in both ends will make life sooooooo much simpler for us.

 

The problem is that everyone that we talked to pretty much said that if there was no technical limitation on using Access then we should spare the user from downloading the extra 50 megs otherwise we would probably risk loosing a lot of customers due to the huge download, at around 80 megs for the full download we even risk loosing broadband users.

 

However, I guess they can always try the online version first (no SQL server download necessary) and if they were to like the product, then they could either ask to get a CD with everything shipped to their home or simply take the hit and download everything from the web. At this point, I feel 75% sure I should ditch the MS Access stuff but I am still hesitant.

 

PS: When using MS Access database the user does not really has to have Access installed, what its needed is the Jet dlls and the OLEDB driver, most PC already have that even in the new Vista OS.

 

Smirk replied on Thursday, October 19, 2006

Or use VistaDB with a 600kb redistributable...  :-)

Mark Haley

Skafa replied on Thursday, October 19, 2006

Or ship it on a CD for a couple of bucks...

ajj3085 replied on Friday, October 20, 2006

rxelizondo:

The problem is that everyone that we talked to pretty much said that if there was no technical limitation on using Access then we should spare the user from downloading the extra 50 megs otherwise we would probably risk loosing a lot of customers due to the huge download, at around 80 megs for the full download we even risk loosing broadband users.

Well, if JET corrupting databases isn't a technical limitation, then sure its fine.  However, as others have suggested, Sql Server Express is a good choice.  There's also Sql Server Everywhere that doesn't have all the features normal Sql Server would, but is a lighter download I believe. 

Hosting a Sql server may be a good idea too, because if the user purchases the software you can ship a cd or something.

HTH
Andy

Brian Criswell replied on Thursday, October 19, 2006

I completely agree with figuerres.  This is how I would handle it.

JHurrell replied on Friday, October 20, 2006

Someone mentioned VistaDB and I'd like to chime in with Firebird which can be accessed via DotNetFirebird and also has a small footprint.

Incidentally, Access DOES support stored procedures, but it's a rather tedious exercise to get them created.

- John

ajj3085 replied on Friday, October 20, 2006

I'm not sure firebird is a good suggestion.. part of the problem is trying to maintain two different database formats.  Firebird would be trading JET for Firebird.

One of the Sql server (Express or Everywhere) would be a better fit, because they are more or less the same product.

JHurrell replied on Friday, October 20, 2006

That is true if maintaing formats is primary. If distribution size is more important, then Firebird might win.

- John

Lakusha replied on Sunday, October 22, 2006

SQL Express is a real, but free, SQL Server 2005 product.

SQL Everywhere is NOT a SQL Server 2005 product (yet at least); It is widely different. See it as another database type as far as development is concerned because you need to treat it this way in your data access layer. See it as a component providing an in-memory relational db.

On the + side:

• you compile with it, so it is part of your app, not a service to install, maintain and patch
• its T-SQL syntax is a subset of the SQL Server TSQL syntax
• it is extremely ligthweight with support for FK (even with CASCADE), outer joins, constraints, transaction isolation, etc.
• it works also on PDA, tablet, etc.
• It supports replication!!
• etc.

On the - side:

• it doesn't support server side code (no triggers, no stored procedures, etc)
• it does NOT support all SQL datatypes (you have NVARCHAR but not VARCHAR and it does not support MAX types or even XML datatypes)
• its syntaxt is really a subsets, so many nice TSQL features won't work (CTE comes to mind, and probably PIVOT/UNPIVOT, etc.)
• Stupidely enough, it does not support SQLCLR...
• etc. (of course it will continue trailing the real SQL product, so we should expect it to add many of those features)

As far as I see it (for a desktop application), the real use of SQL Everywhere is to be a client side data cache (or business object cache ;-) ). The cache can even be persisted between launches and can benefit both 2 and N tiers deployments. In fact it would be a very nice feature to include it in CSLA since it is the BO's responsability to fetch itself, the BO should also be able to handle its own state and decide if it should refresh itself (of course the expiration process would need the collaboration of a cache manager).