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

asked 17 Jul '12, 09:12

javedrehman's gravatar image

javedrehman
256141421
accept rate: 0%

retagged 24 Jul '12, 05:27

Daz%20Liquid's gravatar image

Daz Liquid
861182338

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?

(17 Jul '12, 09:35) Martin

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

Answer: See UNLOAD SELECT BULK INSERT

permanent link

answered 17 Jul '12, 09:48

Breck%20Carter's gravatar image

Breck Carter
27.0k444614889
accept rate: 21%

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.

(17 Jul '12, 16:49) javedrehman
Replies hidden

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.

(18 Jul '12, 03:03) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×45
×29

question asked: 17 Jul '12, 09:12

question was seen: 2,645 times

last updated: 24 Jul '12, 05:27