Might be outgrowing Access but daunted by SQL Server

[Tony Toews [MVP]]

I'm close to completing the consolidation of various small Access databases
and a couple of Excel spreadsheets that my little company uses (5 staff) into
an all encompasing Access database and I was planning on splitting the
database when I was finished to allow simaltaneous use of it by staff on our
small office network.

Excellent.

You've got some good replies however be advised that Aaron Kempf is rather
monomaniacal on the topic of ADPs and SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Armen Stein]

With a SQL Server, you have to have a port for client PCs to connect
to but the big question is where it is available. You could expose
it to the public Internet like you do your web server, but that
would be extremely dangrous.

Hi David,

Lots of SQL Server databases (publicly and privately hosted) are open
outside their domain for external usage without a VPN. SQL Server is
pretty secure if you follow best practices. You can also use other
methods like restricting IP addresses in your firewall. Also, by
definition SQL Azure will be open to the "public Internet" and will
use IP address restriction, so we might as well get used to it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Can anyone suggest a simpler way of achieving remote access to my database?

Now one of the things I didn't see mentioned in the discussion is will the folks who
access your data remotely from overseas always be on a reasonably reliable Internet
connection? If so then a VPN to SQL Server, Terminal Server or Sharepoint would work
well.

If not then you'd have to look at replication, either Jet or SQL Server or possibly
SharePoint has enough capabilty that you could go away from the home network for a
week or two. Of course if replication is required then backups need to be done while
on the road in case the laptop is lost/stolen/etc.

And likely you want the laptop hard drive to be encrypted such as using Windows
Vista/7 Ultimate Bitllocker.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tony Toews [MVP]]

Hi David,

Lots of SQL Server databases (publicly and privately hosted) are open
outside their domain for external usage without a VPN. SQL Server is
pretty secure if you follow best practices. You can also use other
methods like restricting IP addresses in your firewall. Also, by
definition SQL Azure will be open to the "public Internet" and will
use IP address restriction, so we might as well get used to it.

Trouble is I see IP address restriction of little use when it comes to road warriors
working from clients, coffee shops and hotel rooms.

Otherwise that's interesting about SQL Server and external usage. Do you have a best
practices web page for this kind of implementation?

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[david]

While this is technically accurate, this is conceptually misleading
because in client's interaction with server, the question of "where is the
.mdf?" will never be asked by the client. In fact, a properly set up

While this is technically accurate, it is conceptually misleading,
because in a clients interaction with an application, the question of
"where is the database" will never be asked by the client.

If it is asked by the client, it works the same way: to use the
application, you use the connection string, to connect to the
service which provides the records.

server would _deny_ access to the users to those files. Only the
daemon (aka SQLSERVER service) or the administrator would have access to
those files.

Yes, only the daemon (aka SERVER service) has access to those
files. Yes, if you have the correct permissions, you can request that
data from the service. Yes, the permissions and protocols are
different, and allow you to do different things.

Thus, it's important to understand that you do not "browse and open a
file" as you would with other applications such as documents or

You do not browse and open an MDB or MDF file, unless you
want to. Normally, you connect a database application to a database.

Yes you can browse MDF files, either using a file browser or OSQL
or SQLCMD or Active Directory, or using an AD addin or etc.

Yes you can open MDF files. Using OSQL or SQLCMD or etc.
Yes you can open MDB files. Using Access or whatever.

spreadsheet, but rather "connect to" a server using one of many
protocols... TCP/IP, Named Pipes, Named Memory, and then some more.

Yes, you can connect to the SERVER service using one of many
protocols, TCP/IP, Named Pipes, Named Memory, and then some more

thus the question of "where is the file" is totally nonissue

It's a total non issue to the end-user.

However, it is not a non-issue for a person setting up a system,

Unless the end-user is also administering the logical location of
files and the physical architecture of the file-server.
Which is what the Personal Computer revolution was all about,
so you may be familar with that kind of behaviour.

because that's the daemon's job to manage it and you communicate with the
daemon.

You communicate with a daemon regardless of whose job
it is to organise the logical location of files and the physical
architecture of the server.

There are two points there:

1) It's just services communicating with services. It all works
that way regardless of what kind of database you are using.

2) Databases map to URI's because someone set them up
that way. It is all mapped: none of it is physical.

Many people are familier with the simplest way a file mapping
may be set up using SERVER. Far fewer people are familer
with even the simplest way to setup a file mapping using SQL
SERVER.
Actually, it's no more difficult, and no less flexible, it's just
unfamilier.

No. As I explained, daemon does all file handling here.

Yes, as I explaned, the server does all the file handling, and presents
it through the SERVER service and a client at the workstation. At the
workstation, you ONLY deal with the CLIENT service.

Therefore a proper setup will _deny_ the users access to the files. All

Right, this is a difference between the two services.

communication should go through the daemon.

Yes, communications always goes through Windows Services.

So you never put .mdf and .ldf files in a share folder

Conversely, if you want to use the Access/Jet, you never
put the MDB in a Master database.

but rather merely open the port 1433 for TCP/IP or use Named Pipes or
whatever for the clients to connect to.

Yes, if you want to use the native windows database primatives
through the SERVER service you

merely open the port 139 for SMB

This is why if you look at various ODBC connection strings, the only time
you do see a filepath is when you're working with a serverless source
(e.g. a .mdb will have its filepath in the ODBC connection, but you'll
never see that for a Oracle, MySQL, DB/2, SQL Server!)

Databases aren't bits of sand, or bricks. You use a mapping
syntax which depends on the ODBC driver, and you map to
names which may, or may not, mean something to the end user.

For example, it is conventional to use a similar name for the
logical name of a SQL Server database, and for the logical
name of the MDF file in the NTFS file record.

But the most important thing you got wrong is this:

