Lab: Apply a “baseline configuration” and document every change

Section 7 — Lesson 8 (Lab): Apply a “Baseline Configuration” and Document Every Change

Welcome back. This is a job-ready lab because it mimics what DBAs do in the real world:
you inherit an instance (or build a new one), apply a consistent baseline configuration,
capture evidence, and document every change so the environment is supportable.

Your goal is not “random tweaks.” Your goal is a repeatable baseline that you can reapply on any SQL Server instance
and defend in an interview with evidence.

---

Lesson description: You will build a full baseline configuration package for your SQL Server lab instance.
You will capture a “before” snapshot, apply baseline settings (MAXDOP/CTFP, memory, file growth, tempdb, SQL Server Agent checks,
dangerous database options), validate changes with an “after” snapshot, and generate documentation:
(1) a baseline report, (2) change log entries, (3) rollback notes, and (4) a reusable checklist.
This lab becomes portfolio evidence that you can follow change control and reduce outage risk.

---

What You Will Produce (Deliverables)

• Baseline Before Snapshot (evidence files)
• Baseline After Snapshot (evidence files)
• Change Log (human-readable log of every change)
• Baseline Configuration Report (one summary document)
• Reusable Baseline Checklist (so you can repeat this on future servers)

---

Folder Setup (Standard DBA Structure)

Create these folders on your SQL Server machine:

C:\DBA\
  Baselines\
    InstanceConfig\
  Logs\
    Changes\
  Checklists\
  Reports\
  Scripts\
  Outputs\

Tip: Keep naming consistent. This is how DBAs stay organized under pressure.

---

Step 1 — Capture the “Before” Snapshot (Evidence First)

Run each script below and save its output exactly as shown. This proves what the server looked like before you changed anything.

1A) Instance identity + version

SELECT
  @@SERVERNAME AS server_name,
  SERVERPROPERTY('MachineName') AS machine_name,
  SERVERPROPERTY('InstanceName') AS instance_name,
  SERVERPROPERTY('Edition') AS edition,
  SERVERPROPERTY('ProductVersion') AS product_version,
  SERVERPROPERTY('ProductLevel') AS product_level,
  SERVERPROPERTY('ProductUpdateLevel') AS product_update_level;

Save to: C:\DBA\Baselines\InstanceConfig\instance_identity_before_YYYYMMDD_HHMM.txt

1B) sp_configure snapshot (server-level configuration)

EXEC sp_configure;

Save to: C:\DBA\Baselines\InstanceConfig\sp_configure_before_YYYYMMDD_HHMM.txt

1C) MAXDOP + Cost Threshold (quick view)

SELECT
  name,
  value_in_use
FROM sys.configurations
WHERE name IN ('max degree of parallelism','cost threshold for parallelism')
ORDER BY name;

Save to: C:\DBA\Baselines\InstanceConfig\maxdop_ctfp_before_YYYYMMDD_HHMM.txt

1D) Memory config snapshot

SELECT
  name,
  value_in_use
FROM sys.configurations
WHERE name IN ('min server memory (MB)','max server memory (MB)')
ORDER BY name;

SELECT
  total_physical_memory_kb/1024 AS total_physical_memory_mb,
  available_physical_memory_kb/1024 AS available_physical_memory_mb,
  system_memory_state_desc
FROM sys.dm_os_sys_memory;

Save to: C:\DBA\Baselines\InstanceConfig\memory_before_YYYYMMDD_HHMM.txt

1E) File growth snapshot (all databases)

SELECT
    DB_NAME(mf.database_id) AS database_name,
    mf.type_desc,
    mf.name AS logical_file_name,
    mf.physical_name,
    mf.size * 8 / 1024 AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS varchar(20)) + '%'
        ELSE CAST((mf.growth * 8) / 1024 AS varchar(20)) + ' MB'
    END AS autogrowth_setting,
    mf.is_percent_growth
FROM sys.master_files AS mf
ORDER BY database_name, mf.type_desc, mf.file_id;

Save to: C:\DBA\Baselines\InstanceConfig\file_growth_before_YYYYMMDD_HHMM.txt

1F) tempdb layout snapshot

USE tempdb;
GO
SELECT
    mf.file_id,
    mf.type_desc,
    mf.name AS logical_name,
    mf.physical_name,
    mf.size * 8 / 1024 AS size_mb,
    CASE
        WHEN mf.is_percent_growth = 1 THEN CAST(mf.growth AS varchar(20)) + '%'
        ELSE CAST((mf.growth * 8) / 1024 AS varchar(20)) + ' MB'
    END AS autogrowth_setting
