Fog Creek Software
Discussion Board




Jet vs SQL Server for small apps

I've been using both the Jet (MS Access) database engine and MS SQL Server as database back ends for quite a while.  While SQL Server has certain advanages for enterprise level apps, I believe Jet has its advantages for small database apps.  I don't understand the technical reason that Microsoft seems to be encouraging developers to abandoning Jet and move EVERYTHING into SQL Server.

The reason I think Jet databases are ideal for small personal data applications is that the entire database is packaged in a single file that you can copy onto a floppy, send to someone else by email, etc.  If you know how to copy a file, you know how to backup the database.  I believe Joel's City Desk uses Jet, and I also believe MS Money uses Jet, to name a few.

How can a developer use SQL Server to create a small database application that stores all of its data in a single file that can be stored in "My Documents", copied using Windows Explorer, and treated like any other document associated with an application?  Would MS Money, for example, be just as user friendly if it switched to SQL Server?  How?

Troy Wolbrink
Saturday, August 31, 2002

Troy, I think they are doing it and going to MSDE because that has an easier scale up to real SQL Server.  I am sure there are numerous small apps built in Access (Jet) that later become too big and it is a pain to upscale them. 

Correct me if I am wrong, but it will not be much more difficult to deploy the .adp on a machine with MSDE will it?  Here is a link
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/acaccessprojects.htm

ryan ware
Saturday, August 31, 2002

I forgot to mention deployment.  I know MSDE is available as a free (limited) version of SQL Server, so I'm not talking money.  But if I wanted to create an install program that does it all here are my options:

JET
1)  Install MDAC if needed (silently)
2)  Install Jet 4.0 SP3 if needed (silently)
3)  Install my program... done.

MSDE
1)  Determine if SQL Server is already installed.
      - If SQL Server is installed, can you just use it?
      - If not installed, can MSDE be installed silently?
2)  Install MDAC?
3)  Install program.

Troy Wolbrink
Saturday, August 31, 2002

Another way to look at it is a reduced time to market for improvements and a reduced overhead on development and support with only one product to cover instead of two.

If I have 2 database technologies, both have 20 dev and 20 support staff. Now if i decide one of those technologies has reached the end of it's life i can merge those teams and drop one product i have doubled the support and dev teams on each - or i've increased the teams by 10 and taken the chance to fire some dead wood, and in theory at least with more developers and more support staff the products should improve and or get released faster and the support should improve - even if only in terms of more operators means less waiting time for customers.

Which is not to say i disagree with you about Jet still being useful because it most certainly is.

Robert Moir
Saturday, August 31, 2002

Ryan, as for scaling up databases, don't forget Exchange and Active Directory are both JET, and those can scale pretty damn well.

Robert Moir
Saturday, August 31, 2002

Ryan, your link doesn't really cover much, other than that you can create *.ADP projects in Access 2000.  I have no intention of ever deploying a *.ADP project/application.  When I say "application", think more in terms of a real Windows client like MS Money, and not some Access database project with forms.

In other words, leave *Access* out of this.  I don't plan on using it.  I only plan on writing a real windows application that talks to either a *Jet* or *SQL Server* backend.

Troy Wolbrink
Saturday, August 31, 2002

Just to clarify regarding the supposedly free licensing of MSDE:

You get a license for MSDE when you buy a copy of Access.  It is free, but it cannot be freely redistributed.

You can get a license to freely redistribute MSDE when you buy a copy of MS Office Developer's Edition.

But if you don't have a license for a MS Office Developer or you're installing onto a machine that doesn't have a license for MS Access, then you're violating the license provisions for MSDE.

I think a lot of people believe MSDE is free for the taking by anybody, but I think this belief is wrong.  Read the MSDE EULA.  Whether Microsoft cares is anybody's guess, since I don't know of any easy way to purchase licenses of MSDE if you don't have Access or don't have MS Office Developer.

Herbert Sitz
Saturday, August 31, 2002

If you design your classes properly and structure your DB interface in a logical way, upscaling from MSACCESS to SQL Server (or practically anything else) is a simple matter.

I recently converted a very well written and designed VB/ACCESS application to SQL Server, it was a trivial conversion, replace some SQL with calls to stored procedures and everything else hung together perfectly.

Alberto
Saturday, August 31, 2002

Doesn't really matter what we prefer. Read the list of deprecated components in the latest (2.7) MDAC drop. Jet is officially dead now, even though I'm sure Jet 4.0 will continue to ship for a long time. But don't expect it to work well with coming operating systems and applications.

Mike Gunderloy
Sunday, September 01, 2002

The MDSE is clearly distribute-able with you applications, and it is clearly royalty free. This is NOT the same as the single license developer version of sql. (that is NOT distribute-able, and it is also NOT the MDSE). There should be no confusion here.

I think the only thing missing right now is the free download site (more on that in a second!)

If you purchase any of the major development suites from MS, then you are encouraged to distribute and use the MDSE engine.

These tools would include Visual Studio, Or even just the developer versions of Office, VB, FoxPro, and probably a few others I don't list here.

Hence, the engine can be distributed free with your application. Not only that, but the package and deployment options for the *all* of above developer editions allowed the MDSE to be included as a AUTOMATIC install. In other words, the end user did, and does not even have to know you are using this server engine. If some other applications had already installed the engine...that also was handled.

The follwing has a quick blurb on the Royalty issue Check out:

http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

However, it looks like now that before this engine ever caught on, they are now discontinuing the use of it. The below link (only a few months old) states that the MDSE is no longer available.

http://msdn.microsoft.com/vstudio/downloads/addins/msde/default.asp

The above is of course is in the “downloads” section of visual studio. Hence, MS now states that the MDSE is NO longer available. Hence, you can still use it, but MS is not exactly throwing it out the door to get you hooked.  This basically means that they will still provide a single user developer version of sql-server (that is for developers to play and develop with sql server), but they are NOT encouraging the use of the MDSE, which is something that is designed to be distributed to end users for free.

While I make think that this is a mistake, it means for a lightweight and low cost database server product, I have to go else where now.

Regardless, the MDSE was NOT tied to ms-office. The idea here was to make a replacement for JET, and also allow people to take advantage of features and benefit that a true database server gives over a file based product like JET. There seems to be a about face here.

However, since the cost of databases are rapidly dropping (due to open source engines), then perhaps the sql-server will become so cheap as to not make much difference. It already has dropped below the $150 per user, and still dropping.

There is not going to be any money in CAL’s soon anyway (I am talking about *just* database CAL’s here. Unless they throw in all kinds of way cool high end data analysis tools, the cost of a basic data engine connection will drop even further).

In other words, it will be the other features that cost, and the per-user licensee values will continue their free fall. Given that licenses are rapidly getting cheaper and cheaper, then perhaps we don’t even need to bother the free MDSE anyway. I think even MS sees this (and thus it is no need on their part to continue with the MDSE).

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Sunday, September 01, 2002

Albert,

MSDE is not at all discontinued! I think you didn't read the page you linked to correctly.

The link you refer to only says that the old version of MSDE (that was a stripped down version of SQL Server 7 and included with Visual Studio 6) is no longer downloadable.

Instead, you can - and should - use the new version: MSDE 2000 - that is a stripped down version of SQL Server 2000! This version is included in Visual Studio.NET.

Janne
Sunday, September 01, 2002

Thanks..

MSDE is alive and well.

Check out:

http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp

Ok, now that we figure out all is well, we can now continue the debate!!!

Albert D. Kallal
Sunday, September 01, 2002

Albert -- You said:

"The MDSE is clearly distribute-able with your applications, and it is clearly royalty free. This is NOT the same as the single license developer version of sql. (that is NOT distribute-able, and it is also NOT the MDSE). There should be no confusion here."

Well, I think you're adding some confusion.  You imply that anyone can develop apps using MSDE and freely redistribute MSDE.

But in the page at the very link you reference, it says:

"Product Licensing

Licensing for MSDE 2000 is handled differently according to the application it accompanies.

Office XP Professional and Visio 2000. A single user license of MSDE 2000 is granted to licensed users of Office XP and Visio 2000.

Office XP Developer and MSDN Universal Subscription. Developers who build applications on MSDE 2000 have the right to freely redistribute MSDE 2000 as stated in the EULA that accompanies Office XP Developer and MSDN Universal Subscription.

SQL Server. For each MSDE 2000 deployment that connects to or accesses data resources of a SQL Server 6.5, SQL Server 7.0, or SQL Server 2000 database, appropriate licensing must be acquired, either by obtaining a SQL Server CAL for each MSDE 2000 device, or by obtaining SQL Server Processor licensing for the back-end SQL Server the MSDE 2000 devices attach to. "

The way I read that, you have a royalty free runtime license to MSDE only if you have a license of Office XP Developer or a MSDN Universal Subscription.  So the circumstances in which you are free to distribute MSDE with your applications is clearly limited: only if you've got Office XP Developer or the Universal Subscription.

And owners of Office XP Professional only have a license to use it on a single computer with a single user.    So, technically, you can't even program for a client and use their license to enable a multiuser app.

Herbert Sitz
Sunday, September 01, 2002

There *are* a couple of additional aspects to Acces vs. SQL Server that should be mentioned.  I just spent a major weekend geek-out making sure I've got all of these under control as I am looking to distribute an MSDE app soon.

1) MSDE distribution is much more difficult than Access distribution.  Since the Jet engine is so widely distributed, you can often just copy the Access data file on to the end-users machine along with your app and know it will work.  With the MSDE, there are many more installation and configuration issues to deal with.

2) In my app I have to use SQL DMO to ensure that the MSDE / SQL Server is running before the rest of the system starts up.  This isn't an issue for Access since it is a file based database.

3) You don't just open up your MSDE database - you have to attach it to the server.

4) Security is a much larger issue in SQL Server.  You must create (or use) a login and a user account (the login's permissions within your database).  Also, you must ensure that your app has appropriate security going in.

5) You cannot access MSDE as a "server" when it is running on 95/98 so pure 95/98 networks cannot be used when you have multiple users needing access to the data.

All this being said, I think that the MSDE is a good solution for almost any app that will often see multi-user use. 

1) T-SQL stored procedures are extremely useful for building applications.

2) You avoid having multiple network users vying for a file based db thus cutting down on corruptions and improving speed in a network setting.  After dealing with Paradox ("index out of date") for 10 years, I found this to be crucial.

3) Your app can scale from standalone all the way up to near enterprise if built appropriately.

4) There are a variety of very effective tools for viewing and changing the SQL Server/MSDE environment available from MS.

What are my lists missing? 

Mark Brittingham

MDBritt
Sunday, September 01, 2002

Albert said, "... the package and deployment options ... allowed the MDSE to be included as a AUTOMATIC install. In other words, the end user did, and does not even have to know you are using this server engine. If some other applications had already installed the engine...that also was handled.".

I like the sound of that!  Do you have any links to documentation to back this up?  If this were true, this would take care of the deployment issue.

But my nagging question is how to use SQL Server (MSDE) and yet make your app "feel" file based.  In other words, what would be involved in having a database file that you can double click on, have your app open it, attach it to the server, and when done detach it from the server, consolidate it into one file so that the user can copy the file to another location?  (Maybe I just answered my own question.)

Troy Wolbrink
Sunday, September 01, 2002

"But my nagging question is how to use SQL Server (MSDE) and yet make your app "feel" file based. In other words, what would be involved in having a database file that you can double click on, have your app open it, attach it to the server, and when done detach it from the server, consolidate it into one file so that the user can copy the file to another location? (Maybe I just answered my own question.) "

If you want something to feel file based, don't implement it on a SQL server. I think it's that simple.

Robert Moir
Sunday, September 01, 2002

>>5) You cannot access MSDE as a "server" when it is running on 95/98 so pure 95/98 networks cannot be used when you have multiple users needing access to the data.


Actually, you can run the MSDE engine as a server on a win9x box. The only real issue that comes up is that the MSDE allows you to either use the windows security model, or ignore it. (the win9x boxes don’t have the same windows security as NT/win2k..hence you have to ignore it). I have a office 2000 cd here, and the MSDE engine is on the disk. I just popped it in, and It works just fine on a win98 box, and I can connect to the MSDE database via any box on my home network (that is a win9X network- win9x clients --- thus the comments that you can’t use it as a server on a win9x is WRONG). For some reason I had to use the ip address, and could not use the workstation name. My guess is that some wins resolution problem here, but regardless the clients on a win9x network can use the MSDE. It took me less than 1/2 hour to test this. In fact on my test computer, I used access97, and connected to the MSDE via odbc. It worked just fine (as mentioned, I could not use a named server, but using IP address worked).

