by Mary Branscombe

It’s time to start planning for SQL Server on Linux

Jan 03, 2017
Database AdministrationLinuxOperating Systems

You can treat SQL Server on Linux as a new deployment option, not a whole new database. That means you can choose the operating system that best fits your project.

Project management teamork
Credit: Thinkstock

Bringing SQL Server to Linux is one of the bigger steps in Microsoft’s plan to help developers build any app for (and from) any platform. And despite the fact that it was only announced in spring 2016, you shouldn’t think of SQL Server on Linux as being a subset of the full Windows offering, says Rohan Kumar, general manager of the Microsoft database systems group.

“It’s the same SQL Server product,” he tells “We have one SQL Server product, and Windows and Linux are your deployment choices.”

That means you’ll get significant features from SQL Server, from availability groups and Active Directory Authentication to dynamic data masking, row level security and transparent data encryption, plus Azure integrations like stretch database and the always encrypted option. Not all those features are guaranteed to be in the first release of SQL Server on Linux, Kumar cautions. “But in the long term, it’s just the same database. If you want stretch database or backup or replica, you go for it.”

The areas where SQL Server on Linux will differ the most from the Windows Server version are where features depend on the operating system itself. If you use clustering for high availability and disaster recovery (DR), that depends on Windows Server Failover Clustering. On Linux, the way you do high availability and DR differs even between RedHat, Ubuntu and SuSE, and SQL Server on Linux will use open source tools like Pacemaker for clustering.

There are a few database-level features that are also tied closely to Windows, such as FileTable, which lets applications access files and documents stored in SQL Server as if they were in the file system. “With FileTable, we effectively give Windows clients transactional access through the Win32 API so they can access blobs in a much more highly performant and scalable fashion. If you store blobs natively in SQL Server, that will work on Linux but the file table feature will not,” Kumar explains.

“But there are very few features like this. Conceptually, it’s the same SQL Server: all the core relational database, all the investments we’ve made in column store and in-memory OLTP — all of them work across both products,” says Kumar.

The new version of SQL Server in 2017 will ship for both Windows and Linux. After the significant 2016 release, the Windows versions will get a handful of improvements. “We will push the boundaries of the intelligent database with R,” says Kumar (that’s how Microsoft is putting machine learning-based ‘cognitive’ functions like facial recognition and OCR into the database itself and there will be enhancements to PolyBase. “But most of the focus of this release is around making features first class on Linux.”

Choose your database not your OS

Bringing SQL Server to Linux lets you decouple your database decisions from your platform decisions. “It’s about choice,” says Kumar. “Mixed environments are very common; almost every large enterprise has Windows Server and Linux. One of the things we’ve constantly kept hearing from CIOs is ‘when we pick SQL Server, we have to decide on our operating system investments at that time and that’s very different from other databases.’”

Gartner analyst Donald Feinberg tells he hears a similar message from enterprises, and expects SQL Server on Linux to appeal to both new and existing customers. “Historically, there are many customers that chose not to use SQL Server because it only ran on Windows Server. Although many will choose to use Windows Server now, they have the knowledge that SQL Server will run on Linux as another platform choice. So, although they may not use Linux now, they have it as a future option.”

The demand for choice has increased in the last two to three years, says Kumar, and often it’s less about philosophy than about operational structure. “Typically, there are two different teams that manage the operating system and SQL Server, and that coordination becomes a friction point. We don’t want to be rigid and create friction.”

Feinberg expects SQL Server on Linux to be of most interest for enterprise-level rather than department-level SQL Server solutions, although he notes that “it does allow organizations another option for consolidating the smaller departmental implementations to larger servers, running Linux.” It might also mean SQL Server becoming a more common cloud-hosted database offering, he suggests. “It allows cloud service providers the option to use Linux for SQL Server, if they prefer not to support Windows Server.”

Fitting into developer and Linux admin workflows

The key to making SQL Server on Linux easy to adopt is to integrate it well with both the Linux environment and existing SQL Server deployments. You can use a mixed Windows and Linux cluster to migrate a SQL Server database from Windows to Linux without any downtime, and you can even run a mixed cluster in production. Some early adopters in the private preview of SQL Server on Linux (which included more than half of Fortune 500 companies) have been doing just that.

“Customers have also said that they want to be able to back up from Windows, restore on Linux and attach that to their database. This all makes it very easy for SQL server on Linux to just slide into their environment,” says Kumar.

Developers will be able to work against SQL Server on Linux in very much the same way they do with SQL Server on Windows, using SQL Server Management Studio or SQL Server Data Tools in Visual Studio. But developers who are used to working in a different way can also use Visual Studio Code on Linux or Mac to connect to SQL Server and even edit T-SQL. Making SQL Server fit well into that world opens up SQL Server development much wider than the traditional Windows approach.

“As a developer environment, VS Code brings a lot of emphasis on non-Microsoft stack drivers,” Kumar points out. “If you’re a Java developer, a Python developer, an R developer, we’re making all these stacks very first class, not just ADO.NET. We want to go very squarely after developers who have traditionally not looked at SQL Server because it’s limited to Windows. We’re opening that up. We open sourced our JDBC library and that allowed us to get into the Maven framework so it’s very easy for a Java developer to get SQL Server threads deployed on Linux.”

Keeping things familiar for those non-Microsoft developers and for Linux admins is a request Kumar is hearing from many CIOs. “It’s not just that the SQL Server backend runs and performs well on Linux but it has to run in an environment where manageability and tooling come into play.

On Windows Server, the tooling and the management experience is mostly GUI. “In the Linux and the non-Microsoft stack world, there is a lot more emphasis on the command line interface,” Kumar points out. “People think about being able to run Bash scripts, being able to script out the management infrastructure, so a significant portion of the tooling investments we’re making are around that. We want to make the manageability of SQL Server feel very native to Linux admins.”

Making SQL Server feel native to Linux admins means several command line tools for working with SQL Server, on top of the capability to deploy it entirely from the Docker command line. For example, DMVTOOL lets you see SQL Server dynamic management views from the command line, so you can get performance information about the database. There’s also PowerShell scripting support. “PowerShell is getting onto .NET Core and that runs on Linux. We are working on other scripting support like Bash, but there’s a lot of rich support in PowerShell,” says Kumar.

Despite the clear demand for command line tools, Kumar notes that testers who weren’t already familiar with SQL Server have been impressed by the graphical tools like SQL Server Management Studio. “So, we’re looking at how to get some of the very core end-to-end management capabilities exposed through a user interface,” he says.

SQL Server doesn’t bring Windows to Linux

The way Microsoft has brought SQL Server to Linux doesn’t bring a large Windows subsystem with it, but also means it hasn’t had to rewrite some core code.

“Any software that runs on multiple platforms has to create an abstraction layer, so that the code running is not aware of the underlying operating system it’s running on,” explains Kumar. In fact, SQL Server has long had a SQL OS layer on Windows Server that abstracts many of the Windows constructs. “We need a page size higher than Windows supports, we need scheduling constructs a little bit more sophisticated than Windows supports,” he points out.

“What we built is a SQL Platform Abstraction Layer that exposes APIs for memory management, security management, process management or scheduling… all the tasks that run above that are agnostic to the OS they’re running on,” Kumar says. “We picked some code from the Windows core systems to get SQL Server to work. We’re running less than one percent of Windows in the platform abstraction layer and the reason we do that is there are certain libraries we have in SQL Server that come from Windows, like SQL Server XML, which depends on Microsoft XML Core Services, which ships on Windows.”

What the SQL Server team did was combine that less-than-one percent of Windows with technology from a Microsoft research project called Drawbridge, originally designed as a library OS to create very small virtualized containers to run Windows apps on a Windows host OS (Kumar says Drawbridge has an overhead of about 8MB). “That gives us a way that we can implement 30 or 40 application binary interfaces that are very specific to an OS, and we merged that with the SQL OS layer to create the SQL PAL.”

The SQL PAL is self-contained and, most importantly, it’s not going to affect the way you patch and update your Linux servers. “If you change a customer’s patching schedule, that’s almost a non-starter,” Kumar says.

Getting ready for SQL Server on Linux

If you’re interested in running SQL Server on Linux, you’ll want to try out the public preview — not so much to try the features, which should be familiar, but to understand the management and deployment environment.

If CIOs have questions about deploying in their own environment or about specific features they want to see supported, Kumar encourages them to talk to Microsoft now. “Around February to March 2017 we’ll close in on the firm plan for the feature set, based on customer feedback, and share that out more broadly,” he says.

The next step will be a Customer Technology Preview, and Microsoft will work with early adopters to get several production deployments of SQL Server on Linux next year that they can learn from before release.

There’s clearly an opportunity for SQL Server on Linux to take over some roles from SQL Server on Windows, something that’s also simplified by the new consistent programming surface between the different editions, making it easier to develop using SQL Server features like Always Encrypted and choose the right edition and platform when it comes to deployment. “In terms of licensing and pricing it’s just a deployment point,” Kumar emphasizes. “There is no change in price. All the Software Assurance you have, all the licences you have just accrue to this deployment option.”

But you’ll also want to look at areas where SQL Server is newly relevant. “It’s the same programming surface in all editions, even the free SQL Server Express edition on Linux, and that resonates very well with developers,” Kumar says. “With the consistent programming surface and the option of deploying on Linux, they’ve never had a choice like that before.”