A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Might be outgrowing Access but daunted by SQL Server



 
 
Thread Tools Display Modes
  #81  
Old March 18th, 2010, 03:37 AM posted to microsoft.public.access
david
external usenet poster
 
Posts: 34
Default Might be outgrowing Access but daunted by SQL Server


"Banana" Banana@Republic wrote in message
news:4B8B14B3.7010008@Republic...
david wrote:
Banana,

Access/Jet is built on top of the Windows Database primatives.


But what exactly is a 'Windows Database primitives'? I've googled this
phrase and didn't find anything useful. I'd like to know what you mean by
that phrase.


Well, since you ask...

It's like Windows explorer: you never stop to think how it works
any more. But once upon a time, Word Processing was done on
Typewriters, Layout was done on TypeSetters, and databases
were the main reason why commercial computer systems existed.
Even Unix, which in its academic version had no database system,
had built-in database primitives in the version most used inside Bell.

People bought DOS computers and used them for database
systems: the OS primitives which supported this are the
Windows Database Primitives.


DOS had only the most basic database service: read/write/flush
record, and file folders, which were then called directories. The
file folder is the 'Database' unit of DOS-based database systems.

The next important step was the addition of SHARE.EXE,
which added record locking to the file system. Windows 3.x
added a File API which was a thin cover for the DOS file
system, including GET,SET,LOCK, UNLOCK and file
folders.

The next important step was the addition of the Network
Redirector, which was the part of the Network Client which
re-directed File System commands to a Network Server.

The API has changed from Win16 to Win32 to WinNT,
but it has remained backward-compatible to DOS through
the whole change. The data storage system has changed
underneath, but the API has remained backward-compatible.

The Database Primitives than WinXP offers and that are
re-directed to the Server, are still the same database primitives
that DOS offered with SHARE. Create Record. Read Record,
Update Record. Delete Record. Lock Record. Unlock Record.
Missing is FLUSH (required for persistence), indexing, and
transactions. Novell Netware had the missing three: transactions
have finally been added to Windows, but are not yet used by
JET or ACE.

'CRUD' is the basis of all database systems, not just Relational
Database Systems. Create Record, Read-Record, Write-Record,
Lock-Record, Flush, and File Folders were the basis of all DOS
database systems, the systems which were replaced by Access
and SQL Server.

Access was built on top of the OS database primitives, as
were all previous DOS database system. It differed by
including everything inside one file (as SQL Server does).
The code was stored in records in the database. The
queries were stored in records in the database. The
relationships (that is, the table definitions) were stored in
records in the database. And the only way to manipulate
this data was by using the database system. In other words,
it was a very good (for the time and market) implementation
of a Relational Database System.

But the database facilities offered by Access far exceeded
those offered by the OS. Access was not a DOS database
system. It was a system built inside a DOS database
system.

Since that time, the OS has expanded a lot in some
directions, not at all in others.

In particular, DOS and Windows 3.x had no user-level
security. Windows now does have user level security.
Windows Servers now know not only which folders
are shared, but who is allowed access to them.
Windows servers now know not only which records are
locked, but which user and computer has locked them.

If Windows had had user-level security when Access
was first written, Access might have used it. Instead,
Access created its own system of keeping track of which
users and groups had permissions, and which users and
workstation locked records in the database. It did this on
the one hand by using a database of users and groups, and
on the other by using a database of logged-in users and
record locks, which it implements by using OS-level record
locks to lock the Access-level lock records

There used to be some old white papers around which
explained that clearly and with diagrams.

Note, Record locks are not File locks, are not Page locks, are not Sector
locks, are not Cluster locks, because OS-level data records are not sectors,
clusters, files, pages, tables, Access-level records or anything other than
what they a An old OS-level system for data records.

This is all totally transparent to the user, as you would expect.
One of the major aims of computer program development over
the last 40 years has been to isolate function into separate levels
and into separate islands, so that, for example, at the SQL level
you, and your software, shouldn't have to know if you are using
in-process or out-of-process, load-and-go or service, local or
remote, OS or Application. The discussion here has shown just
how successful that effort has been at the user-level.


Anyway, there was a time when creating a new database from
the command line was just as challenging to the average user as
creating a new database from the command line would be to the
average user now. When you would hide your server from the
client tools because a user trying to restore (using RECOVER)
an improperly deleted table (using DEL) would accidently destroy
your whole database. When connect strings like "z:\SALES"
were a foreign language.

We are kind of at that stage with SQL Server now. People
aren't familiar with the browser, or with the commands, or
with the services or with the user interface. They just aren't
familiar. They don't see that underneath it's the same: they
only see the superficial differences.

And , unless you understand what is the same, you aren't
actually going to understand how the differences play out.

That's not the embarrassing ignorance about things like 'ports'
and 'services'.

It's the embarrassing ignorance of 'Access brings the whole
file down',

and it's the more advanced stuff like how SQL Server
can work over a Cisco VPN using Windows security,

and it's the ordinary stuff like understanding that you have
to work with smaller recordsets when using SQL Server.


So the lesson is:

SQL Server is more familiar than you think it is.
It's going to be the superficial differences you notice most.

(david)


  #82  
Old March 19th, 2010, 08:15 PM posted to microsoft.public.access
James A. Fortune
external usenet poster
 
Posts: 903
Default Might be outgrowing Access but daunted by SQL Server

On Mar 17, 7:39*pm, "david" wrote:
It's an interesting concept, to be sure, but if I haven't
misunderstood david's point, it doesn't really serve :-)
to limn the difference


The idea that you can adequately describe a concept
by definining it's differences from a familiar concept is
a beginners mistake in teaching and training.

More fundamentally, the idea that you can teach a
concept by replicating the way an experienced person,
who understands the concepts, thinks about the concepts,
is a beginners mistake in teaching and training.

So, I was never trying to explain the differences between
SQL Server and the service derived from SHARE.EXE.

I was trying to explain what they had in common, for a
person who had described himself as 'daunted by SQL
Server'

The rest of it was trying to help people who were putting up
misleading information about 'opening ports' and 'services'
and stuff like that. Those mistakes always lead to
misunderstandings further down the line.

(david)


I'm not trying to cause you grief. It seemed like you were merging
two separate concepts. I'll take your word for it that you "dumbed
down" your explanation to point out similarities. The only refereed
publication I ever wrote dealt with the issue of learning based on
existing knowledge. It was for the Systems, Man, and Cybernetics IEEE
Journal. The theme was that learning by analogy is a byproduct of
optimization rather than a means to an end. I also gave credit to the
person who pointed that fact out to me. Naturally, the talk went into
more detail than the paper.

James A. Fortune


I'd like a computer to have a model of everything I know, then read
books and just show me the parts I don't already know. -- JK

  #83  
Old April 7th, 2010, 10:09 PM posted to microsoft.public.access
a a r o n . k e m p f @ g m a i l . c o m
external usenet poster
 
Posts: 1,108
Default Might be outgrowing Access but daunted by SQL Server

WTS is for fags

it costs MORE than SQL Server






On Feb 28, 5:47*pm, "David W. Fenton"
wrote:
"Albert D. Kallal" wrote :

"David W. Fenton" wrote in message
There really is no comparison in regard to the cost and
difficulty level.


Cost for me using sql server is less.


Less than for someone who hasn't topped the SQL Server learning
curve, yes, but certainly not less than hosting the app on WTS. Even
though you've got good SQL Server chops, it still takes more time to
port the app to SQL Server and re-design it to be efficient enough
to run across a WAN. That's not a trivial amount of time even for
the experienced developer like you who has done it before and knows
how to run SQL Server and how to interact with it efficiently in
Access. And then there's all the potential for bugs and the like
that come from rewriting code and redesigning forms/reports, and it
seems that WTS wins hands down.

It's not even close, even for the person like you with all the
necessary experience/knowledge to go either way.

Of course, this is all assuming an existing Access application. If
it's a new app, and the user population, security, reliability and
other requirements are suited to Jet/ACE for the back end, I think
Access/Jet/ACE + WTS wins for new development, too (as compared to
architecting the Access app to run across a WAN with SQL Server).
But there, at least, it's a closer issue.

I have many successful applications
running over the Internet using SQL server. However, I done the
learning curve. We don't even used stored procedures.


Certain kind of operations are going to need it, I think.

We use some pass-through,


How do parameters work with pass-throughs? That's the reason I've
used sprocs in the past, because I needed to pass parameters that
weren't available via WHERE clauses.

and
mostly link to views for joins on reports or pick lists. I can
zero in and get a access application up and running for SQL server
in a WAN environment in VERY little time now. I am also using a
hosted option for sql server. And, sql Azure can be had for $10 a
month for a 1 gig database. I believe the trials are on now, and I
can't wait to try Azure.


However, WTS vs SQL server in BOTH cases, a server + VPN must be
setup.


I have argued this for years, but some people (like Lyle) have
repeatedly argued that it's safe to expose your SQL Server to the
public Internet. Others have exposed their RDP port to the open
Internet, which is even more inadvisable (since if breached, would
open the WTS to a much wider spectrum of potential risks than is the
case with a breached SQL Server).

WTS really is far less learning overall.


Learning? There's learning involved? I guess there is from the
standpoint of the person who sets it up and administers it, but for
an experienced Windows sysadmin, that's a remarkably trivial amount
to learn.

And, one does NOT spend money
modifying an already good working access application.


...so you avoid introducing bugs or changed behaviors. The amount of
testing needed for hosting an existing Access app on WTS is really
small in comparison to any other options, seems to me.

So, the larger and more complex the access application, the the
more work needed for SQL server. This thus again favors he WTS
choice.


Exactly. I just don't see any scenario where if WTS is an option
that SQL Server over a WAN is better. Even where WTS is not an
option, I'd expect SQL Server to also not be an option, so that WTS
wins again.

Can you tell I'm a big WTS fan?

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/


  #84  
Old April 7th, 2010, 10:12 PM posted to microsoft.public.access
a a r o n . k e m p f @ g m a i l . c o m
external usenet poster
 
Posts: 1,108
Default Might be outgrowing Access but daunted by SQL Server

wow.. is _TEST_ a valid licensing use of something designed for
_ADMINISTRATIVE_ purposes?

sounds to me like David Fenton doesn't understand the licensing
implications of breaking licensing agreements??



On Mar 3, 10:14*pm, "David W. Fenton"
wrote:
=?Utf-8?B?Sm9uMjI=?= wrote :

My thought process being, I can install SQL Server on my own
workstation for free (Express with SQL Server Management Studio),


That's not going to tell you anything about what performance will be
like over the Internet.

but to do the TS option
properly, we'd need to purchase a dedicated server with
(unfamiliar) server software plus CALs.


Do you have a Windows server? If so, you can test WTS with one of
the administrative logons.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
usenet at dfenton dot com * *http://www.dfenton.com/DFA/


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.