Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

Your Daily Driver
Got an Overhaul

What's new in SSMS 21 and SSMS 22

Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io

Super Hero Suit

Questions

  • What is your role?
     
  • Which version of SSMS do you use?
     
  • Have you tried GitHub Copilot in SSMS yet?

Agenda

  • SSMS 21 - the rebuild, all the new features
     
  • Copilot in SSMS 21 - and why Microsoft changed it
     
  • SSMS 22 - GitHub Copilot, Schema Compare, formatting, and more
     
  • Gotchas and recap

SSMS 21

64-bit, new installer, dark theme

Finally, 64-bit

64-bit

  • The old pain - 32-bit SSMS ran out of memory on big scripts and huge result grids
     
  • SSMS 21 - a 64-bit application on the Visual Studio 2022 shell
     
  • The result - fewer out-of-memory errors on large scripts and result sets

New installer, automatic updates

Install

  • Visual Studio Installer - the same installer Visual Studio uses
     
  • Automatic updates - stop hunting for the download link every few months
     
  • Workloads and Components - install only what you need, smaller footprint

Dark theme

Demo

Object Explorer, Query Editor, Results, Template Explorer.

Tab options

Tabs

  • Vertical tabs - see 20 open windows down the side, not crammed across the top
     
  • Tab coloring - color by project or file type, so prod and dev look different
     
  • Tab width - set a min and max so titles stay readable

Results grid options

Results

  • NULL coloring - give NULL cells their own font and background color
     
  • Grid font - set it once, read it from the back of the room

Modern connection dialog

Connect

  • New dialog - recents, favorites, and Azure browsing in one place
     
  • Opt-in preview in 21 - the classic dialog is still the default here (that changes in 22)

SQL Projects

Projects

  • Database as code - your schema lives in a project, in source control
     
  • Build and publish - deploy from a known-good definition, not ad hoc scripts

Always Encrypted Assessment

Security

  • Find it - right-click a database, Tasks, Always Encrypted Wizard (formerly Encrypt Columns)
     
  • Assessment - flags the columns that can't be encrypted, and why
     
  • Report - export the results to CSV or text
     
  • Skips failures - only the columns that pass get encrypted

Always Encrypted Wizard

Security

  • Online encryption - apps keep working during it, needs a primary key
     
  • Managed HSM - create new keys in Azure Key Vault Managed HSM
     
  • SSMS 22 - Key Vault sign-in fix for federated identity (22.4.1)
     
  • Not supported - temporal and in-memory tables
     
  • After encrypting - clear the plan cache, or the first run of an affected query can fail

Copilot in SSMS, take one

Copilot

  • The first Copilot - built on Azure OpenAI, you brought your own endpoint and keys
     
  • The problem - cumbersome to set up, and clunky to use
     
  • Good news - Microsoft reworked it in 22, so that's the version we'll spend time on

SSMS 22

GitHub Copilot, Database DevOps, and more

Connection dialog, now the default

Connect

  • Now the default - the modern dialog replaces the classic one
     
  • Strict encryption (TDS 8.0) - the secure default for new connections
     
  • Gotcha - no valid certificate means a connection error the first time you connect

Connection dialog upgrades

Connect

  • Hostname in certificate - on the main view, for certs that don't match the server name (22.9)
     
  • Import and export - move saved connections to a new machine (22.9)
     
  • Browse Azure and Fabric - search subscriptions, now including Managed Instances (22.9)
     
  • More saved connections - up to 60 recents and favorites (22.9)
     
  • Entra Integrated - Azure SQL and Managed Instance, even with Web Account Manager off (22.11)

Windows Arm64

Arm64

  • Native Arm64 - connect and query natively on Arm64 Windows (22.0)
     
  • Profiler and Database Engine Tuning Advisor (DTA) - added in 22.3, running as 32-bit apps

GitHub Copilot, the retune

Copilot

  • A new Copilot - Microsoft replaced the Azure OpenAI version with GitHub Copilot
     
  • Just sign in - a GitHub account, no endpoints or keys to wire up
     
  • Chat and completions - reached general availability in 22.4.1

Chat: write, explain, fix

Demo

-- Prompts I actually use in the chat window
Write a query to find the top 10 customers by total order value
Explain what this stored procedure does
Why is this query slow, and how would you rewrite it?
Add error handling and a transaction to this script

Editor autocompletions

Autocomplete

  • As you type - Copilot suggests the rest of the line of T-SQL
     
  • Tab to accept - or keep typing to ignore the suggestion
     
  • Off by default - since 22.5.2, turn them on in Tools, Options, Text Editor, Inline Suggestions

Copilot instructions

Instructions

  • Custom instructions - your own rules for every chat (22.4.1)
     
  • Database instructions - stored on the database, picked up automatically (22.3)
     
  • CONSTITUTION.md - an extended property that sets the login Copilot runs as (22.7)

Agent Mode

Copilot

  • Tasks, not queries - describe the job, it plans the steps (preview in 22.7, GA in 22.10.2)
     
  • What it does - runs queries, reads execution plans, proposes schema changes
     
  • Approvals - it confirms each action before it runs
     
  • Permissions - it runs as the login you connected with

Agent Mode

Demo

Describe the job, approve each step, see what it found.

Agent Mode skills

Agent Mode

  • Performance - slow query investigation, wait categories, IO pressure, memory issues (22.8)
     
  • Health - statistics assessments, resource usage, active connections
     
  • DR - check my geo DR status
     
  • Object Explorer - Copilot can work with the objects you're connected to (22.9)

Agent Mode permissions

Security

Who does Copilot run as?

You.

 

  • Permissions - every query runs with your login's permissions
     
  • Approvals - the prompt confirms intent, it isn't a security boundary
     
  • The fix - a least-privilege login, or agent ExecuteAsUser in CONSTITUTION.md

Database DevOps

Projects

  • Now GA - the Database DevOps workload left preview in 22.10.2
     
  • Create Project from Database - right-click a database in Object Explorer
     
  • 74 item templates - tables, views, security, storage, programmability
     
  • References - DACPAC, another project, NuGet package, or a system database
     
  • Profiles - open .scmp and .publish.xml files from Solution Explorer

Schema Compare

Projects

  • Compare - databases, .dacpac files, and SQL projects (preview in 22.7, GA in 22.10.2)
     
  • Save as .scmp - save the compare and rerun it later
     
  • SQLCMD variables - now supported in the publish dialog

Schema Compare

Demo

SSMSDemo vs. its project, save the .scmp, publish.

T-SQL formatting

Before

select o.OrderId,c.Name,sum(od.Qty*od.Price) total from Orders o join Customers c on c.Id=o.CustId join OrderDetail od on od.OrderId=o.OrderId group by o.OrderId,c.Name
USE [SSMSDemo];
GO
 
-- BEFORE: paste this as one line, then run the formatter live.
select o.OrderId,c.Name,sum(od.Qty*od.Price) total from Orders o join Customers c on c.Id=o.CustId join OrderDetail od 
on od.OrderId=o.OrderId group by o.OrderId,c.Name
GO

T-SQL formatting

After

SELECT
    o.OrderId,
    c.Name,
    SUM(od.Qty * od.Price) AS total
FROM Orders o
    JOIN Customers c   ON c.Id = o.CustId
    JOIN OrderDetail od ON od.OrderId = o.OrderId
GROUP BY
    o.OrderId,
    c.Name;
-- AFTER: what the formatter produces (shown here so you have the target).
SELECT
    o.OrderId,
    c.Name,
    SUM(od.Qty * od.Price) AS total
FROM Orders o
    JOIN Customers c    ON c.Id = o.CustId
    JOIN OrderDetail od ON od.OrderId = o.OrderId
GROUP BY
    o.OrderId,
    c.Name;
GO

Formatter settings

Settings

  • Indents - tabs or spaces (22.9)
     
  • Commas - leading or trailing
     
  • Aliases - AS, an equals sign, or the original style
     
  • Live preview - before and after T-SQL in the settings dialog
     
  • New in 22.10.2 - brackets, identifier casing, IN lists, JOIN and ON line breaks

Grid zoom and status-bar aggregates

Results

  • Zoom the grid - hold Ctrl and roll the mouse wheel to zoom results in and out
     
  • Status-bar aggregates - select numbers in the grid, see SUM, COUNT, AVG at the bottom

Results and diagnostics

Results

  • Save as - Excel, JSON, Markdown, or XML from the grid (22.4.1)
     
  • Include headers - a checkbox on the Save Grid Results dialog (22.10.2)
     
  • Data type tooltip - hover a column to see its type (22.6)
     
  • Execution plan defaults - keep actual plans, client statistics, or live query stats on (22.10.2)

Results grid

Demo

Zoom, aggregates, Save as Markdown, plan defaults.

JSON and vector

New types

  • JSON index wizard - create an index on JSON data through the UI, no T-SQL
     
  • View JSON in the grid - open and read JSON values from the results
     
  • Vector index - right-click Indexes, New Index, Vector Index
     
  • Vector columns - Table Designer and Edit Data both handle the vector type

Fabric integration

Fabric

  • Warehouses and SQL endpoints - work Fabric objects from SSMS
     
  • Browsing - organize by schema, query snapshots, context-aware menus
     
  • Open in Fabric Portal - right-click a Fabric SQL Database (22.10.2)

Odds and ends

More

  • Linked server wizard - a streamlined create flow with encryption options
     
  • Import settings - carry over registered servers, shortcuts, and preferences from a prior version
     
  • Group by schema - a toggle in Object Explorer (22.4.1)
     
  • Database Properties - Auto Index Compaction (22.8) and TIME_ZONE (22.9)
     
  • Migrate to Azure SQL - readiness assessment and migration built into SSMS (22.5)

Keep in mind

Gotchas

  • Strict encryption default - a cert error on first connect can surprise you
     
  • Agent Mode permissions - connect with a least-privilege login
     
  • Copilot completions - off by default since 22.5.2, turn them on
     
  • Stay current - 22.11.0 is current as of October 13, 2026

Recap

Next steps

  • Move to SSMS 21 for 64-bit and dark mode
     
  • Then SSMS 22 for GitHub Copilot and Database DevOps
     
  • Turn on completions, try Agent Mode in dev with a least-privilege login
     
  • Expect Strict encryption, fix your certificates first

Resources

Questions?

Thank you!

Jeff Taylor

Thank you for attending my session today.
If you have any additional questions, please don't hesitate to reach out.

@ReviewMyDB

https://jefftaylor.io/blog

Jeff@JeffTaylor.io