Might be outgrowing Access but daunted by SQL Server

Windows Term Services licensing is what, 20 grand?

SQL Server is free for all practical purposes





On Feb 28, 12:31*pm, "Albert D. Kallal"
wrote:
"Jon22" wrote in message

...

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 * *(Access MVP)
Edmonton, Alberta Canada

WTS sucks a big fat one

and no, moving to SQL isn't expensive.. Everyone's been telling you to
move to SQL for the past decade.

It's your own fault you didnt' listen.




On Feb 28, 4: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/

yes, I use Linked Servers literally 100 times a day, every day,
without fail

I wonder if
the linked server solution would enable it (so that you'd be running
SQL Server on the remote laptop only in order to do the
synchronization of the MDB linked "server").





On Feb 28, 5:52*pm, "David W. Fenton"
wrote:
Banana Banana@Republic wrote innews:4B893809.3070701@Republic:





David W. Fenton wrote:
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 didn't know about the linked server thing. I do know that SQL
Server 2000 supported heterogeneous replication with Jet 4, which
allowed a Jet 4 user to be a subscriber to a replicated SQL Server
database. They dropped that with SQL Server 2005, but I wonder if
the linked server solution would enable it (so that you'd be running
SQL Server on the remote laptop only in order to do the
synchronization of the MDB linked "server").

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

Sharepoint is the future for that, both because it's easier and
because it's the platform MS is putting its resources into to
support disconnected editing.

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.

FWIW, when I'm responsible, I include the MDF/LDF files in the file
backup in addition to running the SQL Server agent to do its thing.
Multiple levels of redundancy are a requirement when it comes to
backups, because the danger is that multiple backups will fail
simultaneously. I've seen it happen so often that I just don't set
up a single level of backup for anyone.

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

- Show quoted text -

=?Utf-8?B?Q1c=?= wrote in
:

The whole setup works wonderfully for us and I often wonder why
one might need anything more sophisticated (= expensive and
time-consuming to learn and maintain).

The scenario you outline will not work in more than 1 out of 1000
cases. It is just wrong.

It's also much harder to update the application part of an unsplit
database, so it must be a pretty trivial app if that's working for
you.

There is really nothing hard about managing a split app and it is
much, much safer and more reliable. I've never once put an unsplit
app into production use with multiple users, and that includes the
very first one I ever did, back in 1996. I don't know exactly where
I got the information that I needed to split it, but that was the
way I designed the whole thing. This was back in the days of Access
2, Windows for Workgroups 3.1 and 10BaseT networking. I was blown
away by Access's ability to cache data (particular for a stupidly
designed combo box that had hundreds of records -- first time you
used it was slow, after that, totally fast).

It just doesn't seem like an issue to me.

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

"Larry Linson" wrote in
:

You can safely disregard Mr. Kempf's comments, even if no one has
refuted the specifics. His useful contributions to this newsgroup,
over all the time he's been posting here, have been minimal.

So far as I can recall, they are not 0.

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

Thank you all very much for your advice, especially those who have taken the
time to follow this issue right through with me and who have outlined their
advice so comprehensively.

At this stage, for the sake of a couple of hours work, I think I'd be silly
not to at least do a bit of testing of the functionality of the database as
it is now (designed entirely in Access) with some of the tables linked to an
SQL Server database (all going well I'll then investigate dsn / dsn-less /
VPN connections etc), with the view to look at Terminal Services / RDP
options if it becomes apparent that too much rewriting is needed.

My thought process being, I can install SQL Server on my own workstation for
free (Express with SQL Server Management Studio), but to do the TS option
properly, we'd need to purchase a dedicated server with (unfamiliar) server
software plus CALs.

My business partner has given me a lot of time and breathing room to get our
internal data and process management all consolidated (he's one of the ones
who needs regular overseas access to the database) and I'd like to be able
say to him "OK, all done. Just do this, this and this and you'll be in. And
it cost us virtually nothing more than my time."

We're nearly 7 years old and so far, I have managed to keep the IT entirely
under my control. I have concerns about outsourcing IT as I feel it's a path
that, once started down, would be very difficult to come back from.

I will ask any new questions I might have relating to this matter in a new
post.

Cheers,
Jon.



"Jon22" wrote:

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. However it is becoming more and more obvious to me that
I am going to need to give staff access to this new database from remote
locations (outside of our office network). Two staff members are regularly
overseas, one is mainly on the road with her laptop etc etc. I've been
playing around with trying to link tables from remote locations (ftp path of
the database on our Network Storage Device at the office) and needless to say
- that don't work. So I've spent the last 10 hours downloading, installing
and trying to get my head around Microsoft SQL Server 2008 Express and how it
might help me out. It all started when I came across a very encouraging
article on keeping your Access database as your front end application and
linking to SQL Server database tables. I'm finding it MUCH more complicated
than I had hoped.

Can anyone suggest a simpler way of achieving remote access to my database?
I have at my disposal a Network Storage Device which has remote access
capabilities (this is where the database is currently stored), our website
which it could be stored on, or I'd be happy to use my quite powerful
workstation at the office as some kind of server. You may have guessed by now
that I'm no IT guru by any stretch so please bare this in mind with any
suggestions.

David;

Go play with your deprecated baby sized jet databases, kid

-Aaron



On Mar 2, 12:08*pm, "David W. Fenton"
wrote:
"Larry Linson" wrote :

You can safely disregard Mr. Kempf's comments, even if no one has
refuted the specifics. His useful contributions to this newsgroup,
over all the time he's been posting here, have been minimal.

So far as I can recall, they are not 0.

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

On 1 Mar 2010 22:53:01 GMT, "David W. Fenton"
wrote:

An
Autonumber should never be exposed to the users,

Hi David,

I agree with this statement if you add the word "almost" before
"never".

We do have apps where we expose the AutoNumber/Identity key to the
user as a read-only "reference" number. They know that there may be
gaps, and that it is merely a unique identifier to be sure that two
people are looking at the correct record. It's especially handy in
scenarios where there are many similar-looking transactions, for
example.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

=?Utf-8?B?Sm9uMjI=?= wrote in
:

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/

"a a r o n . k e m p f @ g m a i l . c o m" wrote

Go play with your deprecated baby sized jet databases, kid

Jet was never officially deprecated, but it didn't get a lot of attention
for a while. Then the product team gave it some attention, and improved it
along with creating a descendant (the ACE database engine) in Access 2007.
At about this time, they ceased recommending ADP as the preferred Access
front-end for SQL Server, and now recommend MDB with ODBC for that purpose.

Some people just can't seem to get the story right... either they are
ignorant or they choose to disseminate false information.

Larry Linson
Microsoft Office Access MVP