locked
Migrate from Access to SQL Server in steps. First data and structure - then fixing issues - then the data only? RRS feed

  • Question

  • Hi

    I am migrating an Access (accdb) database to SQL Server. Seems to work quite well. But there is some issues to handle.
    Like not supported data types (Access Atachment etc), indexing etc to get best performance. I will handle it. But. In the meantime users update the running Access database with new data in their dally work

    Is it possible migrate "data only" after fiixing the the sql server issues?
    Or must I plan a stop of using the Access database during migrating?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Sunday, June 19, 2016 1:38 PM

All replies

  • Hi ForssPeterNova,

    As users update the Access database with new data daily, you can consider to schedule a SSIS package to migrate data from Access database to SQL Server. For more details, please review the following  similar blogs.

    Simple step by step process to import MS Access data into SQL Server using SSIS
    SQL SERVER – Running SSIS Package in Scheduled Job

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Monday, June 20, 2016 7:08 AM
  • Thanks Lydia

    I am running SQL Server 2016 Developers edition 64 bit.
    And for Access its MS Access 2013 32 bit.

    What is the right SSIS for me (I found plenty of links but am not sure of whats the right one in my case) Can you give me a link?

    When I run the SSISDB Upgrade Wizard, found on my computer, I get this message "

    TITLE: SQL Server Integration Services
    ------------------------------
    Invalid object name 'catalog.catalog_properties'. (Microsoft SQL Server, Error: 208)
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1601&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476
    ------------------------------
    BUTTONS:

    OK
    ------------------------------


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Monday, June 20, 2016 5:37 PM
  • Are you using the SQL Server Migration Assistant for Access? It will migrate your data and replace your Access tables with linked tables to SQL Server.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 20, 2016 7:03 PM
  • Hi David

    Yes I use SSMA 6.1 and it works fine. I use it to make a copy of the Access BE database in SQL Server. Problem is that some data types must change. There is no Attachment is SQL Server.
    When I did the migration with SSMA 6.1 and thereafter linked the, now on SQL Server, existing tables to my FE Access database via an ODBC. I found that SQL Server as BE was far more slower in performance compared to the original FE/BE "all in Access database solution".
    Think its indexing of tables I have to deal with.
    But while doing the needed corrections in the BE on SQL Server and testing it the users of the Access application has "produced" a lot more data. That new data I want to add to my new SQL BE when I have found the best setup for indexing etc.

    So what I am looking for is a way to transfer (migrate) data only from the old, but with new data updated Access BE to the new and ready SQL BE.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00



    Monday, June 20, 2016 7:59 PM
  • Hi ForssPeterNova,

    For SSIS issues, please post the question in SQL Server Integration Services forum to get dedicated support.

    Thanks,
    Lydia Zhang

    Lydia Zhang
    TechNet Community Support



    Tuesday, June 21, 2016 7:46 AM
  • The simplest solution is to save a script of all the post-migration changes you make to the SQL Server database.  Then you can re-run the data migration to get the new data and apply the script to apply the changes.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 21, 2016 12:01 PM
  • Hi David

    I am new to SQL Server and SSMA.
    Can you please give me a hint or example on a script of the kind you suggest?
    That would be of great help to me.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, June 21, 2016 7:34 PM
  • What are the "the needed corrections in the BE on SQL Server"? 

    You can generate a change script to apply to the converted database using SQL Server Data Tools.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, June 22, 2016 1:45 PM
  • Hi David

    In Access I use data types like:

    Attachment
    Multivalue field
    Hyperlink

    SSMA 6.1 says:
    A2SS0070: SQL Server Migration Assistant for Access Error message: Datatype is not supported (43) Estimated manual conversion time: 21.5 hr(s)


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Thursday, June 23, 2016 5:13 AM