Writing for MSDE vs. SQL Server

[Debbie]

Hello,
We have an application that is written in Access 2002. We possibly have a need to rewrite it to utilize MSDE and SQL Server. The reason is that this application has been purchased from us and it is based in Access 2002. We don't want to require those customers to purchase SQL Server. Would we have to rewrite it as two seperate applications, one for MSDE and one for SQL Server? Thank you,
Debbie

[Van T. Dinh]

No. MSDE IS a version of SQLServer2K, admittedly very much limited compared
to he full SQLServer2K.

There are a number of restrictions placed on MSDE such as the max. file size
is 2 GB (same as JET) and (I think) max. of 5 concurrent connections.

--
HTH
Van T. Dinh
MVP (Access)

"Debbie" <Deb...@discussions.microsoft.com> wrote in message
news:98B5EA6E-CAB9-4083...@microsoft.com...

[Larry Linson]

A performance delay is inserted on the 6th and following "concurrent batch
process" in MSDE 2000. "Batch processes" are internal activities and do not
represent a number of connections, and certainly not a number of users.
Although Microsoft publishes "optimized for 5 users", many report perfectly
fine performance with 25 or more users (even in previous versions where it
just wouldn't _start_ the 6th concurrent batch process).

An application developed for and tested with MSDE should run just fine if
you replace the MSDE database reference with a reference to a full SQL
Server DB. One developed for SQL Server may run afoul of some other
restrictions in MSDE... don't ask me for details, as I'm certainly not the
expert on MSDE's restrictions.

Larry Linson
Microsoft Access MVP

"Van T. Dinh" <VanThi...@discussions.microsoft.com> wrote in message
news:urbq3VA...@tk2msftngp13.phx.gbl...

[Van T. Dinh]

Thanks for the clarification, Larry.

Glad I put "(I think)" since I use the full SQL Server version.

--
HTH
Van T. Dinh
MVP (Access)

"Larry Linson" <bou...@localhost.not> wrote in message
news:%23E5l0LD...@TK2MSFTNGP11.phx.gbl...

[Debbie]

Thank you both for your replies. It seems I have some work cut out for me!
Debbie

[Tony Toews]

Debbie <Deb...@discussions.microsoft.com> wrote:

>Thank you both for your replies. It seems I have some work cut out for me!

For info on SQL Server conversions and upsizing do a search at the
Knowledge Base at support.microsoft.com using the keywords "upsizing"
to review the various white papers on upsizing Access to SQL Server as
well as to ensure you have any updates required.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[david epsom dot com dot au]

Isn't MSDE also throttled to use only one processor? With only one
processor, all tasks are queued anyway, so queuing after 5 tasks will
have negligible additional impact ????

(david)

"Larry Linson" <bou...@localhost.not> wrote in message
news:%23E5l0LD...@TK2MSFTNGP11.phx.gbl...

[Larry Linson]

"Queueing"? I said "delay", as in "delays are deliberately inserted" which
is what I was told, on good authority.

There can be multi-threading on a single processor and, yes, MSDE is
multithreaded. Is that as great as having a hunkin' multiprocessor server
with SQL Server Intergalactic editon -- no, but it "ain't all bad". I was
not aware that it was uniprocessor only, but as I said, I am no expert on
the details of MSDE.

I have a colleague who does some very complex querying using MSDE and
reports that it is "many times faster" than Jet (which is [all|mostly]
single-threaded).

Larry Linson
Microsoft Access MVP

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:Oz%23b9ecU...@TK2MSFTNGP12.phx.gbl...

[david epsom dot com dot au]

http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_0ciq.asp

I thought MSDE was queuing after 5 connections, which is what,
on good authority, I read, but that MS page agrees with you,
not me. (And note, that page says "8")

I wonder if the MS Data Engine (SS7) was throttled differently
than MS Desktop Engine (SS2000)? I guess that's what you meant
by 'even in previous versions'?

The registry setting for Jet threads is
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0]
"Threads" =dword:00000003
For an example of one of the problems of using a multi-threaded
database engine, see
http://support.microsoft.com/default.aspx?scid=kb;EN-US;147629
This has had specific implications for ODBC connections used
by IIS.

In situations where Jet might spawn new threads within a context
that can not handle thread creation, it might be better to set
the Threads value to 1 instead of 3. In other situations with a
large number of asynchronous operations, it might be better to
increase the number of threads. Note that the jet engine must
be stopped (unloaded) and restarted to change the number of
threads that it can use.

(david)

"Larry Linson" <bou...@localhost.not> wrote in message

news:uy8UAEoU...@TK2MSFTNGP09.phx.gbl...

[Larry Linson]

Actually, the first version of MSDE did not insert delays, it simply would
not start another "internal batch process" after 5, until one had finished.
I guess that would qualify as "queueing". Even so, Roger Jennings, in his
"Special Edition - Using Access 2000" book reported a test with 25 users
working nicely.

I was not aware of the increase, but it is welcome news. It could, indeed,
be related to the change from "MSDE" to "Microsoft SQL Server Desktop
Edition", but I don't know that it is.

Larry Linson
Microsoft Access MVP

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message

news:O7T0O0rU...@tk2msftngp13.phx.gbl...

[david epsom dot com dot au]

I dunno. It LOOKS to me like the previous system of throttling
(limit of processes) would be ineffective in most circumstances,
but easy to justify, since to effectively run more processes
you would need a lot more computer, and the limit would permit
the developers to make code and memory optimisations appropriate
for small systems on small computers, potentially making MSDE
more competitive with Jet. On the other hand, this delay system
just throws away processor time on all connections when the limit
is reached -- which looks like a fairly effective way of enforcing
a licensing restriction.

(david)

"Larry Linson" <bou...@localhost.not> wrote in message

news:eVPKwyyU...@TK2MSFTNGP09.phx.gbl...

[Larry Linson]

Both of them are artificial limits to restrict the number of users of the
free, as is the 2GB database size limit, in my view. But, also in my view,
Microsoft has every right to do that -- they don't have an obligation to
give away a freebie alternative DB (although it seems to me a good move on
their part to give away the restricted version, and let people pay when
their user audience outgrows it).

There is a "Developer Edition of SQL Server" priced very reasonably, with
the same limitations, but including the very useful adminstrative tools of
SQL Server which MSDE / Desktop Edition do not include. But, its license
terms limit it to development and testing, not production use. However, many
of the tools' functions are most useful in design and development.

Larry Linson
Microsoft Access MVP

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message

news:%23z%230$K1UEH...@TK2MSFTNGP09.phx.gbl...

[david epsom dot com dot au]

There was also a rumour that MSDE was not licensed for use on the
internet ??? have you seen that licensing restriction ??? (i couldn't
find such a restriction when I looked) Also, as I understand it,
report services is not licensed for use with msde, which I reckon
was an 'interesting' decision.

(david)

"Larry Linson" <bou...@localhost.not> wrote in message

news:uzlum%23BVEH...@TK2MSFTNGP10.phx.gbl...