nav search
Data Center Software Security Transformation DevOps Business Personal Tech Science Emergent Tech Bootnotes BOFH

SQL Server on Linux: Runs well in spite of internal quirks. Why?

Some limitations but most things work

By Tim Anderson, 24 Nov 2016

First Look Microsoft has released a public preview of SQL Server for Linux, and I took it for a spin.

There are three supported platforms for SQL Server on Linux, these being Red Hat Enterprise 7.2, Ubuntu 16.04, and a Linux-based Docker engine. Installation on other versions of Linux may also work, although unsupported. The Docker image enables installation on Mac or Windows. Microsoft specifies a minimum of 3.25GB of RAM.

I installed on a modest 8GB dual-processor VM running Ubuntu 16.04 "Xenial" for testing. Setup involves agreeing a licence and setting an SA (system administrator) password. I then installed command-line tools, sqlcmd (execute SQL queries) and bcp (bulk copy). The install script prompts you to enable automatic start-up.

What is SQL Server on Linux? It appears to be essentially the same code as Windows SQL Server running on a compatibility layer, called the SQL Platform Abstraction Layer (SQLPAL), which maps Windows API calls to OS system calls. Also in the mix is a piece called SQL OS, described as a "user mode operating system", which abstracts the hardware and provides services such as task scheduling, memory management and exception handling. SQL OS has been in SQL Server since 2005; it was not developed for Linux, but has now been extended.

This means that even on Windows, SQL Server has long used some virtualisation techniques.

Microsoft's slide showing the SQLPAL architecture

Microsoft's slide showing the SQLPAL architecture

While SQLPAL seems to be based on a Microsoft Research product called Drawbridge, it is not the same. Long-time SQL Server team member Slava Oks tweeted that "it is SQLPAL and no longer original Drawbridge". He also stated that "we have removed layers from all critical paths" in order to optimise performance, though another tweet adds that "we haven't had a chance to do much perf work in many areas yet" so this may further improve.

SQL Server on Linux is the vNext version, which is set for general availability in 2017. Pricing and licensing will be the same as on Windows, and the same range of editions, from the free Express version upwards, will be available. Microsoft's Rohan Kumar, SQL Server general manager, told El Reg at the recent Connect event that "it's the same SKUs. It's another deployment option. If you have a SQL licence, you can choose to deploy on Linux."

Despite running the same code as on Windows, the Linux product does not have all its features. Unsupported features in the current preview include full-text search, replication, Stretch DB (extend a database to Azure), Polybase (query data warehouses), always on availability groups, Active Directory authentication, SQL Server Agent, SQL Server R Services, Analysis Services, Reporting Services, Integration Services, Data Quality Services, and Master Data Services. CLR (.NET Runtime) stored procedures are not enabled, according to Kumar, but the team is looking at future integration with the cross-platform .NET Core.

Some of these features may be enabled before general release.

SQL Server Management Studio connected to a Linux instance

SQL Server Management Studio connected to a Linux instance

SQL Server Management Studio (SSMS) remains Windows-only, though there is no problem connecting to a Linux SQL Server instance from Windows SSMS. Developers can also use the database tools in Visual Studio, which allow queries, table design, stored procedure editing, and more.

Cross-platform tools with Visual Studio Code

Using Visual Studio Code with SQL Server on Linux

Using Visual Studio Code with SQL Server on Linux

Another option is Visual Studio Code, Microsoft's cross-platform code editor, which runs on Windows, Mac and Linux. A new SQL Server extension lets you connect to a server and get IntelliSense code completion in SQL files. This is nicely done. Type "use", for example, followed by a letter, and the editor lists all the matching databases.

One of Microsoft's goals here is to allow Mac developers to use SQL Server, and the Linux Docker image combined with Visual Studio Code will give them a reasonable experience, though short of what Windows developers enjoy. This might make sense even in scenarios where the final deployment is to a Windows SQL Server, or to SQL Server on Azure.

The Docker deployment is more complex in that you have to map a local file location to the container in order to persist data outside the Docker image. Early Mac users have reported some issues getting this working.

Databases are fully portable between Linux and Windows, with the obvious proviso that unsupported features will not work. I successfully attached Microsoft's AdventureWorks 2012 sample database on Linux, and then restored the more recent WideWorldImporters sample.

There is an annoyance though, which is the appearance of Windows drive letters in tools like SSMS. For example, a backup file copied to /var/opt/mssql/backup appears in SSMS as c:\var\opt\mssql\backup. That said, the proper Linux locations do work in T-SQL scripts.

Testing SQL Server on Linux went smoothly. I successfully executed T-SQL from both Windows and Linux, using sqlcmd, Visual Studio, Visual Studio Code, and SSMS. One of the demos I tried is a Visual Studio project which demonstrates the performance advantage of in-memory OLTP. I ran a script to create a memory-optimised filegroup and ran the application, which worked correctly. In-memory operation gave an approximately six-fold improvement in this demo, despite the limitations of my small VM.

Testing in-memory features on Linux

Testing in-memory features on Linux

My general experience so far is that SQL Server on Linux works fine as just another instance.

Kumar told me that the team set a performance goal of 75 per cent or better than on Windows, and that this target has already been met. This still begs the question: why deploy on SQL Server on Linux when it will run better on Windows? There are many other database managers native to Linux, including MySQL, PostgreSQL and more.

One scenario is for development and testing as mentioned above. Microsoft also wants to win business from customers who have standardised on Linux servers. There is obvious value here for organisations porting databases from Windows servers. I also note that installing SQL Server on Linux was quicker and easier than running the Windows setup, though that may change as more of the supplementary pieces like Analysis Services are added.

Although a more truly native Linux port might have greater potential, the compatibility and code maintenance advantages of Microsoft's approach do count for a lot, and on the basis of the preview, so far so good.®

The Register - Independent news and views for the tech community. Part of Situation Publishing