Get started with SQL Server on Linux

The first time I heard about this, I thought it was a joke, but now I have been working with Microsoft SQL Server for Linux, for some time, and I think the idea is great. Why not have Microsoft SQL server on as many platforms as possible.

Below I have described how I got my first solution op running. It is an Easy to use Guide with some links to usefull ressources.

IMPORTANT!!! I have absolutely no experience with LINUX, and therefore don’t be afraid of getting started. If I can do it, then you can also do it.

I have installed everything on a computer running windows 10.

To get running you just need windows 10 (with a version that can run Hyper-V). The steps are:

  1. Install Hyper-V on you work computer
  2. Install Ubuntu 16.04
  3. Install Microsoft SQL server on Ubuntu
  4. Install Management tools on Ubuntu
  5. Test sqlcmd on Ubuntu
  6. Connect from Windows and SQL Server Management Studio

Install Hyper-V on you work computer

If you have not already done, that then go to control panel and program / features. Click on Hyper-V and ok. Hyper-V is installed:

install-hyper-v

 

Install Ubuntu 16.04

After installing hyper-V, restart the computer, and when it’s up running again, press windows-S (for search) and choose Hyper-V.

search-for-hyper-v

The following screen will appear, and you are ready to work.

hyper-v

 

First of all, you need to download Ubuntu 16.04. go to the link below and click Download

https://www.ubuntu.com/download/desktop

ubuntu-download

The following screen will appear, and you can choose to download for free (1) or you can “Pay with Paypal”. For now, I suggest that you just download without paying, and if you start to using Linux in production, then play fair and pay a small amount.

So now you just need to wait for download to complete. when it is completed, go back to Hyper-V, and rightclick on you instance, and then choose new virtual Machine (that starts a wizard):

hypev_001

Skip the welcome screen, and choose a name for you virtual Machine (1), A location (2) and click Next (3)

hyperv_002

Then choose the version of Hyper-V Virtual Machine (I have choosen generation 2).

hyperv_003

Next you need to add memory, and I suggest that you choose at least 4 GB (4096), and if it is for test and not production, then choose Dynamically assign memory). Click Next.

hyperv_004

Then you need to configure you network. That requires that you have configured that before you started this wizard (if not, then look at the buttom of this guide, to see how you can install a network). Choose your network and then click Next.

hyperv_005

Now we need to add a harddrive, and I suggest that you just choose the default settings if you are testing (1). Size is 127 GB, but that is thin provisioned, so it is first used on disk when you use it. click Next.

hyperv_006

Now point your installation to the downloaded Ubuntu installation kit (2), and click next.

hyperv_007

You are at the end of the wizard. Click Finish, and you have created your virtual machine (no installation done yet).

hyperv_008

You should have a screen that looks like this:

hyperv_009

Right click your machine and choose settings. We need to setup a few things before we start the machine.

hyperv_010

First thing is to change security. Remove check from Enable Secure boot. Next thing is to Add more CPU. I have choosen two, because I am just testing. You might need to add more under different circumstances.

hyperv_011

Click OK, and return to the mainscreen of Hyper-V. Right click your machine and click Start:

hyperv_009

when that is done, then simple Right click again, and then click Connect (or simply just double click). You should see the following screen.

ubuntu_001

