new technoligy vs jet engine.

[cityrock]

Hello friends.
It has come time for me to face a dilema i have been thinking about for
a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE.
I am the system engineer, administrator and programmer of our company's
database. Our mdb FE is very complex, using hundreds of forms, queries
and reports. I can truely say my DB architacture is good, and having
hundreds of relashionships, between tables is holding good.
All my users connect first to our server with Terminal Services, and
then open their own FE . This gives me the ability to save network
traffice to minimum, as the BE and FE are on the same machine. (By the
way I have multipule back ends as well, some FE's connect to all BE's,
and some to specfic).My server is a x64 AMD Opteron, with 2gb of ram. I
currently have around 20 users logged in during working hours. The
preformance at most times is good, even though alot of updates on
records are being performed by all users (not even alot of record
locks). I can say I am quite happy with these settings, and would have
left it like that if we were not expending.
But we are expending, and at least 40 more users will be joining our
system soon.
Again, i have multipule FE's ,and some users will have more access to
our BE's with alot of updates, and some with only limited (even read
only) access.

So now to my dilema. I have read, and understood why sql is better. It
is mostly because of network performance (which i do not have with my
scenerio), and multi user support (i have read where jet engine MDB's
work with 100+ users though). Also I know the benefits of recovery, and
backup (but again, after working hours the server is locking out all
users, and i have time to maintain my mdbs, as well as back them up. I
also have acceptance from my boss, taking 15 minutes to shut down
users for middle of the day backup if needed).

I also read sql might give me slower performance, but more data
integrity. Dilema Dilema....
So I am at a point, before I upgrade my system, have decided to spend
the next half year rewritting from scratch my back end and front ends.
This i will do, no matter which way i go. As you can probably see my
from post, i prefare using jet MDB. I know it by heart, i have good
programming skills with vba, and like the easiness of queries, forms
and reports in ms access (they fullfill all our companies needs). I
have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....
I honestly say i have not worked much with sql server in the past, and
although understand the logic of ADP's , never before worked with an
ADP project.
I want to stay with MDB and jet, and just rewrite my application, with
efficiant code and db structure. is it possible?
I know its not recommanded, but i am even thinking of buying another
strong server, link the two servers togather, and that way take off
some work load used by terminal services on each server (even though TS
is supposed to manage 100+ users, but dont know how it will hold with a
big Ms Access proccess open on each session).

So to finilize my long post.
The new technoligies of terminal services, the good speeds of internet
in our days, the "not so expensive" strong servers these days, can keep
me working with MDB and JET on a complex database with 70 users? Is it
stupid of me not to upgrade to sql? Can terminal service (i know this
is an access group) hold 70 users with ms access, even just as FE
users?
What would be my alternatives, if any?
Thank you in advance,
Eli

 

[Rick Brandt]

Hello friends.
It has come time for me to face a dilema i have been thinking about
for a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE. [snip]
...I have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....

[snip]

You are operating from a false premise. You do NOT have to convert all queries
to Views/SPs and move to unbound forms to utilize a server back end.

Create the same tables on a SQL Server and link to them in a copy of your app
where all of the jet links have been removed and see how it works.

Will you need some redesign? Absolutely, but not nearly to the extrent that you
seem to think. Most of your queries, forms, and reports will work just fine
with a SQL back end. You will just need to concentrate on the few areas that
either don't work at all or which perform badly. If your existing app is
designed as well as your post implies then I wouldn't be surprised if the
re-work required was minimal.

 

[cityrock]

Hello Rick,
Thank you for your quick reply.
I dont know why exectly, but always thought, only transfering the data
to SQL, and linking the tables to an MDB file will not give me any
advantage. Since I am still using an mdb file, no functionality will be
done on server side, and the mdb will still need to query for all the
data everytime. Will I be able to use my exsisting DAO to update
records? How is record locking going to take effect? Will performance
be better or worse moving to SQL, keeping in mind I still use only
links to the tables, and no ADP application.
I would do a small test right now, just to see functionality, and
update my resultshere , but ofcourse I do not have 30-40 users to test
it with.
I know this is a terminal service question, but does anyone know how
terminal service will hold with 70 users with mdb files open?
Tnx again
Eli

Hello friends.
It has come time for me to face a dilema i have been thinking about
for a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE. [snip]
...I have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....

[snip]

You are operating from a false premise. You do NOT have to convert all queries
to Views/SPs and move to unbound forms to utilize a server back end.

Create the same tables on a SQL Server and link to them in a copy of your app
where all of the jet links have been removed and see how it works.

Will you need some redesign? Absolutely, but not nearly to the extrent that you
seem to think. Most of your queries, forms, and reports will work just fine
with a SQL back end. You will just need to concentrate on the few areas that
either don't work at all or which perform badly. If your existing app is
designed as well as your post implies then I wouldn't be surprised if the
re-work required was minimal.

 

[Rick Brandt]

Hello Rick,
Thank you for your quick reply.
I dont know why exectly, but always thought, only transfering the data
to SQL, and linking the tables to an MDB file will not give me any
advantage.

You get the security, scalability, and reliability of a server database instead
of the lack of same in a file based system.

Since I am still using an mdb file, no functionality will
be done on server side, and the mdb will still need to query for all
the data everytime.

False. Most processing of Access queries against ODBC links will still be
performed by the server. There will be *some* queries that pull lots of data
for local processing, but that will be the exception, not the rule. Your job
after making the change is to find those few queries with that problem and fix
them.

Will I be able to use my exsisting DAO to update
records?
Yep.

How is record locking going to take effect?

IME record locking is not an issue.

Will performance
be better or worse moving to SQL, keeping in mind I still use only
links to the tables, and no ADP application.

Performance will depend on the quality of your queries and the design practices
of pulling the bare minimum of data required to perform a task. If your jet
application is well designed then you are already taken care of in this regard.

I would do a small test right now, just to see functionality, and
update my resultshere , but ofcourse I do not have 30-40 users to test
it with.

Yes, the stress of multiple users is difficult to test for, but a server back
end will tolerate this much better than a file share.

I know this is a terminal service question, but does anyone know how
terminal service will hold with 70 users with mdb files open?

Since everyone is opening the same back end file Terminal Services is not a
factor other than you have eliminated the reliability of the network as an issue
(if the file is located on the Terminal Server).

I have between 100 and 200 users hitting SQL Server back ends with no problems.

 

[Guest]

If you are using transactions, your transactions will break and will
need to be disabled or re-written as native SQL Server transactions.
(Unless you are still using Access 97).

If you have locking problems with your Jet MDB database, using
a linked SQL Server database is likely to make it worse: you would
have to improve your code (and likely choose to re-write as native
SQL Server procedures).

If your database is not too big for Jet, there is no performance penalty
either way for well written code: SQL Server is not much worse than
Jet for the same number of users on the same hardware. If your database
is pushing memory limits, SQL Server will do better at keeping the
indexes in memory and paging out the data.

From what I have read here, 20 users is about the top for TS using
Access on one server, because you run out of memory, but remember
this is the kind of thing MS works on improving: perhaps current
versions of Windows Server and Terminal Services are better. You can
increase your ram to 4GB and use the 3GB memory switch: that will
triple the amount of memory you have available.

Because you are not using the network as part of the database engine,
you are not susceptible to bugs in the network.

Using SQL Server, you will be able to create server farms and hot
switched back up. Actually, no, you won't, but you could hire some one
who could. If you only backup every day, there won't really be much
difference from backup of your MDB every day.

Using SQL Server, you will be able to use local Access clients and
connect to SQL Server over the network. This may allow more
connections to the server than you can put on one Terminal Server,
and may be cheaper than all of those Terminal Services licences,
but is certainly slower for small numbers of users, and much more
subject to slow network conditions like routing and DNS delays.

(david)

Hello Rick,
Thank you for your quick reply.
I dont know why exectly, but always thought, only transfering the data
to SQL, and linking the tables to an MDB file will not give me any
advantage. Since I am still using an mdb file, no functionality will be
done on server side, and the mdb will still need to query for all the
data everytime. Will I be able to use my exsisting DAO to update
records? How is record locking going to take effect? Will performance
be better or worse moving to SQL, keeping in mind I still use only
links to the tables, and no ADP application.
I would do a small test right now, just to see functionality, and
update my resultshere , but ofcourse I do not have 30-40 users to test
it with.
I know this is a terminal service question, but does anyone know how
terminal service will hold with 70 users with mdb files open?
Tnx again
Eli

Hello friends.
It has come time for me to face a dilema i have been thinking about
for a long time, but actually doing nothing. Now its time to act.
The situation is "simple", and i have just spent hours reading other
posts regarding the advantages and disadvantages upgrading my mdb (jet
engine) to an sql BE. [snip]
...I have thousends of hours of code, queiries,forms written, which i would
like to take with me to my knew system, most of them will not be able
to work if i upgrade to sql, without converting all the queires to
views/sql commands, and unbounding forms and controls....

[snip]

You are operating from a false premise. You do NOT have to convert all queries
to Views/SPs and move to unbound forms to utilize a server back end.

Create the same tables on a SQL Server and link to them in a copy of your app
where all of the jet links have been removed and see how it works.

Will you need some redesign? Absolutely, but not nearly to the extrent that you
seem to think. Most of your queries, forms, and reports will work just fine
with a SQL back end. You will just need to concentrate on the few areas that
either don't work at all or which perform badly. If your existing app is
designed as well as your post implies then I wouldn't be surprised if the
re-work required was minimal.

 

[cityrock]

Thank you rick, i now know and understand where i am heading.