SQL Server (VNext) On Linux

Recently, Microsoft announced that SQL server will support Linux and other platforms. Well it doesn’t come as a surprise to those who closely observe what Microsoft is trying to do to stay in the market. Initially, SQL Server was well suited on the Windows Platform (like Windows server). But, other competitors like Oracle, MySQL and so on run on several platforms. So, they are not platform-oriented.

As per the blog:

“We have made it easier than ever to get started with SQL Server. You’ll find native Linux installations with familiar RPM and APT packages for Red Hat Enterprise Linux and Ubuntu Linux, and packages for SUSE Linux Enterprise Server will be coming soon as well.”

There are many features like operating and programming features in this SQL Server VNext. We will see them later.

Initially those who have installed SQL server on PC or server know it takes a minimum of 45 minutes to install the SQL server and then to configure endpoints (Ports). Here, on the Linux or Ubuntu platform the installation of the rest of the configuration merely takes fewer than 3 minutes. So, here we will see installation on Ubuntu 16.xx

The main advantage of purchasing a SQL server license as compared to others like Oracle is when you purchase SQL Server Standard edition 2016 it comes with all reporting services, tools, and other things and you don’t need to buy that separately . Whereas, with Oracle and any other, first you need to pay for the license for the Database and then if you need any other services like reporting services and other tools you need to pay an additional fee.

Let’s Start,

First login on Azure portal

After successfully login you will be redirected to Dashboard

Now we need to create a VM Server where the operating system will be Linux

Click on Virtual Machine->Click on Add above

Click on Ubuntu Server and select Ubuntu Server 16.10

Select Ubuntu Server 16.10

Select Resource manager and then click on Create; after that fill in the details and give a desirable name and enter username and password --we require this username and password to connect to server

Click Ok and then select the storage structure

Here I selected the first option as this is just a demo. That much storage is more than sufficient. Then click on Ok; then it will compute all the requirements and just click Ok . When you click Ok it will be redirected to the Dashboard and it will show you that VM server is getting ready; this will take couple of minutes

So we successfully created VM server here; just validate the details

Now we will connect IP and install SQL server; for that we will connect that IP through Putty

Click Ok and we will enter Username and password

We are successfully logged in ;now we will just run the commands for the SQL server . First, we will download all the necessary packages and then we will install SQL server and then we will start the service .

To install SQL server on Ubuntu just follow these steps

Import the public repository GPG keys

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Register the Microsoft SQL Server Ubuntu repository

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list
Run the following commands to install SQL Server.

sudo apt-get update
sudo apt-get install -y mssql-server

So SQL Server is successfully installed; it took me just 2 minutes to install SQL server

Now, after installation we will run the configuration script for SA passwords

sudo /opt/mssql/bin/sqlservr-setup

Type YES and press Enter

Configure SA password

After configuration of SA password it will ask whether to start SQL service or not type Y and press Enter

So our Setup has been completed successfully

We will verify whether the service is running or not

systemctl status mssql-server

So our service is running.

Now we will configure ports for our SQL server and we will keep the default port which 1433 . So switch back to our Azure Dashboard.

Click on Network Interface ->click on your VM machine

Then click on Network Security Group

Click on Inbound Security Rules as

Configure the port for MSSQL as

It will create a security rule now let's try to connect SSMS .Enter the IP, Username and password

Click on Connect

As you can see from the above output we successfully connected to our SQL server; now let's run some commands. First we will see the version of our SQL Server

Now let's create a Database as TEST

Click on the TEST and let's check the mdf and ldf files

Remember these are now our mount points on Server where these mdf and ldf files are there we will see those on server.

So, this is our directory

Sudo ls /var/opt/

In that mssql/Data folder

From the above output you can see our mdf and ldf files.

Earlier, in windows server these MDF and LDF files used to be on the VM drives we used to extend the space if any additional space was required.

Here these are our mount points /var/opt/mssql/data just like any other mount points on Linux/Unix server.

Now we will create a sample table and insert some data

So it’s working fine just like our earlier versions of SQL server.

Now the question is can we migrate our previous versions of SQL like 2005,2008,2012,2014 to SQL server vnext?

Yes. You can provide your first successful migration of the schema to the destination and then try to move data in chunks .

We will see that I have some tables we will try to migrate on SQL Server vnext

There is an existing database, TEST, in my system we will try to move student table to our SQL server vnext so I generated a script with schema and data

Execute the script

As you can see from the above output we had successfully migrated our table to our SQL Server VNext .

SQL Server VNext Features

Conclusion

With the rise of SQL server vNext many organizations will try to move their existing databases to SQL server Vnext. As developers or DBAs we will get more hands-on with Linux, like managing the process utilizations and so on

References

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-troubleshooting-guide
https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/announcing-sql-server-on-linux-public-preview-first-preview-of-next-release-of-sql-server/
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-connect-and-query-sqlcmd
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-red-hat