SSMS and Azure Data Studio — Connecting and Managing the Database

Two graphical tools dominate SQL Server management: SQL Server Management Studio (SSMS) — Windows-only, mature, comprehensive, the gold standard for DBA work — and Azure Data Studio (ADS) — cross-platform (Windows/Mac/Linux), modern UI, extension-based, and better for developer workflows. For the BlogApp development, either works well. SSMS is better for database administration tasks; ADS is better for running queries, exploring schemas, and working with notebooks. Many teams use both.

Connecting to SQL Server #

-- ── SSMS Connection Details for LocalDB ──────────────────────────────────
-- Server name:   (localdb)\MSSQLLocalDB
-- Authentication: Windows Authentication
-- ⚠ Note: SSMS may not list LocalDB in the Browse dialog — type it manually

-- ── SSMS Connection Details for full SQL Server instance ─────────────────
-- Server name:   localhost  OR  .\SQLEXPRESS  OR  PCNAME\INSTANCENAME
-- Authentication: Windows Authentication (uses your Windows login)
--              OR SQL Server Authentication (uses sa or named SQL account)

-- ── Verify connection and see all databases ────────────────────────────────
SELECT name, create_date, state_desc
FROM   sys.databases
ORDER  BY name;

-- ── Useful exploratory queries after connecting ───────────────────────────
-- Current database
SELECT DB_NAME() AS CurrentDatabase;

-- All user tables in current database
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'
ORDER  BY TABLE_SCHEMA, TABLE_NAME;

-- Row counts for all tables
SELECT t.name AS TableName,
       SUM(p.rows) AS RowCount
FROM   sys.tables t
JOIN   sys.partitions p ON t.object_id = p.object_id
WHERE  p.index_id IN (0, 1)   -- 0=heap, 1=clustered index
GROUP  BY t.name
ORDER  BY SUM(p.rows) DESC;

-- ── Azure Data Studio ─────────────────────────────────────────────────────
-- ADS uses the same connection details as SSMS
-- Additional features over SSMS:
-- • Built-in notebooks (.ipynb) — document SQL queries with markdown
-- • Extensions: PostgreSQL, MySQL, Kusto (Azure Data Explorer)
-- • Dark mode by default, Monaco editor (same as VS Code)
-- • Integrated terminal

Common Mistakes #

Mistake 1 — Browsing for LocalDB in SSMS instead of typing the instance name (connection fails) #

❌ Wrong — clicking Browse in SSMS Connect dialog; LocalDB not listed; developer thinks SQL Server is not installed.

✅ Correct — type (localdb)\MSSQLLocalDB directly in the server name field; click Connect.

Mistake 2 — Using sa account for application connections (security risk) #

❌ Wrong — connection string uses User Id=sa;Password=...; any SQL injection gives attacker full server control.

✅ Correct — create a dedicated, limited-permission login for the application; use it in the connection string.