Does a SQL DB Project Deploy Cause an Outage?

Question

We have a SQL Database Project that we use to make database changes and push those out to each environment (DACPAC). My question is whether even non-breaking changes (i.e. add column, modify body of stored proc, etc.) will cause a database outage or whether it's dependent entirely on the type of change. We do not have it configured to drop the DB and recreate it.

Answer 1

About any change can cause an outage depending on your system. Say for instance that you want to a nullable column to a table:

``
ALTER TABLE tbl ADD newcol int

That's a short and swift operation, but it does require a Schema-modification (Sch-M) lock. Say that there is a long-running query running against the table when you issue the ALTER TABLE statement. This will block ALTER TABLE, even if the query is using READ_COMMITTED_SNAPSHOT, or, God forbid, NOLOCK, because in those case you get a Sch-S lock on the table.

Now while the ALTER TABLE is waiting to get the Sch-M lock, it will block other queries that want to query the table.

Recent versions of SQL Server provides the clause WAIT_AT_LOW_PRIORITY to resolve this situation, but it is only available with ONLINE operations, and thus only in Enterprise Edition.

Replacing stored procedures can also have interesting effects. If someone is running a procedure while you load a new version, that execution may crash if something triggers recompile of a statement.

Thus, even if you are making simple changes, you may prefer to do this off-hours. But you know your system better than I do. As a matter of fact, the system I mainly work with, we often deploy minor updates during offices hours, trying to target lunch time.

Note there that I have not discussed DACPAC as such, but this applies to any updating strategy. The one thing you may want to check about your Dacpacs, is whether they package a suite of operations in a user-defined transaction. Obviously, this increases the risk for conflicts. (I don't use Dacpacs myself, so I don't really know what they do.)

Answer 2

So it sounds like it really boils down to the change that's being made and its impact on the database more than database project deployments taking down the database during the entirety of the deployment. Thank you for your response.