Once upon a time
As a developer, I often create various types of databases on the SQL Server platform. They are used in various types of applications. Some time ago I quit installing SQL Server locally and used virtual machines for that. Recently, I also started using the public cloud to host the databases I create.
However, none of the above solutions is convenient because it entails a lot of time overhead. How much more convenient would it be to be able to set up SQL Server with a few lines of the script in less than a few minutes without using too many resources? In this post I will show that it is possible.
Today
This is where Docker comes in and its ability to host containers locally with Docker for Windows. Docker knowledge is not necessary here, but I encourage those who are not yet familiar with this technology to get acquainted. In addition to using it to “set up” SQL Server instances in less than 10 seconds, you can also use it to host .NET applications on Linux.
Requirements for Docker for Windows:
- Microsoft Hyper-V – the installer enables this option automatically during installation, unless it is enabled.
- Virtualization must be enabled in the BIOS and supported by the computer on which the environment is installed. If your hardware supports virtualization, it will most likely be enabled by default.
- The current version of Docker for Windows runs on Windows 10 64bit Pro, Enterprise, and Education editions (build 14393 or earlier).
Docker installation for Windows includes: Docker Engine, Docker CLI Client, Docker Compose, Docker Machine, and Kitematic.
Configuration in the “open environment” should be carried out in the following steps:
- Download Docker for Windows download.docker.com.
- Docker installation for Windows.
- Download SQL Server 2017 container for Linux
- Launching the container
Docker installation for Windows
- Double-click Docker for Windows Installer.exe to run the installer.
- We follow the installation wizard to accept the license, authorize the installation (installation permissions required), and continue with the installation.
- Click the “Finish” button in the dialog box completing the installation to launch Docker. If it does not start automatically, click Windows Start and type “docker” and run the Docker for Windows application.
Docker – Application Search
If a whale icon appears in the status bar, Docker has started and is running and is available from any terminal window.
Docker in the Windows status bar
4. I recommend a minor configuration change – increasing the resources available for Docker. If I remember correctly, the default CPU and memory limit for Docker is set to 1 CPU and 1024 Mb Memory. I increased it to 2 CPUs and 2048Mb Memory. Of course, we must have the appropriate resources – Docker for Windows will not start if the amount of available memory is too small. For me it looks like this:
Attention! Docker can run in two configurations designed for Linux and Windows containers, respectively. Depending on what containers we want to use, this is how we need to set up our Docker for Windows. By default, the configuration for Linux containers is set. To change the type of containers used to Windows, select the “Switch to Windows containers…” option in the Docker menu. The Docker menu is opened by right-clicking on the whale icon in the status bar.
Download and run the container
As mentioned above, after installing Docker for Windows, it is accessible from any terminal window. I use the terminal built into Visual Studio Code, but you can use any such as PowerShell or Bash. To present this mechanism, I will use the container from the mssql-server-linux: 2017-latest image. I present the script code according to the PowerShell syntax. The difference with bash is that you must use the sudo keyword before each script.
Downloading a SQL Server 2017 Linux container image from Docker Hub
docker pull microsoft/mssql-server-linux:2017-latest
The download should not take long because the image takes less than 500Mb.
Launch the docker container from the downloaded image
docker run -e "ACCEPT_EULA=Y"
-e "SA_PASSWORD=Strong!Passw0rd" `
-p 1433:1433 --name sql1 `
-d microsoft/mssql-server-linux:2017-latest
In order to check whether our container has started, we can use the docker ps -a command. It displays all the containers connected to our local environment. In order to check what containers are working, the docker ps command is enough.
docker ps -a
Additional Docker commands that may be useful:
Change sa (sysadmin) password:
docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd `
-S localhost -U SA -P "Strong!Passw0rd" `
-Q "ALTER LOGIN SA WITH PASSWORD='NewStrong!Passw0rd'"
Container removal:
docker kill sql1
docker rm sql1
Container Launch:
docker start <CONTAINER ID>
where CONTAINER ID is the container ID – you can check it with the docker ps -a command
Exemplary use
We will create two containers named sql1 and sql2. I presented the invocation of the scripts in the screenshot below:
Docker – running two mssql containers
You can now connect to these servers using SQL Server Management Studio, for example. The server names are localhost, 14331 and localhost, 14332, respectively. The username is sa and the password is what was given when the container was started. The result of the connection is presented in the following screenshot:
SSMS connection
Podsumowanie
Jak przedstawiłem powyżej tworzenie instancji SQL Server jest bardzo proste i przede wszystkim szybkie. Nie licząc czasu na instalację Docker dla Windows oraz pobranie obrazu kontenera przygotowanie silnika bazy danych zajmie nie więcej niż 10 sekund. Ponadto możemy sobie takich instancji “postawić” kilka w zależności od potrzeb.