Chapter 2
How SQL Server on Linux Works
In This Chapter
Most SQL Server users will be surprised to learn that SQL Server was actually born on the Unix platform, first ported to OS/2 and later to the Windows NT operating system. So in its early days, SQL Server was in fact a multiplatform technology. SQL Server was originally written by Sybase and released for OS/2 and Windows NT in an agreement with Microsoft. After its business relationship with Sybase ended, Microsoft secured the code base of the database engine and decided to make SQL Server a Windows-only technology, which remained the case for more than two decades.
A second important architecture feature for the future Linux implementation was the development of SQL Operating System (SQLOS), which was written for the SQL Server 2005 release. SQLOS was created to exploit the newly available hardware and to provide the database engine with more specialized services than a standard operating system could afford. SQLOS is also the SQL Server application layer responsible for managing all operating system resources, such as nonpreemptive scheduling, memory and buffer management, I/O functions, resource governance, exception handling, and extended events. SQLOS was never intended to be a platform-independent solution. Porting SQL Server to other operating systems was not in the plan during the SQLOS development.
In an astonishing announcement in March 2016, Microsoft surprised the industry by declaring that SQL Server would be available on the Linux platform sometime in 2017. After that moment, it seems that the industry expected a real port that compiled the SQL Server C++ code base into a native Linux application. Microsoft later indicated that this version would be named SQL Server 2017 and would be available on Red Hat Enterprise Linux, Ubuntu, and SUSE Linux Enterprise Server, in addition to Docker containers. Docker itself runs on multiple platforms, which means that it would be possible to run the SQL Server Docker image on Linux, Mac, and Windows.
But when the first parts of the beta version of the software, called a Community Technology Preview (CTP), were released in November 2016, we were in for another surprise: instead of a port, SQL Server was able to run on Linux thanks to some virtualization technologies based on Drawbridge, the software result of a Microsoft project completed just a few years earlier.
Microsoft soon released more information about how SQL Server on Linux works; its architecture includes several components such as the Drawbridge technology, a revamped SQLOS, and the SQL Platform Abstraction Layer (SQLPAL), the layer that enabled Microsoft to bring Windows applications to Linux. SQL Server 2017 was finally released in October 2017.
This chapter covers some SQL Server history with different operating systems and explains some of the details about how SQL Server on Linux works. This includes describing the interaction between SQL Server and the operating system, decisions regarding its architecture, its software implementation, and other related topics.
The Sybase Years
Microsoft SQL Server was born indirectly as part of an agreement between IBM and Microsoft to develop OS/2, the operating system that was planned to be the successor of MS-DOS. Before the release of this operating system, IBM added a database product to its version of OS/2, so Microsoft needed a database product as well. Microsoft turned to a new company, Sybase, to license its database product. The original agreement between Sybase and Microsoft was to port its database engine, DataServer, originally developed for Unix platforms, to the OS/2 operating system.
Later in the process, to appeal to the large dBASE community, a marketing and distribution deal was also signed with Ashton-Tate, and the final product was named Ashton-Tate/Microsoft SQL Server, which was eventually released in May 1989. It’s interesting to note that Sybase, the company that wrote the original software, was not included in the product name.
The SQL Server database engine was ported to OS/2 from the Unix source code, and Ashton-Tate/Microsoft SQL version 1.0 debuted in OS/2 in May 1989. The agreement with Ashton-Tate did not last long, however, and the product was soon renamed Microsoft SQL Server; its first version under this name, version 1.1, shipped in the summer of 1990.
OS/2 was created as a replacement for MS-DOS, but the market was in for a huge surprise. Instead of moving from MS-DOS to OS/2, users were moving to Windows 3.0. Windows 3.0 had been an unexpected huge success in the computer industry. So it was essential for SQL Server to support Windows as a client as well. To achieve this, Microsoft provided tools for developers to create Windows applications that could connect to SQL Server. (Note that Windows was not an immediate success either, as the first two versions of the software were mostly ignored.)
At that time, Microsoft was also working on an operating system kernel, originally intended to be used on OS/2, but that later became a new operating system called Windows NT, which was released in 1993. For Microsoft, the obvious next step was to port SQL Server to Windows NT.
Suddenly the SQL Server team in Microsoft was faced with the dilemma of simultaneously developing versions for both OS/2 and Windows NT; some of the issues faced with this development would be revisited many years later for the Linux release. The team was faced with a new set of problems: they needed to add an abstraction layer to hide the differences in the operating systems, they’d need major reengineering for both versions of SQL Server, or they’d take a lowest common-denominator approach and not fully use the services or features of either system.
The decision was to keep SQL Server as a Windows NT–only technology, even when at the time it was a new operating system with no installed user base. So this time, SQL Server version 4.2 for OS/2 was ported to Windows NT. Because there was no need to be concerned about portability to other operating systems or creating an abstraction layer, the team could focus on doing the best possible job for only one operating system, Windows NT. It was decided that the goals of portability were in conflict with the goal of creating the best possible software for Windows NT. The final product, SQL Server for Windows NT, would then be more than a port from the OS/2 code, because it required rewriting the kernel of SQL Server, the part of the software that interacts with the operating system.
The initial release, Windows NT 3.1, was shipped on July 27, 1993, and was written in C, C++, and assembly language. (There was no version 1.0 or 2.0.) The first beta version of SQL Server for Windows NT was released in October 1992. SQL Server 4.21a, released in 1993, was the first version to run on Windows. This was the last full port of SQL Server, because Microsoft got the rights for the 4.21a code base and, as mentioned earlier, later changes of the product would focus only on Windows NT without consideration of any future possibility of porting back to Unix or any other operating system.
In those early days, Microsoft was competing with Sybase, which was releasing System 10 for Windows NT. Sybase System 10 was designed in a more portable manner and as a consequence could not perform as well as SQL Server, which was designed, written, and optimized exclusively for Windows NT. In April 1994, Microsoft and Sybase announced the end of their development agreement, and each decided to develop its own SQL Server products. The last version of SQL Server for OS/2, SQL Server 4.2B, was released in 1993.
Although no additional major development happened during the following releases, at least for the purposes of this story, Microsoft would rearchitect the entire database engine and include a brand-new query processor for its SQL Server 7.0 release, which shipped in 1998.
Although the first version of SQL Server was version 1.X, there was no version 2.0 or 3.0. Later, Microsoft would also skip version 5.0, moving from version 4.2 to release 6.0. Finally, instead of using version 8.0 for the SQL Server 2000 release, Microsoft would use a new company-wide naming standard for many of its products, which continues to this day, with SQL Server 2017, although their internal version numbers, which can be returned by using the @@VERSION function, still shows the original version number. For example, SQL Server 2000 was version 8, and the full build name for RTM (release to manufacturing) was 8.00.194. The current version, SQL Server 2017, is version 14.0, and the current software RTM-CU2 shows 14.0.3008.27.
NOTE
For more about the history of SQL Server, at least until SQL Server 2000, read the first chapter of the Inside Microsoft SQL Server books by Kalen Delaney and Ron Soukup, which can also be found online at the bottom of https://www.sqlserverinternals.com/resources.
SQLOS
As mentioned, SQLOS was another very important development for the future Linux implementation. SQLOS was a new operating system layer whose purpose was to provide the database engine with performance and scalability improvements by exploiting the new available hardware capabilities and providing the database engine with more specialized services than the general ones an operating system can offer.
SQLOS was first available on the SQL Server 2005 release. Although in part SQLOS was created to remove or abstract away the operating system dependencies, it was not originally intended to provide platform independence or portability, or to help in porting the database engine to other operating systems. Its first purpose was to exploit the new available hardware capabilities, including Symmetric Multithreading (SMT) and multi-CPU configuration with multiple cores per socket systems, computers with very large amounts of memory, non-uniform memory access (NUMA) systems, and support for hot memory and CPU add-ons and removals. Database engines could benefit from these new hardware and hardware trends.
SQLOS also became the SQL Server application layer responsible for managing all operating system resources, and it was responsible for managing nonpreemptive scheduling, memory and buffer management, I/O functions, resource governance, exception handling, deadlock detection, and extended events. SQLOS performs these functions by making calls to the operating system on behalf of other database engine layers or, as in the cases of scheduling, by providing services optimized for the specific needs of SQL Server. The SQLOS architecture is shown in Figure 2-1.
Figure 2-1 SQLOS architecture
The main architecture design decision behind SQLOS replaced some of the available operating system services. Operating system services are general-purpose services and are sometimes inappropriate for database engine needs, because they do not scale well. Instead of using generic scheduling facilities for any process, scheduling can be optimized and tailored to the specific needs of a database engine, in this case, SQL Server. The main difference between the two is that a Windows scheduler is a preemptive scheduler, while with SQL Server, the decision was to use a cooperative scheduler (aka nonpreemptive scheduler). This implementation improves scalability, because having threads yield voluntarily is more efficient than involving the Windows kernel to prevent a single thread from monopolizing a processor.
The most important objects on SQLOS are memory and CPU nodes, schedulers, and tasks. Schedulers and tasks are easily visible and well known to the SQL Server user through the use of dynamic management views (DMVs) such as sys.dm_os_schedulers and sys.dm_os_tasks. In SQL Server, each user request is an operating system thread, and when a client connects, it is assigned to a specific scheduler. Schedulers are handled at the SQLOS level, which also handles tasks and workers, among other functions. For example, the SQL Server task execution process is shown in Figure 2-2. SQL Server schedulers were introduced with SQL Server 7, but that version still relied on the Windows scheduling facilities.
Figure 2-2 SQL Server task execution process
SQL Server will also detect and work with the then-new NUMA systems. SQL Server 2000 Service Pack 4 included limited support for NUMA systems. Full NUMA support was added when SQLOS was released with SQL Server 2005. Software NUMA is automatically configured starting with SQL Server 2014 Service Pack 4 and SQL Server 2016 (some support was also possible before but required manually editing the Windows registry). Starting with these versions of the database engine, whenever SQL Server detects more than eight physical cores per NUMA node or socket at startup, software NUMA nodes will be created automatically by default.
SQLOS was never intended to be a platform-independent solution, but rather a way to provide purpose-built operating system services to the database engine for performance and scalability with the SQL Server 2017 release.
NOTE
For more details about SQLOS, read the paper “A New Platform Layer in SQL Server 2005 to Exploit New Hardware Capabilities and Their Trends” by Slava Oks, at https://blogs.msdn.microsoft.com/slavao/2005/07/20/platform-layer-for-sql-server/. In addition, in the paper “Operating System Support for Database Management,” Michael Stonebraker examines whether several operating system services are appropriate for support of database management functions such as scheduling, process management, interprocess communication, buffer pool management, consistency control, and file system services. You can use your favorite search engine to find this research paper online.
The Industry Changes
In March 2016 at the Data Driven event in New York, Microsoft surprised the entire technology community by announcing that SQL Server would be released on the Linux platform. In November of that year, at the Microsoft Connect() event in the same city, it announced that the first bits of the technology, the first CTP of the then-named SQL Server vNext, was available for download. At the same event, the Linux Foundation, a nonprofit organization advancing professional open-source management for mass collaboration, announced that Microsoft had joined the organization as a platinum member.
The SQL Server on Linux announcement mentioned that this release would include almost all the features of SQL Server for Windows. Among the features not included for this release were transactional replication, merge replication, Stretch DB, Polybase, distributed query with third-party connections, system extended stored procedures, file tables, CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set, database mirroring, and buffer pool extension. Also some services such as SQL Server Browser Service, SQL Server R Services, StreamInsight, SQL Server Analysis Services, Reporting Services, Data Quality Services, and Master Data Services would not be available on the current release. In addition, only SQL Server default instances (as opposed to named instances) would be available on Linux. You can see the entire and current list of unsupported features and services at https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes.
Several other announcements impacted this SQL Server release as well. During the same Microsoft Connect() event, the first service pack for SQL Server 2016 was announced. Usually a service pack is not a big deal, but this one was different. Microsoft stated that starting with this service pack, all programmability features would be available on all editions of SQL Server. Features such as In-Memory OLTP, columnstore indexes, database snapshots, compression, partitioning, Always Encrypted, Row-Level Security, Dynamic Data Masking, and Change Data Capture, among others, would be available on all the editions of SQL Server, from the free Express edition to Standard and Enterprise. Obviously, this impacted SQL Server on Linux as well, because it applied to every SQL Server release after SQL Server 2016 Service Pack 1.
In November 2014, Microsoft announced that it would open-source the full server-side .NET Core stack and that the open-source .NET would be expanded to run on Linux and Mac OS X in addition to Windows. Visual Studio Community 2013 was also announced as a new, free, and fully featured edition of Visual Studio. SQL Server Developer edition, which is not meant to be run in production environments, was also made available for free according to a separate announcement in March 2016.
Finally, on August 18, 2016, Microsoft announced that it would open-source PowerShell and make it available on Linux. PowerShell is a task-automation command-line shell and scripting language built on the .NET Framework. As I started my career in the Unix world, even before Windows NT, PowerShell for me was just like a Unix shell. In a similar way, Microsoft made available Bash shell on Windows as part of a component called the Windows Subsystem for Linux (WSL). A shell is a user interface to access operating system services, and in the case of Unix, it uses a command-line interface. It is called a shell because it is a layer around the operating system kernel. I will cover more about Unix shells in the next chapter.
Project Helsinki
There are reports that Microsoft had been contemplating porting SQL Server to Unix and Linux as early as the 2000s. One example is the article “Porting Microsoft SQL Server to Linux” at https://hal2020.com/2011/07/27/porting-microsoft-sql-server-to-linux/, by Hal Berenson, who retired from Microsoft as a distinguished engineer and general manager. Also, in an interview with Rohan Kumar, general manager of Microsoft’s Database Systems group, he mentioned that there were a couple of discussions in the past about porting SQL Server to Linux, but such a project was not approved. For more details of the interview, see https://techcrunch.com/2017/07/17/how-microsoft-brought-sql-server-to-linux/.
More recently, starting around 2015, there was a new attempt to port—or in this case to release—SQL Server on Linux. This was called Project Helsinki. The following were the project objectives of releasing SQL Server on Linux:
It would cover almost all the SQL Server features available on the Windows version. Current exceptions were documented earlier in this chapter.
It would offer at least the same security level as the Windows version.
It would offer at least the same performance as the Windows version.
It would ensure compatibility between Windows and Linux.
It would provide a Linux-native experience—for example, installation using packages.
It would keep the continued fast pace of innovation in the SQL Server code base, making sure that new features would appear on both platforms simultaneously.
If you have followed the history of SQL Server so far, you may wonder, since SQL Server was born on the Unix platform and later ported to OS/2 and Windows, why not port it back to Linux? Truth is, however, that after two decades as a Windows-only technology, the code base had diverted hugely from its Unix origins.
Nevertheless, porting during this project was still a consideration. Porting the application from one operating system to another would require using the original source code, making the required changes so it would work on the new system, and compiling it to run as a native application. Porting SQL Server to Linux, however, would require the review of more than 40 million lines of C++ code to make changes so it would work on Linux. According to the Microsoft team, this would be an enormous project and would face the following challenges:
With more than 40 million lines of C++ code, porting would take years to complete.
During the porting project, the code will still be changing. New features, updates, and fixes are performed all the time. Catching up with the current code base was a serious challenge.
In addition, not all operating system dependencies are handled by SQLOS. SQL Server makes a huge number of Windows calls outside, too. After more than two decades of SQL Server being used on the Windows platform only, the product had a large number of references to Windows libraries, which fall into the following three categories:
NT kernel (ntdll.dll)
Win32 libraries
Windows application libraries
The SQL Server team listed the last category, Windows application libraries, as the one with more complex dependencies. Some of these Windows application libraries were Microsoft XML Core Services (MSXML), the Common Language Runtime (CLR), components written in Component Object Model (COM), the use of the Microsoft Distributed Transaction Coordinator (MSDTC), and the interaction of the SQL Server Agent with many Windows subsystems. It was mentioned that porting even something like SQLXML would take a significant amount of time to complete.
So the team, according to several posts and interviews mostly by Slava Oks, partner group engineering manager at the SQL Server team, was considering alternative choices to porting in order to complete the project in a faster way, or at least in a reasonable amount of time. This is where Drawbridge came to the rescue.
A Virtualization Surprise
Although the original Microsoft announcements did not mention whether SQL Server on Linux was going to be a port, the entire technology community assumed it would be, and everybody expected that SQL Server was going to be a native Linux application. It also seems that the first sources reporting that SQL Server on Linux was not a port, but instead was using some sort of virtualization technology came from outside Microsoft.
SQL Server CTP 1 was released on November 16, 2016, and just two days later an article at The Register indicated that SQL Server on Linux was not a native Linux application but was instead using the Drawbridge application sandboxing technology. The article stated that Drawbridge references could be found on the installation, for example, at the /opt/mssql/lib/system.sfp library, which could be easily confirmed.
NOTE
You can still read the article, “Microsoft Linux? Microsoft Running Its Windows’ SQL Server Software on Linux: Embrace, Extend, er, Enter,” at www.theregister.co.uk/2016/11/18/microsoft_running_windows_apps_on_linux.
I sensed at the time that the SQL Server community had mixed reactions to this news. It may have been the disappointment that SQL Server was not going to be a native Linux application, but it later also turned into curiosity, and everybody wanted to know how it worked and how Microsoft was able to run a very complex application such as SQL Server on a different platform without a code port. There was also the initial concern of whether this Linux implementation would offer the same performance as its Windows counterpart.
Drawbridge
Drawbridge was a Microsoft Research project that created a prototype of a new form of virtualization for application sandboxing based on a library OS version of Windows. Drawbridge was created to reduce the virtualization resource overhead drastically when hosting multiple virtual machines in the same hardware, something similar to what Docker would do later. Drawbridge was a 2011 project, while Docker was released as open source in March 2013. Drawbridge, according to Microsoft, was one of many research projects that provided valuable insights into container technology.
In simple terms, Drawbridge took the Windows kernel to run it in user mode in a process to create a high-density container that could run Windows applications. So it was basically taking the entire operating system, Windows, in user mode. The original intention was to use Drawbridge to host small applications in Windows Azure. At the same time, Microsoft starting testing running Drawbridge in other operating systems so they could use this technology as a container to run a Windows application on another platform. One of those platforms tested was Linux.
NOTE
You can find more details about Drawbridge on the Microsoft Research page at www.microsoft.com/en-us/research/project/drawbridge/.
Looking for a Shortcut
It is interesting to learn how the decision was made to use Drawbridge. Drawbridge was used to run small Windows applications on an operating system called Midori. Midori is an operating system Slava Oks help to write, and during a specific project implementation, there was the requirement for Midori to run some Windows applications. Though Midori did not directly have such capability, Drawbridge was suggested as a possible solution. Within a few months, developers had the required Windows applications running in Midori using Drawbridge.
Later, when confronted with the problem of running SQL Server on Linux, someone suggested using Drawbridge to run a Windows application such as SQL Server on another platform, in this case, Linux. Problem was, SQL Server is not a simple Windows application, so the idea originally was not taken too seriously. However, Drawbridge was tested on a few operating systems, and it was learned that a member of the Drawbridge team, Andrew Baumann, already had a working prototype of Drawbridge on Linux partially up and running.
Using Drawbridge, the SQL Server team was able to boot SQL Server on Linux within 30 days of starting the project. Obviously, this was just a prototype, but it showed that this was feasible and that the entire project could be achieved without porting. In this way, the team could focus on testing that all the functionality was there and that everything worked. It also gave the team the possibility and advantage of starting work on the remaining parts of the project, including features that would be intrinsically different on a Linux platform, such as high availability, disaster recovery, security, and performance.
The Drawbridge architecture, as shown in Figure 2-3, offered the following components:
Figure 2-3 Drawbridge architecture
A library OS that was capable of hosting other Windows components For example, using library OS, it was possible to implement MSXML, CLR, and other functionality, which was essential to bring all SQL Server features to the Linux release.
A picoprocess This is a process-based isolation container with a minimal kernel API surface. This part of Drawbridge was not used in the SQL Server project.
Since the Microsoft Research project was complete and there was no support for Drawbridge, the team also took ownership of the Drawbridge code base—and, in fact, they owned the code for all the components involved in the project, including the Linux host extension, which I cover next. Finally, it is also interesting to note that by using these technologies, in theory, Microsoft could bring any other Windows application to Linux without doing a port.
SQLPAL
Although for some SQL Server users, it may seem like SQLOS already provided the abstraction functionality to move SQL Server to another platform, that was not the case with Linux. Though SQLOS was more about services and optimizing for new hardware than abstraction, Drawbridge provided the abstraction that was needed. Marrying these two technologies was the appropriate solution. In fact, the Drawbridge library OS component provided the required functionality. (The second component, the picoprocess, was not required for the project.) The SQLPAL architecture is depicted in Figure 2-4, which also includes a host extension layer that was added on the bottom of SQLOS to help SQLPAL interact with the operating system. Host extension is the operating system–specific component that maps calls from inside SQLPAL to the real operating system calls.
Figure 2-4 SQLPAL architecture
Remember, of course, that this did not mean that completing this project was just a matter of running the current SQL Server Windows executable file on Drawbridge and fixing a few bugs. There was still a large amount of work to do.
As mentioned, earlier in the history of SQL Server, back when the developer team was debating whether to continue releasing the product on both OS/2 and Windows NT, it was evident that some platform abstraction layer (PAL) needed to be created. No PAL was created at that time, however, because the decision was to continue SQL Server as a Windows-only platform. This did not happen when SQLOS was released, as it was not part of the original design.
But now, many years later, when the team faced the dilemma again, a PAL was created to abstract the calls and libraries from the underlying operating system. SQLPAL was created to separate all operating system- or platform-specific code in one layer while keeping the rest of the SQL Server code base operating-system agnostic.
After months of testing SQL Server on Linux, the product offered some duplication of services, as shown in Figure 2-5. As you can see, all components, SQLOS, library OS, and the Linux host extension implemented duplicated services such as object management, memory management, trading and scheduling, synchronization, and I/O services.
Figure 2-5 Functionality overlap
The final architecture needed only single components instead of duplications, however. This was the birth of SQLPAL. The decision was made to keep most of SQLOS, make SQLOS the core, and use some parts of both the Drawbridge library OS and the Linux host extension. The final SQL Server system architecture is depicted in Figure 2-6.
Figure 2-6 SQL Server system architecture
Finally, Figure 2-7 shows the process model when SQL Server is running on Linux. SQL Server runs in a Linux process. The Linux host extension is a native Linux application that first loads and initializes SQLPAL. SQLPAL then starts SQL Server.
Figure 2-7 Process model
Summary
Even if you are an expert SQL Server user and have worked with the technology for years, you may think that this database engine has always been a Windows-exclusive technology. SQL Server in fact started as a multiplatform technology, and its roots actually go back to an operating system called OS/2 and even to Unix. To understand how SQL Server came to the Linux platform and Docker containers, this chapter covered some historic perspectives that mirrored some of the same challenges that were faced today.
SQLOS was created as a platform layer designed to exploit new hardware capabilities and provide database engine–specialized services, but it was never designed to provide platform independence or portability to other operating systems. SQLOS was used again, however, for the Linux release.
When working on the SQL Server on Linux project, the team considered a code port, but since this would be an enormous project that would take years to complete, other solutions were considered, including using the Microsoft Research project Drawbridge. Drawbridge and SQLOS were used on the final release of SQL Server on Linux implementation.