This article explores Windows Subsystem for Linux (WSL) to run the Linux operating system on Windows OS and configure SQL Server on Linux.
The Windows Subsystem for Linux (WSL) is a way for developers to run Linux directly on Windows 10 or Windows Server 2019 systems. Microsoft developed a Linux-compatible kernel interface for running Linux such as Ubuntu, RedHat, SUSE. Suppose you have a Windows Server 2019 server and you want to configure a SQL Server on Linux on it. Previously, you need to have a separate virtual machine for running SQL Server on Linux operating systems. Now, you can leverage WSL for this purpose with quick deployment.
- You can download various Linux distributions from the Microsoft store
- You can install additional software using the Linux distribution package managers
- You can use tools such as vim, emacs or tmux on Windows
Introduction to Windows Subsystem for Linux (WSL 2) for SQL Server on Linux
Windows subsystem for Linux(WSL) 2 increases file system performance and system call capability for Linux on Windows OS. In the following screenshot from Microsoft docs, you get a glimpse of the WSL1, WSL2 comparison.
This article implements the following on an Oracle VirtualBox machine:
- Deploy a Windows Server 2019 VM using the VHD file
- Configure the Windows Subsystem for Linux
- Install SQL Server on Linux ( Version 2019) on Linux OS
- Connects and query SQL Database
Deploy a Windows Server 2019 VM using the VHD file
For this step, you require the Oracle VirtualBox application installed on your system. You can navigate to the URL https://www.virtualbox.org/ and download the latest version.
Its installation is straightforward. However, you can refer to the article Steps to prepare a Virtual Machine for it. Once Oracle VM VirtualBox is installed, navigate to Try Windows Server 2019 on Microsoft Evaluation Center, and you get the following page to download Windows Server 2019 evaluation version. Here you get three options.
- Azure: To configure Windows Server 2019 on Azure infrastructure
- ISO: Download ISO file for Windows Server 2019
- VHD: Download the Virtual hard disk (VHD) file for quick deployment
- Note: You can use the Windows Server2019 evaluation version for 180 days and be familiar with its features
This article uses the VHD file for quickly deploying. Click on VHD (Virtual Hard Disk) and download the file. Its size is approximately 8 GB.
Once Windows Server 2019 VHD file is downloaded, launch Oracle VM VirtualBox Manager and create New.
In the create the virtual machine, enter the following information:
- VM name
- VM machine folder
- Memory (RAM) size
- Hard disk: In the hard disk, select option – use an existing virtual hard disk file. Here, select the VHD file that you downloaded from Microsoft
Click on Create and start the virtual machine. It prepares the VM with the configurations in the VHD file.
In the process of getting ready, it asks for information for the keyboard layout. Further, accept the Microsoft software license terms.
On the customize setting page, enter the password for the built-in administrator account.
Click on finish, and your Windows Server 2019 server is ready for deployments.
Enable the Windows Subsystem for Linux for SQL Server on Linux
To run the Linux distributions on Windows Server 2019, we need to enable “Windows subsystems for Linux” from the Windows feature. You can enable it from Windows GUI or use the Windows PowerShell command.
To enable the feature from the graphical interface, launch Server Manager -> Manage -> Add Roles and Features and check on Windows subsystems for Linux as shown below.
Click Next and follow the feature wizard to install WSL in GUI mode. Alternatively, open the Windows PowerShell in Administrator mode and run the following command.
>Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
It prompts you to enter Y before restarting the computer. It is a quick process for enabling the WSL feature and configuring it with virtual machine restart.
Download a Linux distribution from the Microsoft store
You can download the following Linux distributions from the Microsoft store. To download it from the store, search for the specific distribution and click on Install to download and install it.
Alternatively, click on the distribution of your choice and download it on the virtual machine.
- Ubuntu 20.04
- Ubuntu 20.04 ARM
- Ubuntu 18.04
- Ubuntu 18.04 ARM
- Ubuntu 16.04
- Debian GNU/Linux
- Kali Linux
- OpenSUSE Leap 42
- SUSE Linux Enterprise Server 12
- Fedora Remix for WSL
You can also download the Linux distribution from Windows PowerShell. The following query downloads the Ubuntu 16.04 using Windows PowerShell. It uses an Invoke-WebRequest for downloading the repository from the specified URL.
> Invoke-WebRequest -Uri https://aka.ms/wsl-ubuntu-1604 -OutFile Ubuntu.appx -UseBasicParsing
Once the Ubuntu distribution is completed, extract it using the expand-archive command.
>Rename-Item .\Ubuntu.appx .\Ubuntu.zip
>Expand-Archive .\Ubuntu.zip .\Ubuntu
Now, Browse to the Ubuntu directory and check the executable file name. For Ubuntu 16.04, the file name is ubuntu1604.exe.
Execute the Ubuntu1604.exe, and it starts installation of Ubuntu 16.04 on Windows Server 2019. It takes a few minutes to install Ubuntu using WSL on Windows Server 2019.
Once you have installed the Linux distribution, it prompts you to specify credentials for the distribution default user. This account will get sudo (Super User do) access to perform the administrative activities. If you reset the Linux distribution WSL, you require to reset this account as well. You can create a different user for Unix from the local Windows user. For example, I have sqladmin user as a Windows administrator and linuxadmin as Unix administrator in my demo.
Once you enter the Unix user name and password, it configures the user and automatically sign-in to the Linux distribution.
For example, note the prompt – Linuxadmin@WIN-A96LOA99PE6. Here, Linuxadmin is the user and WIN- A96LOA99PE6 is the hostname.
Now, you can run Linux commands. For example, run ls-la to check directories.
Install SQL Server on Linux (2019) on Windows subsystem Linux
We configured a Windows Server 2019 virtual machine and configured it for Windows Subsystem for Linux (WSL) in the previous step. Now, we will install SQL Server on Ubuntu OS.
Step 1: Import the public repository keys from the Microsoft package store
Run the following command in administrative Windows PowerShell.
> wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
Step 2: Register the Microsoft SQL Server 2019 Ubuntu repository
In this step, use add-apt-repository to register the repository for Microsoft SQL Server 2019 on Linux.
> sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2019.list)"
Step 3: Install SQL Server
First, perform the apt repository update using the following command.
> sudo apt-get update
Install SQL Server using apt-get command.
>sudo apt-get install -y mssql-server
In the end, you get a prompt to install run the mssql-conf utility to perform the complete setup of Microsoft SQL Server.
SQL Server requires the following inputs for finishing the SQL Server configuration:
- SQL Server edition
- Accept license terms and conditions
- Specify SQL Server administrator password
> sudo /opt/mssql/bin/mssql-conf setup
Provide the inputs, and your SQL Server is ready on Ubuntu Linux on Windows Server 2019.
You can use SQL Server Management Studio, Azure Data Studio or SQLCMD utility to connect with SQL Server, create databases and execute queries.
If you are running Windows 10, you can follow the Microsoft docs for configuring SQL Server using the Window’s subsystem for Linux.
Reset Linux administrator password for Windows subsystems on SQL Server on Linux
Suppose you want to change the Linux administrator for Windows Server 2019 subsystem. For this purpose, run the passwd command in the administrative Windows PowerShell, specify a current and new password.
In this article, we learned about utilizing the Windows subsystem for Linux that can configure Linux on the Windows operating system. You do not require a separate virtual machine for running Linux commands and utilities. You can run SQL and NoSQL databases MySQL, PostgreSQL, SQLite, MongoDB and Redis, Microsoft SQL Server on Linux subsystems.
- DTU and vCore based models for Azure SQL Databases - September 27, 2021
- Custom Azure Policy definitions for Azure SQL Databases backup retention periods compliance - September 22, 2021
- Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server - September 16, 2021