SQL Configuration and Monitoring: Course Resources

Which resources are available for configuring and monitoring SQL Server 2025? This lesson answers that question in two different senses. The first is the actual set of tools Microsoft provides for configuring and monitoring an instance, the tools this course will have you using in the lessons ahead. The second is the handful of resources this particular course provides to help you work through it: the sitemap, the glossary, and how exercise submissions work. Both are worth a look before you continue.

In SQL Server 2025, Microsoft provides a rich set of tools, views, and utilities for configuration and monitoring, ranging from GUI-based interfaces to T-SQL-based diagnostics. Below is a categorized breakdown of the key resources you'll use for configuration work specifically.

• SQL Server Management Studio (SSMS) is the main graphical tool for configuring and managing an instance. It covers instance and database settings, index and statistics management, security configuration, a full Query Editor, and Activity Monitor, all from a single application. If you've worked through anything in the previous two courses in this series, you've already used SSMS for this; this course simply asks more of it, on the configuration and monitoring side specifically, rather than introducing it as something new. Server-level configuration in SSMS mostly lives under a server's own Properties dialog, reached by right-clicking the instance in Object Explorer, which is where options like memory limits and the default backup location actually get set through the GUI rather than through sp_configure directly.
• SQL Server Configuration Manager is a Windows-only utility for managing SQL Server services, enabling or disabling network protocols like TCP/IP and Named Pipes, and managing the accounts those services run under. It's a different tool from SSMS, worth knowing by name since the two are easy to confuse: SSMS manages what's inside an instance, while Configuration Manager manages the instance itself at the Windows service level, closer to the Windows Firewall and port considerations this course's second lesson already covered. If a client application can reach the server but a specific connection keeps failing, Configuration Manager is often where the actual cause turns up, a disabled protocol or a service that isn't listening on the port you expected, rather than anything inside the database itself. On Linux, there's no equivalent GUI utility; the comparable job is handled by the mssql-conf command-line tool instead, which edits the same kind of settings through a terminal prompt rather than a Windows MMC-style console.
• Transact-SQL is the scripting path for configuration work that doesn't go through a GUI at all. sp_configure changes advanced server-level options, ALTER DATABASE modifies database-level settings, and CREATE LOGIN and CREATE USER handle security configuration. Scripting configuration changes this way is what makes a change repeatable and auditable, rather than a one-off click somewhere in a dialog box that's hard to reconstruct later.
• SQL Server Data Tools (SSDT) is a Visual Studio workload for building SQL database projects, writing and debugging T-SQL with IntelliSense, and managing deployments through source control and CI/CD pipelines. It currently targets Visual Studio 2022 and 2026; Integration Services, Reporting Services, and Analysis Services support now ship as their own separate extensions rather than being bundled into SSDT directly, which is a change from how SSDT used to be packaged. The core idea behind a SQL database project is treating your database schema the same way you'd treat application source code: checked into version control, built and validated before deployment, and deployed through a repeatable process rather than a person manually running scripts against production from memory. If you're not on Windows, the SQL Database Projects extension for Visual Studio Code covers the same database-project format in a cross-platform editor, continuing the pattern this course's third lesson already established of a VS Code extension standing in for a Windows-only tool.
• PowerShell and the SqlServer module let you script administrative tasks against an instance from PowerShell rather than T-SQL or a GUI. This matters most once a configuration task needs to run repeatably, or across more than one server; a PowerShell script that configures ten instances identically is a lot more reliable than clicking through the same SSMS dialog ten separate times. A cmdlet like Invoke-Sqlcmd runs a T-SQL script from inside a PowerShell session, which makes it easy to fold a database configuration step into a larger automation script that's also doing other, non-SQL Server work on the same machine.

Nothing in this list replaces another item on it entirely; they overlap on purpose. A given configuration task might be doable through SSMS, through raw T-SQL, or through PowerShell, and which one you reach for is often a matter of whether the change is a one-off or something you'll need to repeat, rather than any of them being the single correct tool. This course will generally show the T-SQL approach first, since it's the one that works identically regardless of which GUI tool or platform you're using, and note the GUI alternative alongside it where one exists.

Monitoring draws on a different, partly overlapping set of tools, several of which this course has already introduced in earlier lessons without naming them as a formal list.

