locked
Several questions about Azure and bacpacs RRS feed

  • Question

  • We are running production deployments on Azure and we are using bacpac exports as our disaster recovery strategy. I have several questions relative to this.

    According to a technet article (http://social.technet.microsoft.com/wiki/contents/articles/2639.how-to-use-data-tier-application-import-and-export-with-sql-azure-en-us.aspx), “The export file does not have a transaction log or historical data. The export file simply contains the contents of a SELECT * for any given table and is not transactionally consistent by itself.” Furthermore, the article says that “You may choose to provide transactional consistency for an export operation by creating a database copy on SQL Azure and then exporting from the copy.”

    Question #1: When doing an Export in the Azure Management Portal, is the resulting bacpac transactionally consistent? It being consistent is crucial for us since bacpacs are our only way of handling disaster recovery.

    I have recently installed SQL Server 2012 on my development machine and I’ve tried importing a few bacpacs that were exported from Azure (targeting a SQL Server 2008 instance, but using SQL Server Management Studio 2012). While smaller bacpacs imported successfully, a large bacpac (representing a 10GB database) did not finish even after 19 hours. It seems stuck importing one table (not very big) and there is nothing locked in the DB (select * from sys.dm_tran_locks L inner join sys.dm_exec_sessions S on S.session_id = L.request_session_id where request_type = 'WAIT' yields nothing).

    Question #2: What could be wrong? Should I attempt to import to SQL Server 2012 instance instead?

    I’ve noticed an issue with SQL Server Management Studio 2012 when trying to restore importing multiple bacpacs one after another – it will give a System.OutOfMemory exception. Closing SSMS and re-starting it fixes the problem.

    Question #3: Is this a known bug?

    When running the “Import Data-tier Application” wizard, it displays progress. However, once it’s done, there is no indication of how much time has elapsed.

    Question #4: Is the information about the elapsed time available anywhere? Are there DAC import logs?

    When running the “Import Data-tier Application” wizard, I can press the “Cancel” button – it will prompt me “The wizard has not finished, and all information you have entered will be lost. Are you sure you want to cancel?” However, if I close the SQL Server Management Studio, the dialog will be dismissed without any warning.

    Question #5: Is this a known issue/bug? What happened to my import-in-progress? Can it be resumed?

    When the data in the bacpac is being imported, it’s being done with bulk insert statements. Are constraints disabled when doing this? We have a highly structured database with lots of constraints and triggers and the import of even a tiny database seems very slow (as compared to restore from a backup).

    Question #6: Is there something that can be done to ensure the fastest possible data import?

    Finally, a bacpac is simply a zip archive of a few XML files and JSON files containing the data.

    Question #7: Are there any APIs to manipulate the bacpacs? Interesting scenarios could include partial imports (import data only, only import some data, resume data import).

    Thanks!

    David.

    • Moved by amber zhang Monday, May 21, 2012 2:33 AM (From:SQL Server Application and Multi-Server Management)
    Friday, May 18, 2012 5:44 PM

Answers

  • I just finished a blog entry on DAC and BACPACs here: http://www.sqlskills.com/BLOGS/BOBB/post/DACFx-30-Import-and-Export-with-BACPACs.aspx as part of a 4-part series on DACFx 3.0. It might answer some of your questions. If you believe that you’ve found some bugs in SSMS 2012, the place to report them (or check to see if they are known is http://connect.microsoft.com. There are some third-party tools that do SQL Azure backup (Redgate and others). If you're not happy with BACPACs, you might look into them as well.
    Hope this helps,
    Cheers,
    Bob
    "DavidAir" wrote in message news:eff3908e-1f9b-48fe-8a99-5df2b01fa85f...

    We are running production deployments on Azure and we are using bacpac exports as our disaster recovery strategy. I have several questions relative to this.

    According to a technet article (http://social.technet.microsoft.com/wiki/contents/articles/2639.how-to-use-data-tier-application-import-and-export-with-sql-azure-en-us.aspx), “The export file does not have a transaction log or historical data. The export file simply contains the contents of a SELECT * for any given table and is not transactionally consistent by itself.” Furthermore, the article says that “You may choose to provide transactional consistency for an export operation by creating a database copy on SQL Azure and then exporting from the copy.”

    Question #1: When doing an Export in the Azure Management Portal, is the resulting bacpac transactionally consistent? It being consistent is crucial for us since bacpacs are our only way of handling disaster recovery.

    I have recently installed SQL Server 2012 on my development machine and I’ve tried importing a few bacpacs that were exported from Azure (targeting a SQL Server 2008 instance, but using SQL Server Management Studio 2012). While smaller bacpacs imported successfully, a large bacpac (representing a 10GB database) did not finish even after 19 hours. It seems stuck importing one table (not very big) and there is nothing locked in the DB (select * from sys.dm_tran_locks L inner join sys.dm_exec_sessions S on S.session_id = L.request_session_id where request_type = 'WAIT' yields nothing).

    Question #2: What could be wrong? Should I attempt to import to SQL Server 2012 instance instead?

    I’ve noticed an issue with SQL Server Management Studio 2012 when trying to restore importing multiple bacpacs one after another – it will give a System.OutOfMemory exception. Closing SSMS and re-starting it fixes the problem.

    Question #3: Is this a known bug?

    When running the “Import Data-tier Application” wizard, it displays progress. However, once it’s done, there is no indication of how much time has elapsed.

    Question #4: Is the information about the elapsed time available anywhere? Are there DAC import logs?

    When running the “Import Data-tier Application” wizard, I can press the “Cancel” button – it will prompt me “The wizard has not finished, and all information you have entered will be lost. Are you sure you want to cancel?” However, if I close the SQL Server Management Studio, the dialog will be dismissed without any warning.

    Question #5: Is this a known issue/bug? What happened to my import-in-progress? Can it be resumed?

    When the data in the bacpac is being imported, it’s being done with bulk insert statements. Are constraints disabled when doing this? We have a highly structured database with lots of constraints and triggers and the import of even a tiny database seems very slow (as compared to restore from a backup).

    Question #6: Is there something that can be done to ensure the fastest possible data import?

    Finally, a bacpac is simply a zip archive of a few XML files and JSON files containing the data.

    Question #7: Are there any APIs to manipulate the bacpacs? Interesting scenarios could include partial imports (import data only, only import some data, resume data import).

    Thanks!

    David.


    Tuesday, May 22, 2012 6:12 PM
  • I agree with you. It might be useful to look at third party solutions [note: I don’t work for or represent any third parties], as see if any of them match your needs more closely.
     
    The nice thing about BACPAC format is that it’s database version independent, but Export/Import is not Backup/Restore.
     
    Cheers, Bob
    • Marked as answer by amber zhang Friday, May 25, 2012 3:17 AM
    Tuesday, May 22, 2012 11:25 PM

All replies

  • Hi DavidAir,

    Regarding to your description, seems all questions about export bacpac from SQL Azure to SQL Server instance. Of course, you can export to SQL Server 2012 instead of SQL Server 2008 if you want.

    In order to move data between SQL Servers, and if one of them is SQL Azure you have couple of options, you can try one of them instead of one you are using as below:
     1.SQL Azure Data Sync 

     2.Using SSIS 

     3.Write your own code that will move data using, most probably SqlBulkCopy class. 

     4.If you would like to just copy all the data, you could also use SQL Azure Migration Wizard - you can omit the option for coping the schema, and let it just copy the data.

    Hope this would be useful for you.

    Regards, Amber zhang

    Tuesday, May 22, 2012 5:40 AM
  • Dear Amber Zhang,

    Thank you very much for your answers! I would like to emphasize, however, that our most important scenario is to obtain reliable, transactionally-consistent backups of our customer production databases (Question #1). Once this is established, a secondary objective is to be able to restore the bacpac locally. I understand that there are other technologies available for bringing data from Azure to our local servers, but I want to stick with the Data-Tier Application technology because it requires the minimal effort on our end - we already create bacpacs as part of our backup process, why invest time in creating SSIS or setting up SQL Azure Data Sync?

    The bacpac technology is fine - we would just like to work around its bugs (such as a hanging import, see Question #2).

    So once again, Question #1 is critical for us, and Question #2 is fairly important.

    Regards,

    David.

    Tuesday, May 22, 2012 3:59 PM
  • I just finished a blog entry on DAC and BACPACs here: http://www.sqlskills.com/BLOGS/BOBB/post/DACFx-30-Import-and-Export-with-BACPACs.aspx as part of a 4-part series on DACFx 3.0. It might answer some of your questions. If you believe that you’ve found some bugs in SSMS 2012, the place to report them (or check to see if they are known is http://connect.microsoft.com. There are some third-party tools that do SQL Azure backup (Redgate and others). If you're not happy with BACPACs, you might look into them as well.
    Hope this helps,
    Cheers,
    Bob
    "DavidAir" wrote in message news:eff3908e-1f9b-48fe-8a99-5df2b01fa85f...

    We are running production deployments on Azure and we are using bacpac exports as our disaster recovery strategy. I have several questions relative to this.

    According to a technet article (http://social.technet.microsoft.com/wiki/contents/articles/2639.how-to-use-data-tier-application-import-and-export-with-sql-azure-en-us.aspx), “The export file does not have a transaction log or historical data. The export file simply contains the contents of a SELECT * for any given table and is not transactionally consistent by itself.” Furthermore, the article says that “You may choose to provide transactional consistency for an export operation by creating a database copy on SQL Azure and then exporting from the copy.”

    Question #1: When doing an Export in the Azure Management Portal, is the resulting bacpac transactionally consistent? It being consistent is crucial for us since bacpacs are our only way of handling disaster recovery.

    I have recently installed SQL Server 2012 on my development machine and I’ve tried importing a few bacpacs that were exported from Azure (targeting a SQL Server 2008 instance, but using SQL Server Management Studio 2012). While smaller bacpacs imported successfully, a large bacpac (representing a 10GB database) did not finish even after 19 hours. It seems stuck importing one table (not very big) and there is nothing locked in the DB (select * from sys.dm_tran_locks L inner join sys.dm_exec_sessions S on S.session_id = L.request_session_id where request_type = 'WAIT' yields nothing).

    Question #2: What could be wrong? Should I attempt to import to SQL Server 2012 instance instead?

    I’ve noticed an issue with SQL Server Management Studio 2012 when trying to restore importing multiple bacpacs one after another – it will give a System.OutOfMemory exception. Closing SSMS and re-starting it fixes the problem.

    Question #3: Is this a known bug?

    When running the “Import Data-tier Application” wizard, it displays progress. However, once it’s done, there is no indication of how much time has elapsed.

    Question #4: Is the information about the elapsed time available anywhere? Are there DAC import logs?

    When running the “Import Data-tier Application” wizard, I can press the “Cancel” button – it will prompt me “The wizard has not finished, and all information you have entered will be lost. Are you sure you want to cancel?” However, if I close the SQL Server Management Studio, the dialog will be dismissed without any warning.

    Question #5: Is this a known issue/bug? What happened to my import-in-progress? Can it be resumed?

    When the data in the bacpac is being imported, it’s being done with bulk insert statements. Are constraints disabled when doing this? We have a highly structured database with lots of constraints and triggers and the import of even a tiny database seems very slow (as compared to restore from a backup).

    Question #6: Is there something that can be done to ensure the fastest possible data import?

    Finally, a bacpac is simply a zip archive of a few XML files and JSON files containing the data.

    Question #7: Are there any APIs to manipulate the bacpacs? Interesting scenarios could include partial imports (import data only, only import some data, resume data import).

    Thanks!

    David.


    Tuesday, May 22, 2012 6:12 PM
  • Thanks for the blog link! What worries me is your statement "Export makes a non-transactionally consistant copy of database content that uses the BACPAC format (schema and data) and Import creates a new database with the BACPAC schema and data" - this is consistent with what the older TechNet article said as well.

    Some time ago, we used to do database copies as the only means of backup - this was because the bacpac import had a bug where our DB would fail to import. This has been fixed by Microsoft since - and we happily stopped doing DB copies. When we did, however, we've ran into issues with performance degradation during the copy - and Microsoft told us we should not be doing a copy of a live production database during business hours.

    Now, it looks like before we can export to bacpac, we need to copy first. This extra step is distressing - not only it adds complexity and time to our automated backup process, but it creates potential for performance degradations. I would really like to hear a better answer to this - a smooth backup process is totally crucial to a successful business deployment.

    P.S. And don't even get me started on incremental backups :)


    • Edited by DavidAir Tuesday, May 22, 2012 9:18 PM
    Tuesday, May 22, 2012 9:18 PM
  • I agree with you. It might be useful to look at third party solutions [note: I don’t work for or represent any third parties], as see if any of them match your needs more closely.
     
    The nice thing about BACPAC format is that it’s database version independent, but Export/Import is not Backup/Restore.
     
    Cheers, Bob
    • Marked as answer by amber zhang Friday, May 25, 2012 3:17 AM
    Tuesday, May 22, 2012 11:25 PM
  • I've filed three Connect bugs on the issues discussed here:

    Local restore of bacpac created by Azure hangs for a large production database
    https://connect.microsoft.com/SQLServer/feedback/details/746707/local-restore-of-bacpac-created-by-azure-hangs-for-a-large-production-database

    SQL Server Management Studio 2012 throws an out-of-memory exception when restoring two bacpacs in a row
    https://connect.microsoft.com/SQLServer/feedback/details/746710/sql-server-management-studio-2012-throws-an-out-of-memory-exception-when-restoring-two-bacpacs-in-a-row

    SQL Server Management Studio 2012 does not prompt when closing it while running a bacpac import
    https://connect.microsoft.com/SQLServer/feedback/details/746716/sql-server-management-studio-2012-does-not-prompt-when-closing-it-while-running-a-bacpac-import

    David

    Tuesday, June 5, 2012 6:19 PM
  • I think I found what is going on with my biggest issue (hang on import). Here is my own comment from the Connect bug:

    ------------------------

    I think I have an idea of what's happening. The inserts don't really hang, they're just very, very slow (to the point it becomes unmanageable). The reason? Indexed views!

    I wrote a little tool that reflected over the Microsoft.Data.Tools.Schema.Sql assembly and tried to import the data into the offending table. Result - "hang" (or, actually, the import was fantastically slow).

    I've dropped all the indexed views in our DB, and re-ran the tool. It imported instantaneously.

    I think there is a fundamental issue with the DAC Import process - it's too atomic, it only allows to import, without breaking down into steps. We have had a problem in the past where even the schema could not be re-created due to the NOEXPAND hint because the import process got the order wrong between indexed views and the sprocs.

    Now it looks like it should be pushed even further: don't blindly bulk insert the data when importing a bacpac! What needs to happen is: import data structure first (only tables), then import data (fast!), then import constraints and programmability.

    However, this is not necessary possible with bacpacs because they are not guaranteed to be transactionally consistent.

    Thursday, June 7, 2012 1:57 PM