An initiative of Mobiliar IT

First experiences with SQL Server 2017 on Linux and the Early Adopter Program

Since December 2016 we have been testing SQL Server vNext, now called SQL Server 2017, on Linux and on Windows. Since 2017 we also have been participating in the Early Adopter Program (EAP) for SQL Server, offered by Microsoft to support and guide migrations to the new version.

On June 14th we were invited by Microsoft Switzerland to a Data Amp Partner Event in Wallisellen/Switzerland, to present our experiences we made in the EAP and while using SQL Server 2017 on Linux. It was a nice event with various presentations by Microsoft, including mine. Just one day later, on June 15th, I held the presentation once more, this time for the Swiss Chapter of PASS, which started its local meetup series in Bern that day.

For all those who missed the presentations, I will give a wrap-up below. The slides can be found on our Papers / Docs page or directly behind this link.

SQL Server 2017 and EAP – Why we are interested?

Since our self-developed applications mostly operate on Linux and/or in Docker and up to now we mostly worked with Oracle databases, the announcement of Microsoft to bring SQL Server to both systems immediately got our attention. This could be a way to make SQL Server more interesting for our developers! And since we like to thoroughly test a technology before the first developer asks for it, we wanted to have a look at it as soon as possible.

With the announcement of the EAP, a second reason to start was given. Why try out all on our own when instead we could also get professional support?
As we already are beta customers for Oracle, we know the advantages such programs can offer: Direct contact to product and development teams, fast reaction on feedback and problems, maybe even the possibility to ask for or point out the need for certain features.

What application should one migrate to Linux?

The EAP encouraged a project to be brought to production with the early SQL Server  builds on Linux. Here, too, the answer was already given: For years now we have been developing our own SQL Server maintenance, reporting and monitoring solutions, a set of PowerShell scripts using SQLPS and SQLPSX, reporting information about servers, instances, databases, processes, queries and much much more to a SQL Server 2016 database. From there the data is searchable by SQL but also by the UI we created using Oracle Application Express (APEX).

Since there were often new counters to be collected to investigate a performance issue “just now and for this instance”, the scripts were directly developed against the production database and really needed a development installation to be set-up from scratch. A perfect use-case for the EAP, building this environment first in development and then deploy it to production.

If you are interested in the reporting and monitoring solution we developed, stay tuned for future posts and presentations!

The EAP

The registration for the Early Adopter Program went quite smoothly: We filled out the form, exchanged a small number of mails, discussed the set-up and target application by phone and we were in. Really simple!

From this point on we were guided and supported by Microsoft’s staff, calling regularly to check on status, road blocks, problems, questions and to discuss the next steps.

This was quite helpful, like when we stumbled over some problems in one of the very early CTPs, like a SQL Server restart taking more than 40 minutes with just the tiniest database in it but synchronized in an AlwaysOn Availability Group. Their standard response to our reports was like “we know it already, it’s already fixed in the next CTP which is just about to go public”. Sure enough, two days later the new CTP was there and the issue resolved. This prevented us from searching possible causes and digging for hurdles which were not there.

The same was the case for a rather special test: Out of curiosity, as soon as there was a CTP supporting AlwaysOn Availability Groups, we set up a  “clusterless group” (CLUSTER_TYPE = NONE) between two Linux machines and a Windows server. This resulted in a repeatable split brain occurring every time we did a failover from Windows to Linux. The new Linux primary came up, the second Linux node listening to it and synchronizing, but the Windows machine still claiming to be the primary and accepting writing connections like the real Linux primary node. There was an answer quite quickly, that this was already known, would be resolved in a later build, and that we should initiate the failover by T-SQL and not using Management Studio dialogs.

Like this it went on for some months, up to now, as the last step is going into production. The Linux machines are being prepared, we soon can start with a phase of parallel operation. Stay tuned for upcoming posts!

SQL Server 2017 on Linux

SQL Server 2017 on Linux was installed incredibly quickly:  it just took a few seconds! The same result for the updates. So, machine deployment is made faster, too, which is again a big advantage of the Linux version. The configuration is not as comfortable (yet?) as under Windows, since you do not use Management Studio but a commandline tool: mssql-conf. This is a thing for the windwos SQL Server DBAs: Get used to Linux, this is a commandline – sorry: shell – driven environment. Speaking of it, also to have more than the default instance would be great, but it is also still a CTP, so let’s see what will be done there until the official release.

The step to CLUSTER_TYPE = “EXTERNAL” with CTP2 to give pacemaker full control over the Always On Availability Groups is a good idea, since it greys out the Mgmt Studio possibilities to do a failover and by doing so prevents split brains or other unwanted behavior. But it is also a strange feeling, having gained control from WFCS to SQL Server with Always On Availability Groups  just to give it back now to a cluster application…

Another not yet satisfying fact is that in combination with pacemaker 3 replica are required for HA, since with no synchronizing secondary the primary will be set to read-only. Contrary to Windows, for which two servers were sufficient and you only need a file share whitness to build the quorum, this is a small drawback.

Looking back

So far, bringing SQL Server to Linux seems to be a really good step by Microsoft, closing the gap between developers preferring Linux and companies preferring SQL Server. Surely, this will not mean companies will switch completely from Windows to Linux. But if they run their applications on Linux, they now have the possibility to run SQL Server on the same OS. Or if they insisted on Linux all the time, now they can use SQL Server as well. Moving the data from Windows to Linux is done by backup-restore either for just the switching point in time or for setting up an Always On Availability Group to keep the Linux system current until doing the failover to the Linux machine and removing the windows one from it.

Offering an Early Adopter Program was a decent step, too, since probably many companies would like to try out new technologies or versions, but “do not have the time”,
“do not want to debug all teething troubles” or just wait for official releases. Taking part in an EAP offers quite close guidance and fast help and saves us from investigating in bugs being fixed already in the upcoming preview. That’s an invaluable gift which we are really thankful for. And we hope that many other took or will take part in this profitable offer, too.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Basic HTML is allowed. Your email address will not be published.

Subscribe to this comment feed via RSS

%d bloggers like this: