Lesson 6: Install SSMS and Essential Tooling

Section 5 — Lesson 6: Install SSMS and Essential Tooling

Welcome back. SQL Server is installed, but you cannot work like a DBA until you have the right tools.
In real production environments, the difference between an entry-level learner and a job-ready DBA is often:
tooling discipline—knowing what to use, when to use it, and how to capture evidence.

In this lesson, you will install SQL Server Management Studio (SSMS) and build a small, professional toolkit
that supports administration, troubleshooting, scripting, and documentation.

Lesson description: You will install SSMS (the primary DBA GUI tool),
confirm you can connect to your SQL Server instance,
configure SSMS settings that improve speed and reduce mistakes,
and install essential supporting tools: Azure Data Studio (optional but useful),
PowerShell readiness, Git basics for script versioning,
and a “DBA tools folder” structure for installers, scripts, outputs, and evidence.
You will finish by running a standard “first connection” checklist and saving proof of your setup.

1) Tooling Strategy: How DBAs Think About Tools

A DBA’s tools are not just “apps.” They are part of your operational workflow:

• Connect to SQL Server safely
• Inspect configuration and health
• Execute scripts consistently
• Capture evidence (outputs, logs, screenshots, results)
• Version control critical scripts and runbooks

Your lab should reflect those habits from day one.

2) Install SSMS (SQL Server Management Studio)

Step 2.1 — Download SSMS

Use Microsoft’s SSMS installer (recommended).
Download it from Microsoft’s official site inside your VM.
Save the installer to:
C:\DBA\Installers\

Step 2.2 — Install SSMS

1. Run the SSMS installer as administrator.
2. Accept defaults unless you have a specific reason to change them.
3. Complete installation.
4. Reboot if prompted.

Step 2.3 — First launch check

Open SSMS once to confirm it launches correctly and completes any first-run configuration.

3) Connect to Your SQL Server Instance (First Connection Checklist)

Step 3.1 — Determine your server name

If you installed a default instance, your server name is usually your machine name:
SQLLAB01 (or whatever you named it).

If you installed a named instance, it will look like:
SQLLAB01\INSTANCENAME

Step 3.2 — Choose authentication

• Windows Authentication: easiest and preferred for lab access
• SQL Authentication: use sa only if you enabled Mixed Mode (and only when needed)

Step 3.3 — Run your “first proof” query

SELECT
    @@SERVERNAME AS ServerName,
    @@VERSION AS VersionInfo,
    SUSER_SNAME() AS ConnectedAs,
    DB_NAME() AS CurrentDatabase;

What success looks like: You see server name, version info, your login identity, and database context.

4) Configure SSMS for DBA Work (Important Settings)

4.1 Turn on line numbers

Line numbers make troubleshooting faster (especially when error messages reference line numbers).

• Tools → Options → Text Editor → Transact-SQL → General → Line numbers

4.2 Make results readable and exportable

• Increase maximum characters in text output (for long messages)
• Use “Results to Text” for certain diagnostics where formatting matters

4.3 Enable “Include Actual Execution Plan” habit (for later tuning)

You will use execution plans heavily in performance tuning sections.
For now, know where it is:
Query → Include Actual Execution Plan

4.4 Add a safe-query habit (DBA discipline)

DBAs do not run destructive commands casually.
Build this habit now:

• Always confirm database context: SELECT DB_NAME();
• Use explicit transactions for changes and test with rollback when learning
• Use SELECT first before UPDATE/DELETE

5) Install Supporting Tools (Your DBA Starter Kit)

Tool A (Optional but useful): Azure Data Studio

Azure Data Studio is excellent for:

• lightweight query editing
• notebooks (documentation + queries together)
• extensions (some DBAs prefer it for certain workflows)

DBA note: SSMS is still the main DBA tool for Windows-based SQL Server administration.
Azure Data Studio is a strong secondary tool.

Tool B: PowerShell readiness

PowerShell is essential for DBAs because it supports automation and repeatable scripts.
You do not need to become a PowerShell expert yet—but you must be comfortable running basic commands.

Confirm PowerShell launches and you can run a simple command:

$PSVersionTable.PSVersion

Tool C: Git basics (for your scripts folder)

DBAs store scripts like assets: versioned, organized, and reusable.
You can use Git to track changes to your scripts and notes.
If you do not want Git yet, you can still follow the folder discipline.

Recommended folder:
C:\DBA\Scripts\

Optional Git initialization (only if you already know Git):

cd C:\DBA\Scripts
git init

DBA principle: Your scripts should be treated like production code: documented and trackable.

6) Create a “DBA Evidence Capture” Pattern

In real DBA work, you often must prove what you saw and what you did.
Build the habit now:

• Save query outputs as text files: C:\DBA\Logs\
• Export results to CSV for reporting
• Save screenshots for key milestones (install success, configuration screens)
• Keep a running lab journal in C:\DBA\Docs\

DBA advantage: Evidence makes you reliable and protects you when someone asks “how do you know?”

7) Practice: Your Required Deliverables for This Lesson

1. Install SSMS successfully.
2. Connect to your SQL Server instance using Windows Authentication.
3. Run the “first proof” query and save the output to: C:\DBA\Logs\first-connection.txt
4. Enable SSMS line numbers.
5. Create (or confirm) your folder structure under C:\DBA\.
6. (Optional) Install Azure Data Studio and run the same proof query there.

Lesson Summary

• SSMS is the primary DBA tool for SQL Server administration and troubleshooting.
• DBAs configure SSMS for speed, clarity, and safer workflow habits.
• PowerShell and (optionally) Git support automation and professional script management.
• Evidence capture is a DBA skill: save outputs, logs, and notes consistently.

Next Lesson: Configure File Locations (Data/Log/tempdb Separation Best Practice)

Next, you will configure SQL Server default file locations for data, log, tempdb, and backups.
This is one of the highest-impact configuration habits DBAs use to prevent performance problems later.