SQL Server Database Project structure

Question

I'm beginning to learn SSDT-BI for Visual Studio 2013, and we work with a pre-existing third-party database that another application uses (we add to this existing database as part of our work).

Can a DACPAC only update certain database objects ("If not exists, drop, and create..."), or must it contain an entire database?

I'm basically looking to replace our existing scripts (all of which are basically "If object exists, drop object, then create object") and use some of the features of SSDT-BI like compilation, source control, better deployment packages, etc. and was wondering if this were possible under the existing database scenario I work with. Everything I've tried so far as led to the DACPAC dropping and recreating the entire database which is not what we want.

Any help or insight into how we might be able to achieve this using the BI tools would be appreciated.

Answer 1

By default, you shouldn't be dropping and re-creating every object in the database if you're using SSDT SQL Projects and dacpacs. There is an option to enable this when you publish, but it's pretty easy to disable if you hit the options when publishing and/or save as a publish profile.

SSDT will store your DB as SQL files in a structure containing folders for tables, functions, procs, users, etc. From there it will read that expected structure, generate a dacpac when publishing, compare that dacpac to the actual database, then make the database look like the project. That will result in adding/dropping/renaming objects as needed. (Though you do have the option not to drop objects if they're not found in the project - I find that is a bit safer for production releases.)

I've blogged quite a bit about SSDT (with thanks to many others who got me started) and those articles may help you a bit. They're not actually the "BI" tools, just straight up SSDT as opposed to SSDT-BI. MS didn't do too well naming these toolsets and they acknowledged that by at least tweaking the SSIS/SSAS tools to SSDT-BI. :)

http://schottsql.blogspot.com/2013/10/all-ssdt-articles.html