SQL Server on Linux on Docker quick and easy

  • Article

Often in conversations with healthcare customers the topic of architecture evolutions or roadmaps come up and in every conversation, is mention of a hybrid strategy – one where use of both on premise and cloud technologies need to be factored into the solution (sometimes both near-term & long-term solutions). In addition to those situations sometimes you just want to quickly stand up SQL Server (for use with a demo of Azure ML Workbench (preview), this happened to me recently). Below is the script I use to get SQL Server up and running quickly using Docker. I usually use a combination of vscode, PowerShell & the appropriate extensions (PowerShell Extension & mssql) to make this easy.

Prerequisites:

  1. Docker for Windows (this should work the same on Linux as well with some slight modifications of source paths & maybe syntax)

Optional:

  1. Visual Studio Code
  2. SQL Server Management Studio (may not be needed if you use mssql extension for vscode).

Install

  1. Install Docker for Windows. This is a simple wizard install. Make sure you have enough memory to run SQL Server in Docker – see this: /en-us/sql/linux/quickstart-install-connect-docker

Optionally install the other software. Technically you could run the TSQL code from sqlcmd inside the Docker container but in my setup, I don't usually do that because I want to externalize any data & code from the running container that way I can easily upgrade to new releases of SQL Server as they come and do things like check in my code to a code repository. The rest of the steps can be done when you want to run an instance and serve data but making sure Docker is setup and operational is a good thing to have ready to go.

Start Docker and Run the container

I usually keep Docker turned off and start it up when I need to run a container.

  1. Launch Docker and wait about 30 seconds for it to start up.

  2. Launch vscode and open the PowerShell script with my saved commands.

  3. Run the commands per the script (except the last one). Note that the first time you do the pull it may take a minute or two to download the container image. When you run this the second time docker will check to see if you have the proper image or if a new one needs to be downloaded, if not then docker will save you time and not download the image again.

    # Confirm that Docker is running
    docker ps

    # Start container

    docker pull microsoft/mssql-server-linux:2017-GA

    docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=***************" -p 1433:1433 --name sql1 -d microsoft/mssql-server-linux:2017-GA

     

    # Confirm that the container is running. SQL Server should be up and running now and ready to host data

    docker ps

     

    # Copy sample DB MDF file into container, make sure you change the attach database files to location:

    docker cp C:\Users\username\SampleData\AdventureWorks\AdventureWorksLT2012_Data.mdf sql1:/var/opt/mssql/data/AdventureWorksLT2012_Data.mdf

 

  1. Open the .sql file containing the code needed to attach the database and run the command:

    -- Attach the MDF file copied into the container

    USE [master]

    GO

    CREATE
    DATABASE [AdventureWorksLT2012] ON

    (FILENAME
    =
    N'/var/opt/mssql/data/AdventureWorksLT2012_Data.mdf')

    FOR
    ATTACH;

    GO

     

  2. Open console in vscode by pressing Ctrl + Shift + P

  3. Type "connect" and you should see the option MS SQL: Connect, select it and press Enter.

    1. Follow steps to create a connection profile for this locally running SQL Server, once saved you'll just select the profile

  4. Select the code in the script and execute it by pressing Ctrl + Shift + E

  5. You might see response saying that the database has been upgraded in version (I'm seeing upgrade from version 864 to 863 for my MDF file) but as long as the MDF file is in working order the attach should work as planned.

  6. To confirm the attach run SELECT
    name, database_id, create_date FROM
    sys.databases; and you should see your database listed there. From that point you can run USE command and query as needed.

  7. When finished close all client connections to the server and then go back to cleanup your docker container by running the last line in the script:

    # When finished with SQL Server, use this to stop the container

    docker stop sql1

 

So, there you have it, SQL Server on Linux on Docker on Windows in 10 easy steps. These very simple scripts are saved in this GitHub repo but it's probably easier to just copy and paste or memorize.