Hot Patching SQL Server Engine in Azure SQL Database

Published 09-11-2019 02:51 PM 18.1K Views

Author: Hans Olav Norheim,

Technical reviewers: Jim Cavalaris, Jimmy Cleary, Jim Radigan, Ashish Kishan Mansukhani, Alexey Yeltsov, Brian Gianforcaro, Jasraj Dange, Jon Jahren, Michael Nelson, Peter Carlin


In the world of cloud database services, few things are more important to customers than having uninterrupted access to their data.  In industries like online gaming and financial services that experience high transaction rates, even the smallest interruptions can potentially impact the end-user’s experience.  Azure SQL Database is evergreen, meaning that it always has the latest version of the SQL engine, but maintaining this evergreen state requires periodic updates to the service that can take the database offline for a second.  For this reason, our engineering team is continuously working on innovative technology improvements that reduce workload interruption.


Today’s post, in collaboration with the Visual C++ Compiler team, covers how we patch SQL Server Engine without impacting workload at all.


This is what hot patching looks like under the covers. Read on if you want all the details!This is what hot patching looks like under the covers. Read on if you want all the details!

1. The Challenge

The SQL Engine we are running in Azure SQL Database is the very latest version of the same engine customers run on their own servers, except we manage and update it. To update SQL Server or the underlying infrastructure (i.e. Service Fabric or the operating system), we must stop the SQL Server process. If that process hosts the primary database replica, we move the replica to another machine (requiring a failover).


During failover, the database may be offline for a second and still meet our 99.995% SLA. However,  failover of the primary replica impacts workload because it aborts in-flight queries and transactions. We built features such as resumable index (re)build and accelerated database recovery to address these situations, but not all running operations are automatically resumable. It may be expensive to restart complex queries or transactions that were aborted due to an upgrade. So even though failovers are quick, we want to avoid them.


SQL Server and the overall Azure platform invests significant engineering effort into platform availability and reliability. We can, for example, update the Azure host without restarting Guest VMs. In SQL DB, we have multiple replicas of every database. During upgrade, we ensure that hot standbys are available to take over immediately.


We’ve worked closely with the broader Azure and Service Fabric teams to minimize the number of failovers. When we first decide to fail over a database for upgrade, we apply updates to all components in the stack at the same time: OS, Service Fabric and SQL Server. We have automatic scheduling that avoids deploying during an Azure region’s core business hours. Just before failover, we attempt to drain active transactions to avoid aborting them. We even utilize database workload patterns to perform failover at the best time for the workload.


Even with all that, we don’t get away from the fact that to update SQL Engine to a new version, we must restart the process and fail over the database’s primary replica at least once. Or do we?


2. Hot Patching and Results

Hot patching is modifying in-memory code in a running process without restarting the process. In our case, it gives us the capability to modify C++ code in SQL Engine without restarting sqlservr.exe. Since we don’t restart, we don’t fail over the primary replica and interrupt the workload. We don't even need to pause SQL Server activity while we patch. Hot patching is unnoticed by the user workload (other than the patch payload, of course)!


Hot patching does not replace traditional, restarting upgrades – it complements them. Hot patching currently has limitations that make it unsuitable when there are a large number of changes, such as when a major new feature is introduced. But it is perfect for smaller, targeted changes. More than 80% of typical SQL bug fixes are hot patchable. Benefits of hot patching include:


  • Reduced workload disruption
    No restart means no database failover and no workload impact.
  • Faster bug fixes
    Previously, we weighed the urgency of a bug fix vs. impact on customer workloads from deploying it. Sometimes we would deem a bug fix not important enough for worldwide rollout because of the workload impact. With hot patching, we can now deploy bug fixes worldwide right away.
  • Features available sooner
    Even with the 500,000+ functional tests that we run several times per day and thorough testing of every new feature, sometimes we discover problems after a new feature has been made available to customers. In such cases, we may have to disable the feature or delay go-live until the next scheduled full upgrade. With hot patching, we can fix the problem and make the feature available sooner.

We did the first hot patch in production in 2018. Since then, we have hot patched millions of SQL Servers every month. Hot patching increases SQL Database ship velocity by 50% while at the same time improving availability.


3. Testing and Safety

Hot patching is riskier than normal updates as it introduces new kinds of pitfalls and race conditions. For example, both new and old code may execute concurrently during patching. Adding a field to a class would probably crash. This makes tooling support, code review, reasoning and extensive testing a must.