However, this debate is most interesting. Should one consider a small server? There are several issues here:

Installing. (there was a question in this thread about using the MSDE for custom applications, and installing). A article with more details on use the MDSE as a embeded database can be found at:

http://www.microsoft.com/sql/techinfo/development/2000/embeddingmsde.asp

Products like the new Visio have dumped JET, and actually now use MSDE for data storage (in fact, Visio never used JET). Hence, it seems that these products will, and do install the MSDE engine. Joe’s many articles on eating your own dog food apparently does apply to MS, and they are now using MSDE in place of JET when possible. In other words, for applications that require some kind of database are now using MSDE in place of JET.

However, the real problem lies in the fact that by the time a application gets to the point of needing a server, then one just upgrades right on to sql-server and skips the MDSE.

JET is certainly fine up to 50 users, and credible developers have pushed JET well beyond 100 users when all factors are just correct. Of course, if you go over to the SQL newsgroup you will see all kinds of post weekly that their small JET applications run slower after upgrading to sql server. This just means that a poor design will not be saved by sql server.

Hence, in the typical small business environment of 1-15 users, JET is thus fine. In fact, it is really good, and really cheap. This makes the product compete with sql-server in the low end market.

So, for a general desktop application in the low seat range (say 1-15), should one use a server?

CityDesk for example has a multi-user version. It of course relies on JET. Could/Should the CityDesk multi-user version use the MSDE?. The #1 problem here is of course ease of use, and memory foot print.

The MSDE can be used as a completely non administered database, but it still is a software system that has to be installed, and setup (even if done automatically...which it can be).  As I mentioned, the problem here is that the point at which it becomes real useful to use the MSDE, it is already time to use a true server.

Hence, there is this kind of “Grey” area in which we flirt with the idea of using a server. In this Grey area the amount of extra work to use a server based engine tends to negate the additional benefits we get.

As the MSDE gets better, and easier to use, and Moore’s law continues, then it becomes more and more compelling to use a server. In my hunt yesterday, I had few people email me, and mention that the MSDE 2000 is MUCH better then the old one. This makes sense, since the original was the first time around! At least my concerns about the removal of the MSDE were quashed!

Of course the other issue here is that software is changing.

Ten years ago we could walk into a client and develop a mailing list, and that was a big deal. Today, that same application has to talk to the accounting package, and often data must be exposed to the web. In these cases, then the use of a server even for a VERY SMALL business makes sense. This is because data on a server as opposed to being buried in a accounting package allows MUCH more integration.

Hence, it is not necessary one of performance, or the number of users, but one of sharing and integrating data.

Today, virtually every application I write has to talk to some other database system in the company. In this regards, a server is the only game in town. We must continue this process of moving data out of a application, and into a general based server product. The web is certainly driving this, but so is the fact that customers are much more demanding then ten years ago when they wanted a simple mailing list.

Hence can we start using a server based product for very very low seats numbers? The answer is yes, we will HAVE to, and the deciding factor will be this issue of integration. In another 10 years, it will un-thinkable for any client to store data in anything but a server. This means even commercial products like Quicken, or Simply accounting will have open up their file formats. They will accomplish this by adopting a database server format. Hence, it will be *assumed* that the customer has a database engine, and all applications will have to work with those engines. Hence, you buy WinFax, or Quicken...they will store their data on a server. XML is a debate for another day..but this changes nothing.

This is also why MS is even talking about wrapping the whole OS around a database. It simply is a issue of sharing data.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Sunday, September 01, 2002

You can download MSDE from here:

http://www.asp.net/webmatrix/download.aspx?tabindex=4

It's an MS site.

Szasz Attila
Monday, September 02, 2002

"JET is certainly fine up to 50 users, and credible developers have pushed JET well beyond 100 users when all factors are just correct. "

Albert, what about Exchange and Active Directory? They are both using JET (or a superset of it) and they both handle well beyond 100 users with ease even in less than optimal conditions.

