Post reply

Help needed on Sybase to Sql 2012 migration

  • June 11, 2014 at 1:56 am

    #308986

    Hi All,

    I have recently joined to Sybase to sql server 2012 migration team. Sybase source has close to 105 tables and data is not that huge. Sybase to SQL server schema migration was already done by the team using SSMA before I joined and now the data migration process is in starting phase.

    There are multiple options in discussion (BCP, SSIS, SSMA etc) to migrate data. However, we could not decide the best approach. There are limitations like the dev team do not have access to production environment and hence the deployment team should be given all information to execute the migration steps by dev team.

    If we take SSMA as the option, then SSMA should be installed in all UAT, SIT and production servers which we do not want to do. Also, dev team have to provide all details to Deployment team to install & handle SSMA in UAT, SIT and prod servers which we would like to avoid.

    If we take SSIS as the option then it's just a package that deployment team have to deploy in different environments and run as such. However, dev team is thinking that we need to do a manual mapping of columns for all 105 tables in data flow tasks which would take time.

    Not much explored about using BCP for migrating data.

    Could any one of you please suggest us best option (for migrating data) considering that different team (other than dev team) would be handling the deployments and executions?

    Thanks.

    Regards,

    Suresh

    Regards,
    Suresh Arumugam

  • June 11, 2014 at 2:28 am

    #1720662

    Hi Suresh,

    BCP is the best and fastest way to migrate data. BCP sybase tables into .dat files and then use bulk insert.

    Regards,

    Vijay

  • June 11, 2014 at 5:20 am

    #1720708

    Thanks Vijay. Let me work with the team on your points and get back to in case of any queries.

    Regards,

    Suresh

    Regards,
    Suresh Arumugam

  • June 12, 2014 at 3:18 am

    #1720962

    Hi Vijay,

    The team is thinking of avoiding any intermediate files like CSV etc which can not be avoided when using BCP for data migration. Let me know your thoughts also.

    Also, how about using SSMS Import wizard which will create SSIS package automatically for data migration. Any drawabacks in using Import/Export wizard? Basically we wanted to minimize the manual work of column mapping and data conversion etc. Please suggest.

    Thanks.

    Regards,
    Suresh Arumugam

  • June 12, 2014 at 3:43 am

    #1720977

    The Import/Export wizard can work, but if you hit issues where you need to manually intervene it can be a pain. I'd say the best bet would be to build SSIS packages by hand. Or, at least plan on building some by hand for the areas where there is no simple conversion between Sybase & SQL Server.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 12, 2014 at 5:06 am

    #1720999

    Thanks Grant for your valuable comment.

    SO, do you mean that even if there are 500 tables in Sybase, we need to manually add source and destination data flow tasks in SSIS for each table and have the column mappings and data conversions (if needed) done manually? Please confirm.

    Thanks in advance.

    Regards,
    Suresh Arumugam

  • June 12, 2014 at 10:54 pm

    #1721313

    Hi All,

    Can any one please share your experience or thoughts for my above query?

    Thanks.

    Regards,
    Suresh Arumugam

  • June 12, 2014 at 11:31 pm

    #1721316

    Suresh Arumugam (6/12/2014)

    Hi All,

    Can any one please share your experience or thoughts for my above query?

    Thanks.

    My 2 cent on this, as this is a one-off exercise, let the wizard do the manual work. Use the import wizard and safe to a package. Import into a project and run it from there, gives you an opportunity to tweak and correct as needed.

    😎

  • June 13, 2014 at 3:48 am

    #1721379

    Suresh Arumugam (6/12/2014)

    Thanks Grant for your valuable comment.

    SO, do you mean that even if there are 500 tables in Sybase, we need to manually add source and destination data flow tasks in SSIS for each table and have the column mappings and data conversions (if needed) done manually? Please confirm.

    Thanks in advance.

    Yeah, pretty much. Same thing that Eirikur said. I don't trust the wizard, and, usually, it fails on the more complex parts of the data structure. But, if you assume you're going to have to do SSIS work, the basics can be handled by the wizard.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 16, 2014 at 4:43 am

    #1721893

    Thanks All.

    We are trying to create migration package by using import wizard in SSMS. I am yet to explore the possibilities of editing & deploying the created packages in different environments (SIT, UAT) and to test it.

    Any thoughts if the import wizard created packages can be configured using config files?

    Thanks.

    Regards,
    Suresh Arumugam

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply