How to use Windows hosted file shares to support SQL Server containers

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.

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 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.

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.

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.

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.

Select “NFS Share – Advanced.”

For Authentication, select “Enable unmapped user access, by 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.

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.

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.

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.

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.

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.

Paul Stanton

Paul is a co-founder of WinDocks, and is focused on delivering the best container support for the SQL Server Professional community. He is a former Director at Microsoft, and was involved in early internetworking and cross platform solutions for Windows NT.

He loves to write and is a frequent contributor to technical web sites. He has recently expanded his focus to enable SQL Server containers as part of a Continuous Integration and DevOps process.

View all posts by Paul Stanton

In-container and Mounted Databases:

NFS for Secure File Sharing of Production Data Environments

Workgroup File Sharing Support

Conclusions:

References:

Related posts: