It’s a common problem, how do we stay on top of technology upgrades?
I’ve often seen a company stuck with an old product version 10 or 20 years because the upgrade would be too onerous.
Here are some basic tips to make the process easier. I’m going to concentrate on SQL Server upgrades, but many of these apply to most any product.
Learn SQL, October 27: The SQL Upgrade Chase! Get details and register here.
Use Simple Queries – OLTP
In every new version of SQL Server, we get a new set of T-SQL functionality. This is great, but most of it isn’t necessary for the functioning of a normal OLTP database.
OLTP by design should be short and fast. Concurrency is king in these databases, so queries tend to be simpler.
This is perfect because those base language elements don’t tend to change much (if ever) over time. This makes upgrade very easy: you don’t have to worry about your main codebase becoming deprecated.
Use Simple-ish Queries – Reporting
Now we’ve also got the issue of reporting databases. This can be a bit trickier, but only if you’ve been lazy.
Those new T-SQL features come more into play here. You’re not required to upgrade your code to these new functions, but it would be nice to do that over time.
Deprecation is more of an issue here than in OLTP databases. But that’s only if your code is really far behind.
This is where a lot of companies find themselves. If you put off the upgrade for 10+ years, you’re likely to have a code issue. Plus, you have to refactor your entire codebase and run it through several rounds of regression.
This is expensive and takes attention away from current projects. So staying up to date with new versions of SQL can be essential.
Vault your Code
Using a code vault like GitHub (and the like) can fast-track your upgrades.
When you find code that you need to replace, a central code repository greatly simplifies searching for other instances of that code. It makes the code searchable, and refactoring can be much easier.
Use Stored Procedures
Putting your code into stored procedures (SPs) is smart for performance. And, it’s a great way to keep up with upgrades.
Why? It’s the same as above. When your queries are in SPs, you have a single place to search for code changes.
But, applications that build ad hoc T-SQL make it very hard to catalog what types of queries you have. Those queries don’t actually exist until they’re sent to the database.
Not only that, but changing the application to make code changes is a pain. It generates rounds of regression testing, which takes resources away from current projects.
Do the right thing and just use SPs. (Note that most ORMs support SPs these days, so that’s no excuse anymore!)
Expect More from your Vendors
All too often when we find a software we like, we just buy it. But there’s more to it than that.
Do your diligence and research their policy command-line interfaces, interoperability, and database upgrades.
How well does the software vendor stay on top of newer versions of the database they rely on? This is, arguably, the biggest cause of companies getting behind in SQL upgrades.
Step 1: Fall in love with a software.
Step 2: Get really entrenched in the product.
Step 3: The vendor doesn’t put the effort into staying current.
Step 4: You’re stuck with a database that’s several versions out of sync with the industry.
Ask these questions up front so you don’t find out at the wrong time that they don’t care to move forward.
Ask them: “How long did it take you to upgrade to the last version of SQL Server?” This is a good indicator for how important this is to them.
If you’re lucky, you might even be able to get upgrade guarantees in your contract.
Prioritize Upgrades
Make regular upgrades a priority as a company.
These days there are too many moving parts, and too many security risks, to rest on a 10 yeard old database version. You cut yourself out of some important possibilities without that modern functionality.
You don’t have to be on the bleeding edge. Most companies don’t have the stomach for that, or the need. A good guideline is, be at most one version behind.
It may actually take you that long to do your diligence on the upgrade process anyway, depending on project load.
When they announce SQL Server 2022, that would be a good time to start working on your 2019 upgrade. It’s much easier and cheaper than staying on 2012 and trying to upgrade through all the different versions. This will throw you into a lot more regression testing and decrease your confidence in the process.
Have Long Vision
If you think it’s expensive to upgrade, try waiting until you’re several versions behind. The costs go up drastically.
If cost is a factor, think ahead and set yourself up for success.
Many companies think that putting off these upgrades will save money. The exact opposite is true. Delaying can necessitate hiring consultants to alter data structures, to do your testing, and so on.
Not to mention, many companies have discounts for direct upgrades cause they want their customers on the new systems. This isn’t always the case, but it’s a possibility.
Stop thinking about just the next quarter! Think in terms of 5 years down the line.
