Migrating from MS Access to SQL server

Posted on 2008-11-09
Last Modified: 2012-05-05
I need some coaching in how to get started with SQL.

I would like to migrate an Ms Access database to SQL for hosting on the web.
The application currently works with the Ms Access backend but I've been told that it will be sturdier if the tables are converted to SQL.
I don't know where to start.
I have looked on the web and know that this is an expensive product, but there seem to be a variety of versions from Express which is free but has limitation; I can't tell from what I have read whether express will work for my application.
I'm looking for someone who will have an exchange of postings so that I can figure out what I need to get and where to go from there.
Question by:dhapp
    LVL 3

    Expert Comment

    The SQL server is naturally a good replacement to access, it's easy to import the access database into the SQL server using DTS or SSIS and it is naturally much more scalable when large amounts of requests and data is involved.

    There is one problem though, you are talking about SQL Express, which comes with none of the above mentioned import/export functionality, making the job of migrating potentially alot more difficult.

    You should look at this article:

    Author Comment

    I looked at the article and what I think that I got from it is that the data conversion is possible.
    What I have to weight is the difficulty that you mention versus the price of SQL standard version (approx $1400).  Why isn't there something more reasonably priced?
    I'm not sure if this is relevant:  the app that I have built will be hosted on the web and may get a a large number of hits in the first few minutes that it is available (perhaps 100 hits).
    If I were to get SQL Express and could import the data would the resulting backend support the stress of the many hits?
    LVL 3

    Accepted Solution

    SQL Express is great, I use it all the time for small business applications and I've also used it occasionally as a web backend. Basically, it performs as well as the standard edition up until a certain point, when the sql express limitations kick in. These being:

    1. Max 4 GB individual database size.
    2. It will only utilize 1 GB of memory.
    3. It will only utilize 1 processor.

    For any website/app I'd say SQL Express will be more than capable, and in any case you will be much better off than with Access as the backend. Wether the above limitations will prove a bottleneck depends on how dataintensive your webapplication is, but it is very easy to upgrade SQL Express to standard or enterprise edition later on if necessary.

    To answer your last question, regarding migrating your access database without the import tools of SQL server standard edition: Again it depends on how complex your access database is, but I'm sure I'd save the money and code the conversion myself. From what I've read, Access might even be able to do it for you (bottom of the previous article I linked to).

    Author Comment

    So I have downloaded SQL Express and am trying to install it.
    I found the SQL Server Installation Center and clicked on the System Configuration Checker with shows a dialogue box to Browse For Folder
    I can't figure out which folder to choose.  I have tried many different folders and continue to get the Error box that indicates "C:\WhateverFolderIsSelected is not a valid installation folder."
    Can you help?  (see attached graphics)


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now