Glenn’s Technical Insights For March 20, 2019

(Glenn’s Technical Insights… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can get more visibility. It covers interesting new hardware and software developments that are generally relevant for SQL Server).

 

AMD Processors Not Vulnerable to SPOILER CPU Exploit

A new research paper (PDF warning) details a new speculative execution, side-channel CPU exploit that can dramatically speed up some previously known attack types. All modern Intel processors, going back to the original Intel Core architecture from 2006 are vulnerable to this exploit.

As the paper states:

“The root cause for SPOILER is a weakness in the address speculation of Intel’s proprietary implementation of the memory subsystem, which directly leaks timing behavior due to physical address conflicts. Existing spectre mitigations would therefore not interfere with SPOILER”

Intel was informed of these findings on December 1, 2018. AMD has released a short statement, where they confirm that their products are not vulnerable to SPOILER.

This is another piece of bad news for Intel. Initial analysis indicates that this exploit may be extremely difficult to patch at the software level. Patching at the microcode level could have a serious impact on performance.

Should You Be Worried?

That is a good question. Personally, I am more worried about more primitive, well-known attack methods, especially when it comes to SQL Server. Things like SQL injection attacks, applications using sys admin rights, and people running seriously unpatched systems. Getting those barn doors closed should be a much higher priority for most organizations.

What do you think? Were you concerned about the Spectre/Meltdown exploits in early 2018? Did you do any specific patching for that? I believe in digital “herd immunity” meaning that if a large percentage of the population does a good job of securing and patching their systems, it will help protect everyone. Plus, if you do a good job protecting your system, a higher percentage of lower skill attackers will look for an easier target.

 

Intel Demonstrates 100GbE OCP NIC 3.0 Adapters

At the Open Compute Project (OCP) Global Summit 2019, Intel showed and demonstrated several new 100GbE adapters that use the OCP NIC 3.0 form factor. These are relatively small daughter cards that easily fit horizontally in 1U servers. Intel is a little late to the 100GbE market, behind vendors like Mellanox. Despite this, having more vendors to choose from is a good thing. Intel did a short presentation about the OCP NIC 3.0 specification at the OCP Global Summit that you can watch here.

 

Intel Comet Lake Processors Coming Soon

With AMD’s 7nm Ryzen 3000 mainstream desktop processors getting closer to release (probably announced at the Computex 2019 show), Intel needs a competitive response for 2019. This appears to be a new 14nm Comet Lake family that may also be released in mid-2019. These are rumored to have up to 10 physical cores for desktop chips and up to eight physical cores for mobile chips.

It is unclear at this point whether these Comet Lake processors will work in existing Intel Z390 motherboards. Intel has a pretty mixed track record when it comes to backwards compatibility for motherboards, but perhaps they are changing their ways.

These processors should perform quite well on heavily multi-threaded workloads, and they will give you another alternative to jumping up to a more expensive HEDT system. This line of processors will have to compete with the AMD Ryzen 3000 processors.

AMD Ryzen 3000

Current speculation is that the 7nm AMD Ryzen 3000 series may have up to 16 physical cores in a mainstream desktop processor. The Ryzen 3000 series may also have better single-threaded performance than Comet Lake. If both of these rumors are true, it will be a huge achievement for AMD that will put a lot of pressure on Intel.

ryzen-3000-series-release

Figure 1: AMD Ryzen 3000 Desktop Processor

Healthy competition between Intel and AMD is good for consumers, and I hope it continues. Like many people, I was very tired of Intel rolling out annual product updates of four-core processors with very marginal performance increases. They had been doing this for the past 4-5 years, and they could get away with it because AMD just wasn’t competitive. That is no longer the case.

 

Intel Hires Kyle Bennet of HardOCP

Speaking of Intel changing their ways, they have hired Kyle Bennett (the founder of the HardOCP web site), starting April 1, 2019. He will be the Director of Enthusiast Engagement for their Technology Leadership Marketing group.

As a result, the HardOCP website will be “mothballed” with no new content. The HardForum will be demonetized and sold but will stay in operation. I have been a long-time reader of HardOCP, so I am sorry to see it essentially going away. This is a great opportunity for Kyle, and it is one piece of evidence that Intel is trying to change their image. This is similar to Ryan Shrout of PC Perspective going to Intel last October as their Chief Performance Strategist.

How to Handle Power Grid Failure

The Blizzard

