The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Yesterday we talked about how SSMS, with each new release, is becoming more closely aligned with Visual Studio. Database development, deployment, and management is also becoming more closely aligned with the traditional software development life cycle (SDLC). There are camps both for and against this… but the reality is it’s happening. Want proof? Enter DacPac.
DAC stands for Data Tier Application (yeah, I know, acronym doesn’t quite work there does it?) In it’s simplest terms, a DacPac is a single compiled entity containing all objects of a database. This collection of objects and/or schema can be loosely compared to what a collection of classes, functions, dlls, etc. are to an application executable.
How is this different than a complete collection of create or alter scripts? That’s a valid question. Right about now all the MySQL folks are saying… yeah, we’ve been doing that for years with mysqldump.
One: By packaging it up as a single object it’s less likely that individual objects or scripts will be accidentally modified or left out. Not only that, but by packaging it up this way you can now work more effectively with source control, manage database versions, and establish branches and team development without the risk of mixing up dev, test, and production. Like I said… it’s a way to more closely align database development and management with established SDLCs long practiced in software engineering.
Two: The DacPac framework includes smarter options for deploying upgrades, patches, changes, rollback’s etc. than running manual scripts can or does.
Three: DacPacs can now also contain data – not just schema, more on that below. Sure, this wasn’t impossible with a script based approach but it introduced a whole lot more complexity than most people would like. Again, MySQL folks are all like, whatever – we’ve always done that with scripts.
Not all of these options (specifically two and three) are available right now in SSMS but I’m trying to give you the full vision of what a DacPac offers.
Finally, and probably most importantly to Microsoft, this is the future of working with database deployments in an Azure world via the mindset of a tightly packaged and versioned database existing within a controlled SDLC.
While we are well past version one of DAC – there are still limitations on how much data or the size of databases you’d want to commit yourself to managing through the Data Application Tier mindset. Read up on that on MSDN – specifically modifying existing DacPac’s by deploying a new one and the nuances of upgrading etc. Especially if data is involved.
Knowing that DacPac is a single entity inevitably begs the question… how is it really stored in there? In truth, a DacPac file is just a zip file containing a large collection of XMLs etc. Add a .zip extension to the end of the file and open it up with your favorite zip file reader. It’s an interesting look into how this whole framework runs. While more wordy than TSQL Scripts, XML isn’t anything if it isn’t wordy, it is both easily read, supports an extensible language for ease of enhancement and backwards compatability, and “open”.
As far as storage on the server, a deployed Data Tier Application (DacPac) is registered in the MSDB. Side note: once registered you can utilize SQL Utility against the DacPac, not just the database instance. We talked about that a couple days ago.
DacPac and SSMS:
The true DAC professional will be working mostly via Visual Studio proper via SQL Server Data Tools. I will point you to MSDN for a complete end to end review of DacPac and SQL Server Data Tools (the environment within VS to work in this framework). I’m skimming a lot here – I know – so, let’s jump finally to working with Data Tier Applications and DacPac within SSMS.
DacPac Options- Databases Root Node
From the databases root node right click gives you the option to Deploy or Import a Data Tier Application. Deploy creates a new database on your server by deploying a DacPac. This builds the database as well as registers it on the server in the MSDB database as an Data Tier Application.
The Import option is for BacPac files. What is a BacPac you ask? A BacPac is very similar to a DacPac but where a DacPac is schema only, a BacPac includes data. It is an easy way to move schema and data from one place to another. The schema storage is the same xml files as a DacPac, the additional data however is stored in a json format.
Once you make a selection on either option (Deploy or Import) the rest is a very straightforward Wizard GUI.
As an aside … it’s likely the difference between DacPac and BacPac will at some point in the future be zero. DACfx, the API for working with DAC at a code level, already allows for creation of a DacPac with data included via SSDT. As Azure and DAC continue to mature I expect these two packages to become one in the same.
DacPac Options- <Database> Node
There are a few more options under a specific database node. You can extract a DacPac from an existing database (interestingly enough whether that database is or isn’t already a registered Data Tier Application itself). You can also register the database in place, effectively converting a database into a Data Tier Application. You also have Export options. The difference between exporting and extracting again is that one generates a DacPac (schema and objects) and one creates a BacPac (schema, objects, and data). You can also upgrade an existing DacPac to a new version taking advantage of improved meta data and features related to the SDLC aspects of this framework. Finally, you have options for moving an existing database to Azure.
SSDT and Visual Studio without a doubt offer far more options when working in the concept of Data Tier Applications, specifically via DACfx and SQLPackage.exe etc., but if Microsoft remains committed to this development mindset I only expect DAC features and presence within SSMS to expand.