database question

[victorb17]

I am developing a program that will need to be deployed across multiple computers. It will not need to be networked or anything, just a stand alone program that several people will use.

I usually only write software for my own use, and never need to take into account other user's machines.

My question is: If I use an sql database (preferred) for my software to store info, how will I "email" the program and the database to the other users?

To be more clear: I have sql server running on my machine. Will they need to install it on theirs? They will be novice users and incapable of doing that, and they will be all over the country so troubleshooting will be not fun.

Would it be better to use an access database? I am using a database on "localhost" right now. Should I have it set up differently? Maybe somehow off the server and simply right in the directory?


Thanks for the help, Victor.

[dilettante]

If your program and database are used together as a unit (rather than multiple users on multiple PCs sharing the same copy of the database) then using a Jet (not "Access") database will be a far better choice in most cases.

Jet MDBs make good embedded databases. They are weaker as shared databases and not client/server databases at all. As long as your needs fit within Jet's MDB size limitations this is usually the tool you should reach for first. If required later, "upsizing" a Jet database to SQL Server can be far easier than going the other way when you find you don't need the RAM consumption and background cycle-stealing all day long, administrative headaches, and deployment woes of a SQL Server instance.

And the necessary software is part of Windows anyway: Jet 4.0 has been shipped as part of Windows (again, not Access) for a very long time. At least since the Win2K/Me era and you'll usually find it on most Win9x systems that have been updated over time.


Even so it isn't without deployment issues. But they're seldom any more than the same concerns you'd have if you were just using updateable files of any kind - even text files.

The big issue is where to put the deployed MDB file. In the program's directory? Bzzt! Wrong answer.

Updatable files need to be deployed to a user-writeable location. If multiple users logging onto the machine need access to the same MDB then it gets a little more complicated: your installer needs to create a folder for this and set the necessary security on it for the access your program requires. The obvious place for such a folder on the system drive (usually C:) is under CommonAppData, or else you need to prompt the installing user to supply a data drive and folder location to your installer.

For single user programs (on a PC where each user needs a private copy of the data) you'd put this folder under LocalAppData... or somewhere on a data drive and then invent your own scheme so each user has his private copy kept separate from other users.

CommonAppData and LocalAppData are not fixed locations. These vary by Windows version and how Windows was installed. This is why the OS provides a way for programs to look them up by fixed ID values.


SQL Server (even Express) is another can of worms altogether. You have to get the software deployed, worry about its software versions and whether replacing an older version by a newer one may break other pre-existing applications, deal with "instance" collisions, educate the user regarding administration issues, make sure the target machines are big enough to handle the bloat, deal with ".Net Framework Hell," etc. All in addition to the issues already described above regarding where to put things.

SQL Server Compact is a lot less trouble than Express and up, but it still has numerous gotchas of the same types (software versioning, .Net Framework requirements, minimum OS version, etc.).

But maybe somebody else can point you to a cookbook for safe deployment of Compact or even Express databases.

[victorb17]

I read that Jet is depreciated. Is that true? If so will that be a smart decision going forward?

[dilettante]

I think you read wrong.

Jet still ships as part of Windows through at least Windows 8.

Perhaps you were reading something out of context. For example the Office (Access) team at Microsoft lost control of Jet a long time ago when it was moved to the Windows team. So they took Jet 4.0 and reworked it to create a derivative ACE database engine that is part of Access (and subject to version-churn).

Jet still has better support than something like SQLite and Jet 4.0 has been stable for a very long time now.


However, feel free to work out how to safely deploy something like SQL Server Express.

Embedding SQL Server Express into Custom Applications hits a few of the high points but fails to mention many of the "version hell" and other issues I described above. Xcopy Deployment (SQL Server Express) may also be helpful.


In the end it's up to you.

[Shaggy Hiker]

There are other options. JET databases are pretty simple, as the JET engine is there and you just have to ship the .mdb file (or create it on the fly). However, there are lighter SQL Server instances, as well, such as SQL Server Express and SQL Server CE. I suspect that the former would be too much of a hassle for your situation, but the latter might not be.

One thing you can run into with Jet and .NET development is that you need to target x86 rather than AnyCPU, or it won't work on 64 bit systems. There are no 64-bit Jet drivers, so Jet runs in WoW. That's fine as long as you don't target AnyCPU, because if you do, the program will attempt to use the 64-bit drivers on 64-bit systems and not fail back to the 32-bit drivers when it can't find the 64-bit drivers. Targeting x86 will eliminate this problem.

[dilettante]

Deploying Desktop Applications describes some of the steps for SQL Server Compact. There are links to related topics at the end as well.

However most of the info at MSDN on the subject is very .Net-centered.

[szlamany]

I have not used SQL CE in about 2 years but I do recall that there were some pretty harsh limitations on silly stuff - maybe datatypes? Even the syntax was "reduced".

Nice thing about SQL CE is that it runs "in the process space" of you app - and as such is part of the app install. No other installs required...

[techgnome]

some data types are not supported char and varchar for instance - but nchar and nvarchar are supported... views are, but sprocs are not, neither are CTEs, or CURSORs...

-tg