Does DACPAC extraction cause locking?

Question

Does DACPAC extraction cause locking?

Andrew Causon

SSC Veteran

Points: 264
More actions
August 12, 2016 at 4:30 am

#316155

I'm hoping that someone can answer this as I have scoured the internet for something relevant to no avail.
As a developer coming into an organisation where data security is very tight, I have no access to Live or Pre-production database instances.
I am tasked with aligning the schemas for all our environments to give confidence that what we have in source control is the one version of the truth.
Now there are a lot of databases which will need to be covered, so I decided to automate the extraction of DACPACs for all databases on the selected instances via Powershell and either DacFx or SQLPackage.exe, which works very well.
However, passing this to a DBA to run on Live is making them very nervous.
So, does DACPAC extraction with DacFx/SQLPackage.exe cause locking and risk blocking applications running in the live environment?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Andrew Causon (8/12/2016)
I'm hoping that someone can answer this as I have scoured the internet for something relevant to no avail.
As a developer coming into an organisation where data security is very tight, I have no access to Live or Pre-production database instances.
I am tasked with aligning the schemas for all our environments to give confidence that what we have in source control is the one version of the truth.
Now there are a lot of databases which will need to be covered, so I decided to automate the extraction of DACPACs for all databases on the selected instances via Powershell and either DacFx or SQLPackage.exe, which works very well.
However, passing this to a DBA to run on Live is making them very nervous.
So, does DACPAC extraction with DacFx/SQLPackage.exe cause locking and risk blocking applications running in the live environment?

The first thought I had is that you are handing off a process to the production DBAs but have no idea what impact it has on the environment. In that scenario, I think anyone would be (or should be) nervous. I don't know much about the DACPAC extraction other than it does a bunch of dynamic SQL from what I remember. And any SQL statement will take locks and whether shared or exclusive, those can impact other processes. The impact depends on a lot of variables.

And then my other thought is why would you be in a position to get all the environments in sync when you don't have access to all the environments. That seems odd. But we all are in odd positions sometimes.

Maybe part of the how to decide a direction for this task could be to involve the DBAs and/or your manager in the process? If others are going to have skin in the game, it seems like they might want to have some say in the process.

Are they open to using a third party product? When you want to do compares of a lot of databases in different environments, those tools generally pay for themselves. I personally like using SQL Compare from RedGate. I've recommended it at many places and I think every places has been happy with that purchase. Sometimes talking a company into something like that can be just as an important skill as the more coding centric skills.

Sue

BLOB EATER SSChampion Points: 14609 More actions · Answer 2

I was playing around with this and saw the following - however don't take my word for it test yourself.

DACPACs I saw:

GRANTSch-S

BACPACs I saw:

GRANTX

GRANTSch-S

GRANTSch-M

GRANTIS

Andrew Causon SSC Veteran Points: 264 More actions · Answer 3

Thanks for this - you inspired me to look at trace options and look at locks acquired and indeed for DACPAC extraction I am only seeing Sch-S locks which I now know (after a bit of reading) will only block Sch-M locks for DDL operations - DML operations shouldn't be affected.

Many thanks!

Andrew Causon SSC Veteran Points: 264 More actions · Answer 4

Sue_H (8/12/2016)
Andrew Causon (8/12/2016)
I'm hoping that someone can answer this as I have scoured the internet for something relevant to no avail.
As a developer coming into an organisation where data security is very tight, I have no access to Live or Pre-production database instances.
I am tasked with aligning the schemas for all our environments to give confidence that what we have in source control is the one version of the truth.
Now there are a lot of databases which will need to be covered, so I decided to automate the extraction of DACPACs for all databases on the selected instances via Powershell and either DacFx or SQLPackage.exe, which works very well.
However, passing this to a DBA to run on Live is making them very nervous.
So, does DACPAC extraction with DacFx/SQLPackage.exe cause locking and risk blocking applications running in the live environment?
The first thought I had is that you are handing off a process to the production DBAs but have no idea what impact it has on the environment. In that scenario, I think anyone would be (or should be) nervous. I don't know much about the DACPAC extraction other than it does a bunch of dynamic SQL from what I remember. And any SQL statement will take locks and whether shared or exclusive, those can impact other processes. The impact depends on a lot of variables.
And then my other thought is why would you be in a position to get all the environments in sync when you don't have access to all the environments. That seems odd. But we all are in odd positions sometimes.
Maybe part of the how to decide a direction for this task could be to involve the DBAs and/or your manager in the process? If others are going to have skin in the game, it seems like they might want to have some say in the process.
Are they open to using a third party product? When you want to do compares of a lot of databases in different environments, those tools generally pay for themselves. I personally like using SQL Compare from RedGate. I've recommended it at many places and I think every places has been happy with that purchase. Sometimes talking a company into something like that can be just as an important skill as the more coding centric skills.
Sue

Thank you for your thoughts on this.

I my goal was actually to make it easier for the DBA instead of asking him/her to run the DACPAC extraction one by one. We use SQL Server Data Tools for offline development with SQL Server Database Projects which are source controlled. The DBAs have Redgate tools and SSDT. Using DACPACs is just a convenient way to get access to the schema without having direct access to the source server which is also firewalled - there are too many hoops to jump through right now!

There is a DevOps culture here and so developers take responsibility for the majority of the schema definition even though direct access to live or live-like environments is not allowed. Publishing DACPACs to deploy changes is the norm here, but until now the dropping of objects not in source has been blocked for safety reasons. This has to change so we can fully take control of what is in live (and other environments)

Anyway, I got an answer to my question and at least I can appreciate the potential impact with a little more certainty.

Thanks again,

Andrew