Jeff Taylor

Principal Data Consultant

Database Consulting, LLC

Your Daily Driver
Got an Overhaul

What's new in SSMS 21 and SSMS 22

https://forms.office.com/r/WbEywHu9mX

JaxData.org

Every 3rd Wednesday 6-8pm

Except for May, November and December

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?

The route

  • SSMS 21 - the rebuild, all the new features
     
  • The AI false start - Copilot in 21, and why it changed
     
  • SSMS 22 - GitHub Copilot, Schema Compare, formatting, and more
     
  • Gotchas and Tomorrow's plan

SSMS 21

The rebuild: new engine, new chassis

Finally, 64-bit

The engine

  • 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
     
  • What you feel - fewer out-of-memory errors, smoother on large workloads

New installer, automatic updates

Pit crew

  • Visual Studio Installer - install and update SSMS the modern way
     
  • 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.

Yes, this is the one you will get asked about most.

Tabs that work the way you think

Cockpit

  • 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

Dash

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

Modern connection dialog

Connect

  • Refreshed connect experience - clearer, faster to your servers
     
  • 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

  • In the wizard - assess columns right from the Always Encrypted Wizard
     
  • Why it matters - the banks and healthcare shops in the room have asked for this

Copilot in SSMS, take one

AI, v1

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

A turbo that never quite spooled.

SSMS 22

Race day: the tune that works

Connection dialog, now the default

Tech inspection

  • 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

Windows Arm64

Arm64

  • Native Arm64 build - runs natively on Arm64 Windows, a long-requested one

GitHub Copilot, the retune

AI, v2

  • Out with the old - Microsoft dropped the Azure Copilot for 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 - like IntelliSense, but it understands intent
     
  • Heads up - completions ship off by default since 22.5.2, switch them on in options

Agent Mode (preview)

Demo

  • You say - hand it a goal, not a query (SSMS 22.7)
     
  • It works the problem - runs queries, reads execution plans, proposes schema changes
     
  • You stay in control - READ_ONLY by default, it asks before it changes anything

Powerful, and worth respecting

Agent Mode

Let it work. Read every step.

Treat it like traction control: it helps, but you are still driving. Agent Mode can execute queries and modify schema, so approve deliberately, especially outside dev.

Schema Compare (preview)

Demo

  • Compare - databases, .dacpac files, and SQL projects (SSMS 22.7)
     
  • Save as .scmp - recurring compares become a single click
     
  • SQLCMD variables - now supported in the publish dialog

T-SQL formatting (preview)

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

One command, readable again

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

Grid zoom and status-bar aggregates

Demo

  • 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

JSON gets easier

JSON

  • 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 right from the results

Fabric integration

Fabric

  • Warehouses and SQL endpoints - work Fabric objects from SSMS
     
  • Nicer browsing - organize by schema, query snapshots, context-aware menus

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
     
  • SQL projects in the main UI - with Git branch status in the status bar

Keep in mind

Gotchas

  • Strict encryption default - a cert error on first connect can surprise you
     
  • Previews are previews - Schema Compare, formatting, and Agent Mode
     
  • Copilot completions - off by default since 22.5.2, turn them on
     
  • Stay current - 22.7.1 fixed memory and hangs on large scripts, take the update

Green flag, Tomorrow

Recap

  • Move to SSMS 21 for 64-bit and dark mode
     
  • Then SSMS 22 for GitHub Copilot and the DevOps tools
     
  • Turn on completions, try Agent Mode in dev
     
  • 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