Welcome to Section 6. Up to now, you’ve built a working lab. From this point forward, you stop thinking like a setup user
and start thinking like a real DBA: you learn how SQL Server actually runs, what components are involved, and where problems
show up when something goes wrong.
In this lesson, you will learn the “moving parts” of SQL Server: which Windows services exist, what each service does,
how they communicate, and how to confirm health using both Windows tools and SQL queries.
Lesson description: You will understand SQL Server’s process model at a practical DBA level:
what runs as a Windows service, what runs inside the SQL Server engine process, what SQL Server Agent does,
how optional services (Browser, Full-Text, Launchpad) fit in, and how to verify service identity, startup state,
and log locations. You will complete a hands-on walkthrough to locate your services, confirm they are running,
and capture evidence using built-in DMVs.
SQL Server is not just a database file you open. It is a set of long-running background processes (services)
managed by Windows. These services start, stop, restart, and run under specific identities (service accounts).
When your database is “up,” it’s because the SQL Server services are up.
Think of it like this:
Windows OS ├─ SQL Server service (Database Engine) → runs your databases, queries, memory, I/O, locks ├─ SQL Server Agent service → runs jobs, schedules, alerts, automation ├─ Optional services (Browser, Full-Text, etc.) └─ Supporting components (SSMS, drivers, tools) → connect to the engine
DBA mindset: When something fails, you troubleshoot in layers:
service status → logs → configuration → SQL internals (DMVs) → workload behavior.
This is the service that hosts the database engine: storage, buffer pool (memory), query execution,
locking, transaction logging, recovery, and more. If the Database Engine service is stopped,
your databases are not accessible.
SQL Server Agent is how DBAs automate work: backups, integrity checks, index/statistics maintenance,
ETL job scheduling, monitoring scripts, and alerting.
DBA reality: Many “SQL is broken” tickets are actually “Agent job failed,” “backup failed,” or
“maintenance stopped running.” Agent health is production-critical.
SQL Server Browser helps clients discover named instances and ports, especially when dynamic ports are used.
Many environments do not need it (particularly single default instance servers).
Full-Text is used when applications need advanced text searching beyond standard LIKE patterns.
Not required for basic DBA fundamentals, but common in certain products.
This service supports certain advanced features (for example, external script execution in some configurations).
Many DBA labs do not need it. Treat it like any optional service: only run it if your feature set requires it.
SQL Server can be installed as:
Service naming is your first “tell” when identifying what you’re looking at.
A DBA should be able to glance at services and answer:
“Which instance is this? Which services belong to it? Which ones must be running?”
This shows service status (Running/Stopped), startup type (Automatic/Manual/Disabled), and logon account.
It is useful for quick checks, but DBAs prefer SQL Server Configuration Manager for SQL-related service changes.
DBAs use SQL Server Configuration Manager because it understands SQL Server services and network configuration.
Use it for:
DBA rule: Use SQL Server Configuration Manager for SQL service configuration whenever possible.
It reduces mistakes and keeps settings consistent.
In production, DBAs care about how services start after a reboot:
DBA practice: Only mission-critical services should start automatically.
Unnecessary services should be Manual or Disabled.
A professional DBA doesn’t rely only on what Windows shows. You also verify from inside SQL Server using DMVs.
Run the following queries in SSMS.
SELECT
@@SERVERNAME AS ServerName,
@@VERSION AS VersionInfo,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;
On many systems, you can query service details using sys.dm_server_services.
If your edition/version does not expose it, that’s okay—you still learn the habit.
SELECT
servicename,
startup_type_desc,
status_desc,
last_startup_time,
service_account
FROM sys.dm_server_services
ORDER BY servicename;
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
DBA usage: If an application reports “SQL was down,” this tells you whether SQL Server restarted recently.
DBAs do not guess. They read logs. SQL Server provides key logs:
In SSMS, you can view the SQL Server Error Log under:
Management → SQL Server Logs
You can also read the error log with T-SQL (for quick evidence capture):
-- Reads the current SQL Server error log (most recent entries) EXEC xp_readerrorlog 0, 1;
DBA note: Logs are not optional. They are your primary evidence source during incidents.
DBA takeaway: Many incidents begin as “application complaints,” but the root cause lives in services and logs.
C:\DBA\Logs\section6-lesson1-services-proof.txt
SELECT @@SERVERNAME, @@VERSION;SELECT sqlserver_start_time FROM sys.dm_os_sys_info;SELECT * FROM sys.dm_server_services; (if available)Next, you will learn the difference between an instance and a database,
how objects are scoped, and why DBAs must be precise when they say “server-level” vs “database-level.”
This clears up one of the most common sources of confusion in real production work.
Not a member yet? Register now
Are you a member? Login now