First tests with SQL Server vNext on Linux and Windows

The IT world is changing. The major players are changing. The old rules are evolving and the old barriers between “gardens” are disappearing. Microsoft is changing, but it’s not alone. The first preview of Microsoft SQL Server vNext for Linux debuted in November 2016. On the same day, Microsoft became a platinum member of the Linux Foundation. And on the same day Google joined the .NET Foundation Technical Steering Group (Google announcement here).

SQL server vNext on Linux and Windows

I’m too curious, so I immediately created a couple of VM on Azure to try the new SQL Server on Linux, Ubuntu 16.04, and on Windows Server 2016. The VMs are D2_V2 (2 core, 7 GB ram). Both virtual machines run on a standard storage account, i.e. traditional hard disks.

The setup on Linux was straightforward and fast. Starting from a running Ubuntu 16.04 and following the documentation, in less than 10 minutes I’ve got a running Sql Server. The installation is done through APT. Before starting, it’s necessary to update the package repositories list and the a simple command installs SQL Server:

sudo apt-get install -y mssql-server

After that, another command allows choosing the SA password and setup the start-up mode of the sql service:

sudo /opt/mssql/bin/sqlservr-setup

That’s all. SQL Server is running on Linux! But bear in mind that SQL Server for Linux lacks a lot of features compared to the full version for Windows. Details here. You should also install the tools sqlcmd and bcp for Linux.

On Windows Server 2016 the installation was much longer: the iso or cab needed to be downloaded first. The well-known (old?) graphical setup wizard has many options and executes a lot of checks during the setup. It required more than 40 minutes to complete.

Connecting from a remote machine

I managed both the SQL servers directly from my PC through the internet using the SQL Management Studio (it’s recommended to upgrade to the latest version. Previous version sometime hangs managing Linux machines).

To connect to the remote machines, I needed to add a security rule to allow inbound TCP traffic on port 1433 on both virtual machines. Important note: you should restrict the allowed source to your public IP. It’s not a good idea to expose a sql tcp port to the full internet.

On Windows Server 2016 it’s also required a similar rule on the internal firewall. On the other hand, Linux is already open to accept connections (perhaps too much open, in my opinion).

Using the latest SQL Server Management Studio, I created an empty database on both the VMs. A funny behavior: the paths from the Linux machine are always prefixed with a “C:”, so the resulting path is something like: C:\var\opt\mssql\data\mytestdb.mdf

Restore a database

I downloaded the sample AdventureWork2014 backup from Microsoft site and restored it on both VMs. The command was the same:

RESTORE DATABASE [AdventureWorks2014]

FROM DISK = N'C:\Fabry\sql_backup\AdventureWorks2014.bak'

WITH FILE = 1,

MOVE N'AdventureWorks2014_Data'

TO N'C:\Fabry\mssql_data\AdventureWorks2014_Data.mdf',

MOVE N'AdventureWorks2014_Log'

TO N'C:\Fabry\mssql_data\AdventureWorks2014_Log.ldf',

NOUNLOAD, STATS = 5

RESTORE DATABASE [AdventureWorks2014]

FROM DISK = N'C:\home/Fabry/db_backup\AdventureWorks2014.bak'

WITH FILE = 1,

MOVE N'AdventureWorks2014_Data'

TO N'C:\var\opt\mssql\data\AdventureWorks2014_Data.mdf',

MOVE N'AdventureWorks2014_Log'

TO N'C:\var\opt\mssql\data\AdventureWorks2014_Log.ldf',

NOUNLOAD, STATS = 5

Some performance tests

AdventureWorks2014 database is not so big for a D2_V2 virtual machine. So I decided to “fatten” it using the script from sqlskills.com. It creates two new big tables, Sales.SalesOrderHeaderEnlarged and Sales.SalesOrderDetailEnlarged.

The first run of the script was done without changing the predefined auto-growth of the database files. The resulting mdf was 1.5 GB and the LDF was 5 GB. A lot of time was consumed by allocating new disk space. This is the normal behavior of sql server and both the versions behaved the same way. I did a second run, after removing the two new tables. As expected it was faster.

Linux was faster than Windows on both cases. It’s surprising. I expected SQL server on Linux slower than Windows. I repeated the tests and the result are the same.

I did other tests running different queries and Linux was always faster. I repeated the test after couple of days. The VMs were shutdown, deallocated and restarted. The results were more or less the same. I’m still investigating about these strange results.

(time in milliseconds)

Query_0

SELECT count_big(1) FROM

HumanResources.Department as T1, HumanResources.Department as T2,

HumanResources.Department as T3, HumanResources.Department as T4,

HumanResources.Department as T5, HumanResources.Department as T6,

HumanResources.Department as T7, HumanResources.Department as T8

Query_1

CHECKPOINT

DBCC DROPCLEANBUFFERS

SELECT distinct

sales.SalesOrderHeaderEnlarged.salesorderid,

Production.Product.Name

from Sales.SalesOrderHeaderEnlarged

inner join Sales.SalesOrderDetailEnlarged

on Sales.SalesOrderHeaderEnlarged.SalesOrderID =

Sales.SalesOrderDetailEnlarged.SalesOrderID

inner join Production.Product

on Production.Product.ProductID =

Sales.SalesOrderDetailEnlarged.ProductID

where linetotal > 20000

Query_2

...like Query_1 but without CHECKPOINT and DBCC DROPCLEANBUFFERS

Query_3

SELECT count(1)

FROM [Sales].[SalesOrderDetail] as SOD1,

[Sales].[SalesOrderDetail] as SOD2

WHERE SOD1.ProductID=SOD2.ProductID

AND SOD1.UnitPrice > SOD2.UnitPrice

Query_4

SELECT distinct [CarrierTrackingNumber]

FROM [Sales].[SalesOrderDetailEnlarged]

[UPDATE 2016-12-04]

Following the notes from Marco, I started a larger session of tests using different machine size on Azure. The results are very contradictory and inconsistent, in particular using virtual machine of the DS series. Different runs in different time in the day, showed different results. I can only guess about the reasons (CPU overcommitment?). On the other hand, DSv2 series showed a more stable behavior. The table below shows the results.