Forum Discussion
Helper IWarehouse Schema - Tables CI / CD
Hi giulio-diluca, deployment pipelines are made for deploying code and do maintain the state of database structures or data. To deploy database structures to a Warehouse or SQL database, you should put your database structure into a database project using VSCode or Visual Studio and maintain the database project in your repositorie.
Then using DevOps you can build your project into a .dacpac and deploy it to your Warehouse in every environment. Any change in database structure is now automatically deployed.
But, you need to look into using pre-/post-deployment scripts that you can use for structure changes that could cause data loss. Depending on your database project settings, if you want to add a column to a table and the column is added in the middle of the table, the dacpac deployment will fail with a message that it cannot continue due to possible data loss or it will drop and recreate your table resulting in data loss.
To solve this, you need to use a pre-script to rename or clone your table, then the deployment will recreate your table and the post-script needs to copy the data from the old table into the new table.
In DevOps you can choose to use the integrated SqlAzureDacpacDeployment task or look into SqlPackage CLI for more flexibility.
Hope this helps you on your way. Probably someone else will post more information on how to best maintain pre-/post-script versioning or anything other that I might have mist in this reply.
Super UserHi giulio-diluca, deployment pipelines are made for deploying code and do maintain the state of database structures or data. To deploy database structures to a Warehouse or SQL database, you should put your database structure into a database project using VSCode or Visual Studio and maintain the database project in your repositorie.
Then using DevOps you can build your project into a .dacpac and deploy it to your Warehouse in every environment. Any change in database structure is now automatically deployed.
But, you need to look into using pre-/post-deployment scripts that you can use for structure changes that could cause data loss. Depending on your database project settings, if you want to add a column to a table and the column is added in the middle of the table, the dacpac deployment will fail with a message that it cannot continue due to possible data loss or it will drop and recreate your table resulting in data loss.
To solve this, you need to use a pre-script to rename or clone your table, then the deployment will recreate your table and the post-script needs to copy the data from the old table into the new table.
In DevOps you can choose to use the integrated SqlAzureDacpacDeployment task or look into SqlPackage CLI for more flexibility.
Hope this helps you on your way. Probably someone else will post more information on how to best maintain pre-/post-script versioning or anything other that I might have mist in this reply.
giulio-dilucahI nielsvdc
I'm not a data expert so probably some questions that i'll do might seems obvious
1) not all operations made in a database structure cause loss data right?
2) if an operation that cause loss data is performed in a table in warehouse, only data in this table are loss, the warehouse object is not re-created right?3) using fabric built-in deployment systems ( like git integration, fabric deployment pipeline ) we're able to manage database structure even if some data could be loss based on operations. how they differ from creating a .dacpac considering that seems work the same?
4) .dacpac, with specific option, seems that is able to extract also data from a source database, so i can point a warehouse url from where i can get these data?
Regards,
Giulio