Can dacpac be used to manage large databases?

Question

Our current database has almost 200 MB, but as soon as the application launches, we expect that it will grow to a large volume .. maybe 20-30 GB of data in this.

We plan to use "dacpac" (generated by the database project - SSDT) ​​for deployment on a production server. A database will be created with multiple tables and a variety of source data in lookup tables.

However, the problem is in future deployments, when we will use "dacpac" (generated by the database project - SSDT) ​​to update the database on the production server.

Since I have no experience using dacpac for deployment, can anyone suggest me the following -

• Does deployment affect data volume? Or does it depend only on circuit changes? For example, if the target database is 20-30 GB, how much approximate time can it take only to update it?
• How can we change the database schema?
• Is it possible to roll back the update process if something goes wrong?

And finally, is this a better way to manually write sql scripts to update the database?

Answer 1

• From my experience, the amount of data has an impact on dacpac deployment. The increase in time will depend on what changes are applied in your dacpac in your database. My only advice here is to try checking with large amounts of data in order to evaluate the increase in time. This may be minimal

• All our objects are stored in the SQL Server Data Tools (SSDT) ​​visual studio project, and then are versioned in TFS, so when we need to build based on additional validation, it will create a new version for us

• This may depend on the type of updates used and whether you want to spend time understanding what it takes to roll back each update to the schema.

I like to use dacpac and find it very useful if you place all your SQL objects in the same Visual Studio project. Switching to the manual method may increase the likelihood that you forget to include one or more fixes due to the number of changes needed.