locked
Splitting backend db RRS feed

  • Question

  • Hi

    My backend db is growing like anything. Is it feasible to split the backend db into two and link tables from both to front end? Would it adversely affect front end app performance when some of the tables across the two backends are used in links in forms/reports etc?

    Thanks

    Regards

    Yahya

    Tuesday, June 5, 2012 11:13 PM

Answers

  • Hi Yahya,

    If your backend db is growing that fast I strongly recommend that you move it up to an enterprise solution like SQL Server or Oracle otherwise you are just going to keep splitting the database as they continue to grow.  There are also several free smaller scale solutions you could try such as SQL Express and MySql.

    If this however is not an option then you could split the backend db and I don't think you will get much of a performance hit in your app.  Is it only a limited number of tables that are causing the growth?  If so then you could try moving the tables one at a time to another backend db and gauge the performance as you move each table.  The real problem will be if you have any relationships defined in the backend db these will need to be redefined in your client app - assuming your client app is an Access front-end.

    Regards,

    Ray

    • Proposed as answer by -suzyQ Wednesday, June 6, 2012 1:42 AM
    • Marked as answer by 许阳(无锡) Monday, June 18, 2012 5:51 AM
    Tuesday, June 5, 2012 11:25 PM

All replies

  • Hi Yahya,

    If your backend db is growing that fast I strongly recommend that you move it up to an enterprise solution like SQL Server or Oracle otherwise you are just going to keep splitting the database as they continue to grow.  There are also several free smaller scale solutions you could try such as SQL Express and MySql.

    If this however is not an option then you could split the backend db and I don't think you will get much of a performance hit in your app.  Is it only a limited number of tables that are causing the growth?  If so then you could try moving the tables one at a time to another backend db and gauge the performance as you move each table.  The real problem will be if you have any relationships defined in the backend db these will need to be redefined in your client app - assuming your client app is an Access front-end.

    Regards,

    Ray

    • Proposed as answer by -suzyQ Wednesday, June 6, 2012 1:42 AM
    • Marked as answer by 许阳(无锡) Monday, June 18, 2012 5:51 AM
    Tuesday, June 5, 2012 11:25 PM
  • I agree with Ray, but in answer to your question - you can split the backend into multiple backends.  I have an application that looks at multiple back-ends without any adverse affects (not because of size however).  There are some things to consider though.  When you use the linked table manager, only select tables from the same back-end to relink at a time or you will be selecting the backend db for every table you have selected.  Also, if you have a compact or backup routine, you will need to adjust that to back up and/or compact all back-end databases.  There are other things to consider as well - relationships don't cross databases (at least I haven't seen that) so you have to keep logical tables together to maintain their relationship integrities.


    For the benefit of others, please mark posts as answered or helpful when they answer or assist you in finding the answer. ___ "We came all this way to explore the Moon, and the most important thing is that we discovered the Earth." - Bill Anders, Apollo 8.

    Wednesday, June 6, 2012 1:42 AM
  • Good points Suzy.  If you front-end is an Access client then it is possible to define relationships between tables from different backend databases however the referential options such as "Enforce referential integrity" and "Cascade deletes" aren't available.
    Wednesday, June 6, 2012 1:55 AM
  • Could you be a bit more specific?

    From what size to what size is the back end growing over what period of time?

    Why is is growing so fast?

    If you compact the back end, does it reduce in size considerably?

    Wednesday, June 6, 2012 3:29 AM
  • Good question Alphonse,

    If the db is growing because you have that much data input then great but if it is due to bloat or duplication then...


    Chris Ward

    Wednesday, June 6, 2012 5:23 AM