working with a serverless source (e.g. a .mdb

Look at the second word of this phrase:

File Server

There is a basic misunderstanding that constantly confuses people.
Mostly, they get confused because they don't realise that a file
server is a server, and that messes up their understanding of how
Access/Jet works. That is not relevant here, so I won't say anymore
about it.

connectionstrings.com

To be crystal clear: The users do not need and should not have
access to the .mdf and .ldf files!!

What does that have to do with Port Numbers, Services, or where
you put the database files? Nothing. So what are you on my case
about?

I do believe that "Server" service is not actually germane to the
discussion here. If you read its description, it says it enables file,
print and named piped sharing over the internet. That what it does. SQL
Server services, OTOH, enables clients to connect to this machine and
issue requests for data.

The SERVER service, on the other hand, enables clients to connect
to this machine and issue requests for data. Using the SMB protocol

If you want to learn more about the SMB protocol, which includes
the Record requests, and the Record Locking requests necessary
to support the native Windows database primitives, you will have
to read more than just the 'description' of the SERVER service.

In fact, if I turn off "Server" service, I still can connect to SQL Server
on my SSMS, and reach a Sharepoint site remotely...

In fact, if you turn of the the SQL SERVER service, you can
still connect to the SERVER service, and reach your Access
database remotely (or any database which uses the native
Windows database services)

IOW, "SQL Server" service and "Sharepoint" service are not dependent on
the "Server" service. There is of course the notable exception that if I
wanted to use Named Pipes, then yes, I do need "Server" service. But with
TCP/IP, then no. They have nothing to do with each other.

I'm not sure what your point is? SERVER and SQL SERVER
and IIS are different services.

SERVER is the service (daemon if you will) used for SMB
IIS is the service (daemon) used for HTTP and others
SQL SERVER is the service used for OLEDB and others.

As I explained in my previous reply to Jon22, the term "server" actually
refers to a role, rather than some kind of program.
Yes.

A server usually has a daemon (aka Windows service) that listens on a
specific protocol (e.g. a port on TCP/IP, a region of address for Named
Pipes/Memory, etc.) and react to the requests through there.
Yes.


It goes back to why it's important to not be so concerned with the
questions such as "where is the file?" because those are wrong questions
to ask.

Except when you are setting up your servers or trying to
understand what a 'server' or trying to understand
what a 'service' is

And you've come in here and told us the answer to some completely
different question. You've told us that with the SQL Server, (as with
Access) the end user doesn't need to know where the file is physically.

And then you've told us that the original questions were 'wrong'.

But you've been so quick, you missed an important point: the OP
is not an end-user. He still has to work out where to put the files.

I would question that. A well-written Access application usually will run
on any backend, be it Oracle, MySQL, DB/2, SQL Server with minimum change
(e.g. using linked tables). It all depends on whether one has written the
application specifically for server-client architecture in mind, of which
also benefits even ACE engine.

The original poster has an application that will not run on "any
backend"

He has clearly described the problem, and the observed behaviour.

You have implicitly equated "A well-written Access application"
with "written ... specifically for server-client architecture".

I would question that, but what difference? His application clearly
is written specifically not for server-client architecture, whether or
not you call it "well-written"

There are times and places where re-writing a piece of functionality into
T-SQL, stored procedure, views or other SQL Server objects does make
sense,

This specific case. This specific case about the specific problem
as described in this specific thread.

but I really don't believe it requires a wholesale rewrite.

Well, I haven't seen the application, and you haven't seen the
application, but at least I never suggested a wholesale rewrite.

I suggested a "complete re-write" specifically of the stuff
which is not compatible with a "server-client" architecture"

">>You will have to completely re-write that stuff in TSQL"

But that is largely a function of how well written the application was to
begin with.

And you finish off be again implicitly equating "well written"
with "specifically for server-client architecture."

You know, telling people that you think their questions are
wrong, and suggesting that a clearly described application
architecture is not well written, is mildly insulting at the best
of times, but I think that if you manage to read down this far
you've done your penance.

(david)

 

[Banana]

Thanks Banana! Definately cleared away some of the fog.

Glad to hear that.

Let me know if I'm off the mark here, but I think the best approach for me
is going to be: Copy the tables from my Access database to a SQL Server
database then split the Access database, delete the linked tables from the FE
database and then relink to the tables residing on the SQL database (which
should have exactly the same table names and field names and datatype as the
ones that were put into the now obselete back end Access database so that the
front end database should still function as it does now)

That can work. You'll need to learn which SQL Server data types to
replace though those are minor differences but good for you to know
nonetheless.

Now, just to be 100% sure, also do consider David Fenton's advice of
using Windows Terminal Server because he is correct that this would
require no rewrite of the application so all you would have to do is
move it to a server and deploy there then you're done. We want to be
sure you've considered all possible solutions and what they means to you
in terms of performance, scalability and cost.

I think that I'll be able to link to the SQL database tables easily enough
from my copy of the front end Access database because this FE .mdb file will
be sitting on the same computer if I install SQL Server on my computer.

What about other clients on our office network? Or even more scary, our
computers in our overseas office? How do they find the SQL database to link
to? Do I need to save the SQL database (effectively our back end database) on
to our network storage device which has a static IP address assigned to it
and ftp & https access or our website or something? Or does my computer
(effectively the server once I install SQL Server onto it) suddenly gain
remote access capabilities even sitting behind our LAN router/moden firewall?

Well, yes, SQL Server has to be accessible from somewhere. Static IP is
a simple way to implement this though you'll definitely want to ensure
you have proper security in place (e.g. accept connections from only
certain IPs that your company owns for example) or use a VPN connection
so the connection to SQL Server is as if it's local, as David Fenton
explained.

From what I can gather, when I link to the SQL database tables I'll need to
create new ODBC files which, from my understanding, in the simplest terms
gives the FE Access database the filepath of the BE database objects to link
to? Will this have to be done for each client computer? Or can I create one
database with the linked tables all done and then send this .mdb file to all
the staff to load onto their hard drives? In other words, are ODBC files only
useful to the computer they were created on?

