none
Large amount of data from files to SQL Azure.

    Question

  •  

    I have a large amount of data locally in files. I created a WPF application to parse this data into datastructures: City, TimeZone, Rule, Leap. I need to upload 8,000,000 entities, on average 32,000 entities at a time, with a maximum of about 1,200,000 in one time. I attempted to use LINQ to Sql to upload this data to Azure, I learned the hard way that LINQ is WAY too slow for this. I then attempted to use SqlBulkCopy to do the same. It's a little faster, but I think my connection is being throttled or something because I get a lot of SqlExceptions:

    Yes, my code does implement retries, I Have used up to 5 retries on SqlBulkInserts when I get one of these exceptions. At first I thought this may be due to me uploading too many at a time and Azure throttling my connection or employing some sort of ddos defense, but I allowed my program to only submit 20,000 - 25,000 entities with each SqlBulkCopy, and I got even more errors! A lot more!

    My questions:
    How can I fix these error messages/throttling issue?

    If not then in what other form could I more efficiently load this data into a SQL Azure DB?

    Before you direct me here:
    http://blogs.msdn.com/b/sqlcat/archive/2010/07/30/loading-data-to-sql-azure-the-fast-way.aspx

    Could I possibly load it all to a local database (.mdf) and then upload the DB to Azure through the control panel somehow?

    I considered using BCP, but that would require me to install SQL Server 2008R2 on my laptop, upload the data to a DB there, and then do a BulkCopy (BCP) from there to Azure, possibly yielding the same throttling results as using the SqlBulkCopy CLR class.

    I also considered using SSMS, but as specified in the link: "not recommended for large data uploads."
    Note: I am looking for the best solution in terms of repeatability (I will need to do this every couple of months or so), efficiency, and reliability (I would like not to receive 50 SqlExceptions / connection throttled :P ).

    I have changed my project around so many times to compensate for all of these issues, and I really would like this project to be over.


    -Francisco Aguilera University of Washington Student | Prospective Computer Science Major | Microsoft Student Partner http://students.washington.edu/falven | http://www.facebook.com/falven


    • Edited by falven Tuesday, August 14, 2012 7:32 AM
    Tuesday, August 14, 2012 7:03 AM

Answers

  • Hi falven,

    It could be that you are facing throttling issues, indeed. There are also connection constraints in Windows Azure SQL Database that you should be familiar with.

    Anyways, try to give it a shot with this app: SQL Azure Backup, from redgate. It is now discontinued, but it should still work fine.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Tuesday, August 14, 2012 7:48 AM
  • Why dont you just dump the data in to some local DB and sync it through azure sync?

    any thoughts on that?



    Please mark the replies as Answered if they help and Vote if you found them helpful.

    Hello again, Veerendra,

    Thanks for the suggestion.

    Does this require me to install SQL Server on my machine, or can I just provide the Database .mdf?

    Thanks,


    -Francisco Aguilera University of Washington Student | Prospective Computer Science Major | Microsoft Student Partner http://students.washington.edu/falven | http://www.facebook.com/falven




    Hi falven,

    You can´t provide  *.mdf files. You would have to load it first to SQL Server first. You could use SQL Server 2012 Express LocalDB. And then make use of the SQL Azure Data Sync service...

    Or if you don´t want to go through the setup/configuration process of this service... There is a simpler way... By using the tool i mentioned: SQL Azure Backup (free) - Redgate.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Wednesday, August 15, 2012 9:30 AM

All replies

  • Hi falven,

    It could be that you are facing throttling issues, indeed. There are also connection constraints in Windows Azure SQL Database that you should be familiar with.

    Anyways, try to give it a shot with this app: SQL Azure Backup, from redgate. It is now discontinued, but it should still work fine.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Tuesday, August 14, 2012 7:48 AM
  • Why dont you just dump the data in to some local DB and sync it through azure sync?

    any thoughts on that?



    Please mark the replies as Answered if they help and Vote if you found them helpful.

    Tuesday, August 14, 2012 12:42 PM
  • Why dont you just dump the data in to some local DB and sync it through azure sync?

    any thoughts on that?



    Please mark the replies as Answered if they help and Vote if you found them helpful.

    Hello again, Veerendra,

    Thanks for the suggestion.

    Does this require me to install SQL Server on my machine, or can I just provide the Database .mdf?

    Thanks,


    -Francisco Aguilera University of Washington Student | Prospective Computer Science Major | Microsoft Student Partner http://students.washington.edu/falven | http://www.facebook.com/falven



    • Edited by falven Tuesday, August 14, 2012 8:41 PM
    Tuesday, August 14, 2012 8:09 PM
  • Why dont you just dump the data in to some local DB and sync it through azure sync?

    any thoughts on that?



    Please mark the replies as Answered if they help and Vote if you found them helpful.

    Hello again, Veerendra,

    Thanks for the suggestion.

    Does this require me to install SQL Server on my machine, or can I just provide the Database .mdf?

    Thanks,


    -Francisco Aguilera University of Washington Student | Prospective Computer Science Major | Microsoft Student Partner http://students.washington.edu/falven | http://www.facebook.com/falven




    Hi falven,

    You can´t provide  *.mdf files. You would have to load it first to SQL Server first. You could use SQL Server 2012 Express LocalDB. And then make use of the SQL Azure Data Sync service...

    Or if you don´t want to go through the setup/configuration process of this service... There is a simpler way... By using the tool i mentioned: SQL Azure Backup (free) - Redgate.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Wednesday, August 15, 2012 9:30 AM
  • Hi - Is this a "one time" process?

    if yes - Then you can load the data into your local database and then use the data export feature with SQL Studio client tools to upload the data. Or generate script with insert statements and run it on Azure instance

    If this is not a one time process - then you can probably try inserting smaller chunks of data say 2-5K.


    MkMahesh

    Monday, August 20, 2012 8:47 PM