The buzz around SQL Server on Linux is certainly not new. For more than a year we have been expecting SQL Server to be let loose on previously dreaded Open Source Linux environment. I personally have been waiting to try my hands on doing the install myself and, I recently got round to it. I chose to use Amazon Web Services (AWS) to do my little experiment since I am familiar with AWS services and I think it is one quick way to get a server up and running for experimental purposes. The following steps briefly show how I created my Elastic Cloud Compute (EC2) instance.

Creating an AWS EC2 Instance

Login to your AWS console, navigate to EC2 Service offering and start the EC2 Launch wizard using the button shown in the graphic. I should state here that if you are interested in Amazon Web Services you can consult the documentation available at https://aws.amazon.com/. AWS is an excellent cloud services provider, top of the ranks according to Gartner.

Fig 1. Launch Instance

Choose the RHEL AMI. RHEL is one of the supported distributions of Linux for installing SQL Server vNext, Supported platforms and requirements for installing SQL Server vNext can be found at the SQL-Server-Linux page.

Fig 2. Using and RHEL AMI

Choose an instance type with a configuration strong enough to support SQL Server. I chose the t2.medium instance type since the minimum required memory for running SQL Server on Linux is 3.25GB, which a free tier t2.micro (the type of choice for experiments) will not be suitable for. It is important to know that when choosing an instance type that is NOT free tier, AWS will charge the customer. 

Fig 3. Using T2.Medium Instance Type

Configure additional instance details. For the purpose of this demonstration I have used insecure options because I want to avoid the any inconveniences introduced by proper security. In a live scenario you may want to properly define the IAM role desired for the instance and you may want to use a Custom VPC. You can also consult AWS Security Best Practices White Paper for recommendations on architecting secure cloud infrastructure.

Fig 4. Instance Details

In this step I add two additional Elastic Block Storage (EBS) volumes because I want to create my databases on separate volumes. It is worth noting that EBS is the preferred storage type for database files on AWS.

Fig 5. Adding Volumes

Adding tags on AWS assets is optional but helps in identifying the assets for a number of reasons including billing.

Fig 6. Adding Tags

Security groups are a way of controlling access to your environment at Layer 3 of the OSI model in AWS. In this demonstration I have a security group called MSSQL in which I have defined access to my Linux Server on ports 22 and 1433 from everywhere. Again this is another evil security practice. In a live environment you may want to specify the IP addresses or subnets that should have access to the server on ports 22 (for SSH access) and 1433 (for database instance access).

Fig 7. Configuring a Security Group

Once all options are specified, we are ready to launch our instance. Take note of the warnings I earlier highlighted regarding security. It is worth keeping in mind particularly if going live with your SQL Server instance.

Fig 8. Security Warning

The last prompt you get when creating an EC2 instance is for the Key Pair. For Linux servers, AWS allows access to the servers via SSH using a Key Pair rather than merely a username and password. I have an existing key pair which I selected for the purpose of this demo.

Fig 9. Specifying a Key Pair

Our instance is live and we can see its public IP Address. We shall use this public IP address to connect to the instance using Putty and SQL Server management Studio for SSH and Database sessions respectively.

Fig 10. Viewing the Public IP Address

Installing MS SQL Server

Once our EC2 instance is ready we proceed by taking the following actions:

1. Update the Operating System using yum
2. Download the MS SQL Server installation packages
3. Install SQL Server database engine
4. Install SQL Server tools (sqlcmd and bcp)
5. Connect to the instance
6. Create a database

We connect to our AWS EC2 instance using Putty, a terminal Client tool capable of doing SSH connections to a variety of Unix-like Operating Systems. In order to connect successfully to the server, we must specify the Private Key File (a .pkk file) stored on the client computer which was initially created or selected when building the EC2. The default user for connecting to AWS EC2 Linux machines is ec2-user and we can define and save a session as shown in the graphic with the details we have mentioned.

Fig 11. Saved Putty Session

Fig 12. Path to Private Key File

To prepare for installation of SQL Server vNext, we take the following actions:

Login to the Linux Server and update the OS using as root:

a.       Sudo -

b.      yum install update

Fig 13. Login to the EC2

Download the MS SQL Server RedHat Repository configuration file and run the installation using yum. Once done you can proceed with setup command /opt/mssql/bin/sqlserver-setup. The setup process is very simple: you are prompted to accept the license terms, and then configure the sa password as well as startup options.

a.       curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

b.      yum install -y mssql-server

c.       /opt/mssql/bin/sqlservr-setup

Fig 14. Downloading MS SQL Server vNext Repository for RHEL

Fig 15. Installing SQL Server

Fig 16. Setting Up the Instance

Once installed, you can confirm SQL Server is installed and running on Linux using ps and systemctl commands: ps -ef | grep mssql and systemctl status mssql-server. Observe the owner of the SQL Server binaries (mssql). This is basically equivalent to the service account on Windows.

Fig 17. Using ps to Confirm Installation

Fig 18. Using systemctl to Confirm Operation

Download and install the MS SQL Server tools by issuing three commands:

a.       curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

b.      yum update

c.       yum remove unixODBC-utf16 unixODBC-utf16-devel

Fig 19. Downloading MS SQL Tools

Observe that these binaries have been installed by default in /opt. The default data and log file location is /var/opt.

Fig 20. Navigating the Installation Directory

Accessing the Instance

We can login to the SQL instance the very same way we do on Windows using the client tools sqlcmd and SQL Server Management Studio. The regular command sqlcmd -S localhost -U sa -P <password> works just fine. I was also able to access the instance from my personal laptop because I had opened port 1433 to the world (grievous offence).

By the way, you will observe that the SQL Server Agent Service is disabled. Incidentally it is among the "yet-to-be-supported" services documented for SQL Server on Linux.

Fig 21. Connecting Using sqlcmd

Fig 22. Connecting Using SSMS

We can view properties of the instance and databases just like SQL Server on Windows:

Fig 23. Instance Properties

Fig 24. Database Settings

We can go further to create a database using non-default paths. Before we do that we have to mount our EBS volumes as file systems using the following steps:

1. Identify the device names for the EBS volumes we attached while creating the EC2 instance using the command lsblk. In my case the names of the were /dev/xvdb and /dev/xvdc. I will use these device names in subsequent commands.
2. Create a file system on the volume using the command mkfs -t ext4 /dev/xvdb. Care needs to be taken when using mkfs, it is only required for new, empty volumes since it will perform a format.
3. Create a new directory using the command like mkdir /mssqldata
4. Mount the volume on the directory using the command mount /dev/xvdb /mssqldata

Fig 25. Raw Volumes Before Mount

Fig 26. Mounted Volumes

Fig 27. Output of lsblk after Mount

Once the above is done it will be possible to create a database whose datafiles are sitting on these volumes after ensure the user mssql owns the subdirectories.

1.       mkdir /mssqldata/data

2.       chown mssql.mssql /mssqldata/data

Fig 25. Creating a Database

Conclusion

In summary, installing SQL Server vNext on Linux is extremely simple. The installation itself is as simple as the installation process - all we have is a database engine. be that as it may, the functionalities we expect in the regular database engine we are familiar with are fully available as far as I can currently see. However I did notice I was not able to change the database defauIt locations. This may have something to do with the path naming conventions but I am yet to delve deeper. Overall I do think moving to the Linux platform is a huge leap for Microsoft anyway and I look forward to deploying SQL Server on our production Linux instances. It will do amazing things to our cost-cutting initiatives.

References

• SQL Server Linux Setup
• EBS Using Volumes
• SQL Server Linux Setup Tools
• First Steps with SQL Server on Linux