DSN (which you call ODBC files) are useful to any computer, provided
that they can reach the destination specified on the file. To provide an
example, if you had in your DSN: "Server=192.168.0.100", it'd work for
all computer where it is in the same network with SQL Server (including
those connected via VPN), but that would not work for any client outside
the network because the 192.168.xxx.xxx is a private network IP address;
you'd have to use a public IP address if you want it to be
world-accessible.

So the answer would be "it depends."

As an alternative, you can use "DSN-less connection", means that you
don't have to distribute the DSN, but instead create the link with the
complete paths within code. Doug Steele has an excellent example at his
site:
http://www.accessmvp.com/DJSteele/DSNLessLinks.html

More importantly I guess, am I way off here or perhaps still missing any
vital fundamental steps in my perception of how this is going to all work?

I think you've covered this. As mentioned before, be sure to consider
the other advices as well. If you've decided to press on this route, and
you are interested in some referential materials which also contains
more links, here's two for you:

Beginning SQL Server by Leigh Purvis:
http://www.utteraccess.com/forum/index.php?showtopic=1732935

Beginner's Guide to ODBC by myself:
http://www.utteraccess.com/forum/index.php?showtopic=1843709


Best of luck!

 

[Banana]

But if you're contemplating allowing disconnected use, you'll need
the SQL Server running on the laptops and you'll probably need to
implement SQL Server replication in order to keep the laptop
databases synchronized with the central one. Replication is not a
minor issue, and if you can avoid it, you really should.

If OP wanted to use the application in disconnected state, I would
really not want to entertain the idea of running SQL Server Express on
every client machine. That would be too much administration and training.

I'd daresay it'd be easier to just use Access local tables and write
scripts to synchronize when the laptop comes home. I also understand
that SQL Server supports replication with linked server, but I'm not
sure how well SQL Server handles synchronizing with non-SQL Server
sources or whether it'll be practical in this use, though.

I agree that replication is difficult no matter how you set it up.

In general, backing up the live SQL Server files is not going to
give you a reliable backup. It's just like backing up an MDB/ACCDB
that is open by a user -- you may or may not get a valid file out of
it (it's probably even less likely with the SQL Server files, I
would think). SQL Server has a backup agent to take care of backups
for you, but one of the disadvantages of SQL Server Express 2008 is
that the backup agent is not included! Some backup software is able
to talk directly to the SQL Server and get a backup file, but if
that is dependent on the agent, it won't work with SQL Server
Express.

That's why I had qualified my statement as "even then that is not
strictly necessary." Perhaps I should have had used "is not actually
necessary", because in most practical backup usage, we'd be dealing with
..bak files, not .mdf or .ldf files.

 

[Banana]

It can also not be turned off without disabling your PC, even if you
aren't sharing anything at all.

(I tried it with my first copy of NT, as I wasn't sharing any files)

Well, as I said in my previous reply, I could turn it off without any
ill effects to the connections to SQL Server or Sharepoint. Now, if I
had used named pipes, this would be a problem because Server service
provides this functionality, so in this context, SQL Server service
would have to depend on Server service for named pipes connection.
Otherwise, TCP/IP connection will work with or without Server service.

Maybe it changed since last time you tried it on your first copy of NT?

 

[Banana]

I don't know why, but there seems to be snippets where it should have
been quoted but wasn't so I had to filter which was your words and which
was my words not correctly quoted.

Anyway...

Yes, only the daemon (aka SERVER service) has access to those
files. Yes, if you have the correct permissions, you can request that
data from the service. Yes, the permissions and protocols are
different, and allow you to do different things.

However, you said in your previous reply:

Like Access, they reside where you put them. If you want to share them,
you put them somewhere where they can be shared. Then you set up the
shares so the user can use them.

The statement sounded like you were suggesting that we could put a .mdf
and .ldf files on a share folder. Perhaps that's not what you meant, but
I was very concerned by that statement. Hence, my emphasis that users
doesn't need access to those files, even to "administer" it because it's
the daemon's job to administer it.

You do not browse and open an MDB or MDF file, unless you
want to. Normally, you connect a database application to a database.

How do you connect to a .mdb file? In Access, you get a file dialog and
browse for a file and choose a .mdb file.

In SQL Server, you will not see such choices in most tasks. The closest
to this would be when you do a CREATE DATABASE.

Yes you can browse MDF files, either using a file browser or OSQL
or SQLCMD or Active Directory, or using an AD addin or etc.

I'm not sure I understand what is the point you're trying to make here.
Sure, I can see the files in the Windows Explorer, but in the SSMS, I
don't get asked for the physical location of database I want to connect
to. Same with SQLCMD. I'm not sure what AD has to do with this here.

Yes you can open MDF files. Using OSQL or SQLCMD or etc.

No. It's daemon's decision to open the file - it may be already open
before you connect to the database contained in the .mdf file. The point
is that daemon lives on its own and does its own thing whether there are
clients connected or not. In case of file-based RDBMS such as JET/ACE,
it's the application (e.g. Access) that open & manage files. JET/ACE
runs in-process with Access so it goes out of scope when Access quits.
This is not the case with server-based RDBMS where daemon will continue
to live on its own with or without any client connections.

It's a total non issue to the end-user.

However, it is not a non-issue for a person setting up a system,

Unless the end-user is also administering the logical location of
files and the physical architecture of the file-server.
Which is what the Personal Computer revolution was all about,
so you may be familar with that kind of behaviour.

However, the OP said he got stuck at the prompt to choose a SQL Server
and this was what I was replying to. I think I've already qualified the
few special cases where we do have to be concerned with physical
location, but when OP was trying to upsize it, he was working within a
client context and this is the context I explained.

There are two points there:

1) It's just services communicating with services. It all works
that way regardless of what kind of database you are using.

