In December 2016 Microsoft made their SQL Server database available in Linux. Here we\u2019ll cover how to install and perform basic setup of MSSQL in the RHEL based Linux distribution CentOS.<\/p>\n
First we\u2019ll set up the repository file, Microsoft provide a copy of this for RHEL here: https:\/\/packages.microsoft.com\/config\/rhel\/7\/mssql-server.repo<\/p>\n
We\u2019ll use the wget command to copy this file to the \/etc\/yum.repos.d\/ directory so that we can use it with the yum or dnf package manager.<\/p>\n
[root@centos7 ~]# wget https:\/\/packages.microsoft.com\/config\/rhel\/7\/mssql-server.repo -O \/etc\/yum.repos.d\/mssql-server.repo<\/strong>\r\n<\/pre>\nNow that the repository file is in place, installation is as simple as running the following command. At the time of writing the total size of the package was a 139mb download.<\/p>\n
<\/p>\n
[root@centos7 ~]# yum install mssql-server -y<\/strong>\r\n...\r\n+-------------------------------------------------------------------+\r\n| Please run \/opt\/mssql\/bin\/sqlservr-setup to complete the setup of |\r\n| Microsoft(R) SQL Server(R). |\r\n+-------------------------------------------------------------------+\r\n<\/pre>\nOnce the installation has completed, we are advised to run the \/opt\/mssql\/bin\/sqlservr-setup bash script to complete the setup process.<\/p>\n
During my first installation attempt, I got the following error as my virtual machine was only running with 2GB of memory, so be sure that you have enough memory before proceeding.<\/p>\n
sqlservr: This program requires a machine with at least 3250 megabytes of memory.\r\nMicrosoft(R) SQL Server(R) setup failed with error code 1.\r\n\r\n<\/pre>\nYou\u2019ll be able to proceed once you have adequate memory available.<\/p>\n
[root@centos7 ~]# \/opt\/mssql\/bin\/sqlservr-setup<\/strong>\r\nMicrosoft(R) SQL Server(R) Setup\r\n\r\nYou can abort setup at anytime by pressing Ctrl-C. Start this program\r\nwith the --help option for information about running it in unattended\r\nmode.\r\n\r\nThe license terms for this product can be downloaded from\r\nhttp:\/\/go.microsoft.com\/fwlink\/?LinkId=746388 and found\r\nin \/usr\/share\/doc\/mssql-server\/LICENSE.TXT.\r\n\r\nDo you accept the license terms? If so, please type \"YES\": YES<\/strong>\r\n\r\nPlease enter a password for the system administrator (SA) account:\r\nPlease confirm the password for the system administrator (SA) account:\r\n\r\nSetting system administrator (SA) account password...\r\n\r\nDo you wish to start the SQL Server service now? [y\/n]: y<\/strong>\r\nDo you wish to enable SQL Server to start on boot? [y\/n]: y<\/strong>\r\nCreated symlink from \/etc\/systemd\/system\/multi-user.target.wants\/mssql-server.service to \/usr\/lib\/systemd\/system\/mssql-server.service.\r\nCreated symlink from \/etc\/systemd\/system\/multi-user.target.wants\/mssql-server-telemetry.service to \/usr\/lib\/systemd\/system\/mssql-server-telemetry.service.\r\n\r\nSetup completed successfully.\r\n\r\n\r\n<\/pre>\nThat\u2019s it, Microsoft SQL Server is now running successfully and listening for traffic on TCP port 1434.<\/p>\n
[root@centos7 ~]# systemctl status mssql-server<\/strong>\r\n\u00e2 mssql-server.service - Microsoft(R) SQL Server(R) Database Engine\r\n Loaded: loaded (\/usr\/lib\/systemd\/system\/mssql-server.service; enabled; vendor preset: disabled)\r\n Active: active (running) since Fri 2016-12-30 02:26:37 PST; 38s ago\r\n Main PID: 2974 (sqlservr)\r\n CGroup: \/system.slice\/mssql-server.service\r\n \u00e2\u00e22974 \/opt\/mssql\/bin\/sqlservr\r\n \u00e2\u00e22995 \/opt\/mssql\/bin\/sqlservr\r\n\r\n[root@centos7 ~]# netstat -antp | grep 1434<\/strong>\r\ntcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN 2995\/sqlservr\r\n<\/pre>\nConnecting To MSSQL<\/h2>\n
In order to actually connect to the server from Linux we need to install the mssql-tools package, which comes from a different repository than the one that we just set up. It can be found here: https:\/\/packages.microsoft.com\/config\/rhel\/7\/prod.repo<\/p>\n
First we\u2019ll download a copy of the prod.repo file and place it into the \/etc\/yum.repos.d\/ directory.<\/p>\n
[root@centos7 ~]# wget https:\/\/packages.microsoft.com\/config\/rhel\/7\/prod.repo -O \/etc\/yum.repos.d\/prod.repo<\/strong>\r\n<\/pre>\nWe can now proceed with installing the mssql-tools package, as shown below.<\/p>\n
[root@centos7 ~]# yum install mssql-tools -y<\/strong>\r\n<\/pre>\nOnce this is installed we can use the sqlcmd command to interact with the database. To see how to run sqlcmd, simply run it with the -? option for help.<\/p>\n
Unfortunately it appears that when you specify the -P option for the password, the password must be provided in the command line with no option of being prompted for it later. Keep in mind that your password will be stored in your bash history running it this way.<\/p>\n
[root@centos7 ~]# sqlcmd -U SA -P password<\/strong>\r\n1> create database test;\r\n2> go\r\n1> use test;\r\n2> go\r\nChanged database context to 'test'.\r\n1> create table websites(domain varchar(255));\r\n2> go\r\n1> insert into websites (domain)\r\n2> values ('rootusers.com');\r\n3> go\r\n\r\n(1 rows affected)\r\n1> select domain\r\n2> from websites;\r\n3> go\r\ndomain\r\nrootusers.com\r\n(1 rows affected)\r\n<\/pre>\nIn this example we create a test database with a table named websites and a column for domain names. We then insert a domain name and pull it back out with select, confirming both that we are able to connect and that basic SQL queries appear to be working as expected.<\/p>\n
Summary<\/h2>\n
Microsoft\u2019s SQL Server is now available for installation on Linux. Personally I don\u2019t think I\u2019ll ever use this over other alternatives such as MariaDB or PostgreSQL, so hopefully someone somewhere actually finds this information useful!<\/p>\n