OT: Single-user database backend

OT: Single-user database backend

Old forum URL: forums.lhotka.net/forums/t/5163.aspx


tmg4340 posted on Monday, July 28, 2008

Morning!

I'm going to be doing a project for my wife's business, and I'm looking for some advice.  This will be a single-user app, but it's not the kind of thing you can just whip out in an Access database (or, more appropriately, the Access solution currently in place is not everything it's cracked up to be...)

I don't really want to continue using Access as the backend DB, mostly because I have nagging distrust of Access for databases of any complexity - and this one will have it's fair share.  Plus, I don't want her going into the database directly and "trying things".  I looked at SQL Server Compact Edition, but I'm not sure whether that's really robust enough - it really feels like it's more geared towards mobile apps (even though MS touts it as part of their Syncronization Services package), which makes me wonder how it will deal with larger sets of data.

I really don't want to go to all the trouble to install SQL Server Express, and I don't really need all that power anyway.  Plus, then I have to go to all the coding trouble of checking for the service, starting it as necessary, etc.

Does anyone have any other (preferrably free) ideas?  I don't necessarily have to have something that supports stored procedures, though those would be helpful in a couple of cases.  Basically, anything that I can "embed" within my application (i.e. no external process to consider) would be best.

TIA

- Scott

ajj3085 replied on Monday, July 28, 2008

Well, with Sql Server Express you can specify a connection string that points to an mdf file, and Sql automatically starts when the application connects, and shuts down when the application has finished.  I think the feature is call user instances.  So it provides the power of sql server, but without the need for a service which is always running.

tetranz replied on Monday, July 28, 2008

ajj3085:
Well, with Sql Server Express you can specify a connection string that points to an mdf file, and Sql automatically starts when the application connects, and shuts down when the application has finished.  I think the feature is call user instances.  So it provides the power of sql server, but without the need for a service which is always running.


I think the SQL Express service needs to be running when your app starts. At least that's my experience when I've run demos etc. When they don't work it's because "oops, I forgot to start SQL Express".

Scott, if you don't want SQL Express then I think SQL Compact is your best answer. I haven't tried it but listened to a podcast about "SQL Everywhere" as it was called. I seem to remember that it's basically a collection of dlls that you reference and distribute with your app.

ajj3085 replied on Monday, July 28, 2008

I'd be surprised if that was the case, as that seems to be the opposite of what was intended.

http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx

At any rate, it's not like his application would have to worry about starting or stopping the service... it should be install and go.

tetranz replied on Monday, July 28, 2008

Perhaps you're correct but I'm still not sure if that page means that the actual Windows service starts and stops. I agree that it shouldn't be an issue for Scott anyway. Just set the service to automatic.

SQL Express is a pretty good "deal" for small apps. There's now a free SQL Server Management Studio for it which wasn't available early on.

BTW, I found that page last week when I was working from home and trying to find out why something didn't work. It turns out that there is a bug that prevents SQL Express user instances from working via remote desktop.

Q Johnson replied on Monday, July 28, 2008

I'd suggest you re-think your aversion to Access.

It is clearly the least hassle to make available.  You can save queries and use regular ADO.NET syntax to run them (so what if they have that lame name of 'QueryDefs').

They have a built-in reporting solution (from which most report writer tools can import).

I think it got a really bad rep when it was used extensively for apps in the Access97 version.  Many users thought the database just "got corrupted" on them.  But what really happened was that they ran a long query and when they used Task Manager, they saw the message "Application not responding."  So they concluded that it was corrupted and hung.  But, in fact, it was just busy doing that query.  And when the user took the action to kill the process while the query was running, the database DID get corrupted.

Now that we are, for the most part, more knowledgeable about these things, you really rarely hear of an Access DB "getting corrupted" on its own.

And regarding "prying users" (your wife included), you can just go to the Tools\StartUp menu and likely restrict a user's access to the degree you like.  You can even secure it using its own security model and limit access to those that know the password you choose, if you want to go to those lengths to keep unwanted users out. 

And it sure is easy to make and restore backups <g>.  In fact, prying users usually know enough to make a copy of the mdb and do their prying there.  That's pretty safe (usually <g>).

There's something about doing better with "the devil you know" sometimes, isn't there?

Good luck with whatever you choose.

 

rsbaker0 replied on Monday, July 28, 2008

Q Johnson:

I'd suggest you re-think your aversion to Access.

...

I think it got a really bad rep when it was used extensively for apps in the Access97 version.  Many users thought the database just "got corrupted" on them.  But what really happened was that they ran a long query and when they used Task Manager, they saw the message "Application not responding."  So they concluded that it was corrupted and hung.  But, in fact, it was just busy doing that query.  And when the user took the action to kill the process while the query was running, the database DID get corrupted.

Now that we are, for the most part, more knowledgeable about these things, you really rarely hear of an Access DB "getting corrupted" on its own.

...

We have supported Access for years, along with Oracle and SQL Server. On a single machine, Access is robust enough, but network instability, application crashes, etc. can also result in a corrupt database. We still end up doing compact/repair operations on customer databases every month or so.

Also, our legacy application uses DAO for database access, and since this is basically the native interface it is quite fast. However, in our migration to ADO.NET, we've found Access to be significantly slower than a single SQL Express installation running on the same machine. (Probably because it reparses queries each time versus having cached plans, but I'm not sure why)

I certainly agree it is convenient, but with SQL Express being free, more robust, and faster, I try to steer people away from it if they can live with a slightly larger footprint and administrative tasks during the initial setup.

Marjon1 replied on Monday, July 28, 2008

SQL Express is a more powerful tool, but requires that it is installed and that the service be configured. It then also requires administration in the form of either sql accounts to be created at install or for windows groups to be assigned to SQL roles, etc, etc. The ability to load a mdf file directly is just another way of accessing a database without a user having to make sure that it is there and makes it somewhat easier to manage but is a bit of overkill for most single-user applications.

This is where SQL Compact really shines, as it will automatically start and stop when your application starts, you don't have to worry about SQL instances, security or anything else. Access the compact DB file and basically away you go. It requires no administrative rights to run. Its biggest limitation at this point, is that it does not support stored procedures, however, if you are happy to do manual CRUD operations then that would be the solution I would suggest.

From a installation / size perspective, SQL Express is a massive additional weight. Whereas you only need to provide the SQL Compact dll's and it will run, makes your application much lighter at install.

Marjon

ajj3085 replied on Tuesday, July 29, 2008

Marjon:
SQL Express is a more powerful tool, but requires that it is installed and that the service be configured. It then also requires administration in the form of either sql accounts to be created at install or for windows groups to be assigned to SQL roles, etc, etc. The ability to load a mdf file directly is just another way of accessing a database without a user having to make sure that it is there and makes it somewhat easier to manage but is a bit of overkill for most single-user applications.


From what I read, it's not really like that at all.  User instances are specifically designed for single user applications and the installation was designed so that it can be done as part of a ClickOnce installation.  ClickOnce installation is pretty straightforward (from the user point of view), and it didn't even sound like a "full" install of Sql Server Express was required... but I may be wrong on that point.

Marjon:
This is where SQL Compact really shines, as it will automatically start and stop when your application starts, you don't have to worry about SQL instances, security or anything else. Access the compact DB file and basically away you go. It requires no administrative rights to run. Its biggest limitation at this point, is that it does not support stored procedures, however, if you are happy to do manual CRUD operations then that would be the solution I would suggest.

From a installation / size perspective, SQL Express is a massive additional weight. Whereas you only need to provide the SQL Compact dll's and it will run, makes your application much lighter at install.


Again with User instances you need not worry about security either; I get the impression your database will be in a single user mode anyway.  User instances I don't believe need Admin privledges either. 

See this MSDN article: http://msdn.microsoft.com/en-us/library/bb264564.aspx

So Sql Compact may be a good fit as well, it depends, but I wouldn't rule out Sql Express because of it's "massive additional weight," because while larger than Sql Compact, it's not terribly heavy either.

I think the OP should check this link to decide.. but certainly some version of Sql Server seems to be a good fit.

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx

tmg4340 replied on Tuesday, July 29, 2008

ajj3085:
From what I read, it's not really like that at all.  User instances are specifically designed for single user applications and the installation was designed so that it can be done as part of a ClickOnce installation.  ClickOnce installation is pretty straightforward (from the user point of view), and it didn't even sound like a "full" install of Sql Server Express was required... but I may be wrong on that point.