On March 13-14, 2019, most of eastern Colorado experienced a pretty severe blizzard. A blizzard is when you have snow combined with sustained winds of 35 mph or more that last for three hours or more. This storm was unusual because it was characterized by extremely low barometric pressure. Lamar, CO had an official reading of 970.4 millibars, which may be the lowest in Colorado history. The lower the barometric pressure, the more severe the storm typically is. A storm like this is more serious when you have a power grid failure.

You may have heard this storm called a “bomb cyclone”, which is a reference to bombogenesis. Bombogenesis is when you have a pressure drop of at least 24 millibars in 24 hours. The weather station at my house recorded a low of 979.3 millibars around 10AM on March 13, 2019. You can convert from inHg to millibars by multiplying inHg times 33.8637526.

 

My Electric Company

I get my electricity from Intermountain Rural Electric Association (IREA). IREA is an electric distribution cooperative that covers 5,000 square miles in Colorado. They buy power from Xcel Energy and the Western Area Power Administration. They also buy some solar power from the 13-megawatt Victory Solar facility in Bennett, CO. Unfortunately, they also own 25.3% of Comanche Unit 3 in Pueblo, CO. Comanche Unit 3 is a 750-megawatt coal-fired generation plant in Pueblo, CO that went online in 2010.

I don’t like IREA. Until recently, they were extremely hostile to renewable energy, especially residential solar PV electricity. Unlike most utility companies, they have never offered any rebates or incentives for energy efficiency upgrades. This includes things like getting energy efficient appliances, upgrading insulation, getting a solar PV system, etc.. None of this is encouraged or incentivized by IREA.

IREA also used to include a regular screed in their monthly newsletter about the folly of renewable energy. It was too expensive, it was too unreliable, IREA had a multitude of objections. They have finally stopped doing that, giving in to the changing economics of renewable energy.

 

Power Grid Failure

We lost power twice on March 13, 2019. The first time was for about two hours from about 12PM till 2PM. That was not a big deal. Due to my preparations, we got through that with not much of a problem. The second outage was 16 hours, from about 5PM until about 9:30AM on March 14, 2019. That was less fun, due to the length and the time of day (and night) that it happened.

Our house is primarily electric. We do have a natural gas fueled water heater and furnace, which both require electricity to actually operate. They don’t have pilot lights, and you need electricity for the furnace fan and the exhaust vent fan for the water heater.

 

Residential Solar PV Power

We have a 9.7KW rooftop grid-tied solar PV system that went into service in July of 2015. It consists of (29) 335 watt SunPower SPR-X21-335 panels connected to two SunPower SMA 5000 TL US 22 inverters (PDF). This system has generated 63.5 MWh of electricity so far.

With a typical grid-tied solar PV system, when the public grid goes down, your solar system is automatically shutdown. This happens to avoid backfilling the grid and being a potential safety hazard to utility workers. This means you have no power at all when the public grid is down.

You can get special model inverters that have a dedicated “secure power supply” that lets you flip a switch during a power outage. If the sun is up, (after a short delay), you can then pull up to 1500 watts from two dedicated outlets next to each inverter. This would let you run a decent amount of stuff during the day during an extended power outage (like a zombie apocalypse). It would not have helped last night, since it was dark. This video explains how it works:

Sunny Boy TL-US Secure Power Supply

IREA Limitations

IREA limits you to a 10KW or smaller solar PV system. By Colorado state law, IREA has to offer net-metering, which means that when your solar system is producing more electricity than you are using, your electric meter will run backwards. IREA will track your net metering credits (on an annualized basis). They settle up every May, and issue a bill credit for your net metering credit for the year. Settling up in May is the worst time of the year for the consumer, coming pretty soon after winter.

IREA charges $10.00 per month to be connected to their system. Our solar PV system generates more electricity than we use just about every month. The exception is December and January, because of the shorter days and the sun being lower in the sky. The annual net metering credit is usually about $90.00, so we usually pay about $30.00/year for electricity.

 

Energy Storage

Even with SPS inverters, residential solar PV by itself will not get you through an extended power outage unless you have storage. You won’t get any solar power production at all during nighttime. Your production will also be lowered by bad weather, especially if you have snow on your panels. So far, I have some very limited energy storage capability. This includes:

(3) APC BX1500G UPS

(2) Cyber-Power LX1500GU UPS (two 12V 9.0 Ah batteries)

(1) DeWalt DCB1800B Portable Power Station (with four 20V 5.0 Ah batteries)

The APC and Cyber-Power UPS units both have about 216 watt hours of storage each. The DeWalt Portable Power Station has about 400 watt hours of storage. This means that I only have about 1480 watt hours total (nearly 1.5 kWh). So, I could run a 1500 watt load for about one hour, or a 100 watt load for about 15 hours. That really isn’t enough capacity for most purposes, but it is better than nothing!

 

Energy Storage Performance

In my case, I was able to keep my Arris SB8200 cable modem and ASUS router going through the entire outage. I was also able to keep our two cell phones charged. We had regular internet until Comcast’s neighborhood equipment probably exhausted their batteries sometime while we were sleeping.

Once the sun came up on March 14, I was able to pull power from my SPS outlets on one inverter in order to start charging most of the UPS units and the DeWalt Portable Power Station. I was also able to turn on a small electric heater plugged into the other inverter.

 

Charging from SPS

Figure 1: Charging From SPS Outlets

 

Power Grid Restoration

Luckily, the grid power was restored mid-morning on March 14. If this hadn’t happened, we could have probably limped along for an extended period, not being very comfortable. As it was, our interior house temperature got down to 55 degrees Fahrenheit after 16 hours with no heat. This was while it was 18 degrees Fahrenheit outside. Our house is very well insulated, with R-54 in the ceiling and about R-40 in the walls. We were losing about one degree per hour, which wasn’t too bad.

My Tesla Model 3 was sitting in the garage with a fully charged battery. Over time, it would lose its charge since it automatically keeps the battery pack warm enough to protect the batteries. When the house got too cold to bear, we could have slept in the Tesla with the seat heaters on. If I had a long enough heavy-gauge extension cord (which I don’t), I could have also slowly charged the Tesla from the SPS outlet during daylight hours. Officially, you are NOT supposed to use any type of extension cord to charge a Tesla.

 

Lessons Learned

Well, I don’t have enough energy storage to deal with an extended outage, especially during a blizzard in the winter. I knew that, but this experience reinforced that knowledge. I can keep a small amount of electronics going during the night, and recharge my storage during the day. I also have enough power generation during the day to do a little bit of electrical heating. But that isn’t enough to stay very comfortable.

On the other hand, my current setup can deal very well with short electrical outages. All of the UPS units are mainly there for surge protection and line conditioning. The battery capacity is just a small bonus. So what can I do about this?


Long Term Solutions

The cheapest solution would be a gasoline powered generator, hooked up to a transfer switch. But then I would have to be storing gasoline, and rotating it to keep it from going bad. Plus, I would probably only have a few gallons on hand. I could also siphon gasoline from my wife’s car, but her tank is only about 13 gallons.

Another solution would be a natural gas powered generator from somebody like Generac. This would work as long as you had natural gas.

Generac

Figure 2: Generac Guardian

 

The high-tech, expensive solution is a couple of Tesla Powerwall 2 units. These are 13.5 kWh battery packs with their own inverters. You can use these with or without solar PV. If you have solar PV, it will charge the Powerwall(s) first, before going back to the public grid. When the public grid goes down, you automatically pull energy from the Powerwall until it is exhausted. If you have solar PV, you will run off of that during the day, and then draw from the Powerwall during the night. This would be the most environmentally friendly solution, which is important to me.

 

support-backup-whole-essential_installation-02.jpg

Figure 3: Tesla Powerwall 2

 

Figure 4 shows how this works.

powerwall_energy_consumption

Figure 4: Solar PV and Powerwall

 

The Tesla app that I already use for my car also lets you monitor your solar system and your Powerwall units.

Tesla app

Figure 5: Tesla App for Powerwall

 

Conclusion

This was an interesting, if slightly uncomfortable experience. It was short enough that we did not get truly cold, and we did not lose the food in our refrigerator and freezer. It proved that my limited preparation so far can handle relatively short outages but not longer outages. This is especially true during a winter blizzard which limits your solar production during the day. I also got some real-world experience with how the SPS circuit works.

I am going to make a few small changes and improvements to my current setup, and then decide what I want to do for a much better setup that can handle a longer grid outage. Just like with SQL Server, going through an actual outage teaches you how resilient your system actually is. It also shows where you have room for improvement.

 

 

 

Why You Need SQL Server Upgrade Knowledge

Introduction

Both SQL Server 2008 and SQL Server 2008 R2 are falling out of extended support from Microsoft on July 9, 2019. SQL Server 2012 already fell out of mainstream support on July 11, 2017. Finally, SQL Server 2014 is also falling out of mainstream support on July 9, 2019. Because of these facts, a SQL Server upgrade to a modern version of SQL Server could be in your fairly immediate future. No big deal, right?


SQL Server Support Dates

Figure 1: SQL Server Support Dates


Nothing to Worry About

Many organizations seem to have the idea that upgrading to a modern version of SQL Server is very easy. You can simply upgrade in place. Or you can do a simple detach/attach or backup/restore sequence. After this, you will come up just fine on the new version of SQL Server with great performance. Nothing to worry about, everything will be fine. That could be true, perhaps you will get lucky, without doing any planning or testing. It is more likely that you will have a few issues that you will have to work through after the upgrade effort. This is even more likely if you don’t understand the details about a modern SQL Server upgrade.

Just to be clear, this is not a knock on Microsoft or SQL Server. Each new version of SQL Server is better (in many ways) than the previous version. Upgrading to a modern version of SQL Server is a good thing to do. Your organization will get many benefits with little to no effort. You will also be able to take advantage of many useful new features, perhaps with some development effort. It is just extremely important that you do a proper SQL Server upgrade in order to have the best experience.

Avoiding a Stressful SQL Server Upgrade

How do you increase the likelihood that you will have a good upgrade experience? My first piece of advice is to not underestimate the actual complexity of the subject. Some organizations seem to think this is not complicated at all. After all, they did an upgrade from SQL Server 2005 to SQL Server 2008 ten years ago, and it wasn’t that hard.

Unfortunately, there are new complexities with modern versions of SQL Server. Just doing a blind upgrade, with little analysis and planning, is a much riskier proposition. You may run into pretty severe performance regressions on some queries because of differences in the newer versions of the cardinality estimator. There are many ways to discover this during a proper upgrade planning and migration process. Despite this, I see many organizations that that literally don’t know what they don’t know.

These organizations make fundamental mistakes during their planning and testing that cost them performance and scalability. They make mistakes that cause them to spend much more on SQL Server licensing than they needed to. They use incorrect configuration settings and database properties that negatively affect performance. These types of mistakes often lead to a very stressful experience after the Production migration. I don’t want this to happen to you!


Educating Yourself about SQL Server Upgrades

The first thing to do is to start to educate yourself on the subject. This might involve just doing some research and self-study to start with. If you want to start there, here are some articles and posts I have written on this issue:

Avoiding SQL Server Upgrade Performance Issues

The Importance of Database Compatibility Level in SQL Server

Properly Upgrading SQL Server

Compatibility Levels and Cardinality Estimation Primer

Everyone learns differently, so there are several other options available.


Online Classes from Pluralsight

Your next step might be to watch some of my Pluralsight courses that are relevant to installing and properly upgrading SQL Server. These specific courses are the most useful in my opinion:

SQL Server: Upgrading and Migrating to SQL Server 2016

SQL Server: Installing and Configuring SQL Server 2016

SQL Server: Understanding, Configuring, and Troubleshooting Log Shipping

SQL Server: Improving Storage Subsystem Performance

Live Online Class: IEPUM2017

Another alternative is to take my live online class IEPUM2017: Immersion Event on Planning and Implementing an Upgrade/Migration to SQL Server 2017 the next time we offer it. This is a three day class, with nine hours of lecture and many useful demonstrations that teaches you how to properly plan and implement a successful SQL Server upgrade. The format of this class lets you attend without any travel, and it gives you the opportunity to ask as many questions as you like. You also get a recording of the class, along with all of the class materials and demonstration scripts.


In-Person Immersion Event in Chicago

A great option would be to take our in-person IEUpgrade: Immersion Event on Upgrading SQL Server immersion event class in Chicago on May 1-3, 2019. This is a three day class taught by myself and Tim Radney. This is an intense, deep-dive class with about 22 hours of lectures and demonstrations. Being onsite for three days straight lets you concentrate exclusively on learning.

There is much less chance of being pulled back into your regular day job while you are away at training. This lets you focus on the material.

You also have the opportunity to ask as many questions as you like during the class. You also are able to ask questions to all of the other members of the SQLskills team over the course of the three days. You will have our undivided attention for a longer period of time, especially compared to a quick question at a SQL Server conference.

 

Consulting Help

Perhaps you don’t have the time or inclination to learn all of the gory details about SQL Server upgrades yourself. Maybe you are comfortable with the subject, but you want another set of eyes on your plans to make sure you haven’t missed something important. Maybe you just need an expert to actually help you with the implementation tasks. Whatever type of help you need, we can probably do it.

One way or another, we can help you with this! Just contact us, so we can talk about what your needs are.