Lesson 1

This course introduces you to monitoring and configuring SQL Server 2025. It's the last of three courses in the Microsoft SQL Server System Administration series, and it picks up exactly where the previous one left off. The course before this one walked through designing and building a database from nothing: creating the database itself, defining the tables inside it, and getting the column attributes, keys, and constraints right. That earlier work produced something real, a working SalesDB or TrainingDB instance sitting on a server somewhere, and this course is about everything that happens after it exists: keeping it running, watching how it behaves under real use, and adjusting its configuration as conditions change.

That shift in focus matters more than it might first sound. Building a database is a project with a clear endpoint, the schema is either right or it isn't, and once it's right, it's done. Monitoring and configuring one never really finishes. A database that performs well on day one can slow down six months later as data volume grows, a query that ran fine against a thousand rows can bring the server to its knees against ten million, and a configuration setting that was correct at installation time can become the wrong choice entirely once the workload on the server changes. This course is about building the judgment and the toolset to notice that drift and respond to it, rather than discovering it the hard way when something breaks.

It's easier to see why this course exists with a concrete example rather than an abstract argument. Picture SalesDB, the same database built up over the course of the previous course in this series, six months after it first went live. When it launched, every report ran in under a second, and nobody gave a second thought to how it was configured. Six months later, the Orders table has grown from a few thousand rows to several million, a report that one department runs every Monday morning now takes four minutes instead of one second, and nobody on the team can say exactly when that started or why.

Without monitoring in place, the first anyone hears about this is a complaint, after the slowdown has already been happening for weeks. With it, a DMV query against sys.dm_exec_query_stats would have surfaced that exact report's query climbing the list of most expensive statements on the server well before anyone noticed by hand, an Extended Events session could have captured precisely which execution plan it was using and when that plan changed, and a SQL Server Agent alert could have paged someone the moment the query's average duration crossed a threshold worth caring about. None of that requires guessing or waiting for a complaint; it just requires having the monitoring in place before the problem shows up. That's the difference this course is trying to build: not reacting to a slow database after the fact, but noticing the drift while there's still room to do something about it calmly.

It's worth being explicit about how the three courses in this series relate to each other, since each one assumes the last one is already in hand. The first course covers the fundamentals of SQL Server itself: installing the engine, understanding the instance and system-database structure, and getting comfortable with the tools you'll use throughout the rest of the series. The second course, the one this one builds on directly, covers designing and creating databases and tables: CREATE DATABASE and ALTER DATABASE, column data types and attributes, primary and foreign keys, normalization, and the full lifecycle of creating, modifying, and dropping both databases and the tables inside them.

This course assumes all of that is already familiar, and it doesn't repeat it. When a later lesson in this course talks about monitoring query performance against a table, it assumes you already know how that table was built and what its indexes and constraints are doing; when it talks about configuring a database-level option, it assumes ALTER DATABASE itself is no longer new territory. If any of that feels shaky, the second course in this series is the place to shore it up before continuing here.

The practical upshot is that this course spends very little time on syntax you've already seen and a great deal of time on judgment: which metric actually matters for a given symptom, which configuration option is worth touching and which is better left at its default, and how to tell the difference between a database that's merely busy and one that's genuinely in trouble. That's a different kind of skill than writing a correct CREATE TABLE statement, and it tends to take longer to build, since it depends on seeing enough real examples to start recognizing the patterns on sight rather than looking each one up from scratch.

Older material in this space often points toward the Microsoft Technology Associate (MTA) exams, including Exam 98-364: Database Fundamentals, as the certification to pursue alongside a course like this one. Microsoft retired the entire MTA program in June 2022, and none of those exams are available any longer, regardless of how often you still see them referenced in older study guides or course descriptions.

The current path for SQL Server administration work is the Azure Database Administrator Associate certification, earned through the DP-300 exam, Administering Microsoft Azure SQL Solutions. Unlike the old, version-specific MTA exams, DP-300 isn't tied to one particular SQL Server release. It covers SQL Server on-premises, SQL Server running on an Azure virtual machine, and Azure SQL Database and Azure SQL Managed Instance, treating all of them as variations on the same underlying administration skill set rather than separate tracks. That's a genuinely better fit for how SQL Server administration actually works today, since most real environments span at least two of those deployment models rather than living entirely in one. If you're working toward a credential while taking this course, DP-300 is the one worth pursuing.

