SQL with Manoj

 
In my [previous blog] post we saw how to Create a new Linux VM on Azure. After creating it we connected it from a Windows workstation by using PuYYT tool and tested one Unix command.
 

Here in this post we will see how we can install SQL Server on Linux.

Microsoft is working on a new SQL Server vNext version that will work both on Windows as well as Linux. So with the recent [CTP 1.x] release we can see how does it look like on both the Operating Systems and get some hands on with it.
 

–> Get the Repository and Install SQL Server:

1. Import the public repository GPG keys:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

2. 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

3. Re-synchronize the Package index files from their sources, to get information of the newest versions of packages and their dependencies, and finally Install MSSQL-Server package:

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

After Install it will ask you to run SQL Server setup by following message:

Please run /opt/mssql/bin/sqlservr-setup to complete the setup of Microsoft(R) SQL Server(R)

4. Now post install, we need to configure SQL Server and set the SA password, and SQL services.

sudo /opt/mssql/bin/sqlservr-setup

The Configuration gives following prompts in between:

Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y

5. Post Configuration you can verify that the service is running:

systemctl status mssql-server

1. Import & Register repositories

2. Update sources list

3.a. Install SQL Server

3.b. Install SQL Server

4. Configure SQL Server

5. Verify SQL service is running

–> Install Tools: This will install the command-line tools (sqlcmd & BCP), Microsoft ODBC drivers, and their dependencies.

6. Re-synchronize the Package index files, and Install MSSQL-Tools:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd-13.0.1.0 /usr/bin/sqlcmd
sudo ln -sfn /opt/mssql-tools/bin/bcp-13.0.1.0 /usr/bin/bcp

–> Connect to SQL instance:

9. We will use sqlcmd to connect to SQL Server vNext on our Linux VM. Run the sqlcmd command with following params:
– S: Server Name
– U: User Name
– P: Password

sqlcmd -S localhost -U SA -P ”

–> Run SQL Queries:

10. Now after connecting to the SQL Linux instance, let’s test come queries:

We will check the Version of SQL Server and list out system Databases:

select @@version
go

select database_id, name, create_date from sys.databases
go

Output of version statement:
Microsoft SQL Server vNext (CTP1.1) – 14.0.100.187 (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)

SQL Version and System Databases

11. Let’s do more and Create a new Database, new Table and insert some sample records:

CREATE DATABASE SQLdbOnLinux
go

use SQLdbOnLinux
go
--Changed database context to 'SQLdbOnLinux'.
CREATE TABLE Test (i int, name varchar(25))
go
insert into Test values (1, 'Manoj')
insert into Test values (2, 'Saurabh')
go

--(1 rows affected)

--(1 rows affected)
select * from Test
go
--i           name
------------- -------------------------
--          1 Manoj
--          2 Saurabh

--(2 rows affected)

Create a sample Database, a Table, and insert records

I think you would get amazed like me after using SQL Server on a Linux environment.

In my [next post] I’ll show how we can connect this SQL Linux instance from a Windows workstation via SSMS !!!

–> You can also watch this video on YouTube: