Hello!

In case you missed it, a fortnight ago SQLBits was held at the Telford International Centre. Believe it or not this was in Telford. For those of you not in the know SQLBits is Europe’s largest community SQL conference. I suppose the name would suggest that the conference focuses on SQL Server. In fact it’s far more than SQL, it’s really more of the Microsoft Data Platform. Of course whilst there’s plenty of talks on SQL Server subjects such as Clustered Columnstore, SQL Azure and SQL Server on Linux, there’s plenty of talks on tools such as Power BI, Document DB, R Server…. In fact rather than me evangelise SQLBits go and have a read of the website and take a look at the Twitter account.

And so but as mentioned I attended the full four days [1] of SQLBits. And so but then I had hoped that this post would be a sort of mini diary of the days events, with a few random things thrown in.  However I was unfortunately as sick as a dog throughout the four days so the writing will only be focused on SQL Server for Linux as I was wiped from the full day’s pre-cons and was sick the following week. This is also coupled with the fact that I can never sleep well in hotels. And to turn these facts into a trifecta (great word); as I have 2 kids under 4 I’m not used to sleeping undisturbed, so was constantly waking up wondering why I haven’t yet been woken up by my son crying, or a good solid kick in the head from my sleeping, bed-hogging daughter.

SQL Server on Linux

Originally I had in mind to attend the Migrating to SQL Azure  talk, but at the last minute I decided to sit in on the SQL Server on Linux: A Brave New World. It’s not that I’m working on Linux or anything, I just wanted to ascertain (another great word) where this idea is going: because it’s not even in GA, SQL Server on Linux is yet to be used in any serious capacity, yet they have done a stellar job in getting the database engine and many of the features working on Linux in a seriously short space of time. To the point that it will be released  in vNext, (apparently due this year, yet unconfirmed.) But I wanted to reflect upon where Microsoft are with the project and where the experts see this going.

Corrupt Package Cache in Ubuntu

So, off to Linux on SQL Server I go. And being the diligent chap that I am, I installed Ubuntu on my Surface Pro 3 so that I could follow along with the demos and play around a little bit before hand. Unfortunately mssql-tools kept on crashing within Visual Studio Code and prevented me from connecting to the instance. Also, I could not install GIMP or VLC, 2 things I didn’t desperately need but wanted to have installed anyways so I could spend more time using Ubuntu [2].

Of course, one of the fun things about using software that is not quite ready is that you are susceptible to issues like this. But upon running an update through the terminal I saw the following issue:

W: You may want to run apt-get update to correct these problems E: The package cache file is corrupted

OK, package cache is corrupted. A quick googling returned the fact that I needed to remove the offending corrupt packages and then run sudo-apt update. After I ran this mssql-tools ran nicely and it was far more stable. So if anyone else is having this issue, there’s a potential fix. You’re welcome.

I’m not going to review the full day’s pre-con. It was a good presentation on the subject. And the internet is not sparse on info about SQL Server on Linux. But here is few thoughts I had after the pre-con. I’ve also provided links to more detailed talks throughout my discourse.

Thoughts on SQL Server on Linux: The Good

Identical DBEngine

They have managed to migrate the database engine and many of the features pretty darn quickly. This is all down to a Microsoft Research Project called Drawbridge. If you want to know more about how they did it so quickly, go read the SQL Server team’s blog post. If that’s a little too dry for you, go watch the Channel 9 video.

Developers Won’t Notice

Very few features aren’t available, to the point hat a vast majority of databases could run on Linux no issues. The T-SQL syntax is identical. So developers could get a new database out and hosted on Linux in no time.

Who’s It For?

I was sitting there watching the talk and the demos, and I reflected on the different places I’ve worked at wondering where in their environments would SQL Server on Linux work. And as I’ve worked at only Microsoft workshops I can’t see them adopting it. So it’s clearly aimed at Linux-based companies. And I know of one (client confidentiality prevents me from disclosing who) that are very interested in moving away from Oracle and running SQL Server on Linux. So Microsoft may be onto a winner here.