If DP-300 feels like a lot to take on directly, Microsoft also offers DP-900, Microsoft Azure Data Fundamentals, as an optional, considerably gentler starting point. It isn't a prerequisite for DP-300 and isn't required for this course either, but it's worth knowing it exists if you'd rather build up to the administrator-level exam gradually rather than studying for it cold.

After completing this course, you will be able to:

1. Load data into SQL Server and extract data from it
2. Monitor SQL Server
3. Monitor SQL Server using Windows Server administration tools
4. Monitor SQL Server databases
5. Use Extended Events to trace and monitor server activity
6. Automate administration tasks
7. Configure SQL Server
8. Set SQL Server advanced options

Goal 5 is worth pausing on if you've used SQL Server before, since older material names a different tool entirely for this job. SQL Server Profiler, the tool this goal used to name directly, is officially deprecated. Microsoft's own documentation is explicit about it: both SQL Trace and SQL Server Profiler are deprecated in favor of Extended Events, and Profiler is expected to be removed outright in a future SQL Server version. Extended Events does the same underlying job, capturing and analyzing activity on the server, but it does it using fewer server resources and supports newer SQL Server events that Profiler was never updated to capture at all. Inside SQL Server Management Studio, the graphical entry point to Extended Events is the XEvent Profiler, deliberately named to feel familiar to anyone coming from the older tool; the MSSQL extension for Visual Studio Code offers a comparable Query Profiler view of its own. This course teaches Extended Events as the current tool for the job, and treats Profiler mainly as something you'll still recognize in older documentation and legacy scripts rather than something to build new monitoring around.

Ad SQL Server Azure DB

Several of the goals above point at specific tools rather than general skills, and it helps to know what each one actually is before you reach the lessons that use them.

SQL Server Management Studio (SSMS) remains the primary graphical tool for this course, the same one the previous course in this series used for creating databases and tables. For monitoring specifically, SSMS adds the Activity Monitor, a built-in dashboard showing current waits, active processes, and resource consumption at a glance, and the XEvent Profiler mentioned above for capturing detailed activity traces.

Windows Server administration tools cover the operating-system side of monitoring a SQL Server instance, which matters more than it might seem, since SQL Server doesn't run in isolation from the machine underneath it. Performance Monitor (perfmon) tracks CPU, memory, and disk counters, including a set of counters SQL Server registers specifically for itself, such as buffer cache hit ratio and page life expectancy. Task Manager and Resource Monitor give a quicker, less detailed view of the same underlying resources. Event Viewer surfaces errors and warnings the SQL Server service itself logs to the Windows Application log, which is often the first place a serious problem shows up before it's visible anywhere inside SQL Server itself.

Dynamic Management Views (DMVs) are SQL Server's own built-in window into its current state, queried with ordinary SELECT statements against views like sys.dm_exec_requests for currently running queries or sys.dm_os_wait_stats for what the engine has been waiting on. They replaced the older system tables back in SQL Server 2005, and they're the foundation nearly every other monitoring tool in this course, including Extended Events and Activity Monitor, ultimately builds on or queries directly.

One tool worth mentioning specifically because you may have expected to see it here: Azure Data Studio, which for several years offered its own lightweight way to view Extended Events sessions and query performance data. Microsoft retired Azure Data Studio, with support ending February 28, 2026, and folded its database-management and query capabilities into the MSSQL extension for Visual Studio Code instead. If you come across a tutorial or screenshot showing Azure Data Studio, that's a reliable sign the material predates this change; the MSSQL extension is where that same functionality lives now.

