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

Cross-platform tools with Visual Studio Code

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

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.®