Thoughts On SQL Server on Linux: The Bad

No Dev Tools

OK, there’s vscode, but that doesn’t compare to the fully fledged IDE of Visual Studio/SSDT. And as a guy who thinks that the CI/CD story is really important, to have to still use Windows to do my deb/build/deploy process is somewhat disappointing. Like I get that Drawbridge expedited the migration of SQL Server et al, but I’m not seeing a complete dev tools story here….  I say complete because SQLPackage is available on Linux to run a deploy, but then I still have to find a way of creating a dacpac. Maybe if there’s enough buy-in they’ll figure this out. I hope so. Even a plug-in for Mono would be very welcome. I’ll stop banging this point home soon, but if they want to attract developers who use the LAMP stack and want to see the “M” be “Microsoft SQL Server” then they need something to entice users.

DBA Skills

As little change that there is for developers, and that is entirely a good thing, the same cannot be said for DBA’s. For DBA’s used to Windows there’s a whole new OS: You want perfmon? Tough. Now there’s tools like top/iostat/sar… the list is exhaustive, but it’s a whole new world to get baselines from. Then there’s the matter of monitoring: will the monitoring tool of choice work on SQL Server for Linux: You think DMV’s are the same? Think again.

It’s not all bad as Ola Hallengren’s scripts will work (SQL Agent runs on Linux… huzzah!) And PowerShell also runs on Linux (double huzzah!)

But you may have an uphill battle on your hands convincing Linux Based SysAdmins to install what is essentially a tool that is a Windows clone of bash….

Thoughts On SQL Server on Linux: The Ugly

There’s nothing hideous about it, but there’s a few things that will undoubtedly get fixed up which are a little broken:

The errorlog in SQL Server is still a dos file, so you have to use Dos2Unix to make it legible in Linux.

There’s plenty of references to C:\windows\system32 in the errorlog. In other words, locations that do not exist.

Errors appear that aren’t actually true. I don’t recall specifics right now, but can make the errorlog something that is difficult to trust in.

SQLPackage needs a whole target file path otherwise it saves dacpacs to temp directories that are impossible to find without searching the whole drive.

HA/DR is also something that SQL Server has yet to get right. There are options but it is not a complete story.

There is also a detailed blog post and video from the good people on the SQl Server Team about HA/DR and SQL Server on Linux. All very informative stuff, go and have a read/watch.

Final Thoughts On SQL Server on Linux

I’ve always wanted to see Microsoft run their best products on Linux. There’s no way I’m going to knock something that I’ve always wanted to see. And I want to see a full developer stack become available. It’s just that what with everyone getting so excited about migrating to AWS/Azure/your cloud provider of choice, I think this may just be a bit too late for it to be properly adopted. Maybe.

I am aware that “The Bad” sections was far bigger than “The Good”, but it’s a viable alternative to running on Windows. For Windows companies maybe, just maybe, if I had a replication topology with many subscribers I could see using Linux on SQL Server for the subscribers: particularly where the servers have a low total memory and every GB counts (the theory here being that Linux uses less memory than Windows Sever). All monitoring takes place via the Replication Monitor on the publisher/distributor, and custom monitoring probably won’t need to touch the subscribers. My point is that there would be little need to touch the subscribers for anything outside of them having the databases on them.

Tuesday finished up no time and with me totally failing to go to sleep.

[1] In fact I am not yet done evangelising about SQLBits. See a lot of people seem to forget that this conference is organised entirely by volunteers. Entirely. By. Volunteers. But you would not think of it to be there: over 1,000 people go through the doors, there are over 24 pre-cons, and a ton of talks on the Friday. And on the Friday there is a free party, with a few free drinks thrown in. And this is the night after the pub quiz. And if that is not enough, it is free to attend on the Saturday. And the same high-caliber speakers form the previous 3 days are still around for the Saturday. SQLBits is nuts. It’s brilliant.

[2] I also bought Undertale from GOG……