Search

IT Depends

a database administration blog

Month

July 2017

Working with DACPACs

First, lets outline what a DACPAC is.  According to Microsoft:

“A data-tier application (DAC) is a logical database management entity that defines all of the SQL Server objects – like tables, views, and instance objects, including logins – associated with a user’s database. A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and database administrators to package SQL Server objects into a portable artifact called a DAC package, also known as a DACPAC.” jump to article

So, it’s essentially a package of changes that your development team will bundle up and send over to you to apply to a production server.  I think that’s great and because all the changes are encapsulated in a single file.  As such it can be source controlled, shared, emailed etc.

The DACPAC is a binary file, so you’d think that ascertaining its contents would be a tricky affair but, very much like your Azure DB backups, .bacpac files, it’s just a ZIP archive so editing the dacpac with a .zip extension will allow Windows to open the archive and display the packaged deployment.  Nice.

Not everything is peachy, however.  Firstly, you only really want to be deploying dacpacs on SQL Server 2012 or above.  There’s generally a shoehorn within grasp for most things and dacpacs aren’t an exception to this rule. Having said this, you lose a lot of the advantages if the dacpac has to be pulled apart and executed manually by your Production DBA.

It’s a requirement to match the version of SqlPackage.exe with the instance you’re deploying to.  It’s a world of hurt otherwise.

If your developers are ‘click and go’ developers, you may be taking data and security from the development environment and applying it to production.

If you or your development team are looking at using dacpacs for data tier deployments, I would recommend working closely with your dev team to set this up so that you can avoid the pitfalls and reap all the benefits from their use.

Advertisements

mssqlsystemresource – The database you may have never seen.

When I blog, it tends to be due to me having done something at work and thinking it may be useful, primarily for me to make a note of what I did somewhere but also because this might help someone else.  A while back, I posted regarding moving system databases to another location.  It was a pretty simple and already well documented but there is another database, a secret one which some may not know about and I didn’t think about when moving system databases.  This database is called mssqlsystemresource.

This database can not be backed up like a visible system database or a user database but when moving system databases, you may need to consider this database for the move to be successful.

The mssqlsystemresource database has two main functions.  Firstly, it contains all the physical system objects that come with SQL Server.  When you query sys.objects from database1, for example or any other user database, the object exists physically in the resource database, but logically in your user database.  Secondly, and because of this, it makes upgrades a much simpler prospect since the physical system objects can just be overwritten in one, known place.

For a more detailed overview of this database, here’s a link to Microsoft Docs with more detail:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database

Blog at WordPress.com.

Up ↑