官术网_书友最值得收藏!

Creating Docker images for SQL Server databases

Setting up a database image is just like any other Docker image; I'll be encapsulating the setup tasks in a Dockerfile. Broadly, for a new database, the steps will be as follows:

  • Install SQL Server
  • Configure SQL server
  • Run DDL scripts to create the database schema
  • Run DML scripts to populate static data

This fits in very well with a typical build process using Visual Studio's SQL database project type and the Dacpac deployment model. The output from publishing the project is a .dacpac file that contains the database schema and any custom SQL scripts to run. Using the SqlPackage tool, you can deploy the Dacpac file to a SQL Server instance, and it will either create a new database if it doesn't exist, or it will upgrade an existing database so the schema matches the Dacpac.

This approach is perfect for a custom SQL Server Docker image. I can use multi-stage builds again for the Dockerfile, so you don't need Visual Studio installed to package the database from the source code. This is the first stage of the Dockerfile for the dockeronwindows/ch03-nerd-dinner-db image:

# escape=`
FROM sixeyed/msbuild:netfx-4.5.2-ssdt AS builder

WORKDIR C:\src\NerdDinner.Database
COPY src\NerdDinner.Database .

RUN msbuild NerdDinner.Database.sqlproj `
/p:SQLDBExtensionsRefPath="C:\Microsoft.Data.Tools.Msbuild.10.0.61026\lib\net40" `
/p:SqlServerRedistPath="C:\Microsoft.Data.Tools.Msbuild.10.0.61026\lib\net40"

The builder stage just copies in the SQL project source and runs MSBuild to produce the Dacpac. I'm using a variant of the public sixeyed/msbuild image on Docker Cloud, which includes the NuGet packages you need to compile SQL projects.

Here's the second stage of the Dockerfile, which packages the NerdDinner Dacpac to run in SQL Server Express:

FROM microsoft/mssql-server-windows-express

ENV ACCEPT_EULA="Y" `
DATA_PATH="C:\data" `
sa_password="N3rdD!Nne720^6"

VOLUME ${DATA_PATH}
WORKDIR C:\init

COPY
Initialize-Database.ps1 .
CMD ./Initialize-Database.ps1 -sa_password $env:sa_password -data_path $env:data_path -Verbose

COPY --from=builder C:\src\NerdDinner.Database\bin\Debug\NerdDinner.Database.dacpac .

There are no new instructions here, beyond what you've seen so far. You'll see that there are no RUN commands, so I'm not actually setting up the database schema when I build the image; I'm just packaging the Dacpac file into the image so I have everything I need to create or upgrade the database when the container starts.

In CMD, I run a PowerShell script that sets up the database. It's usually not a good idea to hide all the startup details in a separate script because that means you can't see what's going to happen when the container runs from the Dockerfile alone. But in this case, the startup procedure has a few functions, and they would make for a huge Dockerfile if we put them all in there.

The base SQL Server Express image defines environment variables called ACCEPT_EULA, so the user can accept the license agreement and sa_password to set the administrator password. I extend this image and set default values for the variables. I'll use the variables in the same way in order to allow users to specify an administrator password when they run the container. The rest of the startup script deals with the problem of storing the database state in a Docker volume.

主站蜘蛛池模板: 卓资县| 玉环县| 依安县| 南阳市| 泌阳县| 平塘县| 祁东县| 庆云县| 竹山县| 慈溪市| 平山县| 汉源县| 福海县| 翁源县| 临清市| 文安县| 琼海市| 铜陵市| 衡山县| 吴堡县| 黎川县| 武安市| 全椒县| 荆门市| 堆龙德庆县| 随州市| 宾川县| 阿拉善右旗| 雅江县| 澄城县| 库伦旗| 建瓯市| 麻城市| 河东区| 光泽县| 准格尔旗| 阳信县| 鱼台县| 安西县| 仲巴县| 正定县|