SSMS to Administer SQL Server 2025

SQL Server Management Studio (SSMS) 22 is the primary integrated environment for administering all components of a SQL Server 2025 infrastructure. It replaces the legacy Enterprise Manager that was discontinued in SQL Server 2005 and provides a unified graphical interface for connecting to SQL Server instances, managing databases and security objects, writing and executing T-SQL, monitoring performance, scheduling jobs through SQL Server Agent, and accessing SQL Server 2025-specific features including native JSON tooling and vector data type support. This lesson covers how to install SSMS 22, connect to a SQL Server 2025 instance, and perform the core administration tasks required throughout this course.

Installing SSMS 22

SSMS 22 is distributed separately from the SQL Server 2025 installer and must be downloaded and installed independently. The current release as of March 2026 is SSMS 22.4.1. Download it from https://aka.ms/ssms or from the Microsoft Learn Install SSMS page. The installer filename is vs_SSMS.exe — it uses the Visual Studio Installer experience.

SSMS 22 system requirements and installation notes:

• 64-bit Windows 10 (supported versions), Windows 11, Windows Server 2019, 2022, or 2025
• Initial Windows ARM64 support is included for core scenarios — connecting, querying, and Object Explorer navigation
• SSMS 22 installs side-by-side with older versions (19.x, 20.x, 21.x) — existing SSMS installations are not removed or modified
• Restart the computer after installation to ensure all Visual Studio Installer components are registered correctly
• SSMS 22 provides native support for SQL Server 2025 features including the JSON viewer, vector data type table designer, enhanced IntelliSense, and query hints recommendations — older SSMS versions can connect to SQL Server 2025 but may lack full feature support for 2025-specific capabilities

Connecting to SQL Server 2025

Launch SSMS 22 from the Start menu. The Connect to Server dialog opens automatically on first launch. Configure the connection as follows:

• Server type: Database Engine (the default — leave unchanged for standard SQL Server connections)
• Server name: Enter the target instance. Use . or (local) for the default local instance, .\SQLEXPRESS for a local Express named instance, YourServerName for a remote default instance, or YourServerName\InstanceName for a remote named instance. To connect on a non-default port, use YourServerName,PortNumber.
• Authentication: Windows Authentication is recommended for local and domain environments. SQL Server Authentication requires a login name and password and is used when Windows Authentication is not available — for example, when connecting from a non-domain machine or from an application using SQL Server credentials.
• Click Options to specify a default database, connection timeout, or network protocol before connecting.
• Click Connect.

If the connection fails, verify the following in SQL Server Configuration Manager: the Database Engine service is in a Running state; TCP/IP protocol is enabled under SQL Server Network Configuration; and TCP port 1433 is permitted through the Windows Firewall. For named instances, confirm SQL Server Browser is running.

Object Explorer — Navigating the SQL Server Hierarchy

Once connected, the Object Explorer in the left pane displays the full hierarchy of objects managed by the SQL Server 2025 instance. The top-level nodes and their administrative functions are:

• Databases — lists all system databases (master, model, msdb, tempdb) and user databases. Right-click any database for context-sensitive operations including New Database, Backup, Restore, Detach, Properties, and Tasks.
• Security — manages server-level logins, server roles, credentials, and audit specifications. Right-click Logins to create new Windows or SQL Server logins and assign server roles such as sysadmin, securityadmin, or dbcreator.
• Server Objects — manages linked servers, endpoints, triggers, and backup devices at the server level.
• Replication — configures and monitors transactional, merge, and snapshot replication topologies.
• SQL Server Agent — manages scheduled jobs, alerts, operators, and the job activity monitor. Expand this node to create maintenance plans, automated backup schedules, and index rebuild jobs without writing T-SQL.
• Management — provides access to Activity Monitor, SQL Server Logs, Database Mail configuration, Policy-Based Management, and Extended Events sessions.

Creating a New Database in SSMS 22

Databases can be created through the SSMS graphical interface or through T-SQL in the Query Editor. Both methods produce identical results — the GUI generates T-SQL internally and executes it against the server.

Using the GUI:

1. Right-click the Databases node in Object Explorer and select New Database
2. Enter the database name in the Database name field
3. Configure the data file and log file initial sizes, autogrowth settings, and file paths on the Files page
4. Click OK — SSMS executes the CREATE DATABASE statement and the new database appears under the Databases node

Using T-SQL in the Query Editor:

-- Create the course project database with explicit file placement
CREATE DATABASE TimesheetDB
ON PRIMARY (
    NAME       = TimesheetData,
    FILENAME   = 'C:\SQLData\TimesheetData.mdf',
    SIZE       = 10MB,
    MAXSIZE    = 500MB,
    FILEGROWTH = 64MB
)
LOG ON (
    NAME       = TimesheetLog,
    FILENAME   = 'C:\SQLData\TimesheetLog.ldf',
    SIZE       = 5MB,
    MAXSIZE    = 100MB,
    FILEGROWTH = 32MB
);
GO

After execution, press F5 or click Refresh in Object Explorer to see the new database. The FILEGROWTH value of 64MB for data files and 32MB for log files reflects SQL Server 2025 best practice — autogrowth events in fixed MB increments rather than percentage-based growth, which produces unpredictable file sizes on large databases.

Creating Tables Using SSMS 22

Tables are created under a specific database. Expand the target database in Object Explorer, right-click the Tables folder, and select New Table to open the Table Designer. Define column names, data types, nullability, and primary key constraints through the graphical grid. In SQL Server 2025, the Table Designer supports the new VECTOR data type alongside traditional types.

The T-SQL equivalent for the course project Employees table:

USE TimesheetDB;
GO

CREATE TABLE Employees (
    Employee_ID     INT           NOT NULL PRIMARY KEY,
    Full_Name       NVARCHAR(100) NOT NULL,
    Department_Code CHAR(4)       NOT NULL,
    Hourly_Rate     DECIMAL(10,2) NOT NULL DEFAULT 0.00
);
GO

Writing and Executing T-SQL in the Query Editor

Click New Query in the SSMS toolbar or press Ctrl+N to open a Query Editor window. The Query Editor provides IntelliSense code completion, syntax highlighting, execution plan visualization, and live query statistics. Key Query Editor features in SSMS 22:

• IntelliSense: Auto-completes table names, column names, T-SQL keywords, and stored procedure parameters as you type. Press Ctrl+Space to trigger IntelliSense manually.
• Execution plans: Press Ctrl+L for the estimated execution plan or Ctrl+M to include the actual execution plan when the query runs. Execution plans identify table scans, missing indexes, and expensive operators that degrade query performance.
• Results grid: SSMS 22 includes a zoom control and column resizing for the results grid, making it easier to inspect wide result sets including JSON and vector output.
• JSON viewer: New in SSMS 22, clicking a JSON cell opens a formatted JSON viewer panel — useful when working with SQL Server 2025 FOR JSON output and JSON column data.
• GitHub Copilot integration (preview): SSMS 22 includes a preview of GitHub Copilot for natural-language query assistance — type a comment describing what you want and Copilot suggests the T-SQL implementation.

-- Execute a basic query against the course project database
USE TimesheetDB;
GO

SELECT Employee_ID, Full_Name, Department_Code, Hourly_Rate
FROM   Employees
ORDER BY Full_Name;
GO

Security Administration in SSMS 22

Server-level security is managed under the Security node in Object Explorer. Database-level security is managed under the Security folder within each database. Common security administration tasks:

• Create a new login: Expand Security → right-click Logins → New Login. Specify Windows Authentication (recommended) or SQL Server Authentication. Assign server roles on the Server Roles page and default database on the General page.
• Create a database user: Expand the target database → Security → right-click Users → New User. Map the user to an existing server login and assign database roles such as db_datareader, db_datawriter, or db_owner.
• Grant object-level permissions: Right-click any table, view, or stored procedure in Object Explorer → Properties → Permissions. Grant or deny SELECT, INSERT, UPDATE, DELETE, and EXECUTE permissions per user or role.

Performance Monitoring with Activity Monitor

Activity Monitor provides real-time visibility into SQL Server instance health. Open it by expanding Management in Object Explorer and double-clicking Activity Monitor, or by right-clicking the server name and selecting Activity Monitor. The five Activity Monitor panels display:

• Overview: CPU usage percentage, batch requests per second, waiting tasks count, and database I/O MB per second — the four key indicators of overall instance health
• Processes: All active sessions with session ID, login, host, database, status, wait type, and the T-SQL statement currently executing
• Resource Waits: Aggregated wait statistics showing which wait types are consuming the most cumulative wait time — the primary diagnostic tool for identifying bottlenecks
• Data File I/O: Read and write activity per database file, showing which data files or log files are experiencing the highest I/O load
• Recent Expensive Queries: The queries consuming the most CPU, elapsed time, logical reads, or physical reads since the instance started — the starting point for query performance tuning

SSMS 22 Administration Task Reference

Azure Data Studio as a Complement to SSMS 22

Azure Data Studio is a cross-platform database tool — available on Windows, macOS, and Linux — that complements SSMS 22 for specific workflows. While SSMS 22 remains the primary tool for full SQL Server administration, Azure Data Studio is preferred for notebook-based query documentation, cross-platform development on macOS or Linux, and PostgreSQL or MySQL connections from the same tool. In SQL Server 2025 environments, Azure Data Studio notebooks are particularly useful for documenting VECTOR_DISTANCE queries and AI workload experiments alongside their results in a single shareable file.

Summary

SSMS 22 replaces the legacy Enterprise Manager as the primary administration tool for SQL Server 2025. It provides Object Explorer for hierarchical navigation of server objects, a Query Editor with IntelliSense and execution plan visualization, Activity Monitor for real-time performance diagnostics, and administration dialogs for database creation, security management, backup and restore, and SQL Server Agent job scheduling. SSMS 22 adds SQL Server 2025-specific capabilities including the JSON viewer, vector data type support in the Table Designer, and a GitHub Copilot preview for natural-language T-SQL assistance. Azure Data Studio complements SSMS 22 for cross-platform and notebook-based workflows. The next lesson covers using the Query Editor to write and execute T-SQL statements in detail.