You still need an install of SQL Server Express on the client machine - user instances are still managed by the engine.  That's part of my issue with SQL Server Express - I don't really want to include another 40+ MB of installation for this.  Admittedly, I'll be doing the install, but I didn't really want to add the overhead of another service that I either let automatically start up (thus consuming resources on a machine that is not dedicated to the use of this app), or manually start (thus requiring me to write code to start the service - even with user instances, the service still has to be running.)

ajj3085:
Again with User instances you need not worry about security either; I get the impression your database will be in a single user mode anyway.  User instances I don't believe need Admin privledges either. 

See this MSDN article:
http://msdn.microsoft.com/en-us/library/bb264564.aspx

So Sql Compact may be a good fit as well, it depends, but I wouldn't rule out Sql Express because of it's "massive additional weight," because while larger than Sql Compact, it's not terribly heavy either.

I think the OP should check this link to decide.. but certainly some version of Sql Server seems to be a good fit.

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx

User instances in essence apply an administrative (actually dbo) privilege to the user, as far as the database is concerned.  It really is designed to act like an embedded database, where privilege doesn't mean anything.

I have read this link before, and it's part of why I'm waffling between SQL Server Express and SQL CE.  My problem with CE is that I can see this database getting kinda big, and I don't know how well SQL CE does with larger databases.  It was originally designed to run on mobile devices, where size constraints are king.  The documentation says the database file can get to 4 GB, but what kind of performance am I going to get?  I certainly don't expect it to get that large, but the Access DB is already several MB, and this one is going to have more info.

OTOH, like I originally said, I don't really need all the punch of SQL Server Express.  The database might be on the larger side, but the SQL is not overly complicated.

Maybe I'm over-thinking it...

- Scott

ajj3085 replied on Tuesday, July 29, 2008

Well, wasn't sure if you had seen the white paper or not.  I guess I should also have realized you were the OP.. opps... Smile [:)]

I read the other link very carefully, and it does look like you need admin privs because the "parent" service still needs to be installed.  Seems unfortunate to me, but I guess that's how it is.

I'm not sure how performance would be, if it were me my concern would be lack of SPs, views, functions, no "procedure tsql."

Maybe you should write out the features you'd want from a db and weight them, then see which one adds up the best.

ajj3085 replied on Tuesday, July 29, 2008

Oh... I also hear firebird is an ok database... but never used it, so I can't tell you much about it.  Maybe it's not firebird... it's whatever database mozilla uses.

tmg4340 replied on Tuesday, July 29, 2008

I looked at Firebird a little while ago, and did not come away with a warm fuzzy.  They have an embedded database version, and their databases support SP's, views, etc.  But I've seen conflicting reports as to whether they actually work in the embedded version.  That information is old, but I can't find anything new.  I suppose I could download it and try it out - but then I'd have one more choice to think about, and right now, I don't need more choices.  Smile [:)]

The lack of "procedural SQL" in SQL CE can be a pain, but it's not the end of the world for me.  Since this is a single-user, single-machine installation, a stored procedure isn't really going to gain me a whole lot.  The hop to the database is pretty short, and I could argue that it's easier to write what would be T-SQL logic in C# anyway.  Not having views could be more problematic, though.

I do want to thank everyone for their input!

ajj3085 replied on Tuesday, July 29, 2008

Well, I thought I'd throw it out since you did ask originally. Smile [:)]

It seems like linq to sql works with CE as well, so that could be pretty powerful and might make up for some of the lack of features in CE.

I'd be interested to know what you ultimately decide and how you feel about it down the road.

Good luck!

jeff replied on Tuesday, July 29, 2008

+1 for sql compact.

Don't let the name fool you, these DBs can go up to 4GB. It is a great alternative to Access for single user type scenarios (even though a sql compact db can handle 256 connections). I don't think Access is a real choice for a backend DB anymore.

Plus, if you ever needed to upscale to sql express all your code would essentially just work (as long as you use parameterized sql rather than stored procs). Who uses procs anymore anyways? :)

Test it out, throw a gig of sample data on there, make some relationships / indexes and run some queries from c#. I think you'll find it plenty adequate.


Copyright (c) Marimer LLC