FROM sys.database_files AS mf
ORDER BY mf.type_desc, mf.file_id;

Save to: C:\DBA\Baselines\InstanceConfig\tempdb_files_before_YYYYMMDD_HHMM.txt

1G) SQL Server Agent status + job history snapshot

DECLARE @AgentStatus TABLE (ServerName sysname, ServiceName nvarchar(200), Status nvarchar(200));
INSERT INTO @AgentStatus EXEC xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent';
SELECT * FROM @AgentStatus;

USE msdb;
GO
;WITH JobHistory AS
(
    SELECT
        j.name AS job_name,
        h.step_id,
        h.run_date,
        h.run_time,
        h.run_duration,
        h.run_status,
        h.message,
        ROW_NUMBER() OVER (PARTITION BY j.job_id ORDER BY h.instance_id DESC) AS rn
    FROM dbo.sysjobs AS j
    INNER JOIN dbo.sysjobhistory AS h
        ON j.job_id = h.job_id
    WHERE h.step_id = 0
)
SELECT TOP (25)
    job_name, run_status, run_date, run_time, run_duration, message
FROM JobHistory
WHERE rn = 1
ORDER BY run_date DESC, run_time DESC;

Save to: C:\DBA\Baselines\InstanceConfig\agent_and_jobs_before_YYYYMMDD_HHMM.txt

1H) Database options snapshot (compatibility, AUTO_CLOSE, AUTO_SHRINK)

SELECT
    name AS database_name,
    compatibility_level,
    is_auto_close_on,
    is_auto_shrink_on,
    state_desc,
    recovery_model_desc
FROM sys.databases
ORDER BY name;

Save to: C:\DBA\Baselines\InstanceConfig\db_options_before_YYYYMMDD_HHMM.txt

---

Step 2 — Apply Your Baseline Configuration (Controlled Changes)

In production, baseline values differ by server role and hardware. In your lab, you will apply reasonable baseline defaults
and practice the process, not memorize magic numbers.

2A) Set MAXDOP and Cost Threshold for Parallelism (example baseline)

If you already set these in earlier lessons, you will confirm they match your baseline and document them here.

-- Example baseline (adjust for lab if needed)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;

EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

2B) Set server memory min/max (example baseline workflow)

In real production, DBAs calculate max memory based on total RAM and OS needs.
For your lab, choose a max memory that leaves room for the OS (do not set max memory to total RAM).

-- Example only: set max server memory to 4096MB (adjust for your lab)
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE;

2C) Standardize file growth settings (target user database)

Pick one user database (or AdventureWorks) and ensure fixed growth for data and log.

-- Replace database and logical file names based on sys.master_files output
ALTER DATABASE [YourDatabaseName]
MODIFY FILE ( NAME = N'YourDataLogicalName', FILEGROWTH = 512MB );

ALTER DATABASE [YourDatabaseName]
MODIFY FILE ( NAME = N'YourLogLogicalName', FILEGROWTH = 256MB );

2D) Standardize tempdb layout (add equal-sized file + fixed growth)

If you already created tempdb files in the prior lesson, verify and standardize them here.

-- Example (adjust paths and sizes for your lab)
ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'tempdev', SIZE = 1024MB, FILEGROWTH = 256MB );

-- Add an extra tempdb data file if you do not already have one
-- Comment this out if you already added it
-- ALTER DATABASE tempdb
-- ADD FILE (
--     NAME = N'tempdev2',
--     FILENAME = N'C:\SQLData\tempdb2.ndf',
--     SIZE = 1024MB,
--     FILEGROWTH = 256MB
-- );

ALTER DATABASE tempdb
MODIFY FILE ( NAME = N'templog', SIZE = 512MB, FILEGROWTH = 256MB );

Restart note: If you changed tempdb file layout, restart SQL Server service in your lab
and confirm tempdb starts cleanly. Document the restart as part of change control.

2E) Validate SQL Server Agent is running and jobs can execute

Confirm Agent is running. If you built the health snapshot job, run it once manually and confirm it inserts a row.

2F) Disable harmful database options (AUTO_CLOSE, AUTO_SHRINK)

-- Replace with your target database(s)
ALTER DATABASE [YourDatabaseName] SET AUTO_CLOSE OFF;
ALTER DATABASE [YourDatabaseName] SET AUTO_SHRINK OFF;

---

Step 3 — Capture the “After” Snapshot (Prove Your Baseline)

Now re-run the snapshot scripts and save the “after” evidence files.
Use the same scripts as Step 1, but save with _after_ in the filename.

• instance_identity_after_YYYYMMDD_HHMM.txt
• sp_configure_after_YYYYMMDD_HHMM.txt
• maxdop_ctfp_after_YYYYMMDD_HHMM.txt
• memory_after_YYYYMMDD_HHMM.txt
• file_growth_after_YYYYMMDD_HHMM.txt
• tempdb_files_after_YYYYMMDD_HHMM.txt
• agent_and_jobs_after_YYYYMMDD_HHMM.txt
• db_options_after_YYYYMMDD_HHMM.txt

Save all to: C:\DBA\Baselines\InstanceConfig\

---

Step 4 — Write Your Change Log (This Is What Employers Want)

Create one change log file and record every change in the same format.

Create file: C:\DBA\Logs\Changes\baseline_change_log_YYYYMMDD_HHMM.txt

Copy this template and fill it out:

CHANGE LOG — BASELINE CONFIGURATION
Server:
Instance:
Date/Time:
DBA:

1) Change:
   - What changed:
   - Why:
   - Evidence (before file):
   - Evidence (after file):
   - Risk:
   - Validation:
   - Rollback:

2) Change:
   - What changed:
   - Why:
   - Evidence (before file):
   - Evidence (after file):
   - Risk:
   - Validation:
   - Rollback:

(Repeat for each baseline item: MAXDOP/CTFP, memory, file growth, tempdb, Agent/job validation, DB options.)

---

Step 5 — Produce a Baseline Configuration Report (Portfolio Evidence)

Create a single report that summarizes your final baseline values.

Create file: C:\DBA\Reports\baseline_configuration_report_YYYYMMDD_HHMM.txt

Use this structure:

BASELINE CONFIGURATION REPORT

1) Instance Identity
   - Edition:
   - Version:
   - Host:

2) Server-Level Configuration
   - MAXDOP:
   - Cost Threshold for Parallelism:
   - Min Server Memory:
   - Max Server Memory:

3) tempdb Configuration
   - Data file count:
   - Data file sizes:
   - Data file growth:
   - Log size/growth:
   - Notes:

4) File Growth Standards
   - Percent growth eliminated? (Y/N)
   - Standard growth increments:
   - Databases updated:

5) SQL Server Agent
   - Agent status:
   - Test job executed? (Y/N)
   - Job history verified? (Y/N)

6) Database Options Standards
   - AUTO_CLOSE OFF enforced? (Y/N)
   - AUTO_SHRINK OFF enforced? (Y/N)
   - Compatibility level notes:

7) Evidence Index (Proof Files)
   - List all before/after files paths

---

Step 6 — Build a Reusable Baseline Checklist (So You Can Repeat This)

Create file: C:\DBA\Checklists\baseline_checklist_v1.txt

SQL SERVER BASELINE CHECKLIST (v1)

[ ] Capture instance identity + version
[ ] Capture sp_configure snapshot
[ ] Set/verify MAXDOP and Cost Threshold
[ ] Set/verify max server memory (leave OS headroom)
[ ] Audit all database file growth (remove percent growth)
[ ] Standardize file growth increments for key databases
[ ] Configure tempdb (multiple equal-sized data files, fixed growth)
[ ] Confirm SQL Server Agent running
[ ] Validate at least one Agent job execution + job history
[ ] Audit database options (AUTO_CLOSE OFF, AUTO_SHRINK OFF)
[ ] Capture after snapshots
[ ] Write change log entries with rollback plans
[ ] Produce baseline configuration report + evidence index

---

Practice (Required)

1. Complete “before” snapshots and save all evidence files.
2. Apply your baseline configuration changes (document each change).
3. Capture “after” snapshots and save all evidence files.
4. Create the change log, baseline report, and baseline checklist.
5. In your DBA notebook, answer:
   • Which baseline change reduces the risk of CPU-related query storms?
   • Which baseline change reduces the risk of tempdb allocation contention?
   • Which baseline change prevents surprise performance stalls from file growth?
   • Which baseline change prevents fragmentation churn from “space reclaim” features?
   • What evidence would you show an employer to prove you did this correctly?

---

Lesson Summary

• A baseline configuration is not “tuning.” It is risk management and standardization.
• Evidence-first means before/after snapshots for every change.
• Documentation (change log + report + checklist) is what makes your work production-grade.
• This lab creates portfolio proof that you can manage SQL Server like a real DBA.

---

Next Section Preview

Next, you will move into Section 8 — Security & Access Control where you will learn how DBAs design logins,
users, roles, and permissions using least privilege—plus how to secure SQL Server in a way you can explain confidently.