如何连接sql托管实例_如何使用Windows托管的文件共享来支持SQL Server容器

如何连接sql托管实例

In a previous article, Automate Delivery of SQL Server Production Data Environments Using Containers, we introduced SQL Server containers for delivery of production data environments to development and QA teams. In this article we look at the methods used for working with SQL Server data, and use of file shares to support delivery of production databases with containers.

在上一篇文章“ 使用容器自动交付SQL Server生产数据环境”中，我们介绍了用于将生产数据环境交付给开发和质量保证团队SQL Server容器。在本文中，我们将研究用于处理SQL Server数据的方法，以及使用文件共享来支持带有容器的生产数据库的交付。

容器内和挂载数据库： (In-container and Mounted Databases:)

Containers are SQL Server named instances configured and delivered with data in seconds. Each member of a team works with an isolated SQL Server container on a shared host. Containers can be discarded and replaced as needed. Rather than working with a score of VMs teams work with containers on a single host. The result is automated support for delivery of SQL Server environments for dev and QA, and a dramatic reduction in the number of VMs used, with associated VM maintenance and server license savings.

容器是配置好SQL Server命名实例，并在几秒钟内随数据一起交付。团队的每个成员都使用共享主机上的隔离SQL Server容器。容器可以根据需要丢弃并更换。与使用大量VM相比，团队不会在单个主机上使用容器。结果是自动支持为开发人员和质量保证人员交付SQL Server环境，并显着减少了使用的VM数量，并实现了相关的VM维护和服务器许可证节省。

SQL Server containers include a private file system. The first option for working with SQL Server databases is to copy the database files into the container file system. This is a popular approach for Dev/QA support of environments up to 300 to 400 GB. This method is effectively limited to mid-sized environments, due to the time and network bandwidth involved in the over the network copy of the files. Once the files are on the host, container creation speed is dictated by host IO performance. This method works well for many dev and QA teams. One downside is that the database is lost when the container is deleted.

SQL Server容器包括一个专用文件系统。使用SQL Server数据库的第一个选项是将数据库文件复制到容器文件系统中。这是支持300至400 GB环境的Dev / QA的流行方法。由于文件的网络副本中涉及时间和网络带宽，因此该方法实际上仅限于中型环境。一旦文件位于主机上，容器的创建速度就取决于主机IO的性能。这种方法对许多开发人员和质量检查团队都适用。缺点之一是删除容器后数据库丢失。

An alternative is to mount databases to the SQL Server container. Databases can be hosted on a File Server, Storage Array Network (SAN) array, or other storage system. Each container requires a dedicated file or mount point, so snapshots, copies, or clones are required for each container. Many storage systems can create writeable snapshots in seconds, which enables a 1 TB database to be delivered mounted to a container in seconds. This method is preferred for working with large data environments, as it avoids over-the-network file copies. And, the database persists beyond the container, so is preferred for production environments and whenever data persistence is needed.

一种替代方法是将数据库安装到SQL Server容器。数据库可以托管在文件服务器，存储阵列网络（SAN）阵列或其他存储系统上。每个容器都需要一个专用文件或安装点，因此每个容器都需要快照，副本或克隆。许多存储系统都可以在几秒钟内创建可写快照，从而可以在几秒钟内将1 TB数据库安装到容器中。此方法首选用于大型数据环境，因为它避免了网络文件复制。而且，数据库可持久存储在容器之外，因此对于生产环境和需要数据持久性的情况是首选的。

如何连接sql托管实例_如何使用Windows托管的文件共享来支持SQL Server容器

NFS用于生产数据环境的安全文件共享 (NFS for Secure File Sharing of Production Data Environments)

There are many different file servers and NAS storage solutions. One approach that works well is NFS file sharing between Windows Servers. This approach enables secure support for container creation and mounting of databases to a known container host. In the following step by step instructions are for two Windows Server 2012 hosts, on a shared domain.

有许多不同的文件服务器和NAS存储解决方案。一种有效的方法是Windows Server之间共享NFS文件。这种方法可以为容器创建和将数据库安装到已知容器主机提供安全支持。在下面的分步说明中，介绍了共享域上的两个Windows Server 2012主机。

The following assumes that the two servers have been enabled with NFS services. Start by opening Windows Server Manager and select the file server host, File and Storage Services, Shares, and use the Task dropdown to select a “New Share,” as highlighted below.

以下假定两个服务器已启用NFS服务。首先打开Windows Server Manager，然后选择文件服务器主机，“文件和存储服务，共享”，然后使用“任务”下拉列表选择“新共享”，如下所示。

Select “NFS Share – Advanced.”

选择“ NFS共享–高级”。

For Authentication, select “Enable unmapped user access, by UID/GID”.

对于身份验证，选择“通过UID / GID启用未映射的用户访问”。

Enter the container host that will be provided access to the file share. Note the permissions!

输入将为文件共享提供访问权限的容器主机。注意权限！

Review permissions and choose either Full Control, or Read access as needed.

查看权限，然后根据需要选择“完全控制”或“读取”访问权限。

The Share setup also allows for varied types and files.

共享设置还允许使用各种类型和文件。

Select “Create” and see the confirmation of the Share setup.

选择“创建”，然后查看共享设置的确认。

Open File Explorer and navigate to the share created, right-click and select Properties. Select the “Share” tab, select “share” and enable permissions for Everyone. Remember to click “Apply.”

打开文件资源管理器并导航到创建的共享，右键单击并选择属性。选择“共享”选项卡，选择“共享”，然后为所有人启用权限。记住单击“应用”。

