sql anywhere to sql server migration

[Former Member]

We are migrating our sql anywhere database to sql server for one of our apps. One of my co-worker has written scripts that basically pull data into sql server via sqla linked server using select into statements. The database we are migrating is huge database, for example one of the main tables has over 28 million rows (this table alone takes almost 5 hours to migrate) We cannot use bcp/bul copy because of the format issues between sqla and sql server. My co-worker thinks that reason the migration is taking as long as it is because sqla is not responding fast enough to sql server request to pull the data. Has any one done this before and is there something we can do on sqla end to speed up the process at least from sqla side. Thanks

[MCMartin]

28 mio rows in 5 hours => 1555 rows per second, so you are migrating 1.5 rows per millisecond. I don't know the size of your average row, but with these numbers maybe enlarging the network packets might help. By the way, is network involved at all?

[Breck_Carter]

Question: How do I implement high-speed automated data transfer from SQL Anywhere to Microsoft SQL Server?

Answer: See UNLOAD SELECT BULK INSERT

[Former Member]

thank you to all, I think Breck's solution is probably the best solution since it uses both unload and bulk insert. We are planning to give it a shot if time allows since we have to do the migration this weekend, and yes netwrok is involved.

[VolkerBarth]

What SA version are you using? I'm asking as Breck's solution makes use of the optimized proxy table support in V12.

I share his experience that remote access from SA to MS SQL (via proxy tables and the like) is (much) easier to use and more performant than the opposite Linked Server approach from MS SQL to SA.