Memory hook: Automate a checked procedure and prove recovery against the required RPO and RTO.
Must remember
- SQL Server Agent jobs provide steps, schedules, owners/proxies and notifications on supported platforms. Azure SQL elastic jobs and Automation address other task scenarios. Inspect failed step output, execution identity, dependencies and retries rather than only the overall job status.
- ARM/Bicep/CLI/PowerShell automate infrastructure; database scripts and migration tools manage schema/data changes. Use idempotent controlled scripts, correct execution order, secure credentials and audited approvals. A maintenance job needs a failure notification and recovery path.
- Native full, differential and log backups have restore-chain requirements. Recovery model affects log-backup/PITR capabilities. A point-in-time restore applies the appropriate chain to the selected point; preserve encryption keys and verify the target state. Azure managed PITR/long-term retention have service-specific policies.
- For a native restore, start with the required full backup, optionally apply the appropriate differential based on it, then apply the necessary log sequence before final recovery. SIMPLE has no transaction-log backup chain; FULL supports log backups and suitable PITR; BULK_LOGGED reduces logging for eligible operations but can restrict point-in-time recovery within a log backup containing minimally logged changes. Switching models does not create missing historical backups.
- Always On availability groups, failover cluster instances, log shipping, active geo-replication and failover groups solve different HA/DR needs. Synchronous versus asynchronous replication affects commit latency and potential data loss; automatic failover requires the right supported configuration.
- Design listeners/connection strings, networking, quorum where applicable, login/job dependencies and failback. A secondary can be healthy but underprovisioned for production. Test planned/unplanned failover and backup restore in a safe environment.
- Monitor replication lag, backup age, job health and recovery results. Record actual data loss and time to usable service. Replication does not protect against every logical error; backups and HA complement each other.
Choose under exam pressure
| Requirement | Choice and reason |
|---|---|
| Recover to before an accidental update | A valid point-in-time restore strategy. |
| Run scheduled tasks across SQL Databases | Evaluate elastic jobs and their target/identity model. |
| Regional database outage | A tested supported geo-replication/failover design. |
Traps
- A backup chain with a missing required log may block the desired restore.
- A successful failover can still leave jobs/logins or applications broken.
- HA is not historical recovery.
Active recall
1. Why test restoration regularly?
To verify the chain, keys, permissions, capacity and usable recovery time.
2. What differs between synchronous and asynchronous replication?
Whether commit waits for the required remote acknowledgement, affecting latency and loss exposure.
3. Why inspect the job execution identity?
It may lack permissions available to the administrator who tested the command interactively.
4. What must failback reconcile?
Data changes, replication direction, application routing and dependencies.
5. Does a green backup policy prove recent recovery points exist?
No. Inspect actual jobs/recovery points and their freshness.