Installing and Using Microsoft SQL Server on Linux (Docker)

Hands-On Lab

 

Photo of Landon Fowler

Landon Fowler

Database Training Architect II

Length

00:30:00

Difficulty

Beginner

Starting with SQL Server 2017, MSSQL professionals can take advantage of the benefits of containerization. Whether we're looking to consolidate several workloads onto a single server or just want a means of delivering consistent environments to our developers, deploying SQL Server via containers may be the solution we're looking for. In this hands-on lab we utilize Docker to work with the latest SQL Server 2017 image. Basic knowledge of Docker and Microsoft SQL Server are recommended.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Installing and Using Microsoft SQL Server on Linux (Docker)

Introduction

Your company's flagship application uses Microsoft SQL Server for its backend database. In order to take advantage of the many benefits of containerization, you are investigating migration to a SQL Server 2017 Docker container. As a first step you are deploying the Docker image and verifying that your flagship application can connect to and manage it.

Solution

  1. Begin by logging in to the lab server using the credentials provided on the hands-on lab page.

    ssh cloud_user@PUBLIC_IP_ADDRESS

Download and Run the SQL Server 2017 Image

  1. Pull down the latest image for SQL Server.

    sudo docker pull mcr.microsoft.com/mssql/server:2017-latest
  2. Execute the image providing a password of AwesomePassword!.

    sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=AwesomePassword!' 
    -p 1433:1433 --name sql1 
    -d mcr.microsoft.com/mssql/server:2017-latest
  3. Verify SQL Server is running.

    sudo docker ps -a

Connect To and Administer the Container

  1. Start an interactive bash shell in the container.

    sudo docker exec -it sql1 "bash"
  2. Connect to the SQL Server instance.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'
  3. Create the database AwesomeCompany.

    CREATE DATABASE AwesomeCompany;
    go
  4. Verify the database exists.

    select name from sys.databases;
    go
  5. Create a login for AwesomeLogin.

    CREATE LOGIN AwesomeLogin WITH PASSWORD = 'AwesomePassword!';
    go
  6. Change context.

    Use AwesomeCompany;
    go
  7. Create a user connected to the newly-created login.

    CREATE USER AwesomeUser FOR LOGIN AwesomeLogin;
    go
  8. Add the new user to the db_owner role.

    exec sp_addrolemember 'db_owner', 'AwesomeUser';
    go
  9. Exit the SQL Server instance.

    quit
  1. Log in as AwesomeLogin.

    /opt/mssql-tools/bin/sqlcmd -S localhost -U AwesomeLogin -P 'AwesomePassword!'
  2. Verify you can use the database.

    Use AwesomeCompany;
    go
    
    CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
    INSERT INTO Inventory VALUES (1, 'widget', 100);
    go
  3. Check the results.

    select * from Inventory;
    go

Conclusion

Congratulations — you've completed this hands-on lab!