Robert Moir
Monday, September 02, 2002

I've pushed JET upto 250 users without any problems. But it tends to be slower at opening and closing the database.

Matthew Lock
Monday, September 02, 2002

I realy realy like MSDE. I do believe it is the best free DBMS engine and have used it myself on some projects, but ...

More and more desktop products now seem to use it. This is all very wel, but what leaves me unconfortable is that the support of the engine (which is basically a tuned down SQL 2K) is not included in Windows Update. MS considers SQL Server a business product, and as such it is NOT included in Windows Update.

People rolling MSDE into their application should be aware of this. This means that you should keep in mind that if your app is also deployed in a "home" type environment, YOU will have to provide the nescessary guidance and support for hot-fixes and service packs etc., since your home-type users can not be counted on to keep up-to-date with TechNet.
For the same reason I also consider a "stealth" inclusion of MSDE in bad taste.

This is pure speculation on my part but I think (and hope) that MSDE functionality will become part of the OS (and as such supported through Windows Update) in the future.

Just me (Sir to you)
Monday, September 02, 2002

...If you want something to feel file based, don't implement it on a SQL server...
...YOU will have to provide the nescessary guidance and support...
...your home-type users can not be counted on to keep up-to-date with TechNet...
...I also consider a "stealth" inclusion of MSDE in bad taste...

I know that installing and maintaining SQL Server is well outside the scope of most general computer users.  So, is it the concensus so far that building an app for MSDE/SQL Server can never feel file based.  Could MS Money, for example, use MSDE and still retain something like the single file *.mny document?

...Products like the new Visio have dumped JET, and actually now use MSDE for data storage...

What about Visio.  I don't use it, so please excuse my ignorance.  Is Visio file based?  Does it have a document model (ie. a single data file typically in "My Documents" that you can double-click on to open it in Visio like a Word doc opens in Word)?

Troy Wolbrink
Monday, September 02, 2002

"Could MS Money, for example, use MSDE and still retain something like the single file *.mny document?"

AFAIK not in the sense that copying that one file to another machine with MS Money installed would have moved the actual project. It would need an "import/export" type functionality.

Just me (Sir to you)
Monday, September 02, 2002

Yes visio does indeed have a single document you can double click on, but it doesn't use SQL for storing this data, IIRC it uses it for specialised stuff to do with discovery of a Network/AD

Robert Moir
Monday, September 02, 2002

Robert is correct here.

