Best way to 'Mass' migrate Access DBs to SQL Server

[Mike Shammas]

My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.

They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server in a controlled way, with minimal intervention. Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation? Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?

Any advice would be welcome.

[Tony Toews [MVP]]

Post by Mike Shammas
My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.
They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server in a controlled way, with minimal intervention. Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation? Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?

AFAIK there is no tool for automatized bulk upsizing. One question
though is do these database have just tables or other objects as well?

There is a tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm


Tony

[a a r o n . k e m p f @gmail.com [MCITP: DBA]]

I'd reccomend using something called a 'SCRIPT' to automate this

you can write a SQL statement, and run the batch via command line

you can automate anything in the world that you could possibly want to
do

I've always had to write my own automation / scripts to do stuff like
this

PS - I wouldn't trust Tony talking about migrations to SQL Server, he
tried one once.. because he's a wussy-pants canadian, he wasn't able
to complete it

Post by Mike Shammas
My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.
They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server  in a controlled way, with minimal intervention.  Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation?  Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?

AFAIK there is no tool for automatized bulk upsizing.   One question
though is do these database have just tables or other objects as well?
There is a  tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx
Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page athttp://www.granite.ab.ca/access/sqlserverupsizing.htm
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Granite Fleet Managerhttp://www.granitefleet.com/

[Patrick Jackman]

Although I haven't tried these products I am intrigued by them:
http://www.vb123.com.au/up/orders.htm

Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-
Patrick Jackman
Vancouver, BC

Post by Mike Shammas
My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.
They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server in a controlled way, with minimal intervention. Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation? Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?
Any advice would be welcome.

[a a r o n . k e m p f @gmail.com [MCITP: DBA]]

SQL Server DTS has some great tools to do this.

With SQL 2000, you can use the data import / export wizard in order to
create a DTS package, and then you can save this package as a Vbs
script

then it's easy to copy and paste it into Access Modules and add a
couple of loops

I'm pretty darn positive that I could move hundreds of Access
databases into SQL Server in my sleep

what city are you in?





On Mar 30, 7:41 am, Mike Shammas <Mike

Post by Mike Shammas
My customer is on a path to moving away from Access databases to SQL Server
(an iterim measure to allow them to use an IBM tool (InfoSphere?) to do a
fuller analysis of their data.
They need to get 100's to 1000's of user-built Access databases migrated to
SQL Server  in a controlled way, with minimal intervention.  Ignoring the
fact that data migration can't be dealt with properly this way, what is the
MS recommended route for carrying out such an operation?  Is there a standard
tool for doing this, or is a 3rd party tool available (that won't go down
well), or should be craft some control code around a simpler tool?
Any advice would be welcome.