Chapter 25 · Production Capstone: Design, Secure, Tune, Automate, and Recover SQL Server
Define SLOs, Workload Classes, Data Model, HA/DR Targets, Capacity, and Architecture Record
Turn business expectations into SLOs, workload classes, RPO/RTO, capacity assumptions, edition/topology choices, and an evidence-driven architecture record.
Learning outcomes
A production architecture is not a collection of SQL Server features. It is a set of explicit service promises, workload assumptions, failure boundaries, and evidence that the selected edition/topology can meet them. ServiceHub now has a business requirement: technicians must see and update work orders during regional failures, dispatch reads must remain responsive during peaks, security controls must protect customer details, and operators must be able to prove recovery—not merely own backups. The capstone therefore starts with a measurable architecture record before creating more objects.
Translate business expectations into measurable SLOs, RPO/RTO targets, workload classes, growth and retention assumptions.
Separate availability, durability, performance, security and operability requirements instead of collapsing them into one uptime percentage.
Choose a SQL Server 2025 edition/topology based on required capabilities and document free-lab versus production assumptions.
Record uncertain assumptions and define tests that can falsify them before production cutover.
Create an architecture decision record whose acceptance criteria are observable in later capstone lessons.
ServiceHubCapstone. No production passwords,
certificates, private keys, cloud credentials, or real customer
data are embedded. Enterprise-only capabilities such as advanced
Availability Groups remain design options, not mandatory lab
dependencies. Express has no SQL Server Agent and no AG/FCI;
Standard has Basic AG rather than the full Enterprise AG feature
set.
1. Start with service-level objectives, not server settings
A service-level objective (SLO) is a measurable target such as “99.9% of interactive work-order reads complete within the agreed latency threshold during supported hours.” A service-level agreement (SLA) is a contractual commitment that may include consequences. Recovery point objective (RPO) limits acceptable data loss measured in time; recovery time objective (RTO) limits acceptable service-restoration time. These are different from high availability: HA can reduce outage time, while backup/PITR is what protects against logical deletion, corruption, and some operator mistakes.
USE master;GOIF DB_ID(N'ServiceHubCapstone') IS NULL CREATE DATABASE ServiceHubCapstone;GOALTER DATABASE ServiceHubCapstone SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubCapstone SET QUERY_STORE = ON( OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO, WAIT_STATS_CAPTURE_MODE = ON, SIZE_BASED_CLEANUP_MODE = AUTO);GOUSE ServiceHubCapstone;GOIF SCHEMA_ID(N'ops') IS NULL EXEC(N'CREATE SCHEMA ops AUTHORIZATION dbo;');IF SCHEMA_ID(N'governance') IS NULL EXEC(N'CREATE SCHEMA governance AUTHORIZATION dbo;');GOIF OBJECT_ID(N'ops.Technician',N'U') IS NULLBEGIN CREATE TABLE ops.Technician ( technician_id int IDENTITY(1,1) NOT NULL CONSTRAINT PK_cap_Technician PRIMARY KEY, technician_code varchar(12) NOT NULL CONSTRAINT UQ_cap_Technician_code UNIQUE, display_name nvarchar(100) NOT NULL, region_code char(3) NOT NULL, is_active bit NOT NULL CONSTRAINT DF_cap_Technician_active DEFAULT(1), created_at datetime2(0) NOT NULL CONSTRAINT DF_cap_Technician_created DEFAULT SYSUTCDATETIME() );END;IF OBJECT_ID(N'ops.WorkOrder',N'U') IS NULLBEGIN CREATE TABLE ops.WorkOrder ( work_order_id bigint IDENTITY(1001,1) NOT NULL CONSTRAINT PK_cap_WorkOrder PRIMARY KEY, technician_id int NULL, customer_code varchar(16) NOT NULL, status varchar(16) NOT NULL, priority tinyint NOT NULL, opened_at datetime2(0) NOT NULL, scheduled_at datetime2(0) NULL, closed_at datetime2(0) NULL, description nvarchar(400) NULL, row_version rowversion NOT NULL, CONSTRAINT FK_cap_WorkOrder_Technician FOREIGN KEY(technician_id) REFERENCES ops.Technician(technician_id), CONSTRAINT CK_cap_WorkOrder_status CHECK(status IN ('OPEN','ASSIGNED','CLOSED','ESCALATED')), CONSTRAINT CK_cap_WorkOrder_priority CHECK(priority BETWEEN 1 AND 5), CONSTRAINT CK_cap_WorkOrder_closed CHECK(closed_at IS NULL OR closed_at >= opened_at) ); CREATE INDEX IX_cap_WorkOrder_CustomerStatus ON ops.WorkOrder(customer_code,status,opened_at) INCLUDE(technician_id,priority,scheduled_at);END;IF NOT EXISTS(SELECT 1 FROM ops.Technician) INSERT ops.Technician(technician_code,display_name,region_code) VALUES('T-100',N'Mina Rahimi','N01'),('T-101',N'Owen Brooks','W02'),('T-102',N'Sara Chen','E03');GOIF OBJECT_ID(N'governance.ServiceObjective',N'U') IS NULLCREATE TABLE governance.ServiceObjective( objective_name varchar(80) NOT NULL PRIMARY KEY, workload_class varchar(40) NOT NULL, target_text nvarchar(500) NOT NULL, evidence_source nvarchar(300) NOT NULL, owner_name nvarchar(100) NOT NULL, status varchar(16) NOT NULL CONSTRAINT CK_cap_Objective_status CHECK(status IN ('ASSUMED','TESTING','ACCEPTED','REJECTED')), updated_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME());IF OBJECT_ID(N'governance.ArchitectureDecision',N'U') IS NULLCREATE TABLE governance.ArchitectureDecision( decision_id int IDENTITY PRIMARY KEY, decision_name nvarchar(120) NOT NULL, chosen_option nvarchar(300) NOT NULL, rejected_options nvarchar(900) NULL, rationale nvarchar(1200) NOT NULL, evidence_needed nvarchar(1200) NOT NULL, residual_risk nvarchar(1200) NULL, decided_at datetime2(3) NOT NULL DEFAULT SYSUTCDATETIME());GOUPDATE governance.ServiceObjectiveSET workload_class='OLTP read',target_text=N'Latency target comes from the ServiceHub product SLO; measure p50/p95/p99 under representative concurrency.',evidence_source=N'Application telemetry + Query Store + waits',owner_name=N'Platform owner',status='ASSUMED',updated_at=SYSUTCDATETIME()WHERE objective_name='interactive-read';IF @@ROWCOUNT=0 INSERT governance.ServiceObjective(objective_name,workload_class,target_text,evidence_source,owner_name,status)VALUES('interactive-read','OLTP read',N'Latency target comes from the ServiceHub product SLO; measure p50/p95/p99 under representative concurrency.',N'Application telemetry + Query Store + waits',N'Platform owner','ASSUMED');UPDATE governance.ServiceObjectiveSET workload_class='OLTP write',target_text=N'Committed work orders must survive instance restart; define acceptable RPO for site loss.',evidence_source=N'Restore/failover drill evidence',owner_name=N'Database owner',status='ASSUMED',updated_at=SYSUTCDATETIME()WHERE objective_name='write-durability';IF @@ROWCOUNT=0 INSERT governance.ServiceObjective(objective_name,workload_class,target_text,evidence_source,owner_name,status)VALUES('write-durability','OLTP write',N'Committed work orders must survive instance restart; define acceptable RPO for site loss.',N'Restore/failover drill evidence',N'Database owner','ASSUMED');UPDATE governance.ServiceObjectiveSET workload_class='DR',target_text=N'Restore a validated copy within the business RTO and prove application invariants.',evidence_source=N'Backup headers + restore + CHECKDB + business checks',owner_name=N'Incident commander',status='ASSUMED',updated_at=SYSUTCDATETIME()WHERE objective_name='recovery';IF @@ROWCOUNT=0 INSERT governance.ServiceObjective(objective_name,workload_class,target_text,evidence_source,owner_name,status)VALUES('recovery','DR',N'Restore a validated copy within the business RTO and prove application invariants.',N'Backup headers + restore + CHECKDB + business checks',N'Incident commander','ASSUMED');SELECT * FROM governance.ServiceObjective;The targets deliberately avoid invented universal millisecond values. Your SLO must come from the product and its users. Later lessons populate the evidence column with measurements from this environment.
2. Workload classes make resource conflicts visible
ServiceHub has at least four workload classes: interactive point lookups/updates, dispatcher search/reporting, background integration, and operational tasks such as backup/CHECKDB/index work. Each has a different concurrency shape and business priority. A design that performs beautifully for a single analytical query can still violate the OLTP SLO if it consumes memory grants, I/O bandwidth, tempdb, or CPU scheduler capacity at the wrong time.
| Class | Typical shape | Primary evidence | Failure question |
|---|---|---|---|
| Interactive OLTP | Short selective reads/writes | App latency, plans, locks, log waits | Can one hot key or bad plan stall many users? |
| Dispatch analytics | Aggregates/search by status/region/time | Query Store, memory grants, I/O, tempdb | Does reporting steal resources from writes? |
| Integration | Batches/CDC/outbox-like processing | Backlog, retry, retention, rows/sec | Can replay/deduplication survive outage? |
| Operations | Backup, CHECKDB, maintenance | Duration, I/O, log/AG lag | Does protection work without breaking the protected service? |
3. Capacity is a model with uncertainty
Capacity planning starts with current cardinality, row width, growth, retention, peak concurrency, backup volume and headroom—not “CPU = 70% so buy more CPU.” Record which assumptions are measured and which are estimates. SQL Server 2025 Express can be an excellent learning instance but has production constraints including a 50-GB maximum relational database size and no Agent/AG/FCI. Standard and Enterprise introduce different HA, online-operation and scalability capabilities; Developer editions are free for development/test but are not production licenses.
USE ServiceHubCapstone;GOSELECT DB_NAME() AS database_name, SUM(size)*8.0/1024 AS allocated_mbFROM sys.database_files;SELECT OBJECT_SCHEMA_NAME(p.object_id) AS schema_name, OBJECT_NAME(p.object_id) AS table_name, SUM(p.rows) AS approx_rowsFROM sys.partitions AS pWHERE p.index_id IN (0,1) AND p.object_id IN (OBJECT_ID(N'ops.WorkOrder'),OBJECT_ID(N'ops.Technician'))GROUP BY p.object_id;SELECT name,compatibility_level,recovery_model_desc,is_query_store_onFROM sys.databases WHERE name=DB_NAME();These are measurements at one point in time. Forecasting requires trend data and business projections. The capstone asks the learner to distinguish “known now,” “estimated,” and “must be load-tested.”
4. Choose topology by failure mode
A full Enterprise Availability Group can provide database-level replicas, listener routing and automatic failover when cluster/failover prerequisites are met. Standard provides Basic Availability Groups with a narrower feature set. FCI protects an instance through clustered failover and shared-storage architecture. Log shipping is simpler DR with manual orchestration. None substitutes for tested backups because all can faithfully replicate logical mistakes. The free mandatory lab uses a single instance plus real backup/restore; an optional multi-instance Developer topology can extend the same runbook.
USE ServiceHubCapstone;INSERT governance.ArchitectureDecision(decision_name,chosen_option,rejected_options,rationale,evidence_needed,residual_risk)VALUES(N'Primary data protection model', N'Full backup + log/PITR where business RPO requires; optional AG/DR topology based on edition and infrastructure', N'Replication as backup; synchronous commit as sole recovery strategy', N'Backups address logical/catastrophic recovery while HA reduces selected outage classes.', N'Restore drill, measured RPO/RTO, failover exercise where topology exists, client reconnect test.', N'Single-instance lab cannot prove cluster/network behavior; production requires topology rehearsal.');SELECT * FROM governance.ArchitectureDecision ORDER BY decision_id DESC;5. Wrong approach: choose Enterprise features first, justify later
“Use Enterprise AG + columnstore + In-Memory because it is production-grade” is not architecture. It raises cost and operational surface without proving a workload need. The repair is to trace every feature to an SLO, failure mode, measured bottleneck, or compliance requirement. Features without evidence remain rejected or deferred decisions.
Check your understanding
- Why is an RPO different from an availability target?
- Why classify workloads before tuning?
- Does Developer edition make Enterprise features free for production?
- Why is an AG not a backup?
- What makes an architecture assumption acceptable?
Review the answers
1. RPO bounds acceptable data loss; availability bounds service accessibility. HA can improve availability without guaranteeing recovery from logical damage.
2. Different workload classes compete for CPU, memory, I/O, tempdb and locks; an improvement for one class can violate another class SLO.
3. No. Developer editions are for development/test; production licensing must be evaluated separately.
4. It can replicate logical mistakes/corruption and does not replace point-in-time or independent restore evidence.
5. A named owner, measurable evidence source, test plan, acceptance gate, and residual-risk decision.