Close collaboration with the Visual C++ team was crucial. Furthermore, we did extensive validation before we performed the first hot patch in production. We’ve enhanced SQL test infrastructure to perform hot patching while running our 500,000 functional tests. Additionally, we run hot patch stress, where we patch and unpatch millions of times in a loop, while hitting the server with stress workload. When we finally roll the patch to clusters, the rollout is monitored by the system and automatically rolls back if something unforeseen happens.


4. How Hot Patching Works

Now, let’s dive into the technical details of how hot patching works. Replacing code in a running process is easy; memcpy() solves that. Doing it without crashing is not. A running program is program code (processor instructions – the contents of the compiled image - DLLs/EXEs) and in-memory state. The in-memory state consists of thread contexts and shared heap state. Thread context includes the stack and local variables, as well as the instruction pointer for the currently executing instruction and other CPU registers. The heap includes allocated memory for classes and data structures, such as the buffer pool, procedure cache and running query execution plans.


If we simply copied the contents of a new program image on top of the old one, the program would crash at the most basic level. With profile-guided optimization, the new program image may be very different from the old. Functions may not be in the same place. The size may not even be the same if the patch required inserting code. The (unchanged) instruction pointer of a thread executing in one function would now point to some other random function where execution would continue. It would not be atomic and return addresses on stacks would be invalid. At best it would crash, at worst it would corrupt data.


To simplify the problem, we only update the function(s) that changed between the old and new program code. The basic primitive offered by hot patching is to replace the body of one or more C/C++ functions in a running image with new implementation(s). This happens at runtime, while threads are executing inside the process, including in the function(s) being replaced.

Figure 1. Hot Patching at function/procedure level. To update “blah += 1” inside function Foo(), we load the patched image into memory and 1) redirect all callees of Foo() back to the old image and 2) redirect Foo() to the new image.Figure 1. Hot Patching at function/procedure level. To update “blah += 1” inside function Foo(), we load the patched image into memory and 1) redirect all callees of Foo() back to the old image and 2) redirect Foo() to the new image.

The hot patching process shown in Fig 1 above proceeds as follows.

  1. Modify C++ code
    To prepare a hot patch, we first make the necessary modifications to the C++ code of the function(s) we want to patch - say void Foo(). We then build a new version of the program image – sqlservr_patched.exe, for example. We now have the existing (unchanged) sqlservr.exe that is currently running, and a sqlservr_patched.exe that is sitting on disk. This is the left side of Figure 1. In this example, we are modifying the “blah += 1” statement inside void Foo().
  2. Load patched image into memory
    Next, we load sqlservr_patched.exe into the address space of the running process. An EXE or DLL is just a “data file”, where the “data” is CPU instructions. Loading it into a section of free memory does not disturb the running process. But loading it into memory doesn’t patch anything. We need to hook into the live code in the process.
  3. Redirect function calls
    We hook into live code by redirecting function calls targeting the old function(s) in sqlservr.exe to new replacement function(s) in sqlservr_patched.exe. Outgoing calls from the function(s) are redirected back to the old image – we only want to patch the function itself, not its entire call tree. In Figure 1, blue are active functions and grey are inactive functions. It’s important that we wire up the reverse redirects first in step ①. There are threads executing Foo(), so the nanosecond we redirect it in step ②, threads will start executing the new Foo() function.

If things go wrong, we can detach the patch by undoing step ②, without workload impact. Even hot patches can have bugs!


5. How Hot Patching Works at the Instruction Level

Above, we explained how we “redirect” function calls. But function redirection isn’t something the processor has any concept of – it is executing instructions. What we actually do is to insert (overwrite) a JMP (jump) instruction at the beginning of the function. When a patched function is entered and the processor encounters this JMP, we tell it to jump to the patched version of the function. Since this is a jump, not a function call, there is no extra stack frame. This means we don’t do anything special to return from the function – the RET instruction in the patched function is enough.


For reduced compiled program size, x64 instructions are variable length - 2 to 15 bytes. The first byte or two is the op-code (operation code). To execute an instruction, the processor begins by decoding the op-code pointed to by @rip (the current instruction pointer register). Based on the op-code, the processor knows how long the instruction is. After the processor has executed the instruction, it increments @rip by the instruction length and repeats. The smallest far-JMP instruction we can use is 2 bytes for the op-code and 4 bytes for the address offset.

