How to Install SQL Server on Linux and Create a Database

Cross-platform compatibility is the key to becoming a major figure in today's multi-platform software market. It looks like one of the biggest players in all of technology is finally starting to get it. Back in March, Microsoft announced that it would release its wildly popular database software SQL Server on Linux. The company promised a fully featured version equipped with always-on encryption, in-memory database management, and cross-platform support for mobile devices. Linux fans must wait until some time in 2017 to get their hands on an official version of SQL Server 2016. Meanwhile, we were lucky to catch the free preview of SQL Server for Linux that just hit the web.

Distro Support

The live preview, dubbed SQL Server vNext CTP1, is currently available for Ubuntu, Red Hat Enterprise, and other distros via Docker containers. Lots of options for the Linux faithful. Since I'm running Linux Mint on my box, the examples in this review will pertain to Ubuntu.

Installation

Like many applications, the SQL Server for Linux preview must be installed from the command line. The thought alone filled me nervous tension, but it's actually a simple process. Here's what you do: 1. Open the terminal 2. Grab the public GPG keys to encrypt your sessions by typing the following line: curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - 3. Register the Microsoft SQL Server Ubuntu repository by typing the following line: curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /etc/apt/sources.list.d/mssql-server.list 4. Finally, install an updated SQL Server for Linux by typing the following commands: sudo apt-get update sudo apt-get install -y mssql-server

Configure Your SQL Server

In this step, configuring SQL Server is as simple as creating your login credentials as system administrator (SA). Run the configuration utility and set up a password by entering the following command: sudo /opt/mssql/bin/sqlservr-setup That's all folks! Microsoft SQL Server has been successfully installed on your Linux machine. If you want an added peace of mind, you can make sure the service is running by typing the following command: systemctl status mssql-server

SQL Server for Linux Command Line Tools

The SQL Server for Linux preview is a command line application so you're gonna need the right tools in order to dink around in this environment. Microsoft offers some very useful tools for the job, including sqlcmd. The sqlcmd utility is designed to optimize SQL queries and simplify a number of database administration tasks. For example, it's great for batching processing and other repetitive processes as well as simulating the load of test databases. Bcp is another interesting tool available to SQL Server vNext CTP1 testers. Short for Bulk Copy Program, bcp is a command line utility that lets you copy larger than average query jobs from a database into a data file and vice versa. A simple example would be importing existing employee data into a table you created with SQL Server. BCP is so flexible that it can handle the 'bulk' of your import and export needs. The command line tools are not included with the installation, so we'll have to install both the sqlcmd and bcp utilities on our own. Fortunately it's all pretty simple and straightforward. We'll start by repeating the first three steps of the installation process. But of instead of installing SQL Server again in line 4, we'll install the updated tools by entering the following commands: sudo apt-get update sudo apt-get install mssql-tools Complete the installation process by accepting the License terms and proceed to the next step.  

Read More: Troubleshooting Linux: Problems That Drive You Mad and Solutions to Save the Day!

Connect to SQL Server

Another critical function sqlcmd performs is connecting to the database server itself, which is necessary to create databases, import data, and so forth. We can create a secure connection to SQL Server by simply opening the terminal and running sqlcmd with parameters for our username and the password we created earlier. Your command will look something like the following: sqlcmd -S localhost -U SANote: You can type 'localhost' in place of the username and omit the password to be prompted for it in the next line, as I illustrated in the command above. I find shorter commands are easier to execute and not screw up.Here’s where things get tricky. For some reason, Linux gives no indication that we made a successful connection to the server. Rest easy. If the terminal doesn’t throw up any errors, all systems are most likely go. You’ll know for sure if you see output that resembles the following screenshot:Note: You can also connect from SQL Server Management Studio. I tried to set it up so I could show you guys how much easier database management is with a graphical user interface, but the installation kept hanging at about 70 percent. Blame Microsoft for that. I sure am. 

Read More: Top 5 Linux Community Resources for IT Pros

SQL Server Database Administration on Linux

Once confident I had everything up and running correctly, I decided I'd get a feel for the app by playing database administrator. Our preview has its own set of databases, so it's perfect for this little test. To query SQL Server for a list of existing databases, enter the following lines: SELECT Name from sys.Databases; GO You’ll see that SQL Server already has a default test database available, but we’ll create our own by entering the following lines: CREATE DATABASE dummydb; GO We can prep our newly created database for use by entering the following lines:

USE dummydb;

GO

Next we’ll create a table in our dummy database by entering the following lines:CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);GO Now we’ll insert data into the database by entering the following lines: INSERT INTO inventory VALUES (1, 'pens', 170); INSERT INTO inventory VALUES (2, 'pencils', 174); GO Now that we've created and populated our test database, we can insert additional data, export to outside data sources, and even backup with sqlcmd. If you're looking for a nuclear approach to backup your SQL database, you're better off with an enterprise-grade backup software such as StorageCraft ShadowProtect SPX.

Bottom Line

Microsoft is touting SQL Server 2016 as a solution that will not only raise the bar for relational database management, but support the enterprise's ongoing needs for big data. Admins who know their way around the terminal should feel right at home with the Linux preview. The installation was a breeze and thanks to sqlcmd, I was a lot more comfortable executing T-SQL queries than I thought I'd be. I just hope an official release of SQL Server on Linux means SQL Server for Linux comes in a box with an installation disk and a GUI for those who get queasy in the terminal – not just a code that confines us to the lonely command line.