Chapter 25 · Production Capstone: Design, Secure, Tune, Automate, and Recover SQL Server
Implement Schema, Indexes, Security, Encryption, Agent Automation, and Migration Pipeline
Deploy the capstone schema, indexes, least-privilege roles, encryption boundaries, automation and source-controlled migration state with explicit postconditions.
Learning outcomes
The architecture record now has to become deployable state. This lesson treats deployment as a transaction between source control and a disposable target: schema, constraints, indexes, identities, permissions, encryption boundaries, audit/automation and migration state must be reproducible and verifiable. A successful script is not enough if it leaves an over-privileged user, an untrusted constraint, a missing index, or a migration that cannot be rerun.
Build the ServiceHub schema and index portfolio from explicit workload/access requirements.
Create least-privilege database identities without embedding credentials in lesson source.
Distinguish TLS, TDE and application/column encryption and gate them by edition/topology requirements.
Create idempotent deployment/migration evidence and an Agent-or-external-scheduler automation boundary.
Prove clean deployment state with catalog evidence rather than trusting script exit status alone.
ServiceHubCapstone. No production passwords,
certificates, private keys, cloud credentials, or real customer
data are embedded. TDE and encrypted backup creation are not
available in Express. SQL Server Agent is not available in
Express. The core deployment therefore uses database roles/users
and an idempotent T-SQL runbook; optional Standard/Enterprise
Developer extensions demonstrate TDE/Agent without making them
mandatory.
1. Schema and indexes must encode business invariants
USE ServiceHubCapstone;GOIF COL_LENGTH(N'ops.WorkOrder',N'region_code') IS NULL ALTER TABLE ops.WorkOrder ADD region_code char(3) NULL;GOUPDATE w SET region_code=t.region_codeFROM ops.WorkOrder AS wLEFT JOIN ops.Technician AS t ON t.technician_id=w.technician_idWHERE w.region_code IS NULL;GOIF OBJECT_ID(N'ops.CK_WorkOrder_region',N'C') IS NULL ALTER TABLE ops.WorkOrder WITH CHECK ADD CONSTRAINT CK_WorkOrder_region CHECK(region_code IN ('N01','W02','E03'));GOIF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(N'ops.WorkOrder') AND name=N'IX_cap_WorkOrder_RegionStatus') CREATE INDEX IX_cap_WorkOrder_RegionStatus ON ops.WorkOrder(region_code,status,opened_at) INCLUDE(customer_code,technician_id,priority);GOAdding a nullable column, backfilling, validating, then enforcing constraints is an expand/contract pattern. The index is justified by the dispatcher workload; it is not added because “every filter column needs an index.”
2. Least privilege: login/user/role are separate boundaries
USE ServiceHubCapstone;GOIF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name=N'svc_servicehub_app') CREATE USER svc_servicehub_app WITHOUT LOGIN;IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name=N'svc_servicehub_ops') CREATE USER svc_servicehub_ops WITHOUT LOGIN;IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name=N'servicehub_app_role') CREATE ROLE servicehub_app_role AUTHORIZATION dbo;IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name=N'servicehub_ops_role') CREATE ROLE servicehub_ops_role AUTHORIZATION dbo;IF NOT EXISTS(SELECT 1 FROM sys.database_role_members WHERE role_principal_id=USER_ID(N'servicehub_app_role') AND member_principal_id=USER_ID(N'svc_servicehub_app')) ALTER ROLE servicehub_app_role ADD MEMBER svc_servicehub_app;IF NOT EXISTS(SELECT 1 FROM sys.database_role_members WHERE role_principal_id=USER_ID(N'servicehub_ops_role') AND member_principal_id=USER_ID(N'svc_servicehub_ops')) ALTER ROLE servicehub_ops_role ADD MEMBER svc_servicehub_ops;GRANT SELECT,INSERT,UPDATE ON SCHEMA::ops TO servicehub_app_role;DENY DELETE ON SCHEMA::ops TO servicehub_app_role;GRANT SELECT ON SCHEMA::governance TO servicehub_ops_role;GOEXECUTE AS USER=N'svc_servicehub_app';SELECT TOP(1) * FROM ops.WorkOrder;REVERT;GO
The lab uses WITHOUT LOGIN users so there is no
fake password to copy into production. Real applications should
use an approved authentication model, mapped user/SID, secret
manager or managed identity/Entra mechanism as appropriate.
Permissions are verified with execution context rather than
inferred from role names.
3. Encryption is layered, not a single checkbox
| Control | Protects primarily | Key operational dependency |
|---|---|---|
| TLS | Client/server transport | Certificate trust + hostname validation + driver settings |
| TDE | Database data/log files and backups at rest | Protector certificate/key backup and restore availability |
| Always Encrypted/application crypto | Selected sensitive values from server-side plaintext exposure | Client driver/key-store/model constraints |
USE ServiceHubCapstone;GOSELECT DB_NAME(database_id) AS database_name,encryption_state,key_algorithm,key_lengthFROM sys.dm_database_encryption_keysWHERE database_id=DB_ID();GO-- OPTIONAL on supported Standard/Enterprise Developer lab editions:-- Create a master key/certificate in master, back up the certificate + private key,-- create a database encryption key in ServiceHubCapstone, then SET ENCRYPTION ON.-- Do not run without an explicit key-backup/restore plan.The wrong capstone would enable TDE and then delete the certificate/private key, turning a security feature into a recovery outage. The mandatory path therefore teaches the dependency without fabricating secret material.
4. Migration and automation must be idempotent
USE ServiceHubCapstone;GOIF OBJECT_ID(N'governance.SchemaVersion',N'U') IS NULLCREATE TABLE governance.SchemaVersion( version_id varchar(40) NOT NULL PRIMARY KEY, applied_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME(), checksum_hex varchar(128) NULL, applied_by sysname NOT NULL DEFAULT ORIGINAL_LOGIN(), verification nvarchar(600) NOT NULL);GOIF NOT EXISTS(SELECT 1 FROM governance.SchemaVersion WHERE version_id='2026.08.capstone.001')INSERT governance.SchemaVersion(version_id,checksum_hex,verification)VALUES('2026.08.capstone.001','DEMO-CHECKSUM-REPLACE-IN-CI',N'Constraints trusted; required indexes/principals present; business smoke test passed.');SELECT * FROM governance.SchemaVersion ORDER BY applied_at;In a real pipeline, the checksum comes from the version-controlled migration artifact and deployment fails if a previously applied immutable migration has changed. Express learners can invoke the same script from CI, Task Scheduler, cron or another approved scheduler. Standard/Enterprise Developer learners may schedule a SQL Server Agent job, but the job should call the same tested procedure/script instead of becoming the only copy of operational logic.
5. Prove deployment state
USE ServiceHubCapstone;GOSELECT name,is_disabled FROM sys.check_constraints WHERE parent_object_id=OBJECT_ID(N'ops.WorkOrder');SELECT name,type_desc,is_disabled FROM sys.indexes WHERE object_id=OBJECT_ID(N'ops.WorkOrder');SELECT USER_NAME(role_principal_id) AS role_name,USER_NAME(member_principal_id) AS member_nameFROM sys.database_role_membersWHERE role_principal_id IN (USER_ID(N'servicehub_app_role'),USER_ID(N'servicehub_ops_role'));SELECT grantee_principal_id,permission_name,state_desc,class_descFROM sys.database_permissionsWHERE grantee_principal_id IN (USER_ID(N'servicehub_app_role'),USER_ID(N'servicehub_ops_role'));SELECT * FROM governance.SchemaVersion;A script that exits with code 0 but leaves a disabled constraint or missing permission is a failed deployment. Catalog-based postconditions are the contract.
Check your understanding
- Why use WITHOUT LOGIN users in the mandatory lab?
- Does TDE replace TLS?
- Why should Agent call the same source-controlled runbook as an external scheduler?
- What makes a migration idempotent?
- What proves a deployment succeeded?
Review the answers
1. They allow authorization testing without inventing or embedding credentials/server logins.
2. No. TDE protects database files/backups at rest; TLS protects network transport.
3. It avoids hiding the only copy of operational logic inside msdb and keeps execution testable/versioned.
4. Rerunning it either performs no unsafe duplicate work or converges to the same verified state.
5. Catalog/business postconditions, not merely script completion or job success.