SQL vs. Access/ Cost of Conversion

Posted on 2007-10-12
Last Modified: 2010-04-15
This question has been  asked before however could you explain the difference between SQL and Access?  My background is in Networking.  I know SQL is better but I need to convince upper management.  We are a HIPPA compliance organization.

Also, can you convert an Access Database to SQL.  We are a  non profit organization and really like the set up of an Access Database?  Besides the cost of SQL how much would it cost to convert an empty database  from Access to SQL?  What kind of hardware would you suggest for 30 simotaneous connections and a 2GB database?
Question by:lynndaly2000
    LVL 16

    Accepted Solution

    How is it your HIPPA compliant, are you refering to your Access mdb? I would definetly suggest getting something more secure like sql sever 2005, implement roles, schemas, etc...

    move on up
    LVL 14

    Assisted Solution

    Access is a desktop database, SQL server is a server database.

    There is a free version of SQL server available, but it won't support 30 simultaneous connections.

    If, you are thinking because you may have 30 users using your app simultaneously, it is highly unlikely, given a properly designed data access architecture, that you would really have 30 connections open at one time.

    There are tools available to do the upgrade from access to SQL server.

    And, for hardware, it depends on more than connections and database size, it depends on what you're doing with it, how complex and large are the SQL statements that might be running against it, how much data, how many rows/columns might you be dealing with per statement, are there a large number of complex calculations going on...

    If it were me, and I didn't know what the app was going to look like, perform like, etc. and this will be a standalone database server, I'd look at the recommended hardware requirements for SQL Server, buy a box that meets that, and make sure that it has a little room to grow if you find out it needs more horsepower, especially related to memory, if you can have more memory than the size of your entire dataset, it is pretty unlikely any decently modern CPU etc. is going to have any trouble serving your needs.
    LVL 2

    Assisted Solution

    Migrating Access to Sql is pretty straight-forward.  Below are some links:

    As for hardware for that small of an implementation any basic server should work. I would put in 4GB of RAM for future expansion, plus SQL loves RAM.  If possible buy a server with a RAID controller so you have redundancy and I would put the db and logs on seperate disks (spindles) to increase performance.
    LVL 16

    Assisted Solution

    Access 2003 supports 2GB database size and 255 concurrent user.
    Best for small web and show report only (should be a bit write).

    >how much would it cost to convert an empty database  from Access to SQL?
    Since you have bought SQL, it free :) SQL has a module of import/export data between microsoft products easily.

    If it were me, I will go with free databases e.g. MySql .. or more at
    which quite gain the same performance of SQL, but need more a bit techniq knownledge.
    LVL 56

    Assisted Solution

    by:Jim Dettman (Microsoft MVP/ EE MVE)
    <<This question has been  asked before however could you explain the difference between SQL and Access?>>

      JET is a department level database.  It's designed to be run over a LAN only.  All database processing is carried out on the client side.  The server only acts as a file sharing device.  There is no central DBMS process running things on the server.   As a result, JET lacks several features that most want; roll backs, online backups, and roll forward journaling for recovery.  For a typical read/write app, 30-40 concurrent users is about it.  JET is not a secure database and has limited capabilities for user security.

      SQL Server is server based and is true client/server.  There is one central process that controls access to the databases and processing of databases occurs on the server side (there is an expection to this with Access apps, which I will explain in a minute). It's a lot more scaleable, secure, and has a lot more features.

      So I agree with all the others that SQL Server (or some other client/server RDBMS) is the way to go.  However, while that may be true, the app is another thing.  Yes, you can take data easily and upsize to another RDBMS.  Access even has a built-in wizard for upsizing to SQL Server.

      But the application logic and design needs to be looked at.  Developing for a client/server backend requires a different mindset and approach as most of the time, your working with disconnected data.  Yes, you can move the data to SQL server (or something else) and use ODBC connections to the database and pretty much use the app as is with little effort, but to truely gain all the benefits of a client/server backend, the application will most likely need to be totaly re-written.

      For example, in a JET based query, if anything in the query is JET specific, JET will execute the query on the client side and make individual calls to the ODBC BE for each record.  What needs to be done to take full advantage of the backend is moving to pass-through queries and creation and calling of stored procedures.  By doing that, everything then executes on the server.  You also need to actually change the way you go about doing things.  For example, it's not uncommon in an Access app to bind a form directly to an entire table.  With a client/server backend, this would be a major no-no.  Instead, you'd provide some search function on the form and only fetch the data required by the user.  This would be important for an app that needs to run over a WAN.

      Though this is not overly critical to do if the number of users is small.  Moving to SQL Server would still be a smart move.  You'll gain many features and capabilities, but keep in the back of your mind that if you need to scale up or run over a WAN, the app will need to be re-written.


    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    745 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

    16 Experts available now in Live!

    Get 1:1 Help Now