• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

Migrating from MS Access to SQL server

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.
0
dhapp
Asked:
dhapp
  • 2
  • 2
1 Solution
 
stesomCommented:
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: http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx
0
 
dhappAuthor Commented:
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?
0
 
stesomCommented:
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.
(http://www.teratrax.com/articles/sql_server_2005_express.html)

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).
0
 
dhappAuthor Commented:
Good,
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)

SQLExp2.JPG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now