In this article, we will install the SQL Server Express edition on Windows Server 2016 using a SQL Server Docker container.
Introduction
The virtual machine uses the virtualization technique to virtualize the physical hardware. Suppose we install the Oracle VirtualBox in a physical server. We can configure multiple virtual machines with their operating system, application, software, databases system, networking components. As shown below, we have created two virtual machines. These virtual machines are completely separated from each other. In my earlier articles on SQL Server Always On Availability Group, we configured the AG between the different virtual machines.
In the below image, we see a docker container. In a virtual machine, we install the operating system, but the containers virtualize the host operating system. It also reduces administrative efforts to manage multiple operating systems.
We can use the containers on both On-premises and cloud platforms for multiple applications and different languages.
Image reference: Microsoft docs
You can refer to this article to understand the difference between Virtual machines and Containers.
We can install SQL Server on both virtual machines and containers. We require to install separate SQL Server on each virtual machine, its dependencies such as .Net frameworks. A container makes the task easy for us to quickly create multiple instances of SQL Servers and query databases.
In this article, we will configure the SQL Server container on the Windows Server 2016.
Installing Docker services on Windows server 2016
Usually, docker and container terms are for the Linux operating system. Most of the time SQL database professionals do not familiarize themselves with these technologies due to different operating systems. Windows Server 2016 has in-built support for containers. To use the containers, we use the following steps in Windows Server 2016 server.
- Step 1: Enable Container feature
- Step 2: Install the Docker-Microsoft PackageManagement provider (DockerMsftProvider)
- Step 3: Install the docker package
Let’s start with the docker configuration for the Windows Server 2016.
Step 1: Enable Container feature
Take RDP of the Windows Server 2016 server and navigate to Server Manager-> Add roles and features.
In this wizard, select the installation type – Role-based or feature-based installation.
Put a tick on the Containers feature, as shown below.
To enable the Container feature, we require to restart the virtual machine. You can do it manually or put a tick on the Restart the destination server automatically if required.
It automatically restarts the server and configures the Container in Windows Server 2016.
Alternatively, you can use the below PowerShell command to install the Container feature.
|
1 |
Install-WindowsFeature –Name Containers |
Step 2: Install the Docker-Microsoft PackageManagement provider (DockerMsftProvider)
In the second step, launch the administrative Windows PowerShell to install the Docker-Microsoft Management provider for Windows server 2016.
|
1 |
Install-Module -Name DockerMsftProvider -Force |
Specify Yes to install the NuGet provider.
Step 3: Install the docker package
In this step, we install the PackageManagement PowerShell module in the Windows PowerShell.
|
1 |
Install-Package -Name docker -ProviderName DockerMsftProvider -Force |
Now, open the services and start the Docker Engine service either manually or using the below PowerShell command.
|
1 |
get-service | where-object{$_.name -eq "docker"} | start-service |
The Docker Engine service is in the running status.
Verifying Docker Installation
We can use the docker command to gets its argument, commands and their descriptions.
We use the docker version command to check the docker engine and client version.
Install SQL Server Express edition using SQL Server docker for Windows Server 2016
The docker service must be running to install SQL Server Express. In a traditional environment, we use the installation media and go through the various installation steps. Similarly, for a SQL container, we need an image. We can download the image from the docker hub repository. It is an open-source medium to upload and download the images.
To search for the SQL Server docker images, use the following docker search command.
|
1 |
docker search microsoft | select-string sql |
In this article, we want to install the SQL Server Express edition. To download the repository, we need an active internet connection. It uses the docker pull command for this purpose.
|
1 |
docker pull Microsoft/mssql-server-windows-Express |
It starts downloading the SQL Server docker container images for SQL Server Express edition from the specified repository.
It extracts the SQL Server image after the download completes.
To check the existing downloaded docker images, we can use the docker images command. As shown below, we have mssql-server-windows-express in our local repository.
Now, comes the best part for a SQL Server docker container. Suppose you have to need to prepare multiple SQL database environment for your code testing. Let’s name the environment as development, unit test. In the VM, we need to go through the installation wizard and configure the SQL Server. It can be a complicated task for you, especially if you need it for testing purposes only. With the different environment, you might also complain sometimes- the code was working fine on the dev server.
SQL Server docker container solves the issue. To install SQL Server using a container, run the following command.
|
1 2 |
docker run --name sqlshackdemo -d -p 1433:1433 -e sa_password=India@123 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-express |
Here, we have used the following arguments for the docker run command:
- Name: It is a name for your container. It is good to specify a customized name to identify the container instead of a randomly generated name
- d: SQL Server runs as a service. Therefore, we need to use this option
- p: Use this argument to map the TCP port on the host environment and the container. We specify the port in the format such as -p 1433:1433. Here, the first 1433 port is for the container and the second TCP is for the host port
- sa_password: Specify the password for the system admin account in SQL Server
- ACCPET_EULA: This argument is to accept the end-user license agreements
Run the docker run command specified above and it returns the container ID as shown below:
You can run the docker ps -a command to check the docker containers in Windows Server 2016. As shown here, we have a SQL Server Express edition. It quickly configures SQL Server for you.
To connect to the SQL Server docker instance, we need to IP address of the container. We can use the docker inspect command for this requirement.
|
1 |
docker inspect -f "{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}" sqlshackdemo |
It returns the IP address, as shown below. For my [sqlshackdemo] container, the IP address is 170.30.67.159.
Install SQL Server Management Studio and access the SQL Server container using the IP address, SA authentication.
As shown below, it installed the Microsoft SQL Server Express edition for my Windows container.
Now, suppose you need to install another SQL instance. It is again a fraction of second work. Specify the argument value in the docker run command, and your SQL instance is ready.
|
1 |
docker run --name sqlshackdemo_2nd -d -p 1433:1433 -e sa_password=India@123 -e ACCEPT_EULA=Y microsoft/mssql-server-windows-express |
We get an error this time because of the port number. It is not able to create the endpoint on port 1433 because we already used it for [sqlshackdemo] container.
To use the existing docker container name, we need to remove it.
>docker run sqlshackdemo_2nd
Change the SQL Server docker container port and run the docker run command, as shown below.
Check the IP address and connect to the SQL Server instance in SSMS.
You cannot stop the SQL Server in Docker using the SQL Server Configuration Manager. You can use the docker stop command for this purpose.
Conclusion
In this article, we installed the SQL Server Express edition on Windows Server 2016 using the SQL Server docker container. Docker container is an excellent way to quickly deploy multiple SQL instances and use them for the work. I would recommend you explore it.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023