Docker撰写如何在Windows容器上初始化SQL Server DB？

问题描述

我在Windows 10上为Windows 18.03运行 docker 。我在自己的VS 2017项目中使用 docker compose文件中的 microsoft / mssql-server-windows-express （来自Windows Server）映像。我在这里试图完成的工作是从脚本初始化数据库。我尝试在 docker.compose.yml 中使用命令开关，但没有成功...

I run docker for Windows 18.03 on Windows 10. I use the microsoft/mssql-server-windows-express (from Windows Server) image from a docker compose file in my own VS 2017 project. What I try to accomplish here is to initialize the database from a script. I tried using the "command" switch in the docker.compose.yml but without much success...

这是docker compose文件：

Here's the docker compose file :

  myscustomservice:
    image: myscustomservice
    build:
      context: .\myscustomservice
      dockerfile: Dockerfile

  db:
    image: microsoft/mssql-server-windows-express
    volumes:
       - ".\\data:C:\\data"
    #command: --init-file C:\\data\\CreateLocalDB.sql
    #command: "sqlcmd -U sa -P sUper45!pas5word -i C:\\data\\CreateLocalDB.sql"
    restart: always
    ports: 
      - "1533:1433"
    environment:
      - "sa_password=sUper45!pas5word"
      - "ACCEPT_EULA=Y"

volumes:
  db-data: 

请注意，我已经尝试了注释的2个命令行。第一个没有说找不到文件，第二个只是用那个替换了正常的命令行，因此容器无法启动（或无法启动）。

Note that I have tried the 2 command lines that are commented. First one fails saying it doesn't find the file and second one just replace the normal command line by that one, so the container doesn't start (or doesn't stay up).

在我的本地驱动器上，我有一个C：\myscustomservice\数据驱动器，其中包含文件CreateLocalDB.sql。它安装在C：\data文件夹中的容器上（当我在容器中运行powershell时可以看到它）。

On my local drive, I have a C:\myscustomservice\data drive with the file CreateLocalDB.sql in it. It is mounted on the container in the C:\data folder (I see it when I run powershell inside the container).

sql文件如下所示：

The sql file looks like this :

USE MASTER

CREATE DATABASE [customDB_test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'customDB_test', FILENAME = N'C:\data\customDB_test.mdf' , SIZE = 1594752KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'customDB_test_log', FILENAME = N'C:\data\customDB_test.ldf' , SIZE = 3584KB , MAXSIZE = 2048GB , FILEGROWTH = 10240KB )
GO  

有人知道我该怎么做吗？网上的所有示例都来自linux容器，而该映像来自Windows Server容器。

Does anyone have an idea how could I do this ? All examples on the net are from linux containers, and this image is from Windows Server container.

推荐答案

确定，就这样，最后我不得不创建依赖于 db的anothe服务来调用用于检查数据库是否存在的powershell脚本。如果不存在，我将调用mssql脚本来创建它。

OK, just so you know, I finally had to create anothe service that depends on "db" to call a powershell script that check for database existence. If it doesn't exists, I call an mssql script to create it.

以下是dockerfile：

Here's the dockerfile :

FROM microsoft/wcf:4.7.1
ARG source
# Creates a directory for custom application
RUN mkdir C:\MyCustomService

COPY . c:\\MyCustomService

# Remove existing default web site
RUN powershell -NoProfile -Command \
Import-module WebAdministration; \
Remove-WebSite -Name "'Default Web Site'"

# Configure the new site in IIS. Binds it to port 80 otherwise it won't work because it needs a default app listening on this port
RUN powershell -NoProfile -Command \
Import-module IISAdministration; \
New-IISSite -Name "MyCustomService" -PhysicalPath C:\MyCustomService -BindingInformation "*:80:";


# Add net.tcp support on the new site and change it to web aplication.
RUN Import-Module WebAdministration; Set-ItemProperty "IIS:\\Sites\\MyCustomService" -name bindings -value (@{protocol='net.tcp';bindingInformation='808:*'},@{protocol='http';bindingInformation='*:80:'});
RUN windows\system32\inetsrv\appcmd.exe set app 'MyCustomService/' /enabledProtocols:"http,net.tcp"
# This instruction tells the container to listen on port 83.
EXPOSE 80
EXPOSE 808

这是新的docker-compose文件：

Here's the new docker-compose file :

myscustomservice:
  image: myscustomservice
  build:
    context: .\myscustomservice
    dockerfile: Dockerfile
  ports: 
  - "83:80"
  - "1010:808"
  depends_on: 
    - db
    - db-init

  db:
    image: microsoft/mssql-server-windows-express
    volumes:
     - ".\\data:C:\\data"

    ports: 
      - "1533:1433"
    environment:
      - "sa_password=sUper45!pas5word"
      - "ACCEPT_EULA=Y"
      - 'attach_dbs=[{"dbName":"customDB_test","dbFiles":["C:\\data\\customDB_test.mdf","C:\\data\\customDB_test.ldf"]}]'
  volumes:
    db-data: 


db-init:
  image: microsoft/mssql-server-windows-express
  volumes:
     - ".\\data:C:\\data"

  command: powershell -executionpolicy bypass "C:\\data\\initialize_db.ps1 -insertTestData"

  environment:
    - "sa_password=sUper45!pas5word"
    - "ACCEPT_EULA=Y"


  depends_on: 
    - db

请注意db服务中的 attach_dbs环境变量。这样，它将尝试绑定到现有文件，因此由db_init服务运行的脚本将找到数据库，并且不会重新创建该数据库。

Note the "attach_dbs" environment variable in the db service. This way, it tries to bind to existing files, so the script run by db_init service will find the database and will not recreate it.

powershell脚本 initialize_db.ps1 ：

The powershell script "initialize_db.ps1" :

param([switch]$insertTestData)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "db\SQLEXPRESS"
$database = "customDB_test"
$dbs = $server.Databases
$exists = $false

#This sets the connection to mixed-mode authentication 
$server.ConnectionContext.LoginSecure=$false; 

#This sets the login name 
$server.ConnectionContext.set_Login("sa"); 

#This sets the password 
$server.ConnectionContext.set_Password("sUper45!pas5word")

try 
{
    foreach ($db in $dbs) 
    {
        Write-Host $db.Name
        if($db.Name -eq $database) 
        {
            Write-Host "Database already exist"
            $exists = $true
        }
    }

}
catch 
{
    Write-Error "Failed to connect to $server"
}


if(-not $exists)
{
    Write-Host "Database doesn't exist"
    $StopWatch = [System.Diagnostics.Stopwatch]::StartNew()

    sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_schema.sql'
    Write-Host "Database created"
    $StopWatch.Elapsed

    if($insertTestData)
    {
        Write-Host "Begining data insertion..." 
        sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_data.sql'
        Write-Host "Data inserted"
        $StopWatch.Elapsed
    }

    $StopWatch.Stop()
}

sqlcmd -S 'db' -U sa -P 'sUper45!pas5word' -i 'C:\\data\\CreateLocalDB_user.sql'

该脚本至少运行1个，最多运行3个SQL脚本：

Ths script runs at least 1 and up to 3 SQL script :

• CreateLocalDB_user.sql-重新创建自定义登录名以及db用户，以便您可以与此用户连接


• CreateLocalDB_schema.sql-如果数据库本身不存在，则创建数据库本身


• CreateLocalDB_data。 sql-如果在调用中指定了 insertTestData开关，则添加启动数据（在db_init服务下的docker compose中）

dockerfile服务的端口公开了http的端口80和net.tcp的端口808， myscustomservice的web.config文件公开了wcf服务，如下所示：

The dockerfile of the service exposes ports 80 for http and 808 for net.tcp and my web.config file for "myscustomservice" exposes the wcf service like this :

      <host>
          <baseAddresses>
            <add baseAddress="net.tcp://localhost:1010/myscustomservice/Customer.svc"/>
          </baseAddresses>
        </host>

这篇关于Docker撰写如何在Windows容器上初始化SQL Server DB？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！