Choose Install Ubuntu (note that you can maximize the screen by using the menu at top of the window (View).

Follow the steps (I have just commented the most important, but basically there are the following steps:

  1. Welcome (choose your language. I prefer English).
  2. Preparing to install Ubuntu (I suggest “Download updates while Installing Ubuntu)
  3. Installation type (Choose what you want, I just Choose Erase Disk and Install ubuntu). You a popup is shown, just click Continue again.
  4. Where are you… Choose what you want.
  5. Keyboard layout… Choose what you want.
  6. Who are you? Fill in the required fields.
  7. INSTALLATION IS RUNNING AND YOU ARE READY TO GO.

THATS IT!! I am very positive surprised on how easy it is to setup Ubuntu, and if you are finished correctly, then you should be ready to go and have a picture like this:

ready-to-go

I had to restart the virtual machine to get started, and I had some problems that I could not remove installation media. I simply just turned of the virtual machine and started it again. Then it was up running.

NOTE!!! Please note that you should of course try to set a fixed IP-address, test performance, security and so on. this is, how ever, just a simple guide in getting started.

 

Install Microsoft SQL Server on Ubuntu

So, now lets get started, and remember that we have three steps to do.

  1. Install SQL server
  2. Install the tools
  3. test if we are running.

This part just describes how to install the SQL server, and all steps are described in the following link from Microsoft:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu

First you need to open a terminal. Right click the desktop (1) and choose terminal:

sql_001

When terminal is open, then you need to do one thing, that are not in the description from Microsoft. You need to write Sudo su. You are required to enter your password.

sql_003

Now it is Very simple. Write the following commands (one-by-one):

  1. curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
  2. curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
  3. sudo apt-get update
  4. sudo apt-get install -y mssql-server
  5. sudo /opt/mssql/bin/sqlservr-setup (this will setup the last things. Remember to save the SA password). Start the instance.

And now you just need to confirm that the instance is running:

  1. systemctl status mssql-server

If you see an output like this, then everything is perfect, and you have setup your Microsoft SQL Server on LINUX!!!!!

sql_004

 

Install Management tools on Ubuntu

So, now we need to setup connectivity. At current times the installation of Management tools will install sqlcmd and bcp, there are no Management studio for now. You need to install that on a windows machine to get running.

  1. Open a new Terminal window
  2. Write the Following commands:
  3. sudo su (remember to enter your password)
  4. curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
  5. curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
  6. sudo apt-get update
  7. sudo apt-get install mssql-tools unixodbc-dev

You are reguired to accept licens terms after step 7 (2 times).

If you want to make life easy for your self, then add some references to the two application (Sqlcmd and bcp):

  1. open a terminal windows
  2. Write the following commands:
  3. ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
  4. ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

SO If no errors in the terminal window, then you are up running.

(I had to restart my ubuntu before I could test).

Let’s test it

Test sqlcmd on Ubuntu

  1. Open a new terminal window
  2. Write the command in below picture (1) to connect. Remember to add your SA password
  3. Write the three lines (select * from sys.objects go) (2). When you hit enter after go, you should see all the rubbish below go.

If you see the text below GO, then everything is fine and you are up running!!!


sql_005

Connect from windows and SQL server Management Studio

So, now the final and most important test. How do we test from a windows machine, running Management studio. Before we start, I will do three things (Please note!! I am in a test environment. Be careful in production)

  1. Turn of firewall on the linux machine (Only do that in test. NEVER in production!!!!)
  2. Download the correct version of Management studio (it must support the next version of SQL server – vNext)
  3. Find my IP address.

To turn of the firewall on linux, open a terminal and write

  1. sudo su (remember password). I am not sure this is required, but I do it.
  2. sudo ufw disable

To download the correct version of Management studio, og to this link and download it.

https://msdn.microsoft.com/library/mt238290.aspx

Choose the newest link (When I am writing, It is 17.0 RC1).

Finally you need to find the name of your machine or the IP. I just use IP. In Linux open a terminal and write

  1. ip addr show

Find the IP address. Mine is 192.168.8.102.

So now open Management studio and connect:

ssms_001

and if you see the window below, then you are connected and up running.

 

ssms_002

Please note!! A lot of things needs to be done, but this is the basics!!!

 

(off topic) How to setup a networkcard in Hyper-V

I promissed to show how you can do this in Hyper-V and on my machine it is easy, and if you are on a big network, then it requires a lot of skills.

In Hyper-V choose click on this menu item:

network_001

Choose Create new virtual Switch

network_002

Enter a name (1) and choose type (2). I choose external network and then my network card.

Click OK, and you can connect your virtual Machine to the internet 🙂

network_003

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s