Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: Porting Access to SQL down the line

    I have started a new site using a CMS which can be either Access or MS SQL. For ease of install and cost factors the site is currently running as an Access dbase driven site. If traffic numbers and content grow and Access dbase blows out to, lets say 500meg will I see a major degradation in performance and will it be possible at a later data to somehow export the Access dbase data into MS SQL dbase format. I'm assuming I'd have to engage a fairly competent developer but before I get into the trap of growing a large site, I'd like confirmation that I'd be able to extract all of the data and get it into a more robust solution as and when required. phew!

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Porting Access to SQL down the line

    Upgrading to SQL Server is tricky, since even Microsoft can't convert an MS Access database to SQL Server completely with their Upsize Wizard. Views and action queries are not converted at all, but there are also smaller problems. Loops in your datamodel, for example, can't be handeld by the Upsize Wizard. Boolean fields are correctly converted to BIT fields, but the bahavior changes, since in MS Access a boolean field gets implicitely a default FALSE, but in SQL Server, it's NULL. When an application relies on such a behavior, you get errors.

    See the White Paper on ConversionTool for an overview of shortcomings.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    DoktorBlue thanks for the reply,

    Could you give me any advice on the capabilities of Access in terms of traffic, i.e would 1000 visitor sessions an hour be too much and would a 500 meg dbase be simply too big and slow to read. I'm using 500 meg as currently I have 40 articles and the access dbase is 5 meg. Your thoughts appreciated.

  4. #4
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    My biggest MS Access databases are about 100 MB, with 20 concurrent users, without any performance problems. MS Access is surprisingly strong for a PC-based application. I only encountered one point of attention: disc capacity! Ensure ALWAYS to have enough disc space available. The MS Access database grows to a certain extent, and when there is not enough disc space avaible, your database gets corrupted.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Access

    1k hits an hour...1/2 gig of data?

    And the site is growing?

    Can you say TIMBER?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by Brett Kaiser
    Access

    1k hits an hour...1/2 gig of data?

    And the site is growing?

    Can you say TIMBER?

    Ohh, just to be clear: I would also take an SQL Server solution considering the expected work load. All I was saying about Access are my experiences up to 20 users, which are pretty good.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  7. #7
    Join Date
    Mar 2004
    Posts
    4

    Unhappy

    eek!!

  8. #8
    Join Date
    Sep 2003
    Location
    Memphis
    Posts
    62
    I may get flamed for this, but Office XP does a wonderful upsizing job IMHO as compared to office 2000. I don't know what changed, but it's much much better now.

    I have a web app running on access with about 2000 users. It's NOT GOOD. Not good at all. Just use SQL server. You will thank yourself in the long run. If it's already in Access, then try the upsize with Office XP. It can't hurt to try it.

  9. #9
    Join Date
    Mar 2004
    Posts
    4
    I listened and am right in the middle of trying to get this site up on an MS SQL server as we speak. I am pulling my hair out mind!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •