docker-entrypoint-initdb.sh

#!/usr/bin/env bash



if [ ! -f ~/init.lock ]; then

    # wait for database to start...
    for i in {40..0}; do
      if /opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -Q 'SELECT 1;' &> /dev/null; then
        echo "$0: SQL Server started"
        break
      fi
      echo "$0: SQL Server startup in progress..."
      sleep 1
    done

    echo "$0: Initializing database"

    touch ~/tmp.sql

  #BEGIN DATABASE CREATION
  if [ "$MSSQL_DATABASE" ]; then

    cat > ~/tmp.sql <<-EOSQL
    CREATE DATABASE [${MSSQL_DATABASE}] 
    CONTAINMENT = NONE
    ON  PRIMARY ( 
          NAME = N'${MSSQL_DATABASE}', 
          FILENAME = N'/var/opt/mssql/data/${MSSQL_DATABASE}.mdf' , 
          SIZE = 8192KB , 
          FILEGROWTH = 65536KB 
    )
    LOG ON ( 
          NAME = N'${MSSQL_DATABASE}_log', 
          FILENAME = N'/var/opt/mssql/data/${MSSQL_DATABASE}_log.ldf' , 
          SIZE = 8192KB , 
          FILEGROWTH = 65536KB 
    )
    COLLATE ${MSSQL_DATABASE_COLLATE}
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET COMPATIBILITY_LEVEL = 140
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET ANSI_NULL_DEFAULT OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET ANSI_NULLS OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET ANSI_PADDING OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET ANSI_WARNINGS OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET ARITHABORT OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET AUTO_CLOSE OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET AUTO_SHRINK OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET AUTO_UPDATE_STATISTICS ON 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET CURSOR_CLOSE_ON_COMMIT OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET CURSOR_DEFAULT  GLOBAL 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET CONCAT_NULL_YIELDS_NULL OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET NUMERIC_ROUNDABORT OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET QUOTED_IDENTIFIER OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET RECURSIVE_TRIGGERS OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET  DISABLE_BROKER 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET DATE_CORRELATION_OPTIMIZATION OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET PARAMETERIZATION SIMPLE 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET READ_COMMITTED_SNAPSHOT OFF 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET  READ_WRITE 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET RECOVERY FULL 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET  MULTI_USER 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET PAGE_VERIFY CHECKSUM  
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET TARGET_RECOVERY_TIME = 60 SECONDS 
    GO
    ALTER DATABASE [${MSSQL_DATABASE}] SET DELAYED_DURABILITY = DISABLED 
    GO
    USE [${MSSQL_DATABASE}]
    GO
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
    GO
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
    GO
    ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
    GO
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
    GO
    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
    GO
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
    GO
    ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
    GO
    ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
    GO
    USE [${MSSQL_DATABASE}]
    GO
    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [${MSSQL_DATABASE}] MODIFY FILEGROUP [PRIMARY] DEFAULT
    GO

	EOSQL

    /opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -i ~/tmp.sql

    rm -f ~/tmp.sql

  fi
  #END DATABASE CREATION

  #BEGIN USER CREATION
  if [ "$MSSQL_USER" -a "$MSSQL_PASSWORD" ]; then

    DEFAULT_DB="master"

    if [ "$MSSQL_DATABASE" ]; then

      DEFAULT_DB=$MSSQL_DATABASE

    fi

    cat > ~/tmp.sql <<-EOSQL
    USE [master]
    GO
    CREATE LOGIN [${MSSQL_USER}] WITH PASSWORD=N'${MSSQL_PASSWORD}', DEFAULT_DATABASE=[${DEFAULT_DB}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF    
    GO
	EOSQL

    /opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -i ~/tmp.sql

    rm -f ~/tmp.sql

    #BEGIN BIND USER TO DATABASE AS OWNER
    if [ "$MSSQL_DATABASE" ]; then

      	cat > ~/tmp.sql <<-EOSQL
		USE [${MSSQL_DATABASE}]
		GO
		CREATE USER [${MSSQL_USER}] FOR LOGIN [${MSSQL_USER}]
		GO
		USE [${MSSQL_DATABASE}]
		GO
		ALTER ROLE [db_owner] ADD MEMBER [${MSSQL_USER}]
		GO
		EOSQL

      	/opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -i ~/tmp.sql

      	rm -f ~/tmp.sql

    fi
    #END BIND USER TO DATABASE AS OWNER

  fi
  #END USER CREATION

  #BEGIN INITIALIZE SQL SERVER WITH SCRIPTS
  for f in /docker-entrypoint-initdb.d/*; do
    case "$f" in
      *.sh)     echo "$0: running $f"; . "$f" ;;
      *.sql)    echo "$0: running $f"; /opt/mssql-tools/bin/sqlcmd -U SA -P $SA_PASSWORD -X -i  "$f"; echo ;;
      *)        echo "$0: ignoring $f" ;;
    esac
    echo
  done
  #END INITIALIZE SQL SERVER WITH SCRIPTS

  touch ~/init.lock

fi

echo "$0: SQL Server Database ready"