Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

new technoligy vs jet engine.

Discussion in 'Information Technology' started by cityrock, Jul 28, 2006.

  1. cityrock

    cityrock
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Travel Jet-lag Study Reveals Why Time Changes Are A Struggle Breaking News Sep 11, 2013
    Nature Mysterious Deep Sea Jets Alter Global Climate Breaking News May 20, 2011
    World French Jets Start Patrols as Libyan Rebels Urge Action Breaking News Mar 19, 2011
    Akal Takhat Jethedar - A History (by J.S. Tiwana) History of Sikhism Dec 23, 2009
    Jeth.. Gurmat Vichaar May 15, 2009

  3. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    cityrock wrote:
    > 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, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  4. cityrock

    cityrock
    Expand Collapse
    Guest

    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


    Rick Brandt wrote:
    > cityrock wrote:
    > > 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, Microsoft Access MVP
    > Email (as appropriate) to...
    > RBrandt at Hunter dot com
     
  5. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    cityrock wrote:
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  6. Guest

    Guest
    Expand Collapse
    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)

    "cityrock" <ilaygur@gmail.com> wrote in message
    news:1151864025.477845.187280@m79g2000cwm.googlegroups.com...
    > 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
    >
    >
    > Rick Brandt wrote:
    > > cityrock wrote:
    > > > 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, Microsoft Access MVP
    > > Email (as appropriate) to...
    > > RBrandt at Hunter dot com

    >
     
  7. cityrock

    cityrock
    Expand Collapse
    Guest

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

Share This Page