Happy April Fools Day everyone! I hope that you do not spend too much time reading completely false blog posts today. I, for one, promise that this post is not an April Fools Day joke. Feel free to read on without fear of frustration.
Today I wanted to talk about SQL Server Data Tools, specifically related to database, or dacpac, deployments. For those of you who do not follow me on Twitter, I am transitioning over to a new company in two weeks. Before I get to my point, jump on Twitter and follow me, @SQLHammer ;-). Anyways, I have been doing a lot of knowledge transfer and documenting lessons learned for my current company and SSDT project configurations is one of the hot topics that I’ve been asked to cover. As I formed my thoughts and prepared for various meetings and documentation sessions, I realized that SSDT holds two major areas where improvement is needed. The first is dealing with security and server level objects when dealing with multiple different environments such as development, test, and production. The second is how migration between versions of the database schema can occur. We are going to focus on that second area for the rest of this post.
SSDT does a pretty good job at making database changes declarative. You declare the desired state in the project, build a dacpac, and then SSDT will compare your dacpac to your database and generate the necessary scripts to move it from the current version to the version of the dacpac. While SSDT does a pretty good job, as I said, you are still required to build a plan of action for migrating between versions where a compare tool falls short.
Let us say, for example, that you want to rename a column. Simply renaming the column in the database project will work and you will be able to deploy. Next thing you know, SSDT has dropped your column and created a new column instead of renaming. You didn’t want to keep all the data in that column did you? Another example is when you want to add a NOT NULL column to a table with existing data. Maybe you want to pre-populate the column but you don’t want to use a default constraint because the data is driven from other tables where you will need to use a custom query instead. You cannot accomplish this goal with a single deployment of SSDT by itself. You would have to choose between running a custom script in the post-deployment and then making the project change later, or possibly make the table change in the project and execute a script before, and external to, the dacpac to handle the column creation. To be fair, this type of problem seems inevitable with a comparison tool because it would be a challenge for the tool to know how you needed to handle these scenarios.
This brings us back to our problem, migration between versions of the database schema which cannot be handled by the compare. Microsoft has provided us the ability to execute a single pre-deploy and a single post-deploy script during the dacpac deployment. Technically, you can deploy as many scripts as you wish by using the SQLCMD :r syntax but that is irrelevant for now. With these steps on either end of the work flow, we can easily add custom scripts to handle migration between versions and writing them in a safely re-runnable way is also fairly easy.
So what is the problem? The problem is that the pre-deploy script is not truly a “pre“-deploy. Let’s take a look at the work flow.
As can be seen above, the compare and generation of the deployment script actually occurs before the pre-deploy script is executed. In our scenario where we wanted to add a column and pre-populate, we could not put the script into the pre-deploy because; first a script with the add column would be generated, then the pre-deploy would create the column and populate, then the generated script would execute and throw and exception for the column already existing. It seems to me that the pre-deploy should truly execute before the deployment which includes the generation of the deployment script. That way, the generated script would be derived from the state of the database after the pre-deploy is complete. I do not know this for sure but I believe that the purpose behind this work flow is to validate that the compare and generation can be successful before any code is executed and so that features of SSDT which only provide a report of what would be done can be successful.
For this reason, we run a custom deployment step which calls N number of T-SQL scripts to handle our pre-deploy migration script needs.
Written by Derik Hammer of SQL Hammer
Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around high-availability, disaster recovery, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and entrepreneurial ventures.
Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started SQLHammer.com as one small way to give back and continue the cycle of shared learning.
Derik is the owner and lead author of SQL Hammer, a Microsoft SQL Server resource.
For more information, visit http://www.sqlhammer.com. Follow Derik on Twitter for SQL tips and chat
Leave a Reply