On the same share select the “Security” tab, and confirm appropriate user rights on the files. No changes should be needed.

在同一共享上，选择“安全性”选项卡，然后确认文件的相应用户权限。无需更改。

In a final step, select the “NFS Sharing” tab, which summarizes the share setup. The only machine provided access to the new Share is the Support1 machine users. We now have a secure method for supporting File Shares for a SQL Server container host. This method supports the use of the MOUNTDB command. Be sure to consider network performance. A dedicated high speed LAN is a recommended.

最后，选择“ NFS共享”选项卡，其中概述了共享设置。提供对新共享的访问权限的唯一计算机是Support1计算机用户。现在，我们有了一种安全的方法来支持SQL Server容器主机的文件共享。此方法支持使用MOUNTDB命令。请务必考虑网络性能。建议使用专用的高速局域网。

工作组文件共享支持 (Workgroup File Sharing Support)

While the solution outlined above is preferred, an alternative approach supports workgroup networks. The following configures a shared folder on a private network with Anonymous access. Select folder properties, Sharing tab and click on Advanced Sharing. Select “Share this folder, and click on Permissions.

尽管上面概述的解决方案是首选，但另一种方法则支持工作组网络。以下内容在具有匿名访问权限的专用网络上配置共享文件夹。选择文件夹属性，共享选项卡，然后单击高级共享。选择“共享此文件夹，然后单击权限”。

Open Group Policy Editor (Windows key +R, type gpedit.msc and hit enter). Navigate to Computer Configuration — Windows Settings — Security Options.

打开组策略编辑器（Windows键+ R，键入gpedit.msc，然后按Enter键）。导航到“计算机配置”“ Windows设置”“安全性选项”。

Make the following changes:

进行以下更改：

Enable “Accounts: Guest account status”
启用“帐户：访客帐户状态”
Enable “Network access: Let Everyone permissions apply to anonymous users”
启用“网络访问：让所有人权限都适用于匿名用户”
Disable “Network access: Restrict anonymous access to Named Pipes and Shares”
禁用“网络访问：限制匿名访问命名管道和共享”
As a final step, enter the name of the share you created in “Network access: Shares that can be accessed anonymously”
最后一步，输入在“网络访问：可以匿名访问的共享”中创建的共享的名称。

Now that the Group Policies are updated, open File Explorer, navigate to the shared folder, and select the Sharing tab, select Advanced Sharing, and click on “Share this folder.”

现在，组策略已更新，打开文件资源管理器，导航到共享文件夹，然后选择共享选项卡，选择高级共享，然后单击“共享此文件夹”。

Everyone should be listed, add Guest and ANONYMOUS LOGON and assign Read or Full Access.

每个人都应列出，添加来宾和匿名登录并分配读取或完全访问权限。

Once completed, the \\<MachineName>\Share will support the anonymous access to files that can be mounted to SQL Server containers.

完成后， \\ <MachineName> \ Share将支持匿名访问可以装入SQL Server容器的文件。

结论： (Conclusions: )

The approaches outlined above, and particularly the NFS share, provides practical methods for delivering mounted SQL Server environments for Dev and QA use. The examples are based on the WinDocks Community Edition, a free downloadable edition from WinDocks. WinDocks is an independent port of Docker’s open source to Windows, supporting all editions of Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. WinDocks also supports all editions of SQL Server 2008 onward. Download your own copy and explore the use of SQL Server containers here. Full disclosure, I am a principal at WinDocks.

上面概述的方法，尤其是NFS共享，提供了实用的方法来交付已安装SQL Server环境以供Dev和QA使用。这些示例基于WinDocks社区版，即WinDocks的免费下载版本。 WinDocks是Docker开放源代码到Windows的独立端口，支持Windows 8，Windows 10，Windows Server 2012和Windows Server 2016的所有版本。WinDocks还支持SQL Server 2008以后的所有版本。下载您自己的副本，并在此处探索SQL Server容器的用法。完全公开，我是WinDocks的负责人。

The same approach should also work with Microsoft’s container implementation that is built into Windows Server 2016, and Pro and Enterprise editions of Windows 10. Microsoft’s plans for SQL Server container support appear to be focused on SQL Server vNext. At the time of this article the only SQL Server images available for Microsoft’s containers are SQL Server 2016 Express and SQL Server vNext.

Windows Server 2016和Windows 10的Pro和Enterprise版本中内置的Microsoft容器实现也应使用相同的方法。MicrosoftSQL Server容器支持计划似乎集中在SQL Server vNext上。在撰写本文时，仅适用于Microsoft容器SQL Server映像是SQL Server 2016 Express和SQL Server vNext。

The NFS based file share supports the mounting of databases to containers, or can be a secure source for files copied for “in-container” use. Below we see the end result of a mounted database. SQL Management Studio shows the mounted database dbtest, and File Explorer view confirms the files are not in the container file system. The container can be stopped, restarted, or deleted, and the database files can be freed for use by another container. For further reading on the use of SQL Server containers, refer to the links below.

基于NFS的文件共享支持将数据库安装到容器，或者可以作为复制文件的安全来源，以供“容器内”使用。下面我们看到一个已装载数据库的最终结果。 SQL Management Studio显示已安装的数据库dbtest，并且“文件资源管理器”视图确认文件不在容器文件系统中。可以停止，重新启动或删除该容器，并且可以释放数据库文件以供另一个容器使用。有关使用SQL Server容器的更多信息，请参考下面的链接。

参考文献： (References: )

翻译自: https://www.sqlshack.com/how-to-use-windows-hosted-file-shares-to-support-sql-server-containers/