Architecture: Refactor to Manager-Worker model for Multi-Identity & Scalability

[josephaw1022]

Description

Currently, the KubeSQLServer-Operator operates using a single controller that handles the reconciliation logic for all SQLServer and ExternalSQLServer resources. While efficient for basic setups, this architecture hits a limitation when dealing with Workload Identity or IAM Roles. A single controller instance cannot easily assume multiple distinct identities to connect to various SQL Servers across different security boundaries.

We propose refactoring the operator to a Manager-Worker architecture.

Proposed Architecture: Manager-Worker Model

The main operator controller will act as a Manager. For every SQL Server instance (in-cluster or external) that needs management, the Manager will deploy and maintain a dedicated Worker Pod.

Key Benefits:

• Identity Isolation: Each Worker Pod can run under its own ServiceAccount with specific Workload Identity or IAM Role bindings.
• Scalability: Management tasks are distributed across pods rather than tax a single controller's reconcile loop.
• Security: Authentication credentials (or tokens) are localized to the pod managing that specific instance.

Workflow:

1. User creates a SQLServer or ExternalSQLServer CRD.
2. The Main Controller (Manager) reconciles the CRD and creates a Worker Pod.
3. Authentication details are passed to the Worker Pod via environment variables:
   • Basic Auth: SQL_USER, SQL_PASSWORD (from secrets).
   • Identity-based Auth: AUTH_METHOD=WorkloadIdentity (Worker pod uses its own assigned identity).
4. The Worker Pod connects to its assigned SQL Server and performs all DDL/DCL operations (creating databases, schemas, logins, users).

Visual Representation

graph TD
    subgraph "Kubernetes Cluster"
        M[Main Controller - Manager] -- manages --> W1[Worker Pod - Instance A]
        M -- manages --> W2[Worker Pod - Instance B]
    end

    subgraph "Target SQL Servers"
        W1 -- "Auth: Basic/Workload Identity" --> S1[(SQL Instance A)]
        W2 -- "Auth: Basic/Workload Identity" --> S2[(SQL Instance B)]
    end

Loading

Why is this needed?

This refactoring is essential for supporting modern cloud-native authentication methods and ensuring that the operator can scale securely in complex enterprise environments.

Implementation Notes:

• Consider using a Sidecar or a Job-based approach if long-running workers aren't required, though a long-running pod might be better for continuous reconciliation.
• Define a standard "Worker" image that contains the minimal logic for SQL management.
• Ensure the Manager can track Worker Pod health and restart them if they fail.

[josephaw1022]

The new CRD should be named KubeSqlWorker. The dedicated controller will handle creating the Deployment, ServiceAccount, ConfigMaps, and any necessary Secrets required by the worker pods.

[josephaw1022]

Here is the refined and finalized architectural roadmap reflecting the latest breakdown of controller boundaries and individual web API sub-controller workers:

KubeSQLServer-Operator Manager-Worker Architecture Roadmap

Based on your feedback, this is the finalized roadmap to transition the operator to a flexible, multi-identity Controller-of-Controllers model.

1. Up-Front Work (CRD Re-Design & Refactoring)

The upfront work establishes our Identity Configuration, the Worker CRD, and addresses database-level isolation constraints found in Azure.

A. Introduce the KubeSqlWorker CRD

We will introduce a KubeSqlWorker CRD to act as the explicit configuration glue for the deployments. This gives users fine-grained control over the worker's computing resources and configuration, and acts as the trigger for the Main Controller.

B. Expand Identity on ExternalSQLServer and Re-Introduce ExternalDatabase

ExternalSQLServer and the newly reintroduced ExternalDatabase CRD (required for Azure SQL where certain Managed Identities can only be created at the database level) will be expanded with an Identity Specification:

• Add AuthType field: SqlLogin, WorkloadIdentity, and AppRegistration.
• Secret/Account references: Add ServiceAccountName and ClientId for managed identity mapping.

C. Rip ISqlExecutor Out of Main Controllers

The Main Controller's sole responsibility becomes orchestration. It will no longer directly execute T-SQL or directly create StatefulSets.

---

2. The Controller-of-Controllers Architecture

To keep the architecture scalable and isolated, the Main Controller delegates all actual reconciliation to sub-controllers (Workers) that it deploys into the cluster as .NET Web APIs.

A. Handling Internal SQLServer Resources

• Dedicated Infrastructure Controller: The Main Controller will deploy a single, separate sub-controller (Deployment) specifically for internal SQLServer resources.
• Unified Responsibility: This sub-controller not only manages the base Kubernetes infrastructure (StatefulSets, Services, PVCs, generating the sa Secret) for internal servers, but it also handles all SQL execution mapping to those servers. Any DatabaseUser, SqlServerLogin, Database, or Schema belonging to an internal SQLServer CRD is directly executed by this single dedicated Controller. Since it natively securely manages the sa secrets for the clusters it deploys, this centralized execution makes perfect sense for all non-external databases.

B. Handling ExternalSQLServer & ExternalDatabase Resources

• Individual Worker Controllers: For every ExternalSQLServer or ExternalDatabase CRD created by a user, the Main Controller generates a dedicated KubeSqlWorker (and subsequently a 1-to-1 Web API Deployment, ServiceAccount, and Role).
• These individual workers isolate the blast radius, localized RBAC permissions, and Azure Workload Identity annotations precisely to the target external database. They are isolated exclusively to DDL/DCL (executing T-SQL queries) specifically for their identity context.

---

3. Worker Architecture: Long-running .NET Web API (Deployment)

Whether it's the shared Internal SQL Infrastructure Controller or an individual External Database Worker, the sub-controllers will be structured as .NET Web API projects.

• Structure: This provides out-of-the-box HTTP endpoints (/healthz, /readyz, /metrics) that Kubernetes uses for container probes. The actual reconciliation work executes as a background IHostedService within the API.
• Workload Identity Configuration:
  When AuthType is WorkloadIdentity, the Main Controller generates the ServiceAccount and Deployment for the specific worker with the correct Azure annotations:
  • Service Account Annotations: azure.workload.identity/client-id, azure.workload.identity/tenant-id
  • Pod Label: azure.workload.identity/use: "true"

---

4. Recommended Roadmap Schedule

Milestone 1: The CRD Foundation

• Update ExternalSQLServer and SQLServer to include the Identity spec (AuthType = SqlLogin | WorkloadIdentity | AppRegistration).
• Create / Refine the ExternalDatabase CRD to possess the exact same Identity Spec.
• Create the KubeSqlWorker CRD definition.

Milestone 2: Main Controller Orchestration & RBAC

• Strip T-SQL execution and StatefulSet creation out of the main controllers.
• Update the Main Controller so it spins up the single Internal SQL Infrastructure Controller.
• Update the Main Controller so it automatically generates a KubeSqlWorker (and its isolated Deployment, ServiceAccount, Role, and RoleBinding) for every ExternalSQLServer and ExternalDatabase. This results in N number of External Web API workers, where N is the sum of External SQL Server and External Database instances.
• Dynamically apply the Azure Workload Identity azure.workload.identity/use labels and ServiceAccount annotations based on the target AuthType.
• Main Controller RBAC: Grant the operator's service account permissions to purely create/manage Deployments, ServiceAccounts, Roles, and RoleBindings.

Milestone 3: The Internal SQL Server Infrastructure Controller Image & RBAC

• Create the new C# project OperatorTemplate.InternalWorker as a Web API containing the Health Checks and Metrics endpoints.
• Port over the StatefulSet / Service creation logic alongside the native ISqlExecutor operations for databases/schemas/users/logins targeting the natively generated instances into a dedicated background IHostedService in this controller.
• Worker RBAC: Ensure this Internal Web API Controller receives broad permissions within the target namespace to manipulate Deployments, PVCs, StatefulSets, and internal users/CRDs.
• Deliverable: Published Docker image for the Internal SQL Infrastructure Controller.

Milestone 4: The External SQL Server/DB Worker Image & RBAC

• Create the sequential C# project OperatorTemplate.ExternalWorker as a Web API containing Health and Metrics.
• Port over ISqlExecutor and the T-SQL generation logic into a background IHostedService handling the external logic and Workload Identity connection mapping.
• Worker RBAC: Ensure the generated isolated Role for the individual External Worker's ServiceAccount has strict Role-based permissions strictly limited to get, list, watch, and update status on its assigned DatabaseUser & SqlServerLogin CRDs.
• Deliverable: Published Docker image for the External SQL Worker Controller.

[josephaw1022]

For the sake of time we are going to just keep everything that is related to the operator managed sql server and database in the current controller... only the new crd (kubesqlworker) and the new operator dotnet project will be scaffolded in this...

this pr isnt even going to include support for using managed identity when connecting to the sql server and or database just yet. that will come in a later pr.

so this means that we will skip making the internal controller for now. just stick to the new crd and the new controller.

[josephaw1022]

Proposed Changes

1. KubeSqlWorker CRD

• [NEW] KubeSqlWorker CRD definitions
  We will define a new Custom Resource Definition named KubeSqlWorker. This CRD will act as the configuration glue for worker deployments. The exact details will remain minimal for now as deep Workload Identity specs are deferred.

2. Scaffold New Operator .NET Project

• [NEW] OperatorTemplate.ExternalWorker
  Create a new KubeOps-based operator project using the KubeOps SDK templates.
  • Run dotnet new install KubeOps.Templates
  • Scaffold with dotnet new operator -n OperatorTemplate.ExternalWorker
  • Add the new project as a reference to the OperatorTemplate.AppHost project.
  • Scaffold a corresponding unit test project (e.g. OperatorTemplate.ExternalWorker.Tests) and add a reference to the worker project.

3. Maintain Exisiting Controller Logic

• The current main controller will not be stripped of its T-SQL execution logic or StatefulSet creation logic yet.
• The concept of the "Internal SQL Infrastructure Controller" is skipped for this PR. All logic related to the operator-managed SQL server and database remains functionally untouched in the primary controller.

Verification Plan

Automated Tests

• Run dotnet build to ensure the entire solution, including the newly scaffolded project, compiles cleanly.
• Run dotnet test to confirm that the existing tests still pass and no regressions have been introduced into the main controller.

Manual Verification

• Deploy the operator to a local kind cluster using the existing workflow.
• Ensure the newly added KubeSqlWorker CRD is successfully applied to the cluster.
• Ensure the main operator pod spins up successfully and handles existing CRDs without any errors.