image Microsoft SQL Server 2017 with StorageOS

Microsoft’s latest SQL Server release represents a major update with availability for Linux platform support and support to run as a container.

Linux Support

Microsoft SQL Server for Linux runs with any supported Linux distribution. The recommended distribution for testing StorageOS is Ubuntu 16.04. Additional requirements include:

  • Minimum of 4 GB of disk space
  • Minimum of 4 GB of RAM (has been tested to work with 3.25GB)

Before you start, ensure you have StorageOS installed and ready on a Linux cluster.

Install MS SQL Server with StorageOS

  1. Startup the latest MS SQL Server container on a StorageOS node with a StorageOS persistent volume:

    docker run -e 'ACCEPT_EULA=Y' -e '[email protected]' -p 1433:1433 \
        --name mssql -v mssql:/var/opt/mssql --volume-driver=storageos \
        -d microsoft/mssql-server-linux
    
  2. Confirm a StorageOS volume was created successfully:

    $ sudo docker volume list
    DRIVER              VOLUME NAME
    storageos           mssqldata
    
  3. Confirm SQL Server installation status:

    $ docker logs mssql | more
    ...
    2017-05-04 11:05:10.80 Server      Dedicated admin connection support was established for listening locally on port 1434.
    2017-05-04 11:05:10.80 spid19s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    

Create a Test Database

  1. Connect to SQL Server:

    If you have installed the SQL Server tools separately you can use sqlcmd -S <ip_address> -U SA -P [email protected] to connect from a remote server, alternatively use the docker exec command to run directly from the container. The steps that follow apply to both methods of connecting to SQL Server.

    $ docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -U SA -P [email protected]
    
    1> SELECT Name from sys.Databases;
    2> go
    Name
    --------------------------------------------------------------------------------------------------------------------------------
    master
    empdb
    model
    msdb
    
    (4 rows affected)
    1> create database testdb;
    2> go
    1> use testdb;
    2> go
    Changed database context to 'testdb'.
    1> create table inventory (id INT, name NVARCHAR(50), quantity INT);
    2> go
    1> INSERT INTO inventory VALUES (1, 'banana', 150);
    2> INSERT INTO inventory VALUES (2, 'orange', 154);
    3> go
    
    (1 rows affected)
    
    (1 rows affected)
    1> SELECT * FROM inventory WHERE quantity > 152;
    2> go
    id          name                                               quantity
    ----------- -------------------------------------------------- -----------
              2 orange                                                     154
    
    (1 rows affected)
    1> quit
    

Fail SQL Server on Hosting Node

  1. Kill MS SQL application container:

    docker kill mssql
    
  2. Confirm running state:

    docker ps -a -f name=mssql
    CONTAINER ID        IMAGE                          COMMAND                  CREATED             STATUS                       PORTS               NAMES
    a9982de2801f        microsoft/mssql-server-linux   "/bin/sh -c /opt/mssq"   12 minutes ago      Exited (137) 2 minutes ago                       mssql
    

Recover MS SQL Database on Second Node

  1. Start up a new application container:

    docker run -e 'ACCEPT_EULA=Y' -e '[email protected]' -p 1433:1433 \
        --name mssql -v mssql:/var/opt/mssql --volume-driver=storageos \
        -d microsoft/mssql-server-linux
    

Open Database to Read and Write Rows

  1. Load Database and List Rows:

    $ docker exec -it mssql /opt/mssql-tools/bin/sqlcmd -U SA -P [email protected]
    1> use testdb
    2> go
    Changed database context to 'testdb'.
    1> SELECT * FROM inventory
    2> go
    id          name                                               quantity
    ----------- -------------------------------------------------- -----------
              1 banana                                                     150
              2 orange                                                     154
    
    (2 rows affected)
    
  2. Add a new row:

    1> INSERT INTO inventory VALUES (3, 'apple', 99);
    2> go
    
    (1 rows affected)
    1> SELECT * FROM inventory
    2> go
    id          name                                               quantity
    ----------- -------------------------------------------------- -----------
              1 banana                                                     150
              2 orange                                                     154
              3 apple                                                       99
    
    (3 rows affected)
    1> quit
    

More Information

Starting with SQL Server 2017 CTP 2.0, the SQL Server command-line tools are included in the Docker image. If you attach to the image with an interactive command-prompt, you can run the tools locally.

More information on SQL Server for Linux is available on the Microsoft SQL Server for Linux web page.