Skip to content

Latest commit

 

History

History
205 lines (170 loc) · 6.92 KB

ms-sql-docker.md

File metadata and controls

205 lines (170 loc) · 6.92 KB

Docker commands and provisioning scripts for MS-SQL 2019

Contents [↑]

Volume [↑]

  • Create a volume for MS SQL Server

    host$ docker volume create mssql-2019-data

  • Make sure the volume has been created

    host$ docker volume inspect mssql-2019-data
host$ docker volume ls

Container [↑]

  • Start MS SQL Server container with the volume

    • One time container

      host$ docker container run --name mssql-2019 --hostname mssql-2019 --rm -p 1433:1433 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Test123456!!!!!" --shm-size 2g -v mssql-2019-data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest

      or

    • Restart container always

      host$ docker container run --name mssql-2019 --hostname mssql-2019 --restart=always -p 1433:1433 -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Test123456!!!!!" --shm-size 2g -v mssql-2019-data:/var/opt/mssql -d mcr.microsoft.com/mssql/server:2019-latest

  • Check MS SQL Server container is running properly

    host$ docker container ls

Provisioning [↑]

  • Create a database and an admin user for the service

    host$ docker exec -it mssql-2019 /bin/bash
container# /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Test123456!!!!!'

    • Simple version if the database and the admin user do NOT exist

      mssql> CREATE DATABASE url_service_demo;
mssql> GO
mssql> USE url_service_demo;
mssql> GO
mssql> CREATE LOGIN url_service_dba WITH PASSWORD = 'Test123456!!!!!';
mssql> GO
mssql> CREATE USER url_service_dba FOR LOGIN url_service_dba;
mssql> GO
mssql> ALTER ROLE db_owner ADD MEMBER url_service_dba;
mssql> GO

    • To be compatible if the database or the admin user DOES exist

      -------------------------------------------------------------------------------------------------------------------
-- create database if it not exists
DECLARE
    @db_name VARCHAR(32) = 'url_service_demo',
    @sql_template VARCHAR(MAX) = 'CREATE DATABASE {DB_NAME}',
    @sql_statement VARCHAR(MAX);
BEGIN TRY
    SET @sql_statement = REPLACE(@sql_template, '{DB_NAME}', @db_name);
    EXECUTE (@sql_statement);
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1801
        PRINT N'Database name already exists. Skipping.';
    ELSE
        THROW;
END CATCH
GO

      or

      DECLARE
    @db_name VARCHAR(32) = 'url_service_demo',
    @sql_template VARCHAR(MAX) = 'CREATE DATABASE {DB_NAME}',
    @sql_statement VARCHAR(MAX);
IF EXISTS (
    SELECT name FROM master.dbo.sysdatabases WHERE name=@db_name
)
    BEGIN
        PRINT N'Database name already exists. Skipping.';
    END
ELSE
    BEGIN
        SET @sql_statement = REPLACE(@sql_template, '{DB_NAME}', @db_name);
        EXECUTE (@sql_statement);
    END
GO
      -------------------------------------------------------------------------------------------------------------------
-- use the created database
USE url_service_demo;
GO
      -------------------------------------------------------------------------------------------------------------------
-- create login user if it not exists
DECLARE
    @login_name VARCHAR(32) = 'url_service_dba',
    @login_password VARCHAR(128) = '''Test123456!!!!!''';
DECLARE 
    @sql_statement VARCHAR(MAX) = 'CREATE LOGIN ' + @login_name + ' WITH PASSWORD =' + @user_password;
IF SUSER_ID(@login_name) IS NULL
    EXECUTE (@sql_statement);
ELSE
    PRINT N'Login user name already exists. Skipping.';
GO

-- create admin user if it not exists
DECLARE
    @login_name VARCHAR(32) = 'url_service_dba';
DECLARE
    @user_name VARCHAR(32) = @login_name;
DECLARE 
    @sql_statement VARCHAR(MAX) = 'CREATE USER ' + @user_name + ' FOR LOGIN ' + @login_name;
IF DATABASE_PRINCIPAL_ID(@user_name) IS NULL
    EXECUTE (@sql_statement);
ELSE
    PRINT N'Database user name already exists. Skipping.';
GO

-------------------------------------------------------------------------------------------------------------------
-- grant all privileges
ALTER ROLE db_owner ADD MEMBER url_service_dba;
GO

Stop container [↑]

  • Stop container

    host$ docker stop mssql-2019

Reference [↑]