DB-유

Azure Data Studio for Daily DBA Work

· mssql, tools, azure-data-studio

SSMS was slow to open one too many times on my laptop, so I started reaching for Azure Data Studio for the small stuff. SSMS is still where I go for wizards, Agent job design and the Maintenance Plan designer. For the rest of a normal day (queries against several servers, quick health checks, exporting a result to CSV, runbooks as notebooks) I use ADS. It's lighter, starts in a couple of seconds, runs on Windows, macOS and Linux, and extensions cover most gaps.

This is my setup. I'm on version 1.24 or later, which updates itself monthly, against SQL Server 2012 or later; it works against Azure SQL Database too.

Connections and server groups

Open the Connections pane (Ctrl+G), create a connection, and put every server in a colour-coded group. Production in red, staging in yellow. The colour shows as a bar on every editor tab, which has saved me from running an UPDATE on the wrong server more than once.

Connection settings are stored in settings.json; you can hand-edit them:

"datasource.connectionGroups": [
  { "name": "Production", "id": "prod", "color": "#d13438" },
  { "name": "Staging",    "id": "stg",  "color": "#ffb900" }
]

Passwords go to the OS credential store, not the file, so the file is safe to sync between machines.

Extensions worth installing

From the Extensions pane (Ctrl+Shift+X) install:

  • SQL Server Admin Pack: bundles Agent, Profiler, Import wizard and dacpac support.
  • SQL Server Agent: view job history and enable/disable/run jobs. Good enough for daily checks.
  • SQL Server Profiler: an Extended Events viewer with a Profiler-like UI.
  • Query History: keeps every executed statement per session.
  • PowerShell: run Invoke-Sqlcmd and dbatools in the integrated terminal.

Querying from the keyboard

The editor is the VS Code editor, so IntelliSense, multi-cursor (Alt+Click) and column selection (Shift+Alt+drag) all work. The shortcuts I use most:

Ctrl+N       new query on the current connection
F5           run
Ctrl+Shift+E run selection
Ctrl+Shift+U toggle "Use database" picker
Ctrl+K Ctrl+C  comment lines

Change the active database from the toolbar dropdown or with USE. Results support sorting, filtering, and "Save as CSV / JSON / Excel" from the right-hand icons.

Dashboards for a two-second health check

Every connection has a Server dashboard and each database a Database dashboard. Add insight widgets by editing settings.json:

"dashboard.server.widgets": [
  { "name": "Tasks", "gridItemConfig": { "sizex": 1, "sizey": 1 }, "widget": { "tasks-widget": {} } },
  { "name": "Backup status", "gridItemConfig": { "sizex": 2, "sizey": 1 },
    "widget": { "insights-widget": {
      "type": { "bar": { "dataDirection": "vertical", "dataType": "number", "legendPosition": "none" } },
      "queryFile": "C:/dba/insights/backup-age.sql" } } }
]

backup-age.sql is a plain query returning database name and hours since last full backup. One glance at the dashboard replaces a query I used to type every morning.

Notebooks as runbooks

A SQL notebook mixes Markdown and runnable T-SQL cells. My "new server checklist" notebook has a cell per check (memory settings, MAXDOP, tempdb files, backup age, error log tail). I attach it to the new server and press "Run all". The results stay in the .ipynb, which I keep in Git as evidence of what the server looked like on day one.

-- cell: max server memory
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)';

First launch

Connect to a test instance, open the Server dashboard, and run one notebook cell. If IntelliSense does not appear, check the status bar: it should show the SQL Tools Service as "Ready". On first launch it downloads that service, which needs outbound HTTPS.

Rough edges

  • "Login failed" with Windows auth on macOS/Linux. Integrated auth needs a Kerberos ticket (kinit user@DOMAIN). Otherwise use a SQL login.
  • Slow IntelliSense on large databases. Set "mssql.intelliSense.enableIntelliSense": true and "mssql.intelliSense.enableQuickInfo": false; the object cache is built on first connect and can take a minute on databases with thousands of objects.
  • Results grid truncates long text. Increase "queryEditor.results.maxColumnWidth" or view a single cell with the "Show cell" context option.
  • No Maintenance Plans, no SSIS designer, no Database Diagrams. Correct. Keep SSMS installed for those; the two tools coexist fine.

It won't replace SSMS for the wizards. For the query-and-check work that fills most of my day, I open ADS first.