Voice of the DBA

Tag: upgrades

  • The Dangers of Not Upgrading

    About ten years ago my sister-in-law broke the screen on her mobile phone. She’d had an older iPhone and when she went to upgrade, none of the upgrade processes worked because her OS was so far behind that they couldn’t transfer her information smoothly. She had been avoiding OS updates because they interrupted her life, but that was now a problem because the world had marched so far beyond her version that there weren’t tools, or at least, no one was interested in trying to perform an upgrade across multiple OS versions (I think it was 3 or 5 versions).

    I ran into this recently with someone else I knew, but not for a mobile phone. For TFS 2015. This customer had been working along with this older system and is finally ready to upgrade to Azure DevOps in the cloud. They wanted to know if they could somehow upgrade the TFS database and move all that data easily into the cloud. I said this wasn’t likely easy as this isn’t an upgrade, but an export and import of a lot of data. Microsoft offered a path, but it was multiple upgrades before an export/import, which was deemed too expensive. Right now, I’m not sure what they’re doing to do.

    I know that many people keep old versions of SQL Server out there. Brent’s population report still shows some 2014 and older databases, and I’m sure there are plenty of 2008-era (or older) instances in use. Many of you continue to run older software because it works. That might especially be true on desktops, where we often install specific software for a task that might not change for years. I think there is plenty of value in older software that works well, though there might be security concerns.

    However, for platforms or software where there is a regular upgrade path that you will need at some point, it pays to keep up, at least a bit. Maybe you don’t upgrade with every version, but you likely shouldn’t fall more than two versions behind. Vendors don’t want to maintain compatibility for too long and certainly don’t want to keep providing updates for new features. Even security updates get expensive to produce for old versions with complex test matrices needed to ensure the patches work and don’t break the application.

    Software is a bit of a crazy business. In the analog world, if we own a product and it breaks or has an issue, often there are third parties who can repair it, or even create the necessary parts for the DIY market. Software is different, and the pace of change can be a bit overwhelming, expensive, and annoying. We can’t fix old software, heck, we’re not even allowed to fix old software. We can get into legal trouble if we try.

    I don’t know if there is any good solution, but I certainly do know that if you manage software, you ought to be careful to keep abreast of how often the software upgrades and when support for previous versions is waning. Even if you don’t care about customer support calls, you likely care about upgrading to a newer version at some point, so make sure you know when they might drop support for upgrading from your version. Stepped upgrades work, but they can be expensive and time-consuming, and if there are issues, often vendors aren’t interested in why the upgrade didn’t work from your extremely-old version to a slightly-less-old version.

    I still expect a database version to run for 10 years, but I know that support is tricky, and I also know that I better be ready to upgrade at that time.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

  • Upgrading SQL Server Central

    The decision to upgrade database servers can be a complicated one that involves features, costs, and support requirements. While I think many people would love to run database systems for ten years, often there are concerns about support, which effectively ends after five years for SQL Server. While you can purchase extended support, is that worth the cost? A hard question to answer, but one I’ve had to confront lately. When do you upgrade a database instance?

    I’ve discussed this with a few customers, but it also came up in the context of SQL Server Central. We run a SQL Server 2016 database on Windows Server 2016. Someone recently sent a note that since 2016 is out of support, they’d like to upgrade the systems. They were thinking SQL Server 2019 on Windows Server 2022, but with the release of SQL Server 2022 that doesn’t make sense.

    I want to defer this process as long as possible. To me that means always aiming for the latest and greatest version. SQL Server releases roughly every 2-3 years, so this is the best time to upgrade for us. If we can upgrade before 12 months, we get 4+ years before we revisit this topic. If we were to upgrade to SQL Server 2019, then we’re already down to 2 years of support before we need to consider the topic again.

    I think many DBAs would feel the same way, looking to test and certify SQL Server 2022 for their internal apps. This is one reason why getting the RC0 and RC1 releases of new versions for some initial testing make sense. The sooner you can upgrade in version’s lifecycle, the longer before you do it again. Of course, if you have more than 50 servers, you might just be upgrading every year anyway, as some system is always falling out of support.

    Upgrades take time. There’s the time considering the decision, the testing a new system, the actual upgrade time, whether in place or migration, all of which eat up labor and time. Doing this for more than a few servers can become a full time job in some cases. That makes me start to really see the wisdom of using a PaaS service that’s evergreen.

    For organizations where support matters, then upgrades are a fact of life and a regular occurrence. However, if formal support isn’t an issue, you might feel differently. The more mainstream you keep your feature usage, the more likely that you can go far past 5, or even 10 years, with your database system. I know there are still companies running 2008, 2005, and even a few 2000 servers. I don’t know that the SQL Server Central code would run on SQL Server 2000, but it might. Project Nami is using fairly generic SQL. If we were more concerned about database licensing, likely we’d have not upgraded the site from ASP.NET to WordPress.

    For now, I think we’ll likely upgrade sometime in early 2023. I am not worried if things are delayed, but I do know that we also want to upgrade both WordPress and PHP. There is an order to getting all these steps completed with OS and database upgrades. Because of this, I want to ensure that we proceed soon and get things moving while my schedule is fairly light so that I can test and deal with any issues that arise. Hopefully there won’t be any, but I’ve learned to hope for the best and prepare for the worst, especially with software upgrades.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • The Slow Upgrade of SQL Server Versions

    Every so often Brent Ozar releases a report of his Constant Care customers, showing the versions in use. While I don’t know this client group is representative of the overall SQL Server customer base, I do think it’s close enough to draw some rough conclusions.

    The top version adoption graph that the summer 2021 report shows has 33-34% of the installed based on 2016, about 34% newer (and under support) versions, leaving the last third running older versions. However, the graph I found interesting is the lower one, which shows the distribution over time.

    What I see in this graph is that the older versions (2008-2014) held about 45% of installs in Q1 of 2020. Now they have 31%. The 2017/2019 versions went from 19% to 34%. Those are fairly close numbers, which indicates that a fair amount of these older versions were possibly replaced with newer versions. There could be brand new instances as well as retirement of old ones that weren’t replaced, but in general I’d think this shows that the ten-ish year lifecycle of an instance is probably a good guess. This is about 12 years for 2008, and 7 for 2014, but that’s around ten.

    I tend to think that many of us dislike upgrading our instances across major versions too often. While some DBAs might want to, there aren’t often good business reasons for doing so, and many DBAs are just busy. Taking time to upgrade major versions for the same application, especially on the same hardware, doesn’t always seem worth it.

    In my experience, business people want a long lifetime for a major version. It’s just too expensive and time consuming to both doing this too often. I’ve also found after a couple years, DBAs know the ins and outs of a version, what works well, what tricks are needed to keep things tuned and running. Not to mention that a workload on one version isn’t always faster, and sometimes it’s slower, on a new version. There are definitely DBAs that might not want to bother changing versions and potentially having issues with their current workload from changes, like the change in the cardinality estimator in 2014.

    With modern hardware, for most applications, I think that many instances can run for 10 years. This might be even more true when we factor in that many database servers are virtualized, which means adding a few more cores or RAM is much simpler than in years past.

    How often do you upgrade versions? I’m sure some of you do this regularly, and I know some applications might benefit from upgrades more often then every decade, but I bet a lot of you don’t bother to change this too often. After all, “working” is a feature, and if you have that one, why take chances?

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Compatibility Level Confidence

    SQL Server databases have had a compatibility level in them for a long time. This is a number that essentially corresponds to a version. We had 70 for version 7.0, and then we went to 80 for SQL Server 2000, 90 for 2005, and we currently are at 150, which you just have to know maps to 2019. I really miss real version numbers.

    In any case, there is this statement on the Compatibility Certification page that says this: ” As long as the application does not need to leverage enhancements that are only available in a higher database compatibility level, it is a valid approach to upgrade the SQL Server Database Engine and maintain the previous database compatibility level, with no need to recertify an application. ”

    That’s a good statement, but it feels soft to me. This doesn’t really seem to me that Microsoft wants to stand behind the compatibility level as a guarantee that the way all code works on SQL Server 2017 with compat level 140, is the way it will work on SQL Server 2021 with compat level 140. That pages does say that the queries display the same behavior, and query shapes are protected, but what I, and most people, want to know is that our application will run the same.

    What about statistics behavior? What about encryption algorithms (that’s already been an issue)?  What about the Cardinality Estimator? In my mind, there are still lots of unknowns, and while I appreciate MS moving in this direction, I’m not sure they are as confident as they’d like you to be in their statement.

    I’m sure some of you have used the database compatibility level to upgrade an instance, but keep a database behaving as it would on the prior version. If that’s worked well, let us know. If that caused issues, what did you do or was there a workaround? Trace flags, perhaps some database or instance settings might help, and if they did, I’d like to know. In fact, I think Microsoft would like to know, so leave a comment on how you feel, with our without details.

    Moving forward, would you consider upgrading the engine, without much testing, and assume that your application will still work well? Do you think vendors would provide support with a different version, but the right compat level? I’m not sure they will.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.