I'm not sure about that. If Access were the front-end client to the SQL
Server, that's a application communicating to service... e.g. client to
server. This is not to say that servers can't be also a client, but I
suspect the scenario where it's a service to another service is the
minority here.

2) Databases map to URI's because someone set them up
that way. It is all mapped: none of it is physical.

This is the case with server-based RDBMS, but this is not the case with
file-based RDBMS. As I said, a connection string for an ACE database
requires a filepath. You won't see a filepath in a connection string for
a server-based RDBMS.

Many people are familier with the simplest way a file mapping
may be set up using SERVER. Far fewer people are familer
with even the simplest way to setup a file mapping using SQL
SERVER.
Actually, it's no more difficult, and no less flexible, it's just
unfamilier.

To be honest, you've totally lost me here. I already said that I don't
think "SERVER" (presumably referring to Server service??) is germane to
the discussion - it's an entirely different service from what SQL Server
service does, and as I replied to David Fenton, SQL Server may or may
not depend on Server service on whether we require Named Pipes
connection or not.

And I'm not seeing how file mapping fits in here. Maybe you're referring
to setting up a file server?? If so, that doesn't really have anything
to do with SQL Server, which we still don't deal with it in terms of
filepaths.

Yes, as I explaned, the server does all the file handling, and presents
it through the SERVER service and a client at the workstation. At the
workstation, you ONLY deal with the CLIENT service.

What client service? If Access is the front-end with linked tables to
the SQL Server, it's a client to SQL Server service, but it's not a
service itself. Web browsers are client to IIS and Apache but it's not
service themselves. As I asserted earlier, there is nothing preventing a
server from being a client itself but this is less common.

Right, this is a difference between the two services.


Yes, communications always goes through Windows Services.


Conversely, if you want to use the Access/Jet, you never
put the MDB in a Master database.

As I said earlier, you earlier replied that 'they' (which is not all
that clear and could refer to .mdf/.ldf files) could be put in a share
folder, and that was completely wrong way to do this. Again, maybe that
was not what you intended, but if I could have had read it as referring
to .mdf/.ldf files, then it's possible that others would have had
likewise and thus I replied with a clarification.

I'm not following the phrase "never put the MDB in a master database."
Master database is a SQL Server construct and I don't know why anyone
would want to put .mdb file in there (presumably as a BLOB) and I don't
see how this is a converse of my assertion that .mdf and .ldf don't
belong on a share folder.

Databases aren't bits of sand, or bricks. You use a mapping
syntax which depends on the ODBC driver, and you map to
names which may, or may not, mean something to the end user.

For example, it is conventional to use a similar name for the
logical name of a SQL Server database, and for the logical
name of the MDF file in the NTFS file record.

But the point here is that you never need to know the .mdf's path in
order to connect to the database residing there, whereas with .mdb, you
must know the path to use data within it. That's the whole point,
clearly contrasting a file-based RDBMS from a server-based RDBMS.

But the most important thing you got wrong is this:


Look at the second word of this phrase:

File Server

There is a basic misunderstanding that constantly confuses people.
Mostly, they get confused because they don't realise that a file
server is a server, and that messes up their understanding of how
Access/Jet works. That is not relevant here, so I won't say anymore
about it.

If you're suggesting that JET/ACE is a file server (and thus not
serverless), then I've seen many places making that claim but I do not
think this is entirely accurate. Here's the reason why:

What is a server? A residual program that listens to the specificed
communication protocol and responds to any requests.

In order for the program to be "residual", it has to be started up at
the machine's startup and active the whole time. After all, it couldn't
very well listen if it wasn't alive to begin with, no? This is why we
use daemons or services - they're one that's doing the listening.

For JET/ACE (as well SQLite and SQL Server Compact Edition), they are
not listed as a service. They are just a bunch of .DLLs that may be
called by an application (e.g. Access for ACE for example), and when
called by this application, comes into the memory but it will go out of
the scope when Access quits. ACE uses filesystem to locate the data file
and work on it in much like manner as Word would upon its .doc document
and Excel upon its .xls document.

In case of split databases, when two users connect to same backend, what
it means is there are two ACE engines running on each client. When they
want to get some pages they use cooperative locking, so they "play nice"
with each other when they share the file. There is no central
administration or gatekeeper as is the case with SQL Server service
making decisions who get what access to what data. If there are ten
users, there are 10 ACE engines, all cooperatively sharing a single
file. With SQL Sever, there's only one daemon whether there's zero
client, one client, or nth client.

The reason why ACE has been called a "file server" was because it works
with files and uses pages within it to read the data and write data to
it, but this is not accurate - an actual file server merely means it
provides a set of folders that can be shared among clients. ACE
certainly may require a service of file server, but it's not a file
server itself.

The SERVER service, on the other hand, enables clients to connect
to this machine and issue requests for data. Using the SMB protocol

If you want to learn more about the SMB protocol, which includes
the Record requests, and the Record Locking requests necessary
to support the native Windows database primitives, you will have
to read more than just the 'description' of the SERVER service.

I wonder what you mean by "native Windows database primitives"? I think
I already acknowledged that this is necessary to share files, prints and
named pipes. Also, I'm not sure whether "Record requests" and "Record
Locking requests" are in reference to JET/ACE's constructs or perhaps
SMB has its own construct? I'd have had thought that a file server
dispense a file, while JET/ACE would handle the interior structure, but
I don't know SMB as much as I would. Maybe I'm missing your point here
and would like to hear it.

In fact, if you turn of the the SQL SERVER service, you can
still connect to the SERVER service, and reach your Access
database remotely (or any database which uses the native
Windows database services)

Your reply was very confusing to me that I had to go back and read, and
now I realize you were talking about Server service as essential for
file sharing for _Access_. The whole time I thought you were talking
about it in connection to SQL Server service and that was why I failed
to see how it was relevant to the discussion previously. My apologies.