• Dynamic Management Views (DMVs) and functions expose real-time performance and health data directly through T-SQL: sys.dm_exec_requests and sys.dm_exec_sessions for what's currently running, sys.dm_db_index_usage_stats and sys.dm_os_wait_stats for index usage and wait statistics, and sys.dm_db_log_space_usage for transaction log space. DMVs are where a lot of real monitoring work actually happens; the GUI tools later in this list are largely built on top of the same underlying data these views expose, which is part of why learning a handful of DMVs well pays off even if you mostly work through a graphical tool day to day. A DMV query against sys.dm_exec_sessions joined to sys.dm_exec_requests, for instance, answers "what is this instance doing right now" far more precisely than any single screen in a GUI tool, because you can filter, join, and sort the result exactly the way the situation calls for rather than the way a dialog box happened to be laid out.
• Activity Monitor, the graphical view inside SSMS already mentioned above as a configuration tool, does double duty for monitoring: CPU usage, blocking sessions, I/O statistics, and active queries, all without writing a query yourself.
• Query Store tracks query performance over time, enabled per database with ALTER DATABASE [YourDB] SET QUERY_STORE = ON;. It's useful for two related things: catching a query that's gotten slower over time, a performance regression, and keeping a query's execution plan stable once you've confirmed it performs well, rather than letting the optimizer pick a worse plan later. Because it's enabled per database rather than per instance, it's entirely normal to have it on for SalesDB while leaving it off for tempdb or another database where that history isn't useful.
• Extended Events is the lightweight, current event-tracing framework for deadlocks, blocking, and query execution anomalies. This course's first lesson already covered why it matters: Extended Events is the modern replacement for SQL Trace and SQL Server Profiler, both of which are deprecated, so it's the tool this course teaches rather than the tool older material still points to.
• SQL Server error logs and Windows Event Viewer cover startup messages, login attempts, backup events, and SQL Server Agent job failures, continuing the Event Viewer discussion from this course's second lesson into SQL Server-specific territory. The error log is often the first place worth checking when an instance won't start at all, since it's written to disk independently of whether SQL Server itself is in a state where SSMS can even connect to it.
• Performance Monitor (PerfMon), the Windows-level tool already introduced in the previous lesson, gets extended with SQL Server-specific counters once SQL Server is installed: buffer cache hit ratio, page life expectancy, and transactions per second among them. Because PerfMon counters exist at the Windows level, they're also the natural place to watch SQL Server alongside everything else competing for the same CPU, memory, and disk, rather than looking at SQL Server in isolation from the machine it's actually running on.
• SQL Server Agent, this platform's equivalent of Windows Task Scheduler as this course's first lesson described it, handles job scheduling with built-in success and failure tracking for every job it runs, and shows up again in the error-log discussion above when a job fails. Agent's own job history, viewable right inside SSMS, is frequently a faster way to answer "did last night's maintenance job actually run" than digging through the error log for the same information.
• Built-in Reports are a set of ready-made reports available from the Reports menu in SSMS's Object Explorer: disk usage, top resource-consuming queries, and index usage, all without writing a query for any of it yourself. They're a reasonable first stop before reaching for a DMV directly, especially while you're still getting comfortable with what each DMV actually returns. They're not a replacement for the DMVs they're built on, since you can't filter or customize a built-in report the way you can a query you wrote yourself, but for a quick look at an instance you don't know well yet, they're often faster than writing anything from scratch.

Ad High Performance SQL Server

A quick-reference table is worth having early, even before each of these tools gets its own dedicated lesson later in this course:

Area	Tool or View
CPU and memory usage	Activity Monitor, sys.dm_os_sys_memory
Query performance	Query Store, sys.dm_exec_query_stats
Index fragmentation	sys.dm_db_index_physical_stats
Log space usage	sys.dm_db_log_space_usage
Blocking and deadlocks	Extended Events, Activity Monitor
Job monitoring	SQL Server Agent

None of this is a checklist to memorize right now. Each row in that table, and each tool in the two lists above, gets its own proper treatment later in this course, with actual hands-on practice against SalesDB rather than a one-line description. The point of covering them here, all at once, is so that when a later lesson says "open Activity Monitor" or "query sys.dm_os_wait_stats," you already have a rough sense of where that tool fits among everything else, rather than meeting each one cold with no context for how it relates to the others.

It's worth connecting this list back to the DP-300 skills outline covered in this course's second lesson, since the overlap isn't an accident. "Monitor, configure, and optimize database resources" is one of DP-300's five weighted skill areas, worth 20-25% of the exam on its own, and the tools in this lesson are exactly what that skill area is built around: Activity Monitor and DMVs for monitoring, sp_configure and SSMS for configuration, and Query Store and Extended Events for the kind of analysis that leads to optimization. The "implement a secure environment" and "configure and manage automation of tasks" skill areas lean on the same set of tools too: CREATE LOGIN and CREATE USER for security, SQL Server Agent for automation. Learning these tools well, for this course's own sake, is the same work as preparing for roughly half of that exam's weighted content, whether or not taking the exam is actually part of your plan.

Beyond the SQL Server tools above, this course itself offers a few resources worth knowing about before you continue, separate from anything Microsoft provides.

The Sitemap is the single best resource on this site for finding everything else it offers, including the other courses in this series. It's worth a visit now if you haven't already looked at it, particularly if you've only worked through this series so far and haven't explored what else is available. A site covering this much ground, SQL Server administration, broader database design, and related topics, is easy to only partially discover by following links from lesson to lesson, and the sitemap is the fastest way around that.

The Glossary defines key terms used throughout this course. Those terms appear italicized in the lesson text as you encounter them, and clicking one opens a pop-up window with its definition, so you don't have to leave the lesson or go hunting for a separate glossary page just to look a term up. This matters more in a course like this one than it might in an introductory course, since a fair number of terms here, wait statistics, buffer cache hit ratio, query plan stability, are specific enough that a quick, in-context definition is genuinely useful rather than a formality.

Exercise submissions work a specific way in this course: for exercises that ask for it, you'll cut and paste your own code into a text submission box rather than upload a file. That's a deliberately low-friction setup; there's no file format to get right and no upload step to troubleshoot, just the script or query you wrote, pasted in as plain text. The next lesson covers the virtual learning environment itself in more detail, including exactly how that submission process fits into it.

The next lesson introduces the virtual learning environment you'll actually use for the hands-on work in this course, building on the SQL Server 2025 environment you set up in the previous lesson and the tools introduced here. From there, the lessons move from naming these tools to actually using them: building SalesDB, running the configuration commands covered above against it, and watching the monitoring tools in this lesson report back on what that activity actually looks like from the inside.