Visio (for those that don't know, and the person who asked) is simply a very cool drawing tool from MS. It is really is the new addition to the office suite of products.

It lets you draw things like flow charts. It is great for any kind diagram, or “instructions” that you must make for users. It is great for all kinds of general business communication.  Here is a diagram I made with Visio (I use it a lot).

http://www.attcanada.net/%7ekallal.msn/Articles/PickSql/Appendex1.html

Since Visio is now part of the office suite, it also includes VBA. Thus, there has a been a lot of cool extensions.

At any rate, the corporate edition has all kinds of neat goodies, such as a tool to automatically map/draw your whole network (it builds a list of computers on the network, and draws it for you...it can also inventory stuff on each computer). It also has a bunch of things for database ER diagrams. (it has a bunch of reverse, and forward engineering tools...you can read a Sybase database, and reverse it into SQL-serer). Thus, for many things, Visio needs a place to “store” all this data stuff.

Since MS is not using jet anymore, then Visio used the MDSE engine for all its data storage. It pretty well functions like any other windows application despite this fact (it is all manged for you).

Even Joel has mentioned Visio. The folks at Visio made the product work and feel EXACLITY like one of the office programs. It means that any person using the program could learn use it very quickly. MS liked it so much, that they bought the company. Hence, Joel mentioned that a way to make a lot of money is to make something for the office suite, and get MS to buy it!

The home page for Visio is:

http://www.microsoft.com/office/visio/default.asp

Take one of the quick tours if you want more as to what it does.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, September 02, 2002

Visio is an example of windows app done better than what MS does it - not everybody likes the course MS is taking it now  though

The VBA scripting was there way before MS acquired them.

Karel
Tuesday, September 03, 2002

"Visio is an example of windows app done better than what MS does it "

As a long term Visio user I must strongly object. I have always liked the Visio approach, and their enthusiastic adoption of MS technologies (especially automation) and UI conventions was always superb.

The sour point with Visio has always been quality. The bugs were incredibly numerous. In fact, I know of no other application in its class that was so buggy in critical areas. It always left me with the "it would be so nice if only it worked" feeling. The acquisition by MS gave many of us hope that integration into the MS QA chain would finally get things rolling. Indeed, Visio 2002 is by far the best quality release the product has known.

Just me (Sir to you)
Tuesday, September 03, 2002

"Albert, what about Exchange and Active Directory? They are both using JET (or a superset of it) and they both handle well beyond 100 users with ease even in less than optimal conditions. "

Robert, these products aren't using the exact same JET engine that we are able to redistribute.  But even if they were, from the perspective of the database, these are single-user applications.  All requests are made through a single controlling program, which is the only app that has the database open.  The Exchange server may have hundreds of clients, but the Exchange database has only one.

Curt Hagenlocher
Tuesday, September 03, 2002

Two very valid points, Curt, but it does prove that JET can be made to scale at least in terms of size and complexity of the DB.

I wasn't suggesting that Exchange and AD compared directly to the sort of JET you get with Access, at least not on purpose. Rather, to show the underlying technology concept behind JET could go up to some interesting levels.

Robert Moir
Tuesday, September 03, 2002

Regarding the "making your app feel file-based" question. Check out the VBScript below - shows how you can pass a database name and MDF file name (which doesn't need the *.MDF extension of course) and SQL/MSDE will dynamically attach it if if it hasn't been already. You can imaging you could associate an extension with you app - and the 1st thing you app does if do a connection.Open() with something like the connection string below to get it ready to roll in SQL/MSDE

DynamicAttach.VBS
--------------------------------
Dim conn

set conn = CreateObject("ADODB.Connection")
conn.Open("Provider=SQLOLEDB.1;Data Source=(local);Integrated Security=SSPI;Initial Catalog=TempPubs;Initial File Name=c:\Pubs.mdf")

Dim rs
set rs = conn.Execute("select * from titles")

Dim msg : msg = ""

Do Until rs.EOF
  msg = msg & rs("title") & vbCrLf 
  rs.MoveNext
Loop

conn.Close

MsgBox msg

Duncan Smart
Tuesday, September 03, 2002

>> Initial File Name=c:\Pubs.mdf

Hi Duncan,

This is what I'm looking for.  Thanks!  What has been your experience with this?  Does this permanently attach the database file even after the app closes?  Using this technique, what is needed so that when you close down the app, the *.ldf file is removed and the *.mdf is detatched?

Troy Wolbrink
Tuesday, September 03, 2002

Troy,

Had a look at the docs (SQL Server Book Online, topic: "Setting Database Options") - interesting bit:

"[Database option] AUTO_CLOSE: When set to ON, the database is closed and shut down cleanly when the last user of the database exits and all processes in the database complete, thereby freeing any resources. By default, this option is set to ON for all databases when using Microsoft® SQL Server™ 2000 Desktop Engine, and OFF for all other editions, regardless of operating system. The database reopens automatically when a user tries to use the database again. ***The AUTO_CLOSE option is useful for desktop databases because it allows database files to be managed as normal files. They can be moved, copied to make backups, or even e-mailed to other users.*** The AUTO_CLOSE option should not be used for databases accessed by an application that repeatedly makes and breaks connections to SQL Server. The overhead of closing and reopening the database between each connection will impair performance."

So if you’re using MSDE you can simply do the following when your app shuts down (obviously this will only work if your app shuts down cleanly):

conn.Execute "USE master"
conn.Execute "EXEC sp_detach_db TempPubs"
conn.Close

Note that "Auto Close" is ON for databases automatically with MSDE, but off for standard SQL Server. If you’re using full SQL Server (say user already had standard edition installed) then you would need to do the following straight after the connection is opened:

conn.Execute("EXEC sp_dboption MyPubs, autoclose, true")

Dunc
Thursday, September 05, 2002

Thanks for the info.  Exactly what I was looking for!

Troy Wolbrink
Saturday, September 14, 2002

*  Recent Topics

*  Fog Creek Home