I’ll be honest, when I first learned about SQL Server on Linux, I didn’t get it. It took like an hour for it to make sense to me. After a week of talking to customers, a lot of whom were super interested in deploying SQL Server on Linux or SQL Server in Docker it just makes even more sense to me.
Let’s run down the various use cases, then let’s dive into each one and break them down.
- LAMP stack developers
- Small SQL Instances for 3rd party apps
- Shops which are primarily Linux shops today and want/need to deploy SQL Server
- Shops who want to replace Oracle, MySQL, and/or PostgreSQL
Let’s explore each of these a little more, shall we?
LAMP stack developers
There’s LOTS of developers out there that when they start a new project they fire up new containers and grab PHP, a database (MySQL, PostgreSQL, etc.) and go to town programming away. Microsoft SQL Server being available for Linux and having it being just a quick docker download (or yum install) away gives them the option of having a full enterprise class database behind their application with a quick, easy 2 minute config that’s like 3 questions, for the same cost in development. And when they go to deploy their application to their users (customers) they have the confidence of telling them that the database they they’ve selected is Microsoft SQL Server, supported by a multi-billion-dollar company which they are probably used to dealing with already.
The developer can build their application with confidence knowing that the programming surface is identical between the Linux and Windows versions of the product so they can expect it to react exactly the same no matter if the customer installs it on RedHat, Ubuntu, Docker, or Windows.
Small SQL Instances for 3rd Party Apps
An interesting potential workload that a car manufacture brought up while I was working the booth at the Microsoft Ignite conference, was small database applications like 3rd party applications, or even 1st party applications but which are typically single core VMs with small memory footprints. Today they take up a large percentage of the VMware farm, and what did I think of moving these into a Linux host running Docker containers and running really small containers (assuming that the CPU and memory were fine). My response, sounds like a great use of the docker deployment of SQL Server on Linux.
The docker deployment is going to give you a really thin, really lightweight deployment option as you don’t have to worry about deploying the OS within the VM. In a large host running dozens of containers this could led to some major resource savings.
There are lots of companies which are primarily Linux and don’t want to run or manage Windows servers for one reason or another, but they often have to because they need to support SQL Server to support either line of business applications or back office business applications such as HR applications or payroll applications, even if those applications are Java applications running on Linux servers.
Now those companies can toss their Windows servers and run Linux OSes that they are used to running and still have the SQL Server database that they need to run their applications. The power to do what you need to do, to run your organization and the flexibility to do it the way that you want to do it without any vendor lock in. It’s like Microsoft has been listening to the Linux community or something (no they aren’t going to open source SQL Server, stop asking).
I was shocked the number of people who came to the booth, knowing that I do not work for Microsoft, telling me that this gives them the ability to replace Oracle, MySQL or PostgreSQL in their shops. Most of these companies were primary Linux (or all Linux) server OS shops so suddenly having the flexibility to get out from under the thumb of their Oracle sales rep while staying on their server OS platform of choice, is huge. One customer came up and said that even after the cost of converting their applications from Oracle to SQL Server (which was not going to be cheap), and the cost of paying for the SQL Server licenses, the annual savings was still in the millions because of the Oracle licensing costs. I’m guessing that the Oracle sales guy really isn’t going to like that phone call, but his car is nice enough already.
Given that it’s only been out for a few days, that’s good start.
At Microsoft Ignite, Microsoft announced that they are changing the patching cycle for SQL Server. SQL Server will no longer release Service Packs for SQL Server 2017, instead of releasing only CUs and releasing them much more often. CUs will be released monthly for the first 12 months of a new release, then quarterly after that.
Now some people out there in blogger world have gotten all cranky about this. But this is a good thing. The big downside to CUs for years has been that little disclaimer attached to them, that they should only be installed when you are impacted by an issue that the CU specifically fixes. This disclaimer was there because in the dark ages of SQL Server (SQL 2005 or so) CUs weren’t as well tested as Service Packs, they just couldn’t be because the automation wasn’t there to do so.
Now the automation is there to do the testing.
Because of this, we don’t need Service Packs, because every CU is now effectively a Service Pack. The fixes are going to be coming out faster. This doesn’t mean that there are more bugs that need to be addressed, or that the software is more unstable or any of the other conspiracy theory nonsense that I’ve read about this. It simply means that along with the faster release cycle that we saw for SQL Server 2017, Microsoft has accelerated the release cycle for patches as well.
This all means that if there is a bug in the software that impacts our systems that we have to wait for, this means that we will get the patch faster than we’ve ever gotten the patch before. And without having to open a support ticket and get a hotfix patch. We get this via a fully supported CU patch through a normally supported servicing release. This sounds like a good thing to me.
Does this mean that you need to do more patching of SQL Server? Maybe. If you want every possible patch installed to get every possible fix, then yes. But you probably aren’t hitting every possible bug that’s fixed by every possible CU that comes out. If you are you are either pushing the product harder than 99.9% of the users out there, or you are one unlucky shop.
I’d recommend patching SQL as often as you can. If you can patch each month for that first 12 months, excellent. If you can’t get a monthly maintenance window to get those patching installed then shoot for every other month. If you aren’t being currently affected by an issue that’s being resolved by a patch then waiting for an extra month won’t kill you, and if you are, then getting the patch faster than you would have gotten it under the old servicing model and getting the maintenance window to get it deployed really shouldn’t be that big of a deal.
This year the PASS Summit speaker idol has been full of challenges. Usually, self-imposed because of my insane travel schedule this time of year as I ended up doing most of the organizing from the other side of the planet (cry me a river, I know).
Anyway, self pityisn’t the point of this post. This post is because PASS has decided that they are going to move William Wolf from backup speaker to selected speaker. While I congraduate Bill this leaves me with an opening in my lineup. Fortunetly I have plans to account for this. So the new contestant in the second slot on Friday is Joseph Barth.
The lineup for Speaker Idol now looks like this.
Your Wednesday lineup for speaker idol is:
- Jim Donahoe
- Brian Carrig
- Jonathan Stewart
- Robert Volk
Your Thursday lineup for Speaker Idol is:
- Javier Villegas
- Eric Peterson
- Ed Watson
- Dennes Torres
Your Friday lineup for Speaker Idol is:
- Daniel de Sousa
- Joseph Barth
- Tzahi Hakikat
- Simon Whiteley
See everyone at the PASS Summit.
PASS Summit is just a month away. That means there’s only just under a month left to get signed up for Denny’s Azure precon. You might be wondering why you want to take a full day session on Azure infrastructure. And that’s an excellent question. The answer is quite simple. Azure is a massive platform, with a huge number of moving parts which are constantly changing. In this session, we aren’t going to be spending a lot of time on our good friend SQL Server (or SQL DB for that matter) we’ll be focusing on the rest of the Azure platform.
This includes components such as:
- Azure AD
- Subscriptions (Billing)
- VMs in General
- Resource Manager
- Classic Deployments (like 2 minutes tops)
- Open Source
- Scale Sets
- RBAC (now called IAM)
If you have services in Azure today, if you are going to be moving services to Microsoft Azure in the near future, or you just want to get a better understanding of how things work in the Azure cloud, this is the session for you. At this end of this session, you’ll walk away with a good understanding of how Azure components should be configured, and how your services should be configured within the Azure platform to get the best performance and availability from the platform and your solutions.
You may have seen Joey and I traveling around India in 2016. We were actually delivering content similar to this (this is much newer obviously as Azure has changed a LOT in 12 months) to various customers and partners. If you’re using other consultants to help you with your Azure implementation there’s a decent chance that they’ve taken this training.
Not registered, for PASS? That’s an easy problem to solve. Go register. Already registered for PASS and need to add the precon to your
Already registered for PASS and need to add the precon to your registration? No problem, you just need to email PASS to add it to your registration.
Either way, nice and easy. Using Azure, take the precon. You’ll thank me.
If you were planning on attending the PASS Summit Attendee Orientation webcast on September 14th and couldn’t make it for whatever reason, have no fear, it was recorded and the recording has been posted online. If you forgot to register (or if you did) just to back to the registration page and fill it out and the video will magically appear. It’s that easy. The video is about 45 minutes long give or take. We used a new Skype service to broadcast the video so there were some issues at the begining (which I cut from the video before I uploaded it) so you don’t have to watch nothing for 10 minutes.
If you’re just looking for the information on Speaker Idol and the Karaoke Party, that info is linked here. The tickets for the Karaoke party are going fast so get signed up for a wristband if you’re going to be in Seattle on Tuesday night.
It’s that time of year again. It’s time for me to announce the biggest party at the PASS Summit. This years SQL Karaoke party. This year we’ve got a
couple of GREAT sponsors, SentryOne and SIOS who will be joining us back at CowGirls Inc on 1st Ave in Seattle for an all out party to end all parties. We’ll be rocking like always on Tuesday night, but that means that it’ll be Halloween so feel free to come dressed up, or come dressed as you are.
Like always the party starts at 9:30pm and runs until last call at 1:30am.
The registration link is right here, so get registered. You only need either a cash bar ticket OR an open bar ticket, not both. The open bar will get you free beer, wine or well drinks. The cash bar means that you’ll have to buy your own drinks. The open bar tickets are limited and open they are sold out, they are sold out.
So get registered, claim your wristband and get party with us at our great venue. We’ll see you there.
In past years I’ve been really glad that I’m just the MC of the PASS summit speaker idol because of the strong group of speakers. This year I’m really damn glad that I’m just not a judge because of the amazing group of speakers who submitted to be in the speaker idol. Getting the group down to just the 12 was enough of a challenge for me, I do not envy the judges their job of getting down to the one winner.
Your Wednesday lineup for speaker idol is:
Your Thursday lineup for Speaker Idol is:
Your Friday lineup for Speaker Idol is:
Daniel de Sousa
And we’ll see what the Friday afternoon session looks like.
Check back here as we get closer to the PASS Summit to see who our judges are for the speaker idol (I have to see who’s available during the speaker idol sessions before I can select my judges).
To everyone who submitted, both those who were selected and those who were not selected, thank you for submitting. Without you submitting we can’t have this competition.
For those attending the PASS Summit, be sure to check the PASS Session schedule for the speaker idol time slots (usually early afternoon Wednesday and Thursday, then early’ish Friday then late Friday for the finals) and come see these great speakers present.
Not going to happen.
Microsoft’s currently policy is to not deprecate any additional features and to not remove any additional features. This doesn’t mean that they’ll enhance those features, but they won’t be removing them. So get over the fact that they are still there. If you don’t want to use them, don’t. But they aren’t costing you anything by existing.
I was working with a client recently and they had an interesting problem. Recently they moved their application from a physical machine to a VM. And when they did performance took a nose dive. We’ve all heard this story before. They’ve got a crack team at this client and had checked all the usual suspects including adding more RAM and more CPUs (even though CPU was low already).
When I started looking at the system I saw minimal IO, in fact basically no IO on the system. So we got a user on the phone, and I profiled based on their username. I saw a huge number of commands go streaming by, all with a runtime of 0 milliseconds. Nothing really worth noting here except that SQL was only getting 6-8 commands per second. The client application in this case is running on Citrix within the same VMware farm so the client and the database are physically very close to each other, so unless the application is doing a lot of work on each row it should be throwing more commands at SQL than that.
Now in a perfect world I’d have the vendor fix their horrible code so that it wasn’t doing the RBAR, but the vendor couldn’t be convinced that this was a problem because none of their other clients were having a problem. In-fact the other installations at this client weren’t have a problem. But they were all much smaller installations.
What we ended up doing was turning a few things off, both in Windows and in VMware.
In Windows we turned off Receive Side Scaling, the TCP chimney, network auto tuning and task offloading. The thought behind all these components is that they are trying to optimize the network, but doing that takes time. Granted that time is just nanoseconds, but when you’re trying to through thousands of RBAR queries against a server at a time, a few hundred nanoseconds each is going to add up to seconds. When we profiled the server and let it run, we saw more than 16,000 commands being sent to the database just to open a single window in the application.
The code that we used to disable all these features is all done using the netsh command, and is shown below.
netsh int tcp set global chimney=disabled
netsh interface tcp set global rss=disabled
netsh interface tcp set global autotuninglevel=disabled
netsh int ip set global taskoffload=disabled
The vendor also recommended turning off IPv6 but I wouldn’t recommended that, especially if you are in a clustered SQL environment as modern versions of Windows clustering require IPv6.
On the VMware side we also turned off Interrupt Coalescing on the virtual NICs. The goal of having this on, to quote Jim Hannon from House of Brick is “to benefit the entire cluster by reducing the CPU overhead of TCP traffic”. Now in this case we have CPU to burn, and a very latency sensitive application. So turning it off became an option. This is done by changing the VMware advanced setting ethernetX.coalescingScheme (if the setting isn’t there you have to add it). Then set it to disabled like so.
ethernetX.coalescingScheme = "disabled"
With all these changes in place we fired up the VM and got the application user back on the phone and had them click the button again. And we were back in business. Response time was right were we expected it to be, and were it had been in the physical world before the migration.