Install and Configure SQL Server vNext on Linux Azure VM
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.
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:
Setting system administrator (SA) account password…
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
–> 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:
sudo apt-get update
sudo apt-get install mssql-tools
–> 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) – 22.214.171.124 (X64)
Dec 10 2016 02:51:11
Copyright (C) 2016 Microsoft Corporation. All rights reserved.
on Linux (Ubuntu 16.04.1 LTS)
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)
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 !!!