locked
Uploading database to Azure is so slow; how do the "big boys" do it? RRS feed

  • Question

  • I have a local SQL Server database that is approximately 1.5 GB in size. I was able to successfully remove all extended properties from the database and other objects that are unsupported in SQL Azure. I was then able to successfully "Deploy Database to Windows Azure SQL Database" using SSMS. However, the process took somewhere between 12 and 21 hours. I don't know the exact length of time as I went to bed at about the 12 hour mark.

    There is one table in the database with about 1.25 million rows in it (and that seems to be where the majority of time was spent), and many of the other tables have tens of thousands of rows in them. This is a fairly large database considering the size of my client, but it's certainly not a huge database in the overall scheme of things.

    The copy that I did was just for test purposes, to make sure it would work. I'm now at the point where I'm going to be copying the actual database to Azure soon. If the process took just a couple hours, I'd do it at night and switch my client over to the Azure database the next morning. However, this process takes almost an entire day, and they can't be without their database for a whole day (even on the weekend). How do people generally accomplish something like this?

    Monday, May 25, 2015 2:45 PM

Answers

  • I hear you laud and clear :-) 

    So I will not give you many options.

    The answer for your question is, there isn't any service provided by Microsoft for the miraculous reduction in the time to migrate your on-premise database.

    * However there is a preview (NOT RECOMMENDED TO PRODUCTION) feature called Data Sync Service which syncs database between On-premise and Cloud SQL Database. 

    http://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started-sql-data-sync/ 

    Note: However the support for DSS is very limited. 

    * Database migration Wizard is another tool which would automatically perform all the necessary changes with respect to schema and structure (eg: 2 naming syntax)

    * If you export the database to bacpac and manually copy the database to Azure storage, the time taken to import the database into an Azure SQL database will be lesser. (In place of 12-21 hours, you'd spend 6-7 hours roughly; as the method you're trying is keeping a temporary copy of the bacpac locally, then performs the upload and then import).

    I hope it helps!

    Regards,

    Karthik

    Monday, May 25, 2015 8:02 PM

All replies

  • Hi,

    Thank you for your question.

    I am trying to involve someone familiar with this topic to further look at this issue. In the meantime, please have a check on the below link and check if it helps.

    http://azure.microsoft.com/en-gb/documentation/articles/sql-database-migrate-ssms/

    Regards,

    Mekh.

    Monday, May 25, 2015 5:07 PM
  • Thank you, Mekh. I'm doing exactly what is described in the first part of that article. I'll try doing it the way the second part of the article describes, though I don't expect to see any difference.

    My issue isn't so much that it takes 10 or 20 hours to run. My issue is that by the time it has finished running, the source database on the local server will have changed substantially. The only "solution" I see to this is to run some kind of data compare utility repeatedly against the source and target, incrementally copying changes to the target database until it has settled down to where I can take it offline for an hour or so - long enough to do the final data comparison and change my application to point to Azure rather than the local server. However, that doesn't seem like an elegant solution.

    Monday, May 25, 2015 5:33 PM
  • I hear you laud and clear :-) 

    So I will not give you many options.

    The answer for your question is, there isn't any service provided by Microsoft for the miraculous reduction in the time to migrate your on-premise database.

    * However there is a preview (NOT RECOMMENDED TO PRODUCTION) feature called Data Sync Service which syncs database between On-premise and Cloud SQL Database. 

    http://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started-sql-data-sync/ 

    Note: However the support for DSS is very limited. 

    * Database migration Wizard is another tool which would automatically perform all the necessary changes with respect to schema and structure (eg: 2 naming syntax)

    * If you export the database to bacpac and manually copy the database to Azure storage, the time taken to import the database into an Azure SQL database will be lesser. (In place of 12-21 hours, you'd spend 6-7 hours roughly; as the method you're trying is keeping a temporary copy of the bacpac locally, then performs the upload and then import).

    I hope it helps!

    Regards,

    Karthik

    Monday, May 25, 2015 8:02 PM
  • Thank you :-) I'm going to try the BACPAC option copied to Azure Storage and see how that performs for me.
    Monday, May 25, 2015 11:36 PM
  • Good morning, Karthik,

    Going the "dacpac to Azure Storage" route made all the difference in the world! The entire time to create and upload the dacpac to Azure Storage, and then restore the database from Azure Storage, took just about exactly an hour.

    Thank you so much for the suggestion :-)

    -Eric

    Tuesday, May 26, 2015 1:12 PM