DacPacs, if you’re not aware of them, are normally used to represent a release from an SSDT project. They are the end-product of a development. The DacPac file format is just a .ZIP containing mostly XML data. You can use it to copy, deploy, compare, drift-check or synchronise databases. You can create DacPacs very simply from existing databases via SSMS.
Until the release of DacFx v3, the support library for creating and using DacPacs, I couldn’t use them because they gave only support for simple databases for small developments, and didn’t support the features of the newer versions of SQL Server. I’m glad I didn’t spend any time on them because DacFx v3 changed all the APIs so none of my scripts would have then worked. However, now that most of the quirks of the system seem to have been ironed out, I’ve been keen to get acquainted with it.
After a lot of testing and scripting, my conclusion is that it is great to see Microsoft put energy into solving some of the problems with the database Lifecycle. If we can get to a point where it is reasonably quick and effortless to both deploy and update any small-scale SQL Server database, then there will be a lot less temptation for developers to kick the problem down the road a bit by using a schema-less NoSQL datastore. Here, we have an attempt to pin down both the code and the static data of a ‘version’ of a database within a single artefact, which can be used for the basic operations of build, test and delivery. It is an initiative that deserves support, surely?
From a distance, it all looks impressive. Closer up, there are still problems. One can shrug off slow performance, but not problems of accuracy. On a fairly large back of test databases it, for example, on my tests I found that it mistakenly believed some functions were different when they weren’t, and failed to spot functions that needed to be deployed. To be fair, some operations were done impressively well, but there was a whiff of wet paint in the air. I suspect that some, but not all, problems were due to syntax errors, but when you are synchronizing database, the process has to be ‘transactional’ and roll back cleanly on the first likelihood of a problem. It never pays to 'swallow' errors in this sort of task.
I dislike the XML format of the information in a DacPac because I think that the ‘point of truth’ for any particular version of a database lies in the SQL scripts in source control, and the 'portable' model should be easily verified against the version in source control. Also, an XML model is redundant, because a database can be defined purely in SQL and the parser allows us to buld a model of any database. However, I reckon it is time to participate, even if it is for low risk tasks, and get involved in influencing the future direction of the utility. Are you using DacPacs? If so, what are your experiences? If not, then why not? I’d be interested in your views.
