SQL Server和容器 (SQL Server & Containers)

There’s a lot of buzz around containers at the moment but not so much in the SQL Server world which I find odd as, to me as a SQL Server DBA, the technology has a lot of benefits that are worth exploring especially when it comes to development environments.

目前，容器周围有很多嗡嗡声，但是在SQL Server领域却没有那么多，我觉得很奇怪，因为对我来说，作为SQL Server DBA，该技术具有很多优点，值得探讨，尤其是在开发方面环境。

I’ve been working with containers for a while now and one thing that has struck me is that I always seem to install the Docker Engine on a Windows Server 2016 server with the GUI. That seems a bit redundant to me as all interactions with the Docker Engine are through the command line so why waste server resources in displaying a desktop? Surely we should always be working with Windows Server 2016 Core when using containers?

我使用容器已有一段时间了，令我震惊的是，我似乎总是在使用GUI的Windows Server 2016服务器上安装Docker Engine。 这对我来说似乎有点多余，因为所有与Docker Engine的交互都是通过命令行进行的，那么为什么要浪费服务器资源来显示桌面？ 使用容器时，我们当然应该一直使用Windows Server 2016 Core吗？

I’ve looked around for a complete guide to configuring and running containers on a Core installation but couldn’t find one so I’ve written this guide to go through the steps.

我到处寻找了关于在Core安装上配置和运行容器的完整指南，但是找不到一个完整的指南，因此我编写了该指南来逐步进行操作。

初始服务器配置 (Initial Server Configuration)

The first steps to perform once Windows Server 2016 Core has been installed are to configure the network settings, rename the server and then join a domain (optional).

安装Windows Server 2016 Core后要执行的第一步是配置网络设置，重命名服务器，然后加入域（可选）。

To set a static IP address you must first identify the ID of the network adapter. To do this run:

要设置静态IP地址，您必须首先标识网络适配器的ID。 要执行此操作：

netsh interface ipv4 show interfaces

netsh接口ipv4显示接口

The ID of the Ethernet network adapter shown above is 3. Using that we can then run the following to configure it:

上面显示的以太网网络适配器的ID为3。使用它，我们可以运行以下命令对其进行配置：

netsh interface ipv4 set address name=”3” source=static

address=xx.xx.xx.xx mask=255.255.255.0 gateway=xx.xx.xx.xx

netsh接口ipv4设置地址名称=“ 3 ”源=静态

地址= xx.xx.xx.xx 掩码= 255.255.255.0网关= xx.xx.xx.xx

This will set a static IP address, network mask and default gateway.

这将设置一个静态IP地址，网络掩码和默认网关。

The next steps are optional but I always run through them when working with a new server. First thing is to rename the server so run the following to find out the current assigned server name:

后续步骤是可选的，但在使用新服务器时，我始终会遍历所有步骤。 第一件事是重命名服务器，因此运行以下命令找出当前分配的服务器名称：

hostname

主机名

Once we have the current name we can rename the server by running:

获得当前名称后，可以通过运行以下命令来重命名服务器：

netdom renamecomputer CURRENTNAME /NewName:NEWSERVERNAME

netdom重命名计算机 CURRENTNAME / NewName： NEWSERVERNAME

Then restart the server:

然后重新启动服务器：

shutdown /r /t 0

关机/ r / t 0

Once the server has restarted it can then be joined to a domain. To do this run:

服务器重新启动后，便可以加入域。 要执行此操作：

Netdom join SERVERNAME /domain:DOMAINNAME /userd:USERNAME /password:*

Netdom加入 SERVERNAME / domain： DOMAINNAME / userd： USERNAME / password： *

Enter in the fully qualified domain and a user’s details that has domain admin rights. Once that’s executed the server needs to be restarted once again.

输入完全限定的域和具有域管理员权限的用户详细信息。 执行完该服务器后，需要再次重新启动。

安装Docker引擎 (Installing the Docker Engine)

Now that the server has been configured we can install the docker engine. I’m no longer going to be running scripts in a console session but you can continue like that if you want to but what I’m going to do is switch to a remote powershell session. If you do not know how to set that up, full details on how to do so can be found .

现在已经配置了服务器，我们可以安装docker引擎了。 我不再打算在控制台会话中运行脚本，但是如果愿意，您可以像这样继续，但是我要做的是切换到远程Powershell会话。 如果您不知道如何设置，可以在找到有关如何设置的完整详细信息。

To install the Docker engine the following two powershell scripts must be run. Firstly, we need to install the NuGet package provider:

要安装Docker引擎，必须运行以下两个powershell脚本。 首先，我们需要安装NuGet软件包提供程序：

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 –Force

Install-PackageProvider-名称NuGet -MinimumVersion 2.8.5.201 –强制

Now install the DockerMsftProvider module which is a powershell module for discovering, installing and updating Docker:

现在安装DockerMsftProvider模块，该模块是用于发现，安装和更新Docker的powershell模块：

Install-Module -Name DockerMsftProvider –Force

安装模块-名称DockerMsftProvider-强制

Then we can install Docker:

然后我们可以安装Docker：

Install-Package -Name docker -ProviderName DockerMsftProvider –Force

安装软件包-名称docker -ProviderName DockerMsftProvider-强制

And finally restart the server:

最后重启服务器：

Restart-Computer -Force

重新启动计算机-强制

Once the server has restarted we can verify that the Docker Engine is running by executing:

服务器重新启动后，我们可以通过执行以下命令来验证Docker Engine是否正在运行：

get-service docker

获取服务泊坞窗

Now that we know the service is running we can also verify that the Engine is responding to requests by running:

现在我们知道服务正在运行，我们还可以通过运行以下命令来验证引擎是否正在响应请求：

docker version

码头工人版本

拉出初始图像 (Pulling an initial image)

We’ve configured our server and installed the Docker Engine. Now we can pull an image from the Docker Hub to use to build containers. To search for all Microsoft SQL Server images available that we can use run:

我们已经配置了服务器并安装了Docker Engine。 现在，我们可以从Docker Hub中提取映像以用于构建容器。 要搜索我们可以使用的所有可用的Microsoft SQL Server映像，请运行：

docker search microsoft/mssql-server-windows

docker搜索microsoft / mssql-server-windows

The image that we want is highlighted above. It is the vNext Enterprise Evaluation edition of SQL Server.

我们想要的图像在上方突出显示。 它是SQL Server的vNext企业评估版。

To download the image to our local Docker repository run:

要将映像下载到我们的本地Docker存储库，请运行：

docker pull microsoft/mssql-server-windows

docker pull microsoft / mssql-server-windows

Once that’s complete we can verify that the image is in our local repository ready to be used:

完成此操作后，我们可以验证该图像是否在我们的本地存储库中可供使用：

docker images

码头工人图像

运行我们的第一个容器 (Running our First Container)

We can now run our first container. The command to do this is:

现在，我们可以运行第一个容器。 执行此操作的命令是：

docker run -d -p 15777:1433 –env ACCEPT_EULA=Y sa_password=Testing11@@ –name myfirstcontainer microsoft/mssql-server-windows

docker run -d -p 15777：1433 –env ACCEPT_EULA = Y sa_password = Testing11 @@ –name myfirstcontainer microsoft / mssql-server-windows

The run command tells the Docker Engine to build and start a new container, the -d switch tells the engine to run the container in the background so that we can continue to use this shell window. Then we map the host’s port 15777 to the container’s 1433 port which will allow us to remotely connect to the instance of SQL in the container.

run命令告诉Docker引擎构建并启动一个新容器， -d开关告诉引擎在后台运行该容器，以便我们可以继续使用此shell窗口。 然后，我们将主机的端口15777映射到容器的1433端口，这将使我们能够远程连接到容器中SQL实例。

The following switch ACCEPT_EULA=Y accepts SQL Server’s end user licence agreement, if this isn’t specified the container will not run. We then change the sa password, give the container a name and finally tell the Docker Engine which image to build the container from.

以下开关ACCEPT_EULA = Y接受SQL Server的最终用户许可协议，如果未指定，则容器将不会运行。 然后，我们更改sa密码，为容器命名，最后告诉Docker引擎从哪个镜像构建容器。

Once that’s done we can verify that the container is running:

完成后，我们可以验证容器是否正在运行：

docker ps

码头工人ps

And we can connect remotely to the container in the same way that we would connect to a named instance:

而且我们可以以与连接命名实例相同的方式远程连接到容器：

建立自定义图像 (Building a Custom Image)

Creating an empty SQL container in a short space of time is very useful but there is more to containers. By using DockerFiles we can run commands when we create containers to add databases into SQL Server. We can then save that container as a custom image so that we can build more, identical, containers from that image all of which will contain the databases that we originally added in.

在短时间内创建一个空SQL容器非常有用，但是容器还有更多的用途。 通过使用DockerFiles，我们可以在创建容器以将数据库添加到SQL Server时运行命令。 然后，我们可以将该容器另存为自定义映像，以便可以从该映像中构建更多相同的容器，所有这些容器都将包含我们最初添加的数据库。

To do this we need to create a directory to host our dockerfile and the database files:

为此，我们需要创建一个目录来托管我们的dockerfile和数据库文件：

cd c:\

mkdir docker\mycustomimage cd docker\mycustomimage

CDC：\

mkdir docker \ mycustomimage cd docker \ mycustomimage

Then we can create our dockerfile:

然后我们可以创建我们的dockerfile：

new-item dockerfile -type file

新项目dockerfile -type文件

To edit the content within the dockerfile run:

要编辑dockerfile中的内容，请运行：

set-content dockerfile

设置内容dockerfile

Enter the following on each line:

在每行上输入以下内容：

 FROM microsoft/mssql-server-windowsRUN powershell -Command (mkdir C:\\SQLServer)COPY DatabaseA.mdf C:\\SQLServerCOPY DatabaseA_log.ldf C:\\SQLServerENV sa_password=Testing11@@ENV ACCEPT_EULA=YENV attach_dbs="[{'dbName':'DatabaseA','dbFiles':['C:\\SQLServer\\DatabaseA.mdf','C:\\SQLServer\\DatabaseA_log.ldf']}]" 

I’ll go through each line to detail what the command is telling the Docker Engine to do:

我将逐行详细说明该命令告诉Docker Engine要做的事情：

   FROM microsoft/mssql-server-windows 

This line tells the Docker Engine which image to build the container from

该行告诉Docker引擎从哪个镜像构建容器

   RUN powershell -Command (mkdir C:\\SQLServer) 

This will create a C:\SQLServer directory within our container

这将在我们的容器中创建一个C：\ SQLServer目录

   COPY DatabaseA.mdf C:\\SQLServer  COPY DatabaseA_log.ldf C:\\SQLServer 

These lines copy the database files from the host into the directory we created in the container

这些行将数据库文件从主机复制到我们在容器中创建的目录中

   ENV sa_password=Testing11@@  ENV ACCEPT_EULA=Y 

We need to accept the end user agreement and specify the sa password

我们需要接受最终用户协议并指定sa密码

   ENV attach_dbs="[{'dbName':'DatabaseA','dbFiles' ['C:\\SQLServer\\DatabaseA.mdf','C:\\SQLServer\\DatabaseA_log.ldf']}]" 

Finally we tell the Engine to attach the database to the SQL instance in the container

最后，我们告诉引擎将数据库附加到容器中SQL实例

Once you’ve entered all the lines above hit enter twice to set the content and then verify by running:

输入以上所有行后，请单击两次以设置内容，然后运行以下命令进行验证：

docker get-content dockerfile

泊坞窗获取内容泊坞窗文件

We now need to copy over our database files to the server. To allow us to transfer our database files, run:

现在，我们需要将数据库文件复制到服务器上。 要允许我们传输数据库文件，请运行：

netsh advfirewall firewall set rule group=”File and Printer Sharing” new enable=Yes

netsh advfirewall防火墙设置规则组=“文件和打印机共享”新启用=是

I created a database on my local instance of SQL Server, stopped that instance and copied the files to the server. Once the files have been transferred your mycustomimage folder should look like this:

我在SQL Server的本地实例上创建了一个数据库，停止了该实例并将文件复制到服务器上。 传输文件后，您的mycustomimage文件夹应如下所示：

Now we can build our custom image:

现在我们可以构建我们的自定义图像：

docker build -t mycustomimage .

docker build -t mycustomimage。

The . is important as it tells the Docker Engine to look in the current location for a dockerfile to process its commands.

的。 这一点很重要，因为它告诉Docker引擎在当前位置查找dockerfile来处理其命令。

The output of the Docker Engine shows that it is running each command in sequence. It builds intermediate containers at each step and then cleans up after itself. Once complete we can verify our new custom image:

Docker Engine的输出显示它正在按顺序运行每个命令。 它在每个步骤都构建中间容器，然后对其进行清理。 完成后，我们可以验证新的自定义图片：

Let’s build a container from our new image. So, run:

让我们根据新图像构建一个容器。 因此，运行：

docker run -d -p 15789:1433 –name mycustomcontainer mycustomimage

docker run -d -p 15789：1433 –命名为mycustomcontainer mycustomimage

N.B. – Notice that we didn’t have to specify that we accepted the end user agreement or set the sa password. This was already done in the DockerFile so we won’t have to do it again for any containers that we create from our custom image.

注意：请注意，我们不必指定我们接受最终用户协议或设置sa密码。 这已经在DockerFile中完成，因此我们不必再对根据自定义映像创建的任何容器进行此操作。

Verify that the container has been built:

验证容器已构建：

docker ps

码头工人ps

Remotely connect to confirm that our database is available in the SQL instance via SSMS using the server hostname and port number that we specified on container creation:

使用我们在容器创建时指定的服务器主机名和端口号，通过SSMS进行远程连接以确认我们的数据库在SQL实例中是否可用：

This is where containers show how powerful they are. We can spin up a container running a SQL instance that is configured exactly how we want it to be in seconds.

这是容器显示其功能强大的地方。 我们可以启动一个容器，该容器运行一个SQL实例，该实例的配置完全按照我们希望的方式在几秒钟内完成。

I hope that this has been informative and that you can now use this information to assess the technology to determine whether it can be of use in your day to day work.

我希望这能提供很多信息，并且您现在可以使用此信息来评估该技术，以确定它是否可以在您的日常工作中使用。

资源资源 (Resources)

翻译自: