In a previous post I went through how to deploy SQL Server running in an Azure Container Instance using Terraform.
In that post, I used hardcoded variables in the various .tf files. This isn’t great to be honest as in order to change those values, we’d need to update each .tf file. It would be better to replace the hardcoded values with variables whose values can be set in one separate file.
So let’s update each value with a variable in the .tf files.
Click through for a demo.
What this is going to do is create an Azure Container Instance Group with one container it in, running SQL Server 2019 CTP 2.5. It’ll be publicly exposed to the internet on port 1433 (I’ll cover fixing that in a future post) so we’ll get a public IP that we can use to connect to.
Notice that the location and resource_group_name are set using variables that retrieve the values of the resource group are going to create.
Cool! We are ready to go!
Fun stuff, and Andrew promises more.
In the last few posts I have moved from building an Azure SQL DB with Terraform using VS Code to automating the build process for the Azure SQL DB using Azure DevOps Build Pipelines to using Task Groups in Azure DevOps to reuse the same Build Process and build an Azure Linux SQL VM and Network Security Group. This evolution is fantastic but Task Groups can only be used in the same Azure DevOps repository. It would be brilliant if I could use Configuration as Code for the Azure Build Pipeline and store that in a separate source control repository which can be used from any Azure DevOps Project.
Luckily, you can
And Rob shows us how it’s done.
A micro module is very small in scope and often has a single function. Building a micro module is about getting back to the basics and keeping everything as simple as possible.
There is a lot of good advice out there on how to build a module. That guidance is there to assist you as your module grows in size. If we know that our module will not grow and we will not add any functions, we can take a different approach even though it may not conform fully to the community best practices.
There are a few things which differ from standard module best practices.
The other day someone checked in some code and every now and then the build would fail with the error
Msg 15151, Level 16, State 19, Line 51
Cannot drop the event session ‘ProcsExecutions’, because it does not exist or you do not have permission.
I decided to take a look at the code and saw what the problem was. I will recreate the code here and then show you what needs to be changed. This post will not go into what Extended Events are, you can look that up in the SQL Server Extended Events documentation
I like these
IF NOT EXISTS checks on release scripts as that makes them re-runnable. Even if you don’t use continuous integration for release scripts, you may sometimes hit F5 one too many times.
This is where we start the decline phase in our story. Our temporary procedures existed as a bridge from the old procedures which took
ClientIDand new procedures which will take
ProfileID. With our final versions of procedures, we replace
@ProfileIDin the input parameters and update any conditional logic within filters to use
The only remaining use of
ClientIDin these procedures is as an output in select statements, as we still need that for the old code; by this time, all of those references are
ProfileID AS ClientID. Otherwise, these new procedures are built around
We still have the original procedures that the application code uses and they reference our now-finalized procedures. These procedures are now transition procedures—we need them right now but as we move application code over to the new procedures, we can deprecate these. But I’m getting ahead of myself a little bit.
Click through for the full story as well as a bunch of pictures which completely understate the vastness of work done.
I’m getting close to the end of my series on near-zero downtime deployments. This latest post involves identity column changes:
There are some tables where you create an identity value and expect to cycle through data. An example for this might be a queue table, where the data isn’t expected to live permanently but it is helpful to have a monotonically increasing function to determine order (just watch out for those
wrap-aroundsand you’re fine). An example of reseeding is below:
DBCC CHECKIDENT('dbo.MyTable', RESEED, 1);
This operation needs to take a
LCK_M_SCH_Mlock, otherwise known as a schema modification lock. Any transactions which are writing to the table will block your transaction but so will any readers unless you have Read Committed Snapshot Isolation turned on or the reader is in
If you are using RCSI and don’t have extremely long-running transactions, this is an in-and-out operation, so even though there’s a little bit of blocking, it’s minimal.
Not all changes are this easy, though.
The locking story is not the same as with the primary and unique key constraints. First, there’s one extra piece: the transition will block access to
dbo.LookupTableas well as the table we create the constraint on. That’s to keep us from deleting rows in our lookup table before the key is in place.
Second, the locks begin as soon as we hit F5. Even
SELECTstatements get blocked requesting a
LCK_M_SCH_Slock. Bad news, people.
So what can we do to get around this problem? Two routes: the ineffectual way and the ugly way.
Despite my being a ray of sunshine here, you should still check this out. It’s shorter than the average Russian novel, at least.
The realm of More Significant Changes is not where you often want to be. There’s a lot of scaffolding code you need to write. Basically, suppose you want to make a repair on the 5th story exterior of an 8-story building. You have a couple of options: the YOLO option is to kick everybody out of the building and have people rappel from the top of the building down to the 5th story to make their changes. They need all of the people out of the building because shut up it’s a strained analogy. This approach is kind of inconvenient: people have to stay out of your building until your people are done repairing the exterior. That’s blocking in the database world.
On the other side, you can build a bunch of scaffolding and attach it to the exterior of the building, perform your repairs, and tear down that scaffolding. While the scaffolding is up, people come and go like normal and don’t even think about it. As you tear the scaffolding down, you temporarily block the door for a moment as you’re finishing your work. This is much more convenient for end users and fits the “near-zero downtime” approach we’re taking.
Strained analogies aside, this is a long post on making a series of table-related changes without your end users noticing.
Versioning a procedure is pretty simple: you create a new procedure with alterations you want. Corporate naming standards where I’m at have you add a number to the end of versioned procedures, so if you have
dbo.SomeProcedure, the new version would be
dbo.SomeProcedure01. Then, the next time you version, you’ll have
dbo.SomeProcedure02and so on. For frequently-changing procedures, you might get up to version 05 or 06, but in practice, you’re probably not making that many changes to a procedure’s signature. For example, looking at a directory with exactly 100 procedures in it, I see 7 with a number at the end. Two of those seven procedures are old versions of procedures I can’t drop quite yet, so that means that there are only five “unique” procedures that we’ve versioned in a code base which is two years old. Looking at a different part of the code with 879 stored procedures, 95 have been versioned at least once in the 15 or so years of that code base’s existence. The real number is a bit higher than that because we’ve renamed procedures over time and renamings tend to start the process over as we might go from
dbo.SomeNewProcedurewhen we redesign underlying tables or make other drastic architectural changes.
The secret is, I’m always versioning.