Microsoft announced in the autumn of 2014 that it plans to increase support for Docker containers in Windows Server 2016. The statement further underscores Microsoft's growing concern about Linux and Open-source tools. In the next few months,. NET Core opened the source code, Microsoft and Red Hat formed an alliance, and announced plans to migrate SQL Server to Linux. The Windows Community's support for containers is in the offing, so let's explore the use of SQL Server containers in depth.
The concept of container and virtual machine containers has been around for decades (remember BSD jails was roughly released in 2000), but it is now pouring into the mainstream of the adoption of Docker open source projects. The Docker design includes an elegant application packaging approach and a server-side API for managing containers. The core of the design is to solve the "hell-dependent" application package by embedding the software in a container mirror. By using the Docker container, developers can ensure that applications developed on notebooks can run on a shared test server or public cloud.
Just as the Win32 API controls desktop applications, Docker is rapidly becoming the de facto container packaging industry standard API. Microsoft is implementing a subset of the Docker API for Windows Server 2016 to participate in this standardization process through the Open Container Initiative (Container Initiative). Users can benefit from Docker support for the industry, including AWS, Azure hosting services, cloud services, and third-party monitoring, logging, clustering, and other management tools.
The container has the advantages of high speed, light weight and increased resource utilization. The container can be started in just a few seconds, often with only a small incremental load on the host. It is also common to run a large number of containers on a shared development, test server. Virtual machines, on the other hand, take a few minutes to start, and only a lower (10-15%) resource utilization. The prevalence of container development and testing is due to its high speed and flexibility in creating environments, as well as a noticeable reduction in the need for virtual machines to be used (or maintained).
Container mirroring and Warehouse containers provide user and process isolation to protect applications from other containers for undesirable operations. NET and SQL Server containers also provide valid namespace isolation. The industry is extremely concerned about enhancing the safety of containers, and the main cloud provider (Azure, AWS) wraps containers in virtual machines to ensure their safe use. Containers also have methods to manage resource usage, such as limiting CPU, disk, and network use.
The container is built by Docerfile. Docerfile is a text file that contains a series of build container commands. Dockerfile starts with a base mirror and adds code and data to the container. The container can be saved and reused as a "mirror". Mirroring can be saved in a shared warehouse.
relational database containers and SQL Servermysql and Postgres belong to the most popular container mirrors in the Docker hub. Database containers are used to build the same, isolated, sandbox environment for development and testing. Data can be placed in a container or mounted (mount). The SQL Server container is an instance of SQL Server based on the Microsoft Shared DLL architecture, which provides support for running multiple instances of SQL Server on the same host over the past decade. The container provides additional process and user isolation and SQL Server configuration that users can access through SQL Management Studio or other tools. Once the build is complete, the container can be saved as a mirror, using mirroring to help the team build multiple identical instances on a shared host. Container-specific advantages include:
Speed: SQL Server containers can provide services in seconds and provide effective support for development and testers who frequently need to deactivate and quickly replace instances.
Efficient: Multiple SQL Server containers can be efficiently run on the same shared host and support the consolidation of multiple virtual machines for a single shared virtual machine for development test teams, which reduces the cost of licensing (license) and maintenance accordingly.
Continuous integration (CI), continuous delivery (CD) Support: SQL Server containers provide support for automatically increasing mount databases, build-time run configuration scripts, and automated build support for continuous integration and continuous publishing processes.
High Availability (HA), disaster preparedness (DR) Support: SQL Server containers Support Integrated management of database replicas, snapshots, mirrored third-party systems, and a significant amount of work to demonstrate the effectiveness of a range of operations. This is described in detail below.
Microsoft is ready to implement Windows Server support for Docker in the second half of 2016. Microsoft is designed to support the Windows Server 2016 "kernel" or no output interface of the Nano server. Interestingly, Microsoft is silent. Windows Server 2016 supports SQL Server containers, and the first SQL Server container demonstrated this spring is also running on a future Linux portable version of SQL Server! This means that SQL The server may prefer to support Linux hosts rather than Windows containers. This is understandable, after all, Microsoft has chosen to implement the Linux-style container and application configuration.
Microsoft's Windows Server 2016 container is designed to be a shell container that is compatible with. NET, Windows console applications, and services. However, SQL Server and other Windows applications are not suitable for management in the shell. SQL Server containers on Windows Server 2016 will need to use SQL Management Studio or other tools to run scripts or to add and Mount databases. In simple terms, these manual operations are required by SQL Server containers to hinder the speed and automation provided by the container.
Fortunately, developers who are interested in porting SQL Server containers based on Docker open source projects can find implementations outside of Microsoft. Windocks is a start-up organized by a former Microsoft engineer who has released a portable version of Docker Open source projects for Windows 8, Windows 10, Windows Server 2012, and Windows Server 2016. Windocks supports all versions of SQL Server 2008 (and R2), 2012, 2014, and 2016. Windocks support for Windows Add containers, allowing users to use SQL Server licenses in containers. This version is available for free, based on the Windocks community version.
Data persistence and SQL Server container
SQL Server containers and instances of SQL Server do not differ in use, so you need to select local or mounted databases. The Windocks SQL Server container supports the use of the ADDDB command to replicate a database to a container, while the MOUNTDB command supports mounting a local or network database.
ADDDB Copy the database (Primary, secondary, and log files) into the container, append when the container is started, and split when the container is stopped. SQL Server containers provide namespace isolation, so multiple identical instances can run in containers on the same host without regard to naming and user conflicts.
Once the creation is complete, the container and its appended database can be saved as a new mirror, and the developer and tester can then easily generate the instance based on the requirements. The container can be instantiated in a few seconds, depending on the size and number of databases. This is a common way to support development and testing, and some users can have good performance by adding 30 or more databases to the container.
The "in-container" data is the same as the data that is added locally to the instance. The container owns the data and persists them in the container's file system, and is affected by the container downtime. Databases can be accessed through SQL Server Management Studio or other standard tools.
The following is an example of a dockerfile use of adddb, consisting of two named databases containing a primary database file and multiple secondary database files:
Adddb dbname primarydb.mdf secondarydb.ndf secondarydb.ndf
ADDDB dbname2 primarydb.mdf secondarydb.ndf
ADDDB copies the database to the container, MOUNTDB supports mounting a local or network database. Windocks supports mount points that are independently generated by the container. In this respect, windocks reflects the direction of future Docker design.
Use MOUNTDB to have the database mount and append when the container is started, and uninstall and Peel when the container stops. Only one mount is supported per database. Unlike containers that use ADDDB, the mounted database is not placed in a SQL Server container mirror. To support mounting on more than one container, the database must establish a copy and create or clone multiple mount points.
The following is an example of MOUNTDB using local and network databases:
Mountdb dbname C:\path\Primarydb.mdf
Mountdb dbname2 \networkpath\dbname2.mdf \networkpath\dbname2.ndf
Container The following example describes the process of building a SQL Server container using ADDDB:
First step: Open a new command line window
Step Two: Enter the >docker build C:\Windocks\samples\Test1 windocks The client returns a string containing the container ID, port, and SQL Server sa password.
Step three: >docker start, where containerid can use only a portion of the container ID.
The Docker command and return output are shown in the following figure. Each container is a fully isolated instance of SQL Server and has namespace isolation that can be accessed through SQL Server Management Studio. In this example, the database AdventureWorks is copied into the container and appended when the container is started.
The schema and design of the database can be updated, and a new SQL Server image is created after the container is stopped. The entire team can use this SQL Server image on a shared windocks host.
This process shows the popularity of quickly sharing instances of SQL Server in the same standalone container. A SQL Server change script can be exported as needed to update the source database.
Container and database clones are useful for database cloning when working with large databases, and it supports publishing multiple containers of cloned databases for development or testing (some clone databases are still writable). Windocks is an extensible system that supports the management process involved in creating snapshots and cloning processes.
Support for increased snapshots and clones is provided by the Administrator, who can choose to enable the Privilege command in the Windocks node.cfg file. In the example below we use a simple "copy" command to show the process.
Dockerfile can define a SQL Server container that contains the cloned database that is mounted. Note the use of environment variables in the container.
Earlier this year windocks and NetApp explored the use of containers to integrate NetApp's snapclone process (SDCLI.exe). The results were satisfactory, and only one step was deployed in 50 seconds to mount the SQL Server container that mounted the 750G clone database!
SQL Server container support for legacy projects
Users ' interest in Windows containers is rising rapidly, and a survey of Windocks community downloads shows an astonishing range of uses, most popular for development and testing, but there are many things to support legacy projects such as SAP and Microsoft Dynamics. In these cases, the SQL Server backend is containerized and the associated support environment is simplified.
Integrating SQL Server and. NET containers and existing schemas
Luckily, the container is right. NET application provides the perfect support, and integrating SQL Server and. NET container environments is straightforward. Asp. NET schemas define the use of XML files and Web.config to define configuration and link strings between. NET applications and SQL Server containers.
Here we use the example in the \windocks\samples\testdotnet directory in Windocks. NET application to give an example. Use Notepad to open web.config, configure the connection string to point to the host address, port, and SQL authentication information for the SQL Server container.
Use the running SQL Server container information to modify the host name (if required), the port, and the SQL Server sa password. Save and close the file (check, do not save as a. txt file). and run:
Open the browser, access the host address and port to view the integrated application.
There is another way to integrate the container and the DNS naming service. This method uses the Windows Netsh tool to dynamically assign the container to a known IP address at build time.
Conclusion The SQL Server container uses the shared DLL architecture validated by Microsoft to provide hands-on support for instances of SQL Server on a shared host. The popular use cases are in the development and testing sessions, but there are parts that are used to test legacy projects (primarily SAP and Microsoft Dynamics) using a containerized SQL Server backend. The reduction in the number of virtual machine requirements varies, but the average user report has a 3-5x reduction, while significantly reducing the overhead of license and host maintenance. Because updates are automatically propagated when the container is refreshed, the maintenance of the host is simplified.
The examples mentioned here focus on the database engine, and there is still more work to be done to explore the container's support for SSIS and other SQL Server services.
Other promising usage scenarios that are being explored include migration workloads, high availability, and disaster recovery. The speed and portability of containers make these more feasible, practical, and easier to test. Additional time and testing can also help answer whether the SQL Server container is ready to run in a production environment.
Given Microsoft's lack of a clear plan, SQL Server containers are unlikely to receive practical support in Windows Server 2016. Since Windows Server 2016 is not supported, the ideal container support will be the Linux porting version of future SQL Server, and we look forward to the progress in 2017.