Except when you are setting up your servers or trying to
understand what a 'server' or trying to understand
what a 'service' is

And you've come in here and told us the answer to some completely
different question. You've told us that with the SQL Server, (as with
Access) the end user doesn't need to know where the file is physically.

And then you've told us that the original questions were 'wrong'.

But you've been so quick, you missed an important point: the OP
is not an end-user. He still has to work out where to put the files.

Well, I answered this way because OP said he was stuck on the prompt to
choose a SQL Server. In this context he was the client so I did believe
that by stepping back and explain why this was being asked of him it
would help him better understand the overall process, including the
physical location of .mdf and .ldf.

You have implicitly equated "A well-written Access application"
with "written ... specifically for server-client architecture".

Perhaps so, but my intention behind this is indicate that how much work
will depend on how well-written it was to start with.

You know, telling people that you think their questions are
wrong, and suggesting that a clearly described application
architecture is not well written, is mildly insulting at the best
of times, but I think that if you manage to read down this far
you've done your penance.

I have no intend to insult others. I have no specific knowledge of OP's
application, which is why I wanted to clarify that this is a function
and leave it up to OP to hopefully work out a realistic assessment of
how much work he will need to invest into achieving his end goals. As
for questions being wrong, I perhaps have had went on a limb out there
and made guesses at what OP was thinking about, but the intention was to
help reorient the OP with how server works.

 

[Jon22]

Thanks David, they sound like convincing enough arguments to me. I have spent
way too many hours on this Access database to go back and rewrite anything.

Can you clear something up for me though. Is Windows Terminal Services
exactly the same thing as Remote Desktop? Or is it a platform that allows
multiple simultaneous Remote Desktop connections or something?

The reason I ask is, I regularly use Remote Desktop to access/connect to my
workstation at the office from my notebook at home and I have to say, I find
it great for some things but not so great for others. The concept is
brilliant but I don't like the way the interface looks through this
connection or how it reacts to commands. Everything looks pixelated and runs
'jumpy' and not very responsively.

So with WTS would the staff be just remotely opening the desktops of their
computers on our office network and then opening the Access database from
there? Or is it something closer to a copy of the Front end being on their
remote computers and the back end being on our office network and the two
talk to eachother via WTS?

One other thing, the firewall settings on our 2701HGV-W Gateway modem only
allows one instance of Remote Desktop to be used at a time between all the
computers on the network (although this is a relatively small issue in the
scheme of things).

 

[david]

Banana,

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

There is no indexing, or transactions, in the database
services that JET uses. Transactions in particular have
recently been added to Windows, and are not used by JET.

Jet builds it's own locking and Workgroup security on
top of the Windows Services. Workgroup security,
because Jet pre-dates Windows security. Locking,
because without Windows Security, Jet needed to build
it's own lock database to be able to report who had
a record locked.

So the Jet/MDB locking is done by locking records in
the lock file, rather than by locking records in the data
file. The locking of records in the lock file is done by
using Windows Record locking.

Windows has a mechanisim for reading/writing/locking
records. Jet uses this to get what are sometimes called
'physical records' from the database. On old IDE drives,
physical records are disc sectors, not even disk clusters,
and certainly not what is returned by a record request,
so that nomenclenture is not correct, but if the OS was
integrated with the File System and the Disk Controller,
as they were in old OS's, then the OS database primatives
would return 'physical records'.

ACE uses Windows security instead of Jet Workgroup
security, which is good, because Jet Workgroup security
is very old, not properly implemented, and has never been
updated.

But ACE as written can't do table level security. ACE
needs Windows record-level security to implement ACE
table-level security. Windows security doesn't have record-
-level security, and Windows table-level security was
based on a different model. So ACE will get table-level
security if ACE is completely re-written, or if Windows
adds record-level security.

(david)

 

[Jon22]

Replication is out of the question. Too much of the data relies on unique
values that if doubled up would cause massive headaches at the time of
consolidating it all.

 

[Jon22]

I've been looking up Windows Terminal Server and related subjects. We do not
have at this point in time a stand alone server computer. Is Terminal Server
a type of system software? Should I be considering buying a new computer to
act as our server which would run this software?

 

[Albert D. Kallal]

Can you clear something up for me though. Is Windows Terminal Services
exactly the same thing as Remote Desktop? Or is it a platform that allows
multiple simultaneous Remote Desktop connections or something?

No they are not the exactly the same thing. Windows terminal services uses
what is called the remote desktop protocol to send mouse clicks and little
text boxes down to the client. However the term window terminal services
generally means you're using remote desktop. Perhaps more specific is a
windows terminal server is something that creates multiple desktops that
people can remote into.

I mean if you have one computer and one desktop, and 10 people connect to
it, they would normally all see the same desktop (I think it's quite obvious
that that kind of setup is not going to be very useful).

Windows terminal services, or what is normally referred to a terminal server
is simply a good powerful server that setup and it allows multiple copies of
windows and allows one to have multiple users each with their own desktop
separate from each other running all at the same time on the ONE box. In a
sense you can think of windows terminal server as almost like a web server
that allows people to view all these pages being dished out, but in place of
dishing a web pages, it's dishing out windows desktop to each user.

What this means is that in terminal services, you're are connecting to a
computer box that's running windows in a way that allows each user to have
their own desktop. The beauty of this system means it doesn't matter what OS
or how crap of a computer you are running to CONNECT to this box. In some
cases some companies actually use this because then they can go to a
computer junkyard and pick up the cheapest worst pieces of junk desktop
computers. They then by ONE nice server in which they install windows 7,
office 2007 and all latest software. What this means is that each of the
client computers only need to connect to this remote desktop. Any additional
maintenance, setup of software, adding hardware, fixing problems are now
done on ONLY ONE box.

The type of and condition of the client computers don't matter. Heck you can
even use Macintoshes if you want. So using window terminal services is well
liked by some companies because they never have to upgrade any of the old
client computers sitting around in the office. Some companies are still
running old computers with windows 98 as cleints. These boxes are free at
your local computer junkyard. So, 64 megs of ram is planning for this
system, whereas the typical computer today is shipping with 4 gigs of ram.
So some companies actually adopt remote desktop and windows terminal
services even for all users (not just remote ones). They do this because it
means then they never have to upgrade any of their cheap crap computers in
the office anymore. And when they have to upgrade to the next version of
officer something, they never have to install any upgrades are software on
any of the client computers. Another significant advantage is that any user
can work in any desktop anywhere in the company, and instantly be using the
same desktop they've used for years, and if they work at home when they
remote in, again they get the exact same desktop. So, they can work on any
computer, remote into the termal server, and presto, they have their same
experience and the same desktop they use every day. Because all processing
ran and software is managed and run on that terminal server, then it's not
significant or relevant as to what kind of client computer you have. You can
have a client piece of junk computer, but install the latest windows 7 and
office 2010 on the terminal server if you want. So you get a great
centralized management of all software, and the client hardware computers
don't matter anymore.

The reason I ask is, I regularly use Remote Desktop to access/connect to
my
workstation at the office from my notebook at home and I have to say, I
find
it great for some things but not so great for others. The concept is
brilliant but I don't like the way the interface looks through this
connection or how it reacts to commands. Everything looks pixelated and
runs
'jumpy' and not very responsively.

Well there's probably 15 to 20 different remote desktop systems available in
the marketplace, and you even see products like "goto my pc" heavily
advertised on TV and in magazines.

However, if you are in fact using the remote windows desktop, then the
pixelation is a function of what resolution and setup you're using for the
remote system. You should get at least as smooth as experience, and even
some what smoother than that of say using a web browser and web based
application. And, web applications are generally nowhere near as rich and
functional as a typical windows desktop application. Of any remote
technology I've used, the remote desktop protocol, and connecting to windows
terminal server cans to give about the best remote experience possible, and
with any high speed Internet connection, use to get a windows like
experience with little difference then running everything on your local
computer.

So with WTS would the staff be just remotely opening the desktops of their
computers on our office network and then opening the Access database from
there?

That is correct. As mentioned you don't actually have to have 10 computers
sitting unused physically sitting in the office. A windows terminal server
allows you to create as many users as you want each with their own copy of
windows and a separate desktop on that one computer (that one computer is
typically referred to as the window's terminal server). So for each
additional user you add to that box, you don't have to install setup and
purchase a whole new computer. However, if each existing user already has a
working "physical" computer at work, then remote into that box would be a
solution (and you are hinting and suggesting that you're capable of remoting
in this way now).

Or is it something closer to a copy of the Front end being on their
remote computers and the back end being on our office network and the two
talk to eachother via WTS?

No, it is a true windows remote system. Nothing is placed or installed on
the client computer. The only thing you need on the client computer is the
software or program that allows you to connect to the terminal services, and
that's called the remote desktop client. In fact for Windows XP, and
certainly any editions later the so called remote desktop client is pre
installed (built) into windows. This fact only saves you having to install
some software on each person's computer that would allow you to connect to
windows terminal server. This client "system" uses what is called RDP
(remote desktop protocol). There are many different remote desktop systems
in the marketplace. RDP is particularly efficient, and well suited to lower
bandwidths as it tends to only send mouse clicks, characters (text) and only
part of the screen that changes. There are also free products the
marketplace that don't have the windows terminal server part, don't use RDP
but allow remoting into a desktop. VNC, or ultra VNC is such an example.
Perhaps you using VNC? And, don't confuse VNC with a VPN (virtual private
network).

One other thing, the firewall settings on our 2701HGV-W Gateway modem only
allows one instance of Remote Desktop to be used at a time between all the
computers on the network (although this is a relatively small issue in the
scheme of things).

That makes sense ONLY in the case to allow one to tunnel into ONE particular
desktop box within your Office Network. If you take (setup) a server and
install and set up windows terminal services, then you only in theory
connecting to one computer in the office again, but that computer is capable
of vitalizing and dishing out multiple instances of desktops to multiple
users. It would be like saying that your system only allows everyone to
connect to one server, but that server has your files on it. So this might
be a limitation of your gateway modem, but it's really not a issue or
problem because you're using all the resources to go through one particular
"port" to connect to one particular computer. It's just that the particular
computer you're connecting to is not some type of SQL server, or in this
case a windows terminal services server that is set up to dish out multiple
desktops. So even when using windows terminal server you're only connecting
to the one box, but the one box a capable of servicing many people. In a
nutshell this limitation likely would not be significant if you're using
windows terminal server.

It is perhaps also interesting to point out that the remote desktop system
that you see built into windows also is based on and uses the remote desktop
protocol. So as a trial run, if you are in fact using remote desktop and
the issues of resolution and mouse clicks is a problem, then this is a
great way to give you an idea of the type of experience users will receive
when using windows terminal services. You should be able to crank up the
resolution, and fix those pixliation issues. Keep in mind that remote
desktop is not great for graphic intensive applications as that requires too
much network bandwidth. So if you after reading the above decided to dump
all your desktops or use crap computers, and centralize all of you software
and support services into one box, it would not work well for an
organization that's doing computer drafting or cad type graphics intensive
applications. On the other hand if it's just some data entry into some
forms for typical access application that's not graphically rich, then many
companies will actually make the decision to centralize all their software
support and services into one box.

However, in your case you would not be using windows terminal services to
replace all your desktops, but only to allow remote access to a particular
application. However, WTS still implieds that each user logging into the
terminal server will have their own separate desktop. I should point out
that windows terminal services also does have setup options that allow when
the user connects to one of those desktops, that an application (such as
your access application) can automatically run when you connect. So, it is
not necessarily that each user actually have to see that separate desktop,
but conceptually they all still have their own desktops when using a windows
terminal server.

 

[Albert D. Kallal]

I've been looking up Windows Terminal Server and related subjects. We do
not
have at this point in time a stand alone server computer. Is Terminal
Server
a type of system software? Should I be considering buying a new computer
to
act as our server which would run this software?

That is a correct assumption. All of the later editions of the so called
server editions of windows, such as windows server 2003, or windows server
2008 all allow windows terminal services to be installed and setup and run
as a service. In fact, as far as I know, windows terminal services is
already preinstalled on later server editions of windows servers. In the
past you had to install and set up this WTS software. So, from a conceptual
point of view, it's not a whole lot different than dedicating a server, and
installing SQL server services on that server. So, the only real difference
here is, that windows terminal services is preinstalled on most server
additions.

 

[Albert D. Kallal]

ACE uses Windows security instead of Jet Workgroup
security, which is good, because Jet Workgroup security
is very old, not properly implemented, and has never been
updated.

I should probably point out that the way that jet or ace works in the above
statement is exactly the same.

But ACE as written can't do table level security.

You should probably add to this that you're talking about a accDB file. ACE
still fully supports the jet workgroup security model if you open an older
mdb file format.

I guess the main point at the end of the day here is there is not some
special consideration or modifications in ACE as compared to JET in how JET
deals with and works with and interacts with the built in window security
model and file system.

The only real change here is that were being encouraged not to use the jet
workgroup security model with ACE. However, ACE still does fully support
workgroup security.

It's somewhat misleading to state that is ACE can't do table-level security,
because ACE still does support jet workgroup security.

And to be 100% fair, I don't believe you ever made the claim that somehow
ACE works with, or interacts any differently than that of when JET opens
that plane jane windows file.

To my knowledge, both jet and ace open and work with those window files
exactly the same way.

So, ACE does in fact have the JET workgroup code base and does in fact
support jet workgroup security.

Perhaps the biggest news here is that the reason why ACE (jet) is being
extended now is because the Access team now owns the jet code base. Prior to
access 2007, the access team really could not make modifications to JET, or
now what we refer to as the ACE engine.

For Access 2010, that ACE engine now has stored procedures and table-level
triggers. So no matter how we slice and dice this the mere fact of the
Access team gaining ownership of jet is rewarding us with all kinds of new
features. Those feature range from the new cool offline disconnected mode
when working with SharePoint, or stored procedures and triggers at the
table-level.

And wonders of wonders, it also means that we get a 64 bit version of our
lovable jet engine due to office 2010 having a 64 bit version of VBA
available.

 

[David W. Fenton]

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]

There are many different remote desktop systems
in the marketplace. RDP is particularly efficient, and well suited
to lower bandwidths as it tends to only send mouse clicks,
characters (text) and only part of the screen that changes.

The real reason this is efficient is because it sends all this
information in the same encoding that is used by the components of
Windows, using the native graphics primitives that are used to
communicate between the Windows graphics subsystem and the hardware.
Thus, the native Windows calls are sent from the remote Terminal
Server to your own local computer, and then translated into the
exact commands necessary to tell your computer's graphics card how
to paint the screen.

Programs like VNC, which I use all the time, BTW, send bitmaps. It's
like the difference between scalable fonts and bitmapped fonts in
terms of the amount of data sent. A scalable font sends the font
definition once (which is a series of mathematically described
curves) and to render any particular font, the point size and weight
and so forth are sent. With bitmapped output, every single pixel's
position and color has to be sent.

The result is that remote sessions via Remote Desktop Protocol with
compatible hardware use very little bandwidth and are even usable
over a 28.8 dialup connection (though usually with reduced color
depth and resolution to gain some speed). It's not great, but it's
acceptable when that's the best possible connection. With anything
approaching even American DSL seeds (300Mbps and above), it's very
little different than running locally, except when Internet
congestion gets in the way (which happens a lot less than you'd
expect with any reasonably reliable broadband connection).

It *is* necessary that the Terminal Server itself be connected to a
broadband connection that is sufficient to support the number of
simultaneous users, but I've found that even this is not as taxing
as you'd expect, since users are not updating the screen all the
time, so they don't need full bandwidth all the time. That is, if it
takes 300Mbps to update the screen reasonably responsively, the user
only needs that every few seconds. In between, other users can be
serviced at full bandwidth. Thus, I always allocate bandwidth at
about 128Mbps per simultaneous user, which I've found to be more
than enough to give all users nice snappy response.

 

[Banana]

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.

Jet builds it's own locking and Workgroup security on
top of the Windows Services. Workgroup security,
because Jet pre-dates Windows security. Locking,
because without Windows Security, Jet needed to build
it's own lock database to be able to report who had
a record locked.

Just to check my comprehension here:

By 'Windows service', we're referring to file sharing.
By 'Windows security', we're referring to filesystem permissions.
By 'Jet Workgroup security', we're referring to User-Level Security.

If that is correct, then:

I agree that JET/ACE depends on Windows service for locking the .ldb
file. Heck, it has to for all other file operations. No different from
any other applications, really.