Figure 2 Naïve function redirection at instruction level - the first 10 bytes of function Foo() shown. Redirection by overwriting the beginning of the function with a far jump (op-code 0xFF25) will crash when @rip is incremented by 5 and a partial address is interpreted as an op-code.Figure 2 Naïve function redirection at instruction level - the first 10 bytes of function Foo() shown. Redirection by overwriting the beginning of the function with a far jump (op-code 0xFF25) will crash when @rip is incremented by 5 and a partial address is interpreted as an op-code.

There may be threads executing in the function we are patching. We don’t freeze them because that would impact the workload. Figure 2 shows what would happen if we overwrote the beginning of the function with a 6-byte JMP while the processor was in the middle of executing the now-gone MOV instruction at offset 0. It would increment @rip by 5 and try to decode the byte at that address as an instruction. That isn’t an instruction, but instead part of the address offset we filled in. The program would crash.

Figure 3 Function redirection with function padding and a trampoline. We write a near-jump (0xEB) into the function and a far-jump (0xFF25) into the padding. This avoids partial instruction execution.Figure 3 Function redirection with function padding and a trampoline. We write a near-jump (0xEB) into the function and a far-jump (0xFF25) into the padding. This avoids partial instruction execution.

To solve this, we use a trick called a trampoline – see Figure 3. We compile (link) all SQL Server images with function padding – 6 bytes of empty space between functions. We use this space to write the 6-byte JMP from before. We then write a smaller 2-byte JMP instruction into the beginning of the function that jumps to the function padding (it can only jump to offsets +/-127 bytes). The compiler guarantees all functions start with an instruction >= 2 bytes.


6. Functional Correctness of Patches

When compiling a program, the compiler guarantees that your program is correct as a whole. It does not guarantee that any function looks like the source code. Compiler optimizations may, for example, inline a function into other functions, remove an if-branch never taken, change the number of parameters and many other tricks.


Figure 4 Compiler optimizations create trouble for hot patching. In this example, the compiler removed code needed by the hot patch.Figure 4 Compiler optimizations create trouble for hot patching. In this example, the compiler removed code needed by the hot patch.

This spells trouble for hot patching because those optimizations may change when we modify code. Figure 4 shows an example. Here, the compiler found that nobody ever calls ExecuteAction(ACTION2), so it removed the switch-case for it. If we patch PatchedFunction() to start using ACTION2, it won’t work – we also need to patch ExecuteAction.


This is where collaboration with the Visual C++ compiler team was crucial. By building hot patching into the compiler, it can detect and correctly handle such cases and we can guarantee that the hot patch is functionally equivalent to the source code. Inlining and globals also require special consideration:


Inlining: If a function Parent() contains a call to Foo(), the compiler may inline Foo() into Parent() to avoid the overhead of a function call. Patching Foo() won’t fix the copy inside Parent(). The solution is to also hot patch Parent(). Parent() may also have been inlined some other place – if so we need to patch that function too, recursively.


Globals: A global variable is nothing but a compiler-assigned memory location inside the compiled image. A reference to the global variable in code translates to instructions referencing this memory location. This presents a problem for hot patching. sqlservr_patched.exe::Foo() would be referencing a memory location inside sqlservr_patched.exe when the location of the global at runtime is in sqlservr.exe.


This is solved by the compiler transforming global references to proxy references. These references are patched up at runtime to point to the correct location. Figure 5 shows the compiler transformation.

Figure 5 Compiler transformation of globals to proxy references that can be patched to the right address at runtime.Figure 5 Compiler transformation of globals to proxy references that can be patched to the right address at runtime.

7. Closing Words and Next Steps

With the capability in place, we are now working to improve the tooling and remove limitations to make more changes hot patchable with quick turnaround. This benefits you as a customer on our platform by allowing us to fix bugs and issues quickly without impacting your workload. For now, hot patching is only available in Azure SQL Database, but some day it may also come to SQL Server. Let us know via if you would be interested.


We hope this gave you a view into some of the exciting work we are doing. While this barely scratches the surface of hot patching, please leave comments and questions below or contact us on the email above if you would like to see more in-depth coverage of cool technology we work on.


Great post! The idea is very interesting and you explained the meaning of "hot patching" great. You provide the insights on the concept "Hot Patching" and the architecture, but after reading the post I feel that I got nothing which I can take with me and implement.


As you said "this barely scratches the surface of hot patching", so it is time for some C/C++ code to show how do you redirect function calls targeting the old function(s) to new replacement function(s). I know how to load classes dynamically and add functions for example but how do you redirect the call for existing function (in practical)? Can you provide a link maybe to give a starting point for developers?


I loved the idea, and now I would like to implement same procedure in my app for example, but you do not give me any starting point. I want to see some code of what you do and not just the explanation of how it is done in general :smile:.


>> Let us know via if you would be interested.

Not clear to me "interesting in what".  You mean interesting to have the same procedure in SQL Server on-premises? If so then obviously it sound awesome.

Senior Member

This is very impressive. However, there is one statement that isn't quite true: "We have automatic scheduling that avoids deploying during an Azure region’s core business hours." Deployments routinely occur at 4pm or even 3pm in any given region. Azure SQL Database updates occurred today in North Europe (Ireland) at 4pm Ireland time, as one example: "This notification is for upcoming scheduled maintenance to your Azure SQL Database instance(s) in North Europe. This is scheduled to occur between 16:00 UTC on 14 Sep 2019 and 07:00 UTC on 15 Sep 2019." I suppose it depends on how you define "core business hours".


Hi @pituach - Thanks for the feedback!


The C code we use to perform the patching (instruction overwriting) is not too complicated and does pretty much what is described above. It goes roughly like this:

  1. Load the patched image using LoadLibrary()
  2. Find the module base address of the image to be patched.
  3. Use the pre-computed function offset(s) within each image to acquire a void* to where the functions start.
  4. Deref the pointers and write JMP instructions directly to the memory address.

But it's only a small piece of the puzzle. The hardest part of hot patching is to prove that redirecting a function will do the right thing. For example, is it referencing other functions or globals, has it been inlined, are compiler optimizations going to cause trouble etc. This is what I briefly touch on in section 6, but this topic is so large that one could write a PhD thesis about it.


So I had to limit the scope to not turn the blog post into a book :) Given that the scope of doing it safely is beyond what I can cover here, I didn't want to include examples of doing something that would almost certainly go wrong.


But it's great to know that there are folks out there that want more technical details. Maybe I'll do a follow-up post later.


- Hans


Hi @m60freeman - thanks for the feedback!


Core business hours is defined as 08:00 (8am) to 17:00 (5pm), local time. In your example below, 16:00 UTC is actually 17:00 local time because during daylight savings time (DST), GMT standard time is UTC+1 hour, not UTC. You can see that on the end time - the deployment stops by 7am UTC, which is 8am local time.


Let me know if you have further questions.


- Hans

Thanks :thumbs_up:

Senior Member

@norheim Here is a recent notice for West Europe for 15:00 UTC, and there have been many more like it:




Rescheduled 8/31/2019 6:41:07 AM (UTC)


This notification is for previously scheduled maintenance to your Azure SQL Database instance(s) in West Europe. Unfortunately, the maintenance is taking longer than expected. The maintenance is currently paused and will be resumed at 15:00 UTC on 31 Aug 2019 and is expected to complete by 06:00 UTC on 01 Sep 2019. Feel free to refer to your portal for more information ( and for any additional questions, please contact support.

Resource Group


Server Name


Database Name / Elastic Pool







Service Name


SQL Database

West Europe


Hi @m60freeman,


West Europe is in the Netherlands, which is following Central European Time - CET (UTC+1) in the winter and Central European Summer Time - CEST (UTC+2) in the summer. The notification you pasted above is for 15:00 UTC, which is 17:00 or 5pm in local time (CEST).


- Hans

Senior Member

@norheim Thanks for the additional information. That makes me feel a lot better! I didn't realize the EU time zones moved forward an hour in the summer instead of back an hour like in the U.S.

Occasional Visitor

In this process, how you "stay under the radar" with anti-virus and advanced threat protection software.  I would think this process could easily be interpreted as an attack.   

@Trayce_Jordan , if you execute this procedure from application which was configure in the white list of the anti-virus app then what is the issue? Any anti-virus app today has options to configure white list


@Trayce_Jordan The actions we perform are of similar nature as what a debugger would do, so it's not necessarily the case that antivirus software would conclude it's malware. In either case, an exclusion can be registered as @pituach pointed out.

Version history
Last update:
‎Nov 09 2020 09:41 AM
Updated by: