Narcis Miclaus

8 min read

SQL Server, from the ground up Lesson 2 / 40

Installing SQL Server and connecting with SSMS

Developer Edition on Windows, Docker on Mac or Linux, and picking between SSMS and Azure Data Studio. The 20-minute setup you'll use for the rest of the course.

The fastest way to get nowhere with SQL Server is to skip the install. You can read ten thousand words about indexes, but if you can’t run SELECT 1 on your own machine, none of it sticks. So today we’re installing SQL Server Developer Edition, picking a client, and verifying that the whole thing works.

This lesson is the only one in the course where the instructions branch by operating system. Pick your platform, follow it through, and by the end you’ll have a running SQL Server instance and a client that can talk to it. Total time: about 20 minutes if your internet isn’t sulking.

A quick compass

You need two things: a server (the engine that stores and runs queries) and a client (the program you type queries into). We’ll install the server first, then the client.

  • Server options: SQL Server Developer Edition on Windows, or SQL Server in Docker on any OS, or Azure SQL Database in the cloud.
  • Client options: SSMS (Windows only, full-featured), Azure Data Studio (any OS, modern and light), VS Code with the mssql extension (any OS, lightest of all).

My recommendations, in one breath: if you’re on Windows, install Developer Edition natively and use SSMS. If you’re on Mac or Linux, run SQL Server in Docker and use Azure Data Studio. If you just want a cloud-only setup, create a free-tier Azure SQL Database and connect from Azure Data Studio. Any of those three will work for the whole course.

Path A — Windows: Developer Edition + SSMS

If you’re on Windows 10 or 11, this is the friendly path.

Step 1: Download Developer Edition

Go to microsoft.com/sql-server/sql-server-downloads and click “Download now” under Developer. You’ll get a tiny installer called something like SQL2022-SSEI-Dev.exe. Run it.

Step 2: Basic install

When the installer asks about the install type, pick Basic. “Custom” is for people who already know exactly which features they want. “Basic” installs the engine with sensible defaults, which is exactly what you want.

Accept the licence agreement. Pick an install location (the default is fine). Click Install. Go make coffee. When you come back, you’ll see a success screen with:

  • Instance name: MSSQLSERVER (this is the default instance)
  • Connection string: Server=localhost;Database=master;Trusted_Connection=True;
  • SQL Server version: something like Microsoft SQL Server 2022 (16.x)

That’s your server. It’s running. It’s listening. Good.

If at any point the installer says “a previous installation has started but not completed, and you need to reboot” — reboot. This message is not a suggestion. SQL Server holds a grudge until it gets its reboot.

Step 3: Install SSMS

SSMS stands for SQL Server Management Studio. It’s a separate download from the engine. Same page, scroll further down. Click “Download SSMS” or go directly to aka.ms/ssmsfullsetup. Run the installer. Click next, next, done.

Step 4: Connect

Launch SSMS. The “Connect to Server” dialog pops up. Fill in:

  • Server type: Database Engine
  • Server name: localhost (or . or .\MSSQLSERVER)
  • Authentication: Windows Authentication (no username/password needed if you installed as yourself)

Click Connect. If the left-hand Object Explorer fills in with a tree starting at your server name, you’re done. You have SQL Server running and SSMS connected.

Step 5: Run your first query

Click the “New Query” button in the toolbar. A tab opens. Type:

SELECT @@VERSION;

Press F5 to run. A result grid appears at the bottom with a long string that starts with Microsoft SQL Server 2022 (RTM) or similar. Congratulations. You are now running SQL Server.

Path B — Mac or Linux: Docker + Azure Data Studio

SQL Server doesn’t natively install on a Mac. On Linux you can install natively, but Docker is easier and gives you the same environment as everyone else. This path works identically on both.

Step 1: Install Docker Desktop

Grab Docker Desktop from docker.com if you don’t have it already. Start it. Accept the defaults. Wait for the whale icon in your menu bar to stop bouncing.

Step 2: Pull and run the SQL Server image

Open a terminal and run:

docker pull mcr.microsoft.com/mssql/server:2022-latest

docker run -e "ACCEPT_EULA=Y" \
           -e "MSSQL_SA_PASSWORD=YourStrong!Passw0rd" \
           -p 1433:1433 \
           --name sqlserver \
           -d mcr.microsoft.com/mssql/server:2022-latest

That’s it. You now have a SQL Server 2022 container running in the background, listening on port 1433 of your localhost. The SA (“system administrator”) password is YourStrong!Passw0rd — change it to something you’ll remember, but it has to meet SQL Server’s password policy (at least 8 characters, a mix of upper, lower, digits, symbols). Too-weak passwords fail silently and you’ll spend 20 minutes wondering why you can’t connect.

Verify the container is running:

docker ps

You should see a line mentioning sqlserver with a status of Up X seconds.

Step 3: Install Azure Data Studio

Download Azure Data Studio from azure.microsoft.com/products/data-studio. It’s a free, Electron-based, VS-Code-flavoured editor for SQL Server. Works on Mac, Linux, and Windows. Install it the normal way.

Step 4: Connect

Launch Azure Data Studio. Click “New Connection” in the sidebar. Fill in:

  • Server: localhost,1433
  • Authentication type: SQL Login
  • User name: sa
  • Password: YourStrong!Passw0rd (or whatever you set)
  • Database: leave empty (we’ll connect to master by default)
  • Name (optional): “Local Docker SQL Server”

Click Connect. You should see a “Home” dashboard fill in with a green “Connected” badge.

Step 5: Run your first query

Click “New Query.” A tab opens. Type:

SELECT @@VERSION;

Press F5 or click the Run button. A result appears showing Microsoft SQL Server 2022 (RTM-CU...) and a Linux string. That’s expected — the container is running Linux-based SQL Server. Same engine, different host.

Path C — Cloud: Azure SQL Database free tier

If you’d rather not install anything locally, Microsoft has a free-tier Azure SQL Database that’s genuinely free-forever (up to 100,000 vCore seconds per month, which is way more than you’ll use for this course). It’s a decent option if your laptop is old or you want to practice connecting to a cloud database.

Rough outline: create a free Azure account (credit card required for identity, no charges for free-tier usage), go to the Azure Portal, search for “Azure SQL,” create a new database, pick “Free” as the pricing tier, note the server name and admin credentials, then connect with Azure Data Studio. This takes about 15 minutes if it’s your first time and 5 minutes if you’ve used Azure before.

I won’t walk through the Azure UI step-by-step here because Microsoft changes it roughly every six months and any screenshot I include will be wrong by February. But if you search for “Azure SQL Database free tier quickstart” the current docs will walk you through it cleanly.

For the rest of the course, I’ll assume you have some SQL Server to talk to, and I’ll note when a feature specifically doesn’t exist on Azure SQL Database (a few of the lessons on SQL Agent and the First Responder Kit fall into this category).

SSMS vs Azure Data Studio, the one real choice

Two clients, both free, both from Microsoft. Which should you use?

SSMS, if you’re on Windows, is still the most powerful tool for SQL Server work. Full Object Explorer with every possible right-click action. Database diagrams. Maintenance plan designer. Profiler. Activity Monitor. SSRS and SSIS integration. Deep execution-plan inspection. If you’re going to be a full-time DBA, you’re going to live in SSMS.

Azure Data Studio, on the other hand, is the modern cross-platform editor. It looks and behaves like VS Code. It has tabs, themes, git integration, the terminal built in, Jupyter-style notebooks, and a plugin ecosystem. For everyday query writing and light DBA work, it’s more pleasant than SSMS. It lacks some of the deep DBA tools (no profiler, no maintenance plan designer), but those aren’t things you’ll miss until well into your DBA career.

My advice: if you’re on Windows, install both and use whichever suits your current mood. For the course, most screenshots will be from SSMS because that’s what most SQL Server tutorials show, but every code example works in either.

The AdventureWorks sample database

At some point in the next lesson or two we’ll want a pre-populated database to play with. The two canonical ones are AdventureWorks (a bike-shop OLTP database) and Northwind (an ancient trading company, much smaller). We’ll use AdventureWorks.

To install it: go to github.com/Microsoft/sql-server-samples, download the AdventureWorks2022.bak file, put it in a folder SQL Server can read, and restore it:

RESTORE DATABASE AdventureWorks2022
FROM DISK = N'C:\path\to\AdventureWorks2022.bak'
WITH MOVE N'AdventureWorks2022'
        TO N'C:\SQLData\AdventureWorks2022.mdf',
     MOVE N'AdventureWorks2022_log'
        TO N'C:\SQLData\AdventureWorks2022_log.ldf',
     REPLACE;

If you’re running Docker, copy the .bak file into the container first with docker cp, and adjust the paths to something under /var/opt/mssql/data/. We’ll cover backup and restore properly in lesson 31; for now this single command is enough.

Sanity checks: the four queries to run right now

Once you’re connected, paste these four queries into a new window and run them. If all four return sensible results, your install is good to go for the rest of the course.

-- 1. Version and edition
SELECT @@VERSION AS version_string,
       SERVERPROPERTY('Edition')          AS edition,
       SERVERPROPERTY('ProductVersion')   AS product_version,
       SERVERPROPERTY('ProductLevel')     AS product_level;

-- 2. List the system databases
SELECT name, database_id, create_date
FROM sys.databases
ORDER BY database_id;

-- 3. Who am I, what session am I?
SELECT SUSER_SNAME()    AS login_name,
       USER_NAME()      AS user_name,
       @@SPID           AS session_id,
       HOST_NAME()      AS client_host;

-- 4. A tiny compute query
SELECT 1 + 1 AS two_plus_two,
       GETDATE() AS server_time;

Query 1 tells you what you’re talking to. Query 2 should show you master, tempdb, model, and msdb at minimum — the four system databases SQL Server needs to run. Query 3 tells you who SQL Server thinks you are. Query 4 is the smoke test. If it returns 2 and today’s date, you’re good.

What if something went wrong?

The top three errors on first install:

  • “Cannot connect to server, error 40.” SQL Server service isn’t running, or TCP/IP isn’t enabled. Open SQL Server Configuration Manager (Windows), expand “SQL Server Network Configuration” → “Protocols for MSSQLSERVER,” and make sure TCP/IP is Enabled. Restart the SQL Server service.
  • “Login failed for user ‘sa’.” On Windows local installs, SA login is disabled by default and you should use Windows Authentication instead. On Docker, double-check you’re using the SA password you set in the docker run command.
  • “The password didn’t meet complexity requirements.” Make your password at least 8 characters, with at least one upper, one lower, one digit, and one symbol. SQL Server silently rejects weaker passwords and the error message isn’t always helpful.

If you’re truly stuck, docker logs sqlserver (Docker) or the SQL Server Configuration Manager (Windows) will tell you what went wrong. Nine times out of ten it’s one of the three above.

Next lesson: databases, schemas, tables — the nesting dolls. Bring your sample database, your coffee, and your curiosity.

Search