However, I am not sure that ULS does builds on Windows Service - it's a
part of JET/ACE design and merely restrict access to various objects
within the file within JET/ACE's context. It simply doesn't function
outside that context (e.g. open that same file in a hex editor and
there's no object-level security anymore).

So the Jet/MDB locking is done by locking records in
the lock file, rather than by locking records in the data
file. The locking of records in the lock file is done by
using Windows Record locking.
Agreed.

Windows has a mechanisim for reading/writing/locking
records. Jet uses this to get what are sometimes called
'physical records' from the database. On old IDE drives,
physical records are disc sectors, not even disk clusters,
and certainly not what is returned by a record request,
so that nomenclenture is not correct, but if the OS was
integrated with the File System and the Disk Controller,
as they were in old OS's, then the OS database primatives
would return 'physical records'.

FWIW, I'd think we've long ago moved from needing to concern ourselves
with such low-level details such as clusters, sectors and tracks of the
hard drive. I believe JET/ACE deals with the file in terms of pages
rather than sectors, and pages are always a fixed size.

ACE uses Windows security instead of Jet Workgroup
security, which is good, because Jet Workgroup security
is very old, not properly implemented, and has never been
updated.

But ACE as written can't do table level security. ACE
needs Windows record-level security to implement ACE
table-level security. Windows security doesn't have record-
-level security, and Windows table-level security was
based on a different model. So ACE will get table-level
security if ACE is completely re-written, or if Windows
adds record-level security.

To be honest, that part was confusing to me until Albert posted his
reply which made a bit sense out of this.

If we're still on 'Windows security' = 'filesystem permissions', then I
should point out that I think we're talking about two different security
models. ULS works at object level while filesystem permissions works on
file level. Filesystem permissions cannot be used to manage objects
within the file so I think this is a case of comparing oranges to
apples, and to be sure, they weren't mutually exclusive. We could use
_both_ filesystem permissions and ULS to secure the same file (and
subsequently the objects within the file). (keeping in mind Albert's
clarification that both JET and ACE are capable of using ULS - it is a
question of whether we use the .mdb format or .accdb format rather than
whether we use JET or ACE)

I'm also aware of 'Windows Record Locking', which basically operates on
a file based on a byte offset. However you mentioned 'Windows
table-level security', which a google turned empty. Could you clarify
what you mean by 'Windows table-level security', please? Otherwise, I
think that if the point is that filesystem permissions does not work
below the file level (e.g. Windows record level), then we're in agreement.

 

[David W. Fenton]

Can you clear something up for me though. Is Windows Terminal
Services exactly the same thing as Remote Desktop? Or is it a
platform that allows multiple simultaneous Remote Desktop
connections or something?

Albert has explained all of this, but it's important to realize that
Terminal Server predates Remote Desktop. Back in the NT 4 Server
days, the Terminal Server version of Windows Server was a separate
product, and cost more money and licensing was very expensive. It
was also really primitive unless you used Citrix's add-ons to it.

Microsoft licensed Citrix's technology for inclusion in Windows
Server 2000, and from then on all Windows Server versions include
WTS support (by default, there are two sessions available, but only
to users who are administrators). If you install client-access
licenses (CALs) on the Terminal Server, remote machines can connect.
In order to run software in their terminal server session, though,
they have to have the appropriate licenses on their local desktop.
Any user with Office installed on the local desktop will be granted
permission to run Office apps on the Terminal Server.

Now, originally the terms under which all that happened were quite
friendly and loose -- you didn't need the exact same version (you
could have Access 2000 and run Access 2003 in your terminal
session), but with Office 2007, this has become much stricter. You
now really do need the matching version of Office and the exact same
app (for instance, having Office 2007 Standard installed locally
doesn't allow you to run Access 2007 remotely). Also, the
requirements for the server have been changed -- it must be Windows
Enterprise Server in order to provide remote users the opportunity
to run Office 2007 applications.

If you stick with Access 2003, you can use any version of Windows
Server except Small Business, and as long as your users have a
version of Office from 2000 on installed, they'll be able to use it.

Now, the Remote Desktop you're familiar with is based on the exact
same technology, and was introduced in Windows XP. However, it has
the restriction that only one user at a time can connect, and it can
only have one session at a time. It doesn't allow multiple sessions
as is the case on a Windows Terminal Server. But the underlying
technology that enables the remote control of the PC on the other
end is exactly the same, i.e., RDP, or Remote Desktop Protocol.
That's why the Remote Desktop client can be used to connect to a
workstation and to a Terminal Server, because the protocal is
identical. The only difference is how things are handled on the
remote machine, which is dependent entirely on what version of
Windows it is and what's installed in terms of licenses.

There are 3rd-party products like Winconnect (Google it to find out
more) that also use RDP. Citrix provides GoToMyPC using the
technology, as do LogMeIn.com and any number of other remote access
solutions. Winconnect is different in that they have a server
version that can be installed on a desktop and allow multiple users
to connect. I haven't used it but others I respect have said it's
quite a good product. If I had a small client who needed remote
access to support a couple of remote users and didn't have a Windows
server, I'd definitely consider Winconnect.

[]

One other thing, the firewall settings on our 2701HGV-W Gateway
modem only allows one instance of Remote Desktop to be used at a
time between all the computers on the network (although this is a
relatively small issue in the scheme of things).

It's likely not a limitation of the firewall, but of the
configuration it's running in. Most firewall/routers have port
forwarding such that the administrator maps a port to a particular
application and destination computer. All remote control
applications have a default port (VNC's is 5900, for instance), and
if you want to support more than one computer, you usually configure
more than one port so that the user requests a connection to a
particular computer by using the port number allocated to that
destination PC.

With Windows Terminal Server, there's one port that serves everyone.
I wouldn't recommend having a port open through the firewall (the
scenario you describe in your office is not one I'd recommend
either), but instead set up a VPN for all purposes, so only one port
is open for all applications and uses by remote users. They connect
to the VPN, authorize against your domain controller (or whatever is
used for VPN authorization) and then have access to the resources on
the internal network similar to the way they do in the office
itself. Thus, there's nothing opened up to the Internet except the
VPN port, which is usually a much stronger "lock on the door" than
any of the alternatives, since individual keys and encryption
settings can be required for users to be able to log on (i.e., it's
insufficient to just provide a valid username/password -- you have
to submit appropriate credentials beyond that).

I think that addresses all the things that Albert left out of his
very complete answer.