Help needed on Sybase to Sql 2012 migration

Question

Post reply

Help needed on Sybase to Sql 2012 migration

Suresh Arumugam

Hall of Fame

Points: 3370
More actions
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

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

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

vijay.singh 14112 SSCommitted Points: 1589 More actions · Answer 1

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

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 2

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

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 5

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

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 6

Hi All,

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

Thanks.

Regards,
Suresh Arumugam

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 7

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.

😎

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 8

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Suresh Arumugam Hall of Fame Points: 3370 More actions · Answer 9

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