Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 39 ยท Getting Started

Installing SQL Server and Its Tools

Set up SQL Server on Windows, Linux or Docker, choose between SSMS, Azure Data Studio and sqlcmd, connect for the first time and run a test query.

To follow this course on your own machine you need two things: a server (the SQL Server engine) and a client (a tool where you type queries). They can live on the same computer or on different ones. This lesson walks through the realistic options, shows how to connect, and explains the first-time errors people hit most often.

You do not have to install anything to learn the language from this site, because standard SQL runs in the editors here. But real T-SQL features such as variables, procedures and MERGE only run on a real server, so installing a free one is worth the twenty minutes.

Choose how to get a server

Option Best for Notes
Developer edition (Windows or Linux) Learning the full product Free, all Enterprise features, not for production
Express edition Small apps, light practice Free, 10 GB database limit
LocalDB Developers on Windows A minimal on-demand engine that starts when your app connects; installed with Visual Studio or the Express installer
Docker container Linux, macOS, throwaway test servers Runs the Linux build of the engine
Azure SQL Database Cloud practice Pay-as-you-go or free-offer tiers; the engine is managed for you

Windows installer

Download the Developer or Express installer from Microsoft, run it, and pick Basic for a default instance or Custom to choose features. During setup you decide the authentication mode (see below) and, in Custom mode, the data folders. After the engine is installed, install SSMS separately; it is a different download.

Docker

On any machine with Docker, one command starts a server. The password must be strong (upper and lower case, digits, symbols, at least eight characters), and you must accept the license:

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

Port 1433 is the default SQL Server port. Because it can be reached from the network, never expose a container with a weak password to the internet.

Authentication modes

SQL Server supports two ways to prove who you are:

  • Windows authentication uses your Windows account; no password is sent to SQL Server. It is the preferred choice on a domain.
  • SQL Server authentication uses a login name and password stored in SQL Server, such as the built-in administrator sa. It is the usual choice on Linux and Docker.

"Mixed mode" means both are allowed. If you installed in Windows-only mode and try to use sa, the login fails even with the right password until you enable mixed mode and restart the service.

Choose a client

SQL Server Management Studio (SSMS) is the full-featured Windows tool: query editor, graphical execution plans, Object Explorer, job scheduling, security dialogs, backup and restore wizards. It is the tool this course assumes for administration lessons.

Azure Data Studio is a lighter, cross-platform editor (Windows, macOS, Linux) with notebooks and extensions, better for developers who mainly write queries. VS Code with the MSSQL extension is similar.

sqlcmd is the command-line client, ideal for scripts and servers without a GUI:

Terminal
sqlcmd -S localhost -U sa -P "Your_Str0ng_Pass!" -C
1> SELECT @@VERSION;
2> GO

-S is the server, -U/-P the SQL login, and -C trusts the server certificate (handy for local development; avoid it against real servers). On Windows with Windows authentication, use -E instead of -U/-P.

Connecting

Every client asks for the same few values:

  1. Server name. localhost, . or (local) for a default instance on your machine; localhost\SQLEXPRESS for the Express named instance; (localdb)\MSSQLLocalDB for LocalDB; host,1433 for a specific port.
  2. Authentication type and credentials.
  3. Encryption options. Recent client drivers encrypt by default and expect a trusted certificate. For a local development server with a self-signed certificate you may need to tick "Trust server certificate".

Once connected, open a new query window and run a first statement:

SQL
SELECT @@VERSION AS version,
       DB_NAME()  AS current_database,
       SUSER_SNAME() AS login_name;

@@VERSION returns the product and build, DB_NAME() the database your window is using (usually master at first), and SUSER_SNAME() your login. These are T-SQL functions, so the editor below uses a stand-in that reads SQLite's equivalent facts.

SQL Stand-in: check what you are connected to

Press Run (or Ctrl + Enter) to see the result here.

Looking around: the catalog

SQL Server exposes metadata through catalog views. The most useful ones to remember:

SQL
SELECT name, create_date FROM sys.databases;      -- all databases on the instance
SELECT name FROM sys.tables;                      -- tables in the current database
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS;                  -- standard, portable catalog

The portable INFORMATION_SCHEMA views exist in many systems; the sys. views are SQL Server's richer native catalog. The stand-in below lists the tables of the sample shop database by reading SQLite's catalog table, which plays the role of sys.tables.

SQL Stand-in: list the tables (like sys.tables) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

First-time problems

  • "A network-related or instance-specific error." The server is not running, the name is wrong, or TCP/IP is disabled. Check the SQL Server service, the instance name, and that port 1433 is reachable.
  • "Login failed for user 'sa'." Wrong password, SQL authentication is not enabled (Windows-only mode), or the sa login is disabled.
  • "The certificate chain was issued by an authority that is not trusted." Trust the server certificate for local development, or install a proper certificate.
  • Docker container exits immediately. The password did not meet the complexity rules, or ACCEPT_EULA=Y is missing; read docker logs sql1.

Create a throwaway database for practice and leave the system databases alone. The next lesson explains what each one is for.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which TCP port does SQL Server use by default?

    1433 is the default SQL Server port. 3306 is MySQL and 5432 is PostgreSQL.
  2. 2. Which edition is free, includes all Enterprise features, and is meant for development only?

    Developer edition has the full feature set but is licensed for development and testing only. Express is limited in size and features.
  3. 3. You can log in with Windows authentication but "sa" is rejected with the right password. What is the likely cause?

    SQL logins such as sa only work when the instance allows mixed mode (SQL Server and Windows authentication).
  4. 4. Which client tool runs scripts from a command line?

    sqlcmd is the command-line client, well suited to scripts, automation and servers without a GUI.

Summary

  • You need a server (Developer, Express, LocalDB, Docker or Azure) and a client (SSMS, Azure Data Studio, VS Code or sqlcmd).
  • Windows authentication uses your Windows account; SQL authentication uses logins stored in SQL Server.
  • Connect with a server name such as localhost or localhost\SQLEXPRESS; local servers may need the "trust server certificate" option.
  • @@VERSION, DB_NAME() and the sys. and INFORMATION_SCHEMA views tell you where you are and what exists.

Next: Databases, Schemas and Objects