SQL Server on Linux
When Wim Coekaerts, Microsoft's vice president for open source, took the stage at LinuxCon 2016 in Toronto last summer, he came not as an adversary, but as a longtime Linux enthusiast promising to bring the power of Linux to Microsoft and vice versa. With the recent launch of SQL Server for Linux, Coekaerts is clearly having an impact.
PowerShell for Linux and bash for Windows heralded the beginning, but the arrival of SQL Server, one of the most popular relational databases out there, offers Linux shops some real opportunities—and a few conundrums.
Clearly, the opportunity to deploy SQL Server on something other than a Windows Server means you can take advantage of the database's capabilities without having to manage Windows hosts to do it. If you're a mostly-Linux shop (or want to be) and you have customers looking to deploy workloads and applications that require SQL Server, you now have a true Linux solution.
At the same time, if you're big enough to have cabinets full of database server hardware, you probably have databases that serve real-time workloads and databases that underpin your data warehouse. If you're running the latter on beefy hardware necessary to manage the overhead of both Window Server and SQL Server, the advent of SQL Server on Linux might give you an alternative.
For instance, you might shift your lower-resource data warehouses to resource-sipping Linux servers with SQL Server. That could save you on hardware and migration costs, given that there are no structural differences between SQL Server running on Windows or Linux.
If you were contemplating shifting your data warehouse from SQL Server to MariaDB or Oracle to take advantage of Linux hardware savings, you wouldn't have to fret about the conversion costs. Even though you'd still pay for SQL Server licenses, you could save on the costs to convert and migrate to make up the difference.
On the conundrum side, you may ask why you might need Microsoft's offering at all. Afterall, open-source databases like MariaDB (or MySQL) and PostgreSQL are robust, well tested, free and supported by large communities. Why introduce a historically closed-source proprietary tool to your open-source environment? SQL Server 2016 Standard lists for about $3,717 per core, though the Developer and Express versions are free, with Express able to handle up to 10GB for your data-driven applications.
Since none of us lives in an ideal, pure-Linux world, the fact is there are times in the enterprise when you can—or must—use SQL Server. There's a lot of solid, legacy software out there that uses the Microsoft database for the back end, which means given the choice, many of us would like a Linux option.
Fortunately, you can try it out without really breaking a sweat. You can install SQL Server on Red Hat Enterprise Linux 7.3, Ubuntu 16.04, SUSE Linux Enterprise Server v12 SP2 or pretty much anywhere as a Docker container.Installing and Running
To get a taste of SQL Server for Linux, I decided to run it from a Docker image running on a separate Ubuntu 16.04 box with well more than the 4GB of RAM and 4GB of storage required. I set it up on a remote Linux host so I could test remote connections.
Pulling SQL Server from Docker is trivial:
$ sudo docker pull microsoft/mssql-server-linux
Depending on your network speed, this will set up the image in just a couple minutes. When the pull is complete, you can start the SQL Server container from the command line with a few straightforward parameters:
$ sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=(!)Superpassword' -p 1433:1433 -d microsoft/mssql-server-linux
Note that the command includes a tacit "Yes" to Microsoft's End-User Licensing Agreement and that the server is running on the common SQL Server TCP port 1433. The password must meet Microsoft's minimum guidelines; if not, the container will start, but then quit after about two seconds.
$ docker ps shows the container is up and running:
CONTAINER ID IMAGE EDD1E0EBEA6E microsoft/mssql-server-linux ↪STATUS PORTS ↪Up About an hour 0.0.0.0:1433->1433/tcp
Accessing the database server is equally straightforward:
$ sqlcmd -S 192.168.1.15 -U SA -P '(!)Superpassword'
There are some handy tools you can use on Linux, Mac or Windows to connect to the
SQL server instance. I used Microsoft's
sqlcmd tool on Mac and Linux boxes to
connect to the running database instance.
Once connected with
sqlcmd, you can start running all your familiar SQL commands.
I created a database called "testdb", created a table called
"inventory" and added three rows of data. Logging in, I was able to
connect and query the results quickly:
The containerized SQL Server can be managed through a number of other tools, including Visual Studio Code and SQL Server Management Studios for Windows. Red Hat and SUSE already have built-in management tools available in their enterprise server editions.
No matter how you choose to deploy SQL Server on Linux, you'll find it easy to start testing it and getting a feel for how it might play in different database scenarios you have in mind. You also might find that having a Microsoft tool running happily on Linux isn't as far-fetched as it might've seemed even just a year ago.Resources
SQL Server on Linux: https://www.microsoft.com/en-US/sql-server/sql-server-2017
Install SQL Server in a Docker Container: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker
Install SQL Server on Ubuntu: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu
Install SQL Command-Line Tools for Linux: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu
Microsoft SQL Server Pricing: https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing