Have You Patched For Spectre/Meltdown Yet? (And more on patches)

It’s security week here at DCAC (you can join us on Friday January 19th, 2018 at 2PM in a webcast to talk more about security) and I wanted to focus on patches. I wrote a couple of weeks ago about the impact of Spectre and Meltdown to SQL Server (and just about every other thing that runs on silicon chips). Well in the interim, Microsoft has patched all currently supported editions of SQL Server—the patches can be hard to find but are all summarized in this KB article. I can’t emphasize enough the need to patch all of your infrastructure for this—the vulnerabilities are big and they are really bad. While you may have physically isolated servers (though these are a rarity in modern IT) an attacker may have gained access to your network via other credentials that were taken from an unpatched server.

So to summarize, you need to patch the following:

  • System BIOs
  • Hypervisor
  • Guest Operating System
  • RDBMS
  • Browser
  • Your Mouse (probably)

That’s a lot of patching. And a lot of downtime, and testing. It sucks, and yeah, it’s probably also going to impact server performance. You still need to do it—unless you want to be next guy blamed by the CEO of Equifax.

Which brings me to my next topic.

023

What is your patching strategy?

In my career I found enterprise IT to be stodgy and not always open to new ideas. We were also slow to move generally, and operated a couple of years and versions behind modern IT. However, all of the large enterprises where I worked (5 different Fortune 100s) were really good at centralized management of systems. Which made things like patching much easier. At the telecom company where I worked, I remember having to patch all Windows Servers to fix a remote desktop vulnerability—it was one my first tasks there. We had System Center Configuration Manager to patch (and inventory the patch) of all of those servers. We had a defined maintenance window, and good executive support to say we are going to apply system updates, and you should build customer facing applications to be fault tolerant.

Smaller organizations have challenges with patching—Cabletown had a team of two people who’s job was to manage SCCM. Many smaller orgs are lucky if they have a sysadmin and Windows Server Update Services. So how do you manage updates in a small org? My first recommendation would be to get WSUS—we have it on our organization, and we’re tiny. However, you still need to manage rebooting boxes, and applying SQL Server CUs (and testing, maybe). So what can you do?

  • Use the cloud for testing patches
  • Get a regular patching window
  • Use WSUS to check status of updates
  • When in doubt, apply the patch. I’d rather have to restore a system than be on the news

I mentioned the cloud above—one thing you may want to consider for customer facing applications is platform as a service offerings like Amazon RDS, Microsoft Azure SQL Database, and Azure Web Apps. These services are managed for you, and have been architected to minimize downtime for updates. For example if you are using Azure SQL Database, when you woke up to the Meltdown/Spectre news, your databases were already protected. Without significant downtime.

SQL Server 2017 Temporal Enhancements

One of the most popular features in my talks about SQL Server 2016 has been the temporal tables feature. If you aren’t familiar with this feature you can read more about it on Books Online here. In a nutshell, you get a second table that tracks the lineage of your data. This is fantastic for all sorts of scenarios up to and including auditing, data recovery, fraud detection, or even slowly changing dimensions.

th

This is implemented in SQL Server via a history table—a second copy of your data that maintains timestamps of when the data is valid. As you can imagine this table can grow quite large—Microsoft does us a couple of favors: the history table is page compressed by default (you can use columnstore) and you could put the history table on a different filegroup. The only major issue was to truncate or delete data from history table for pruning purposes, you had to turn of system versioning, or the glue that makes that this feature work.

Starting with SQL Server 2017 (and Azure SQL Database) you can define a retention period and have SQL Server prune records for you. This is awesome and easy—see how to implement here.

Always On Availability Groups transport has detected a missing log block…

If you are running SQL Server 2016 (especially before CU3) you have received this error:

DATE/TIME:    8/21/2017 11:24:53 AM

DESCRIPTION:    Always On Availability Groups transport has detected a missing log block for availability database "Database". LSN of last applied log block is (99939:95847:0). Log scan will be restarted to fix the issue. This is an informational message only. No user action is required.

COMMENT:    (None)

JOB RUN:    (None)

Image result for missing block

I’ve talked with the product team about it, and its just something that happens, and is more of an informational message. Based on some discussions I’ve have on #sqlhelp on Twitter, it may be related to not enough network bandwidth between nodes. But if you see these sporadically, relax, it’s nothing major.

SQL Server on Linux Licensing

Now that SQL Server 2017 has gone GA and SQL Server on Linux is a reality, you may wonder how it effects your licensing bill? Well there’s good news—SQL Server on Linux has exactly the same licensing model as on Windows. And Docker, if you are using it for non-development purposes (pro-tip: don’t use Docker for production, yet) is licensing just like SQL Server in a virtual environment, you can either license all of the cores on the host, or simply the cores that your container is using.

But What About the OS?

So let’s compare server pricing:

  • Windows Server 2016 Standard Edition– $882
  • Red Hat Enterprise Linux with Standard Support—$799 
  • SuSE Enterprise Standard Support—$799
  • Ubuntu—Free as in beer

As you can see most of the licenses are the same whether you are on Windows or Linux. I’m not going to get into the religious war to say which is the best distro or if you need paid support, just simply putting the numbers out there.

HA and DR

There’s still more to come on the HA and DR story, so stay tuned. But assume the standard free license with SA for an idle secondary.

Ignite—Important Data Announcements You Probably Missed

So, if you have been living under a rock for the last year (and yes, he really is president), you may not have heard that SQL Server 2017 launched yesterday, and it runs on Linux!!! If you want to know more about that you can read my article over at Redmond mag. SQL Server 2017 will be released next Monday, October 2nd. But there were a lot of other Microsoft data platform announcements, that you might have missed, because I know I did, and I watched the keynote and stood in the SQL Server booth all day.

cities-of-learning-announcement-at-the-2014-open-badges-summit-to-reconnect-learning-1-638.jpg (638×359)

  • Azure SQL Database Machine Learning Support—If you are using a premium Azure SQL Database you can now take advantage of R and most of the goodies that are available in SQL Server. You can learn more about that here. Python isn’t there yet, and some options are limited, but it’s a nice start
  • SSIS as a Service Public Preview—Anyone who has tried to use Azure Data Factory, well hi, Meagan! There’s a new release of ADF that will support using SSIS packages, which should make the service much more friendly to use. You can learn more about that here.
  • Public Preview of Managed Instances—We’ve been hearing a little bit about this for a while, and I’ve been fortunate enough to be part of the private preview. This is hybrid PaaS/IaaS product, that will be easy to migrate to. It will also have the added bonus of allowing you to bring your own license to the party. This isn’t quite available yet, but based on yesterday’s announcement, should be soon. There’s a little bit of info in this post from Microsoft.
  • SQL Data Warehouse BIG SCALE—Do you have a Neteeza? Are you sick of paying them $500k/yr for support? Azure SQL Data Warehouse has a solution for you (bring a black card only, please). You can go up to 18,000 DWUs using NVMe hardware in the cloud. This offering is aimed at large customers who want to remove expensive on-premises appliances and get the flexibility that the cloud offers.
  • Azure SQL Database vNet Support—This is something that a lot of a customers have been asking for in Azure SQL DB. The ability to not have a public facing endpoint (even though you could lock it down), but it can allow you have total isolation. The bigger benefit of this is that you can allow more granularity in what connects to your SQL DBs from Azure. You can learn more about the preview here.

There are a lot of announcements this week, and not a lot of time in the keynotes to get to them all. So I thought this might be helpful.

SQL Server 2017—SELECT INTO With Filegroup Support

One of the things I really appreciate Microsoft doing in recent releases of SQL Server is fixing some of the longstanding Connect items. These aren’t necessarily bugs, but design gaps—a good example of this was with SQL Server 2016, where the ability to truncate an individual partition came into effect. Some of these are minor, but have real impact into usability and functionality of the RDBMS.

Image result for file cabinet

The feature I am highlighting here is the SELECT INTO syntax for SQL Server. In the past, this syntax could only be used to create the new table in the user’s default filegroup. There were workarounds like changing the default filegroup for the user, but it was an extra step that shouldn’t have been needed. Starting with SQL Server 2017, T-SQL will support the ON syntax in this command:

SELECT * INTO dbo.NewProducts FROM Production.Product ON SecondaryFG

This isn’t huge, but it is a very nice thing that Microsoft has fixed.

%d bloggers like this: