Search

IT Depends

a database administration blog

Category

Uncategorized

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

Moving SQL Server system databases

This one doesn’t come up that often for me.  Since it doesn’t I feel I need to blog about it as an aid to memory and to anyone else who stumbles across it.

So, if you need to move your system databases as part of a routine operation and to a location on the same server/instance, it is actually a surprisingly easy and easy to figure out process.  The whole thing end to end falls into the following steps.

  1. Modify startup options -d and -l to point to the new location of your Master database data and log files.
  2. Alter the file locations of the remaining SQL Server system databases (model, tempdb, msdb etc.).
  3. Stop the SQL Server service.
  4. Move the data and log files to their new homes.
  5. Start the SQL Server service.

For those a little newer to this sort of operation, I’ll detail the first two steps.  The last three shouldn’t really need covering here.

  1. Modify startup options -d and -l to point to the new location of your Master database data and log files.
  • Open SQL Server Configuration Manager
  • In the left pane, select SQL Server Services
  • In the right pane right click > properties on SQL Server (INSTANCENAME)
  • On the Startup Parameters tab select the line with the -d switch, edit the parameter in the box above with the new Master database data file and click Update.  Do the same for the log file, using the parameter with the -l switch.

2.  Alter the file locations of the remaining SQL Server system databases (model, tempdb, msdb etc.).

  • For each data and log file associated with each of the system databases you want to relocate, simply run the following command, replacing with your own pertinent data.  Note that NAME is the logical database name.
ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBData, FILENAME = 'G:\system_db\MSDBData.mdf'); GO

ALTER DATABASE msdb
MODIFY FILE ( NAME = MSDBLog, FILENAME = 'G:\system_db\MSDBLog.ldf'); GO

Before moving on to later steps, you can check to make sure the locations have updated correctly by querying sys.master_files and checking the physical_name column.

BOL article can be found here.

Blog at WordPress.com.

Up ↑