how to include data while extracting dacpac file

Question

Hi,

Requirement - to have ci/cd pipeline for DB deployment when a db changes in dev, pipeline has to deploy it to higher environment.

We have chosen dacpac deployment, but as per Microsoft document it might include data which is also limited to 10,000rows and In the Extract Settings section, you can choose to extract schema only or to extract schema and include table data. If you choose to extract schema and data, you can select the tables for which you would like to extract data. But there is no such option to select tables to include data(attached image for reference).

Should we register the database as DAC within instance to include data? what is the process to have both schema and data. Can we use .bacpac for this scenario , if so how well it integrates with cicd lifecycle?

Thanks in advance!

Answer 1

Hi Gnanasoundari,

Thanks for reaching out to Microsoft Q&A.

For best results:

DACPAC: Use for schema only deployments.

BACPAC: Use for schema + data deployments and it can be integrated into your CI/CD process using tools like SQLPackage and Azure Pipelines.

To meet your requirement of including both schema and data in your database deployment pipeline using DACPAC or BACPAC, here's how you can try...

• DACPAC (Schema Only)
  • DACPAC files are primarily intended for schema-only deployment. This aligns with Microsoft's recommendation for most scenarios involving schema changes. However, DACPAC does not support including data (apart from limited support for static lookup tables as you mentioned, capped at 10,000 rows).
  • Since DACPAC does not natively include large datasets, it is suitable for cases where only schema changes need to be deployed across environments.
• BACPAC (Schema + Data)
  • BACPAC files include both the schema and data. They can be a better fit for your scenario, where you need to move both schema and data as part of a CI/CD process. BACPAC is more suited to scenarios where you need a copy of the database (including the data) and is frequently used for migration between environments.
• Steps to Create a BACPAC File
  • In SSMS you can export a BACPAC file by using the "Export Data-tier Application" wizard.
  • This option includes both schema and data from the tables. You will get a BACPAC file that contains all the information necessary for both schema and data migration.
• CI/CD Pipeline Integration
  • You can integrate BACPAC with your CI/CD pipeline using tools like Azure DevOps or GitHub Actions.

During the deployment you can use the SQLPackage tool to deploy BACPAC files to higher environments. It supports importing BACPAC to an Azure SQL Database or SQL Server instance. Azure Pipelines has built-in tasks like Azure SQL Database deployment that support importing BACPAC as part of a pipeline.

• Registering the Database as DAC
  • Registering the db as a DAC does not change the ability to include data. DAC registration is more about versioning and managing schema changes across environments rather than including data. For including data, you would still need to work with BACPAC, as DACPAC is limited in this aspect.
• Considerations for BACPAC in CI/CD
  • When using BACPAC in your CI/CD pipeline, consider that it may involve larger files due to the inclusion of data.
  • Automate the export and import of BACPAC using Azure DevOps pipelines by leveraging SQLPackage.exe to ensure the process integrates smoothly into your CI/CD pipeline.

Please 'Upvote'(Thumbs-up) and 'Accept' as an answer if the reply was helpful. This will benefit other community members who face the same issue.