SSIS poor performance pushing data to SQL Azure

Question

I have been trying to get data up to my SQL Azure database for a while.  We attempted to use SSIS, but the individual data tasks execute painfully slow (6+ min for 45K records).  Looking at SQL Azure during the push, we can see that tasks suspend (hang) for unknown reasons.  Does anyone have information on a better way to get data to SQL Azure?

Answer 1

We are working on other options for v1, but for now SSIS is your best bet.  I'll check on the perf and hang issues.  Do you have any more details?

EDIT: We'll need your server name and database name to dig more into the hangs.  You can email me this info to stan.kitsis at microsoft.

Stan

---

Program Manager, SQL Azure

Answer 2

Hi jkparker,
   Please refer SSIS performance tuning whitepaper at http://technet.microsoft.com/en-us/library/cc966529.aspx. You may want to take advantage of parallelism and buffer size to improve performance in your SSIS package. We are also working to provide BCP support in the near future. 

Windows Azure Platform Training Kit has some guidance and demos for moving data in and out of SQL Azure


Thanks,
Abihttp://www.microsoft.com/downloads/details.aspx?FamilyID=413E88F8-5966-4A83-B309-53B7B77EDF78

Answer 3

Thanks for the information Stan. 

Here are some more details....

We created simple SSIS package containing a single data flow task in control flow.  The data flow task contains an ADO Net Source to ADO Net Destination with 2 ADO.Net Connection Managers.  One ADO.Net Connection Manager is pointing at the local database and the other ADO.Net Connection Manager is pointing at the SQL Azure database. 

When the package is executed you can see the first batch of 5000+ records is transferred almost immediately.  Then there is a long 1 to 2 min pause before the next batch of records is transferred continuing this way for 6 to 8 min until all the records are transferred.  This behavior is not consistent; sometimes the pauses are smaller or longer.  In some cases the SQL Azure connection is actually forcibly closed prior to the data transfer completing (BTW this happens in SQL Server Management Studio also).  I am executing this package on a desktop machine with Quad Core Processor and 8GB of memory.  Also a test of local database to another local database completes in less than 16 seconds for the same amount of records.  A test of the same package from local server to remote network server completes in around 16 seconds.

As this is a single package, the parallel execution settings really don’t matter.  I have played with the transaction isolation level and that did not make a difference.  I am willing to forward the actual package if that helps.  Thanks in advance for the help.

Answer 4

Thanks for the info.  Can you also supply the servername/database name?

---

Program Manager, SQL Azure

Answer 5

Would you agree that the parallelism settings only apply if you have multiple data flows defined in a control flow?  If this is excluded then the only settings which can affect performance are the buffer settings (remember this is a very simple package).  According to the article those buffer settings are calculated at runtime based on estimated row size.  Since this data flow is a table to table exercise the column data types are explicit and there shouldn't be a perf hit for deriving that.  Is there anything else you can direct me to?

Answer 6

jkparker,
... curious to know what version of Visual Studio you are using.

thanks,
-Larry

Answer 7

Do a database migration with SQL Azure Migration WIzard. It may not get in your data but you will get in the tables. Go to SQL Manager and run 'insert' statements to populate with data. It is much faster than SSIS. It is way faster than SSIS. Perhaps there is way to bring in data with the Migration wizard as well. I have not tried.

For downloading SQL Azure Manager and SQL Azure Migration Wizard search internet or go to my URL where I have copied the links:

http://hodentek.blogspot.com/2009/09/two-great-tools-to-work-with-sql-azure.html

---

mysorian

Answer 8

Hi Abi,

I need to replicate the data from local SQL DB 2005 to Sql Azure. I doubt whether replication option is available in sql azure so i am trying to create an sql package for the same which will (Delete all the records and insert all the records from local db) Is there any better way of doing this in sql azure? I see only the examples to move data out of sql azure i dont see how to move data into sql azure in the demos. I would appreciate if you can get pointers on this.

Thanks,

Jaycbe

Answer 9

Jaycbe, have you looked at SQL Azure Data Sync? http://www.microsoft.com/windowsazure/developers/sqlazure/datasync/