Solved

Upgrade Access to,SQL Server 2008 R2

Posted on 2014-02-16
15
292 Views
Last Modified: 2014-03-24
I need to upgrade my access database to SQL SERVER. Can anyone tell me the process?
0
Comment
Question by:Idarac
15 Comments
 
LVL 11

Expert Comment

by:Technodweeb
ID: 39862738
First questions, do you have a SQL server? Why do you need to upgrade? Is this for a web application or desktop? How many simultaneous users will you have connecting? What version of MS Access?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39862869
The first thing you need to understand is that you are not "upgrading" when you go to SQL Server.  The data is being migrated to a totally different database that may or may not have all the functionality and performance you enjoyed with MS Access.

Having said that, yes you can just export your data from MS Access into SQL Server, however if you do that, you may find that the end result is not what you anticipated.  What I would do is analyze your database structure and see how it can or cannot be used in MS SQL Server, taking advantage of the strengths found in SQL Server and mitigating the functionality that is not there and you were used to when using MS Access.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39863114
Give us some more details as to what you're trying to pull off here, and more importantly why.   If you can't provide us some details, we'll assume that this is a homework question and treat it accordingly.

As the above experts stated, an Access to SQL conversion is a big deal and not to be taken lightly.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 1

Author Comment

by:Idarac
ID: 39865658
OK fair enough this is not a homework question. Let me get some more details.
0
 
LVL 1

Author Comment

by:Idarac
ID: 39877345
Here is what I have. The client wants to get rid of Access as the database and replace it with an SQL Server database. The first phase will be replacing the the backend with SQL Server. The next phase will be replacing the Access frontend with a .Net app.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39878400
Actually the first phase is analyzing the database structure and code in MS Access to see how that translates to MS SQL Server.
0
 
LVL 1

Author Comment

by:Idarac
ID: 39918721
OK I have been through the database structure and looked at the fields I think they should be OK to go from Access to SQL Server. I have documented the code changes I will have to do but I know there will more that I missed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39919377
Then I would start by creating the tables and views in SQL Server and migrate the data.  There are a number of tools that can help you with this process, but nothing compares to doing it manually.
0
 
LVL 1

Author Comment

by:Idarac
ID: 39930047
Once my app is ready for production I need to copy the data over all at once on a weekend or at night after hours

Do you know what tools so I can look for them?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39930664
I am confused.  if it is ready for Production that means that you have already developed and tested it using SQL Server.  If that is the case than I would use Red-Gates's SQL Server Compare and SQL Server Data Compare to migrate the schema and data from your Development or Test SQL Server database to your Production SQL Server database.
0
 
LVL 1

Author Comment

by:Idarac
ID: 39946422
OK that will be helpful.

Our access app will be active while we develop our new app. It is coded in C# and we will move the data from Access to the Production SQL database. We will probably have overnight to do the migration so I am wondering what is the fastest way to do it.

Have you used SSMA for creating SQL database?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39946662
It is coded in C# and we will move the data from Access to the Production SQL database. We will probably have overnight to do the migration
Again, I am trying to understand.  Are you saying that you are developing your app using C# against an MS Access database and then want to port it directly (overnight) to Production using a SQL Server database without first testing using a SQL Server database?  If that is true, to put it bluntly, you are in for a wild ride and if your job depends on it you may want to update your resume.
0
 
LVL 1

Author Comment

by:Idarac
ID: 39950420
Sorry about the confusion. But yes that would be a wild ride wouldn't it.
As we do our development yes we will be creating the new SQL Server database. The new app will be created against the new SQL Server database.
But while we are developing the new app and the new SQL Server database our existing app using the Access database will be in production.
So when development is complete we will have the new SQL Server database complete.

We will have
1. Our new SQL Server database
2. The existing production Access database

Now we have to move the Access data into our SQL Server database.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39950946
Then I go back to the recommendation I made earlier, namely:
I would use Red-Gates's SQL Server Compare and SQL Server Data Compare to migrate the schema and data from your Development or Test SQL Server database to your Production SQL Server database.
0
 
LVL 1

Author Closing Comment

by:Idarac
ID: 39951026
Thank you
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
IIF help, YN field 7 22
SSRS Enable Remote Errors 4 26
syntax sql error 2 14
Combo Box with a control source is "locked" 10 9
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

803 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