SQL Server Agent is the tool behind Goal 6, automating administration tasks, and it ties directly back to something the previous course in this series already introduced in passing: msdb, one of SQL Server's four system databases. SQL Server Agent stores every job, schedule, and alert it manages inside msdb, which is exactly why that database's description in the earlier course called out scheduled jobs and backup history as part of its job. A SQL Server Agent job is a named sequence of one or more steps, each one typically a T-SQL script, an operating-system command, or a PowerShell script, that can be run on demand, on a recurring schedule, or in response to an alert raised by something else on the server. A nightly job that rebuilds fragmented indexes, a weekly job that runs a full backup and verifies it, or an alert that pages someone the moment a database's transaction log fills past 90 percent, are all ordinary examples of what SQL Server Agent is for, and later lessons in this course build several of them from scratch.

Knowing how SQL Server got to its current state helps explain why certain monitoring tools exist, and why some older ones, like Profiler, are on their way out.

SQL Server's roots go back to 1989, when Microsoft, Sybase, and Ashton-Tate jointly released SQL Server 1.0, a 16-bit database built for IBM OS/2. A version for Windows NT followed in 1993. SQL Server 6.0, released in 1995, was the first version built specifically for NT without direction from Sybase, and the 6.5 release that followed in 1996 introduced the first versions of two tools whose names will sound familiar even now: Enterprise Manager and SQL Server Agent.

SQL Server 7.0, released in 1998, was a full rewrite of the database engine in C++, the first version that was genuinely Microsoft's own code rather than an evolution of the Sybase engine it had originally licensed. It introduced OLAP Services, English Query, replication, and full-text search, along with Data Transformation Services, the tool that would eventually become SQL Server Integration Services. SQL Server 2000 extended that engine into the enterprise, adding clustering, table variables, user-defined functions, indexed views, and basic XML support; Northwind joined Pubs as a sample database around this time.

SQL Server 2005 was another full rewrite of the engine, and a consequential one for anything related to monitoring specifically: it replaced the older system tables with Dynamic Management Views, the same DMVs covered in the tools section above, and it's also the release where SQL Server Management Studio first replaced both Enterprise Manager and Query Analyzer as the primary administration tool. AdventureWorks replaced Northwind and Pubs as the standard sample database around the same time.

SQL Server 2008 added FILESTREAM storage, spatial data types, Resource Governor, transparent data encryption, and the first native date and time data types separate from the old combined datetime type. The releases that followed moved quickly: 2012 added Always On availability groups and ColumnStore indexes; 2014 added In-Memory OLTP; 2016 added Always Encrypted, Row-Level Security, and native JSON support, along with the first preview of SQL Server on Linux; 2017 made that Linux support generally available alongside Docker container support; and 2019 introduced Big Data Clusters, bundling Apache Spark and Hadoop Distributed File System support directly with the database engine. Microsoft discontinued Big Data Clusters a few years later, announcing their retirement in 2022, so you'll see that feature referenced in older material without it being something to actually plan around today. SQL Server 2022 added Ledger, for blockchain-style tamper evidence on a table's history, along with tighter integration with Azure Synapse and Azure Purview.

SQL Server 2025, the version this course is built around, continues that trajectory squarely into AI-assisted data work: native JSON and VECTOR data types, vector indexing and built-in embedding generation for similarity search using functions like VECTOR_DISTANCE, regular-expression support in T-SQL and in CHECK constraints, and deeper integration with Microsoft Fabric, including dedicated resource governance for data-mirroring workloads. Monitoring and configuring a database engine with this much surface area, spanning everything from a traditional OLTP workload to vector similarity search, is exactly what the rest of this course is about.

You will learn and practice SQL Server system administration skills throughout this course with the assistance of interactive tools, the same hands-on approach the previous course in this series used for designing and building databases and tables. Where that course had you write CREATE DATABASE and CREATE TABLE statements against a database you were building from scratch, this one will have you query DMVs, set up Extended Events sessions, and work through configuration changes against a database that's already up and running, since that's the situation you'll actually be in once you're administering a real SQL Server instance.

The Series
Configuration and Monitoring is the last of three courses in the Microsoft SQL Server System Administration series. The next lesson covers the prerequisites you should already have in place before continuing, both the SQL Server environment itself and the knowledge carried forward from the earlier courses in this series.