Across fifteen years and numerous companies and industries, you end up working on projects that stick with you. This is one of them: a strangler fig modernisation against a legacy desktop application.
It started as a Hackathon Friday proof of concept. One Friday to answer the question: if we host the database ourselves, can we connect to it directly and build something lighter on top? The answer was yes. The proof of concept became a real project. What made it real was not the Azure setup (that part was done in a day) but the data investigation, which took weeks. The rest of this post is about that.
The software managed operational workflows and was deeply embedded in how the business ran. It was not going anywhere. It ran on Windows and required an RDP session to use from anywhere else. There was no API. No REST endpoint, no webhook system, nothing. It was never designed for integration.
What there was: a SQL Server database on an Azure VM that the company hosted itself. And that database contained everything the application knew.
The immediate pain point was a workflow that required staff to open a full Windows RDP session just to complete what was, structurally, a simple read-then-write operation. The overhead of maintaining that setup across multiple sites, licensing, hardware, support time, was significant enough that someone eventually asked whether there was a better way. That question landed on my desk.
My first question back: if we host the database, can we connect to it directly and build something lighter on top? It turned out we could. So we did. The table and column names in this post are illustrative, not the vendor's actual schema. This is strangler fig modernisation. The vendor app keeps running. We just stop using its front end for the things we can do better ourselves.
The strangler fig pattern: Named after the tropical fig that grows around a host tree, gradually replacing it. You build new functionality around the edges of the old system, eventually replacing it entirely without ever needing a single "big bang" migration. The old system remains operational throughout.
The Real Problem Isn't Cloud Architecture
When I started this project, I assumed the hard part would be the Azure setup. Getting VNET integration working. Configuring NSG rules. Sorting out the Flex Consumption plan. The usual infrastructure plumbing. On Hackathon Friday, I had the architecture running and talking to the database by end of day.
That is when it became clear the hard part was elsewhere.
The hard part is understanding how the vendor database actually works.
Here is what makes it unusual: there is no single stored procedure, no single view, no single query you can run that returns a complete operational record the way an operator would see it on screen. The database is not designed with an API contract. It is designed as a backing store for a desktop application that does all the assembly work on the client side.
"The front end is the orchestrator. There is no single 'give me record 8001 fully assembled' query. The native client does all the fan-out work itself."
The native client behaves like this: when an operator opens a record, the application resolves the component pieces from several related tables. For each piece, it resolves the associated grouping record. For each group, it walks every associated task. For each task, it fetches the spatial data and the timing data associated with it, often in batches. Then as a final pass, it resolves all the lookup values and place names.
This is classic vendor native client behaviour. A lot of tables get queried, in sequence, to assemble what looks to the user like a single screen. Assembly logic lives in the application binary. That pattern made sense in an era when the application and database sat on the same LAN and round trips were cheap. It does not make sense when you are trying to build a responsive API that returns a complete record in a single call, and I suspect it is not doing the native app's own performance any favours either.
The solution is not to replicate this fan-out pattern in our API layer. That would be slow, fragile, and defeat the purpose. Instead, we build consolidated server-side queries, views and stored procedures that do the assembly work once, in SQL, and return a complete record in a single call. The database is fast at joins. Let it do what it is good at.
Client-side fan-out
Many tables queried in sequence, some batched, some genuinely N+1. Each table queried independently. Assembly logic lives in the application binary. Only works well over a fast LAN connection. Impossible to replicate cleanly in an API layer.
Server-side consolidation
One stored procedure per API endpoint. Joins happen in SQL where they belong. Returns a complete, pre-assembled result set. API layer is thin, just authentication, serialisation, and the proc call. Fast over any connection.
The detective work: None of this is documented. The vendor has no ERD we can request. Understanding the relationships between entity_record, entity_group, task, task_detail, and location required tracing which queries the native client fires and in what order, then reverse-engineering the join logic from observed behaviour. This is the actual hard work of strangler fig modernisation against a vendor system.
Four AIs, One Answer
Before committing to an architecture, I did something I have started doing for any significant technical decision: I asked four independent AI systems the same question, with the same context. Not because I cannot decide myself, but because convergent recommendations from different models, trained on different data, by different organisations, is a useful signal that an approach is well-established rather than novel or risky.
The question: given that we need an API layer to progressively replace a vendor app, the database runs on an Azure VM we own, and our existing stack is Azure-native (Static Web Apps, Functions, Azure SQL), should we host the API on IIS on the same VM, or on Azure Functions with VNET integration to reach the VM's private IP?
All four said the same thing.
Recommended Azure Functions + VNET on the basis of stack consistency, security isolation, and the strangler-fig pattern. Suggested a dedicated portal schema to separate our tables from vendor tables. Raised Entra ID SSO as the right auth pattern.
Most detailed breakdown. Emphasised that IIS introduces a second operational paradigm, patching, app pool management, SSL cert rotation, all on the same box as the database. Recommended Flex Consumption for VNET reliability.
Focused on separation of concerns, a heavy API process could starve SQL Server of CPU/RAM when both share a VM. Also flagged DNS resolution as a specific gotcha requiring Private DNS Zone or direct IP.
Most cost-focused. Framed it as: IIS appears free but costs 1–2 days/quarter in engineering time for maintenance. Functions has a known monthly spend but eliminates web server management entirely. Suggested a warm-ping to eliminate cold starts for terminal users.
The convergence matters, with a caveat: four models, trained by four different organisations, independently landing on the same recommendation for the same reasons is a useful sanity check, not proof. It does not replace a real security review or a proof of concept, but it did give me confidence the pattern was well-established rather than something I was talking myself into.
Why Not Just Use IIS on the Same VM?
A colleague raised this early on. The VM is already running. IIS is free. Why add complexity? It is a fair question, and the short answer is: because putting the API on the same machine as the database means a failure in one can take down the other, and because it introduces a completely different deployment and maintenance model from everything else we run.
| Consideration | IIS on VM | Azure Functions + VNET |
|---|---|---|
| Security posture | API and DB co-located. A compromise of the web layer now sits on the same host as the database, shortening the path to the data and widening the blast radius of any incident. | Isolated SQL stays on private IP, never exposed to internet. |
| Blast radius | Memory leak or crashed app pool starves SQL Server of resources on the same box. | Isolated Misbehaving function cannot affect DB performance. |
| Ops overhead | Windows patching now affects both DB and API. IIS hardening becomes ongoing. | Managed Azure manages the runtime. No IIS to patch. |
| Cost | $0 additional VM is already running. | Flex Consumption: rounds to a few cents/month at current traffic levels. |
| Latency | Zero hop Same machine. | Negligible for our workload over VNET. Not measured, not a bottleneck. |
| Future roadmap | Tightly couples API to VM config. Harder to decompose. | Modular Each endpoint deploys independently. Ideal for strangler fig. |
The only scenario where IIS clearly wins is ultra-low-latency, high-frequency transactional workloads, thousands of synchronous CRUD operations per second, or a dependency on Windows-only components like COM+. Neither applies here. Our traffic is internal, low-to-moderate, and primarily batch-oriented.
The Setup: The Full Stack
The actual infrastructure is straightforward once you understand the moving pieces. I am not going to walk through every step here, but the key architectural decisions are worth surfacing because they are the ones that matter for anyone doing something similar.
The full stack, from the user's browser to the vendor database, looks like this:
Azure Static Web App for the front end
The front end is a static site hosted on Azure SWA. No server, no container, no deployment pipeline beyond pushing to a branch. SWA has built-in Entra ID authentication, which handles the access control story entirely: only users who belong to a specific Entra security group can reach the application at all. No custom login page, no token management code, no auth middleware.
The tablet itself signs in using a shared kiosk identity that belongs to that security group. This means the device is always authenticated. The application does not need to know which individual is at the tablet. It handles individual identity through its own logic, the PIN lookup against the database. The Entra layer just ensures that only authorised devices can reach the API in the first place.
Dedicated subnet, delegated to Functions
The Function App gets its own subnet within the existing VNET. This subnet is delegated to Microsoft.App/environments, which is the delegation Flex Consumption requires (it differs from Premium and Dedicated plans, which use Microsoft.Web/serverFarms). Microsoft's minimum is a /27, but a /26 gives more headroom for scale-out. It does not need to be routable from the internet.
NSG rules: Functions subnet → VM on port 1433 only
The Network Security Group on the VM's subnet allows inbound traffic from the Functions subnet on TCP 1433 (SQL Server) and nothing else. The Functions subnet's NSG allows outbound to the VM subnet on 1433. Everything else is denied. This is the smallest possible network aperture for the job.
Flex Consumption plan
Not Premium, not Consumption. Flex Consumption supports VNET integration at pay-per-execution pricing, is confirmed available in Australia East, and is already in use across our other systems. At current traffic levels, internal users only, low volume, the execution cost rounds close to zero, though Key Vault, storage, and Application Insights add a small monthly cost on top. If traffic grows, it scales.
Key Vault for connection strings
No secrets in app settings. The Function App has a managed identity with Get permission on specific Key Vault secrets. The connection string to the VM's SQL Server lives in Key Vault, referenced via @Microsoft.KeyVault(SecretUri=...) syntax. If someone gains access to the Function App's configuration, they see a vault reference, not a password.
Connectivity smoke test
Before writing any business logic, we deploy a single function that does one thing: connect to SQL Server on the VM's private IP, run SELECT 1, and return the result. If this works, the entire chain is validated, VNET delegation, subnet routing, NSG rules, DNS resolution, SQL authentication, Key Vault secret retrieval. One test. Whole chain confirmed.
import azure.functions as func
import pyodbc, os
def main(req: func.HttpRequest) -> func.HttpResponse:
conn_str = os.environ["OPS_SQL_CONNECTION"]
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
cursor.execute("SELECT 1 AS alive")
row = cursor.fetchone()
conn.close()
return func.HttpResponse(f"Connected: {row[0]}")
What About App Service?
A secondary question I explored: should this be an Azure App Service instead of Functions? The answer from all four AI consultations was consistent: Functions is right for this phase. App Service becomes the migration path if complexity grows significantly, requires complex middleware chains, or needs long-running background processes.
The key insight is that Flex Consumption closes the two main gaps App Service previously had over Functions: VNET cost (no fixed plan required) and cold starts (configurable "always ready" instances, though those carry a baseline cost). At low traffic, accepting occasional cold starts keeps the monthly bill near zero. If warm response times become a requirement, always-ready instances are the lever, and the cost is still well below a Premium plan. Either way, Functions is the simpler, cheaper, more aligned choice for this phase.
The architecture is designed so that migrating from Functions to App Service is a configuration change, not a rewrite. The stored procedures stay the same. The authentication logic stays the same. Only the hosting model changes. That is deliberate.
The migration threshold: If the portal grows beyond 50 endpoints, needs WebSocket connections for real-time features, or requires complex middleware (rate limiting, request transformation, response caching), that is when App Service becomes the better fit. Until then, Functions keeps things simple and cheap.
The Stored Procedure Contract
Every database interaction in this system goes through a named stored procedure. No raw SQL in the application code. No ORM generating queries. No ad-hoc statements assembled from string concatenation. A function endpoint calls a proc by name, passes parameters, and gets a result set back. That is it.
This is a deliberate architectural choice, not a preference. It limits blast radius in three ways:
The API can only do what the procs allow
If a function endpoint is compromised, the attacker can call stored procedures. They cannot run arbitrary SQL. They cannot drop tables, read unrelated data, or modify the schema. The procs are the attack surface, and we control exactly what each one does.
The portal schema separates our code from the vendor's
All our stored procedures, views, and tables live in a portal schema. The vendor's tables stay in dbo. This makes it immediately visible which objects are ours and which are theirs. If the vendor ships an update, our schema is untouched. If we make a mistake, the vendor's schema is untouched.
The SQL login has EXECUTE permission only on portal procs
The service account used by the Function App has no direct SELECT/INSERT/UPDATE on any table. It can only EXECUTE procedures in the portal schema. Those procedures internally read from vendor tables (with carefully constructed joins) and write only to portal-owned tables. The permission boundary is explicit and auditable.
The tradeoff I'm not going to pretend isn't there: pushing every query into a stored procedure means business logic lives in the database layer, not in application code. That means schema changes and logic changes are coupled, code review happens in SQL rather than in pull requests with the same tooling as everything else, and version control for procs takes more discipline than version control for a codebase. We accepted that coupling in exchange for the blast-radius containment. It is a real cost, not a free security win.
[portal].[GetEntityStatus] -- Our proc, reads from dbo tables via JOINs
[portal].[GetEntityRecord] -- Our proc, returns assembled record data
[portal].[LogPortalActivity] -- Our proc, writes to portal.activity_log only
[dbo].[entity_record] -- Vendor table, read-only from our procs
[dbo].[entity_group] -- Vendor table, read-only from our procs
[dbo].[task] -- Vendor table, read-only from our procs
The First Surface: Replacing an RDP-Dependent Workflow
The first endpoint replaced a workflow that required staff to open a full Windows RDP session just to complete a simple read-then-write operation: look up their record, verify the business rules, write a timestamped event back to the database. The logic was straightforward. The overhead was not.
We replaced it with a browser-based interface on a cheap tablet: one API call in, one API call out, no RDP, no licensing per terminal, no hardware dependencies. The operation that used to require a full desktop session took under two seconds in a browser.
That was the point. Not the technology for its own sake, but removing a layer of operational friction that existed only because the original application was built for a different era.
The cost argument is simple: a full Azure Virtual Desktop (AVD) session plus licensing plus hardware plus IT support overhead per terminal, multiplied across every site. Versus a browser tab on a cheap tablet with no peripherals and near-zero Azure Function cost. The arithmetic does itself.
Lessons Learned
The project is still running, but these are the things I would tell someone starting the same work.
The database investigation is where strangler fig projects actually live or die
The cloud architecture for this kind of project is a solved problem. VNET integration, managed identity, Flex Consumption, these are well-documented, well-understood, and the Azure docs will walk you through them. What no documentation prepares you for is mapping an undocumented schema, tracing query patterns from a client binary, and working out the business rules from observed behaviour. That is the part that took weeks. If you are planning a strangler fig project against a vendor system, budget your time accordingly: the infrastructure is a day, the data investigation is the project.
I forgot about Query Store (and it cost me weeks)
This is embarrassing. I know about Query Store. I have used it before. But when I started reverse-engineering the database, my instinct was to go table by table: SELECT TOP 100 * FROM entity_record, look at the columns, try to find foreign keys, guess which columns join to which tables. I did this for dozens of tables. It was slow, tedious, and often led nowhere.
Then one day a cloud engineer mentioned offhand that "some of the queries are returning thousands of rows each call." And I thought: wait, you can see what queries are running? Of course you can. Query Store was enabled the entire time. I could have looked at the top queries by execution count, seen exactly which tables the native client hits and in what order, and reverse-engineered the join patterns in a fraction of the time.
It still would not have been simple, there are no stored procedures or views in the vendor database, just raw ad-hoc SQL fired by the native client. But Query Store would have given me the table relationships and access patterns immediately instead of me puzzling them out manually. Sometimes you forget the tools you already know. Write them on a sticky note.
Start with the highest-value, lowest-risk surface
This first endpoint was valuable because it was the thing causing the most operational pain. It was low-risk because the operation is primarily a read with a single write at the end. If our endpoint returns bad data, nobody loses anything, the user just falls back to the old process instead. This is not true of a record modification endpoint where a bug could corrupt operational data. Start where the value is high and the downside is small.
The vendor app is not your enemy
Strangler fig does not mean the old system is bad. The vendor software is good at what it does. It is not going anywhere soon. What it is not good at is being accessed from a modern device without RDP. We are not replacing the operational engine. We are replacing the access layer. The vendor app will keep running, doing what it does well. We are just giving people a faster, simpler way to interact with the data it produces.
Being a data person is an advantage here. Not a limitation.
I did not come into this project from a software engineering background. My instinct when facing an unfamiliar system is not to look at the application code. It is to look at the data. What tables exist. How they relate. What the indexes and constraints tell you about access patterns and cardinality. What Query Store reveals about query frequency and execution shape. That is just how I think.
On this project, that instinct turned out to be exactly the right starting point. Every application in the world is ultimately a layer on top of data. If you understand the data well enough (how it is structured, how it moves, what the rules actually are at the storage level) you understand the application. Not just what it does on screen, but why it does it that way.
"Strangler fig is not a weekend project. It is a posture. You commit to building one surface at a time, proving value, and expanding only when the previous surface is stable. The patience is the pattern."
What started as a Hackathon Friday proof of concept has one surface live and a handful more mapped out. I don't know yet whether we'll eventually replace every screen the vendor app offers, or whether we'll stop once the highest-friction surfaces are gone and leave the rest running quietly in the background. I don't think that question needs an answer yet. Each endpoint we ship removes a little operational pain on its own, independent of whether there's ever a "done." That's the part of the pattern that took me longest to actually believe.