A guide to using SQL Server on Linux for the Tachyon databases. After installation of SQL Server, then what? How can you integrate Tachyon? This guide will help.
Why Would I Try This Out?
First of all, let’s emphasize that running the Tachyon databases on Linux is not supported by 1E. However, “not supported” doesn’t mean “it won’t work.” Rather, it means “don’t call us if it doesn’t work.”
When I found out that Microsoft had released SQL Server on Linux I thought that it would be a valuable experience to spend a small amount of time trying it out, mainly to satisfy my curiosity about how well it would work and how easy or difficult it would be.
Installing SQL Server on Linux
The documentation pages (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup) describe the process in a way that is sufficiently clear. There is support for Red Hat, SUSE and Ubuntu. I decided to use Red Hat Enterprise Linux because I already had it installed in a virtual machine that was available for the experiment.
I simply followed the step-by-step instructions in the documentation, which in summary involves downloading the installer from the repository (a simple curl command) and then installing using yum. This executes surprisingly quickly–much faster than running Setup to install the Windows version of SQL Server.
After installation, you run the command mssql-conf setup, which basically asks you for the sa password, and you are done. The documentation suggests running systemctl to verify the status of the server:
You can also use systemctl to start, stop, enable, or disable the service.
Connecting to SQL Server
There is a downloadable version of SqlCmd that can run on Linux, but I didn’t bother with it. Since I already had SQL Server with all its client tools installed in another virtual machine, I just connected remotely to the Linux server. SQL Server Management Studio (SSMS) connected immediately using the credentials for sa that I entered during installation.
I created a test database and did a bit of experimenting, both by typing SQL commands and by using the user interface in SSMS. Everything seemed to work the same as on the Windows version. The file names used for creating the database use a syntax for their path that looks like Windows instead of Linux: C:\var\opt\mssql\data.
Attempting to use the option for “Edit Top 200 Rows” on any of the tables made SSMS crash every time I tried it out. Note that this crash refers to the client application running on Windows; the service on Linux was unaffected. This is the only problem I experienced during the brief time that I spent testing the server.
Once I knew that SQL Server was working, I wanted to subject it to a known workload. So, I decided to install Tachyon and tell it to create its databases on the SQL Server on Linux.
Unfortunately, I found a problem as soon as I started running the Installer: Our current Tachyon installer asks only for the server name and not for the credentials it should use for the connection. It presumes that integrated Windows authentication is available, and connects using the credentials of the logged-in user who is running the installer. However, SQL Server on Linux supports only SQL Server authentication and not Windows authentication.
My workaround was to install Tachyon on a local SQL Server that I had on Windows. In my case, this was a Developer Edition, but the free (and smaller) Express Edition would have been sufficient for this purpose. Once the installation was done, I took a backup of the TachyonMaster and Responses databases, and restored the backups on the Linux server. The restore commands ran with no problem (using the same windows-like syntax for the filenames that I mentioned above), and they were able to restore the database created on Windows into the server running on Linux without a glitch.
I then went over all the .config files for the various Tachyon components that use the TachyonMaster database, and changed the connection strings to point to the Linux server and use the SQL Server credentials instead of the default Windows authentication.
The Responses database is not configured in the .config files. Instead, there is a field in the DataStoreConfiguration table in the TachyonMaster database that contains the corresponding connection string. I also changed this string to point to the Linux server, although this is not a requirement: Tachyon is able to run the Responses database on a server that is not the same that contains the TachyonMaster database.
After everything was configured, I went to the Tachyon Explorer interface, uploaded some Product Packs, assigned permissions to a user, and sent some queries to Tachyon. Everything worked at the first try, exactly the same as it does when SQL Server is installed on Windows. From the point of view of the user, there was no perceptible difference that would indicate that the database was “unusual.”
Examining the values in the database tables did not reveal anything unexpected. All the tables and their fields looked the same as when the server is installed on Windows.
Note that I did not perform an exhaustive testing for all the Tachyon features. For instance, I did not try out the “Export” feature in Tachyon, which internally uses the bcp command to export the Responses tables into a network share. In theory it should work, since the bcp is launched on the Tachyon server and not on the database server, but I did not verify this.
I did a bit of experimenting, sending some queries that returned a few thousand rows (which is a small workload by Tachyon standards). I did this on a Tachyon installation connected to the Linux SQL Server and another one connected to a Windows Server. Both servers were running on similarly dimensioned virtual machines under the same host. The apparent sensation of speed was similar in both cases. This is not a formal benchmark with precise speed measurements, just a subjective impression from the point of view of the user. My sensation was that the speed was similar in both cases, with no obvious “winner” one way or another.
This experiment was done using a CTP version of SQL Server on Linux. Therefore, this should not be considered ready for production use. But the subjective impression about its behavior indicates that, once there is a final, supported release, SQL Server on Linux will probably be perfectly adequate for production use by sophisticated SQL client applications such as Tachyon.