Virtualisation technology has revolutionized the way we experiment and enhance our skills. With tools like Hashicorp Vagrant, we can now easily set up and configure virtual machines for various purposes. In this article, we will walk through the steps to build a working SQL Server on Linux box using Vagrant on a Windows PC.

Prerequisites

Before we begin, make sure you have the following software installed:

• Vagrant – version 2.0.0 or above
• Oracle VirtualBox – version 5.1.28 or above
• Powershell – version 4 or above
• .Net Framework – version 4.5 or above

You can check the versions of Powershell and .Net Framework by running the following commands in Powershell:

		$psversiontable
		ls -r 'HKLM:\software\microsoft\NET Framework Setup\NDP\v*' | foreach { $_.getValue('Version') } | sort -unique
	

Getting a Vagrant Virtual Box up and running

First, create a folder on your PC where you want to set up the virtual machine. For example, create a folder called “VirtualBoxes” and then create a sub-folder called “SQL2017”.

Open a command window and navigate to the relevant folder:

		cd \VirtualBoxes\SQL2017
	

Now, run the following commands to initialize and start the virtual machine:

		vagrant init hashicorp/precise64
		vagrant up
	

This will download the necessary template for the virtual machine and start it on your Windows PC. You can verify the virtual machine by opening Oracle VirtualBox and checking the list of boxes on the left of the screen.

In the \VirtualBoxes\SQL2017 folder, you will see a file called Vagrantfile. This file contains the configuration for building the virtual machine.

Configuring the Virtual Machine for SQL Server

Before installing SQL Server, we need to configure the virtual machine with the necessary settings. Open the Vagrantfile using a text editor and make the following changes:

		config.vm.box = "ubuntu/xenial64"
		config.vm.network "forwarded_port", guest: 1433, host: 1433, host_ip: "127.0.0.1", id: "Sql Server"
		config.vm.network "private_network", ip: "192.168.33.10"
		config.vm.provider "virtualbox" do |vb|
		    vb.name = "SQL2017"
		    vb.memory = "4096"
		end
	

These configurations set the box template to “ubuntu/xenial64”, forward the TCP port 1433 for SQL Server, assign a private network IP address, and allocate 4GB of memory to the virtual machine.

Save the changes to the Vagrantfile and run the following command to apply the new configuration:

		vagrant reload
	

Once the configuration is applied, you can see the updated settings in Oracle VirtualBox.

Installing SQL Server 2017

Now that the virtual machine is configured, we can install SQL Server 2017. Create a shell script called “install_mssql2017.sh” and add the following commands:

		sudo su
		MSSQL_SA_PASSWORD='Equ1f@x!'
		SQL_INSTALL_USER='DavePoole'
		SQL_INSTALL_USER_PASSWORD='5@d0ldG1t!'
		curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
		curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | sudo tee /etc/apt/sources.list.d/mssql-server-2017.list
		sudo apt-get update
		sudo apt-get install -y mssql-server
		sudo /opt/mssql/bin/mssql-conf setup accept-eula
		echo "PATH=$PATH:/opt/mssql-tools/bin" >/etc/profile.d/mssql-tools.sh
		echo Done!
	

Save the script and add the following line to the Vagrantfile to run the script after the virtual machine boots up:

		config.vm.provision "shell", path: "install_mssql2017.sh"
	

Now, run the following command to destroy the previous virtual machine and start a new one with the updated configuration:

		vagrant destroy
		vagrant up
	

This will install SQL Server 2017 on the virtual machine. Once the installation is complete, you can connect to the SQL Server instance using SQL Server Management Studio or any other tools on your workstation.

Conclusion

Using Vagrant and VirtualBox, we can easily set up and configure a SQL Server on Linux box for testing and experimentation. This allows us to create a ring-fenced environment without compromising our workstations. With the ability to script and automate the setup process, we can quickly provision and rebuild virtual instances as needed. This provides a convenient and efficient way to learn and experiment with SQL Server and other technologies.

So, why not give it a try and start building your own SQL Server on Linux box with Vagrant?