April 28, 2011 at 9:25 am
we have a application which is in Access.
Now we are developing the application into web application by using .Net and SQL Server.
I have to move all the access tables/data into the new sql server database.
tables are not same, there are some tables which are divided into 2 in sql server database.
Could someone please me with the steps involed in data migration from access to sql server.
how should i plan about the data moving
i have to move the data from access database to sql server development database then once
testing is done then i have to move to QA and finaly to production..
so i need the steps required for data migration.
what are the steps i need to use for data mapping,creating scripts and loading data
from access to sql server
Please someone help me with this task.
April 28, 2011 at 9:30 am
you can use SSIS services and Linked Server to move database access to SQL Server
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 28, 2011 at 9:33 am
thanks for the reply
I know about the tools
but my question is how to plan.
as mentioned one table in access is divided into 2 tables in sql server.
what i want is how to plan(design document) about tables mapping in 2 different databases.
April 28, 2011 at 9:49 am
you should define or maintain a document which have ERD(Entity Relation Diagram) and mapping like primary key and foreign key relation,you have applied normalization that you will define all table mapping and purpose on access database
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 3, 2011 at 7:03 am
Let your migration script become your design document. Create a .sql script that creates the database, creates the tables, indexes, views, procedures, everything . Use something like OPENROWSET to grab the data from the Access database and fill the tables in the SS database. Place comments in your script that define the plan /* Step 1: , Step 2:, Step 3: ...*/
Now whenever you run the migration script, you can judge the output. The script evolves with the schema changes you and your team decide to make. Test that database; change the script; delete the database and re-run. Place this script in your source control and check in the iterations as it changes. Even though it will be used only once, it will be important to the success of your new project.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 3, 2011 at 7:23 am
I like the comment idea. I personnally put those in prints so that I can follow along as the script executes. Also you have more logged info when it fails.
Never used SSIS so I can't preach for / against it.
May 3, 2011 at 7:39 am
easiest way without having to learn SSIS is to just use SQL Server Migration Assistant for Access v4.2. This has a wizard to walk you through migrating your access tables to SQL Server. Once in SQL Server, you can then write/run your scripts to normalize the data.
May 3, 2011 at 7:42 am
calvo (5/3/2011)
easiest way without having to learn SSIS is to just use SQL Server Migration Assistant for Access v4.2. This has a wizard to walk you through migrating your access tables to SQL Server. Once in SQL Server, you can then write/run your scripts to normalize the data.
What's the difference, benefit?
The code to import is simply select * into #tmp FROM openrowset() blah
[E]TL.
Move on.
I've done that once and never had any issues. (I'm not being thick, I'm just wondering how much faster / easier it is to use that tool).
May 3, 2011 at 7:51 am
You're right. I should revise my post to say "an easy way...".
The difference is a gui that could handle converstions if necessary.
The benefit would be that it is easy and uses a gui which also provides some minimal reporting on the process.
I haven't done a lot of Access-->SQL Server migrations, but that's an easy way to get it done for someone who doesn't sound too sure about scripting.
May 3, 2011 at 7:58 am
calvo (5/3/2011)
You're right. I should revise my post to say "an easy way...".The difference is a gui that could handle converstions if necessary.
The benefit would be that it is easy and uses a gui which also provides some minimal reporting on the process.
I haven't done a lot of Access-->SQL Server migrations, but that's an easy way to get it done for someone who doesn't sound too sure about scripting.
Ok. I would have expected a "good" too to also recreate the indexes, views, PK / FK constraints, defaults, etc. On top of conversions of course.
Do it do any of those?
If not, for me anyways, it's just as fast to do select intos #tmp while I loop in the list of tables in the system table of the db. From there, the real work begins! Have you ever seen a migration where the main goal was not refactoring of the app? I've never seen one upgrade just to move from MS to SQL for a datastore.
May 3, 2011 at 8:31 am
It does transfer the keys and indexes as well. I think it converts Access "queries" to views. The conversions can be a little screwy sometimes because some data types just don't transfer.
I've done a migration where the goal wasn't refactoring. The reason for the migration was because the database was just getting too big (many attachments per recod) and couldn't be encrypted anymore. Now, because of the differences in the two databases, I ended up normalizing some of it. Not elegant or efficient by any means, but it's a temporary "zomg make it work!" solution.
May 3, 2011 at 9:53 am
Ninja's_RGR'us (5/3/2011)
I like the comment idea. I personnally put those in prints so that I can follow along as the script executes. Also you have more logged info when it fails.Never used SSIS so I can't preach for / against it.
Yep, exactly. I put thoughts and design notes into the comments, but use the print statement to log the status of the output, like so:
--step 2
--make a list of aux tables to drop, with exceptions for tables that have been used.
PRINT 'creating step 2 list...aux tables and views'
If your processes take a while, Lucky9, and you want to see the progress as it happens, use this instead
RAISERROR('Create & import wiz tables',10,1,0) WITH NOWAIT
I haven't used SSIS or SSMS for a migration. I like being in control of the whole process.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 3, 2011 at 9:55 am
How can you NOT use SSMS?
Do you mean SSMS import wizard?
May 3, 2011 at 10:20 am
Ninja's_RGR'us (5/3/2011)
How can you NOT use SSMS?Do you mean SSMS import wizard?
HAHA! Acronym confusion levels maxed! By SSMS, I meant SQL Server Migration Assistant for Access that calvo had referenced.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
May 3, 2011 at 10:25 am
toddasd (5/3/2011)
Ninja's_RGR'us (5/3/2011)
How can you NOT use SSMS?Do you mean SSMS import wizard?
HAHA! Acronym confusion levels maxed! By SSMS, I meant SQL Server Migration Assistant for Access that calvo had referenced.
So SSMAA?? :w00t:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply