The same answers,
on a supported release
We look after Microsoft SQL Server databases and the moves they need: each server taken to a supported release, each database’s queries kept on the behavior they had until their plans are checked, and failovers rehearsed, so you know what an upgrade changes before it reaches your users.
Thanks, we’ll be in touch soon.
{{ heroStatus }}
You’re in good company
A few of the companies we’ve worked with.
When the upgrade keeps waiting
Each SQL Server release has two periods of support, each ending on a date Microsoft publishes: mainstream support, with every kind of update, then extended support, with security updates only.
Servers often stay on a release well into its extended support, because moving could change how the app’s queries, its requests for data, are run.
A newer release brings a newer query planner, the part that chooses how each query runs, its plan. Microsoft says its plans are usually better, and some queries can still get a slower one.
Each database also has a level, which decides which of the newer planner’s behaviors its queries get. So the server can move first and its queries’ behavior later, with the database’s own record of their plans to compare.
So the real work is moving in an order that shows what changes, one database at a time.
What we build
Upgrades that keep each query’s behavior until it’s checked, queries kept quick, reports that don’t make saves wait, and databases kept running, backed up and restorable.
Upgrades in two moves
The server taken to a supported release first, each database keeping the level it had, then each level raised in turn, once the record of its plans is in.
Queries that stay quick
The slowest queries found in the database’s own record of their plans and how they ran, the indexes they need added, and the statistics the planner reads kept current.
Reports that don’t hold up saves
Reads that use the last saved version of each row instead of waiting on a save, tried on a copy first, with the space the versions take watched.
Set up to keep running
Copies of the databases on other servers, one name for the apps to connect to, every sign-in and scheduled job the app needs on every server or kept in a group that carries them, and failovers, the switch to another copy, rehearsed, with what came back written down.
Backups that restore
The log backed up on a schedule, so it reuses its space and a restore can reach a chosen moment, and restores rehearsed on a separate server, each one timed.
Moved from the setup you have now
Between your own servers, onto newer ones, or to a managed service in your cloud, the new server catching up before the switch and every sign-in and job checked after.
Access kept to what each app needs
A sign-in for each app with only the rights it needs, roles for the people who run the servers, encrypted connections, and encryption where the data needs it.
Watched once it’s running
Where work waits on other work, the slowest queries, the log and disk space, how far each copy trails, and alerts your team sees and acts on.
AI where it helps
On a release that has them, vectors kept beside the rows and searched in the database, under the same rights as everything else.
- Speed beyond the database itself:Performance & Optimization
- Pipelines and analytics fed from the database:Data Engineering
- Reports built on these databases, from one shared model:Analytics development
- Moving to the Microsoft Azure cloud:Software development
- Servers and monitoring around the database:Cloud, DevOps & Security
- Security reviews across the whole app:Application Security
- Developers in your own team:Dedicated team
The new release first, its new behavior after
After an upgrade, each database keeps the level it had, as long as the new release supports it, so its queries keep the behavior they had.
The level decides which of the newer planner’s behaviors a database’s queries get. It’s set for each database on its own, and it can be raised, or set back.
The database’s own record keeps each query’s plans and how they ran, so there’s something to compare against before anything changes. Microsoft says the newer planner usually chooses better plans, and a query that gets a slower one can be told to use its earlier plan from the record.
So we upgrade the server first, let the record fill, then raise each database’s level in turn, with any query that slows kept on its earlier plan while it’s fixed.
Raise the level, and see what changes.
Queries at Pellham Heating
An example- A furnace’s history{{ r0S }}
- Tune-ups due this week{{ r1S }}
- Visits by month{{ r2S }}
The server is on the new release. The database keeps the level it had, so each query runs as it did, and the record keeps their plans.
At the new level, the queries get the newer planner’s choices. Two run quicker. Visits by month got a slower plan.
Visits by month uses its earlier plan from the record while it’s fixed. The others keep the newer choices.
The level is back where it was, so each query runs as it did. It can go up again when you’re ready.
The level is back where it was, and it can go up again when you’re ready. The plan kept from the record stays kept for Visits by month until it’s released.
Each database moves to the new level on its own, once its plans are recorded.
{{ lvSaid }}
Each release on its published dates
Each release follows Microsoft’s Fixed Lifecycle Policy: mainstream support, with functional, performance, scalability and security updates, then extended support, with security updates only, each ending on a date Microsoft publishes.
Once extended support ends, Microsoft no longer services the release. Some releases can have paid security updates for a time after that.
Within a release, updates come in turn, and we try each one on a copy first. An upgrade in place goes only from the releases Microsoft lists as its sources. Older servers move side by side, to a new server, which leaves the old one as it was until the new one has run.
A backup can’t be restored on an earlier release than the one that made it, so the way back from a move is the old server, never a restore. With copies on other servers, the copies are updated first, then the primary role is moved, then the old primary is updated. Each database’s level is raised after the move, as above.
So we keep a list of each server’s release and its end dates, take each update in turn on a copy first, and move each server well before its extended support ends.
Start with every server, its release and its databases
The review covers each server’s release, its newest update and its end dates; each database’s level, and whether its record of plans is on; the slowest queries and their plans, and where reads and saves wait on each other; each database’s recovery model, the setting that decides when its log reuses its space, with its log backups and how far back a restore can reach; the backups, where they go, when one was last restored and how long that took; the copies on other servers, and when a failover was last tried; the sign-ins and scheduled jobs on every server; each app’s sign-in, its rights, roles for people, and encrypted connections; the space each server keeps for temporary work, and disk space; and where it all runs, on your own servers, on virtual machines in a cloud or on a managed service in your cloud.
The first step we suggest from it is tried on a copy before anything changes: the next upgrade rehearsed, with each database’s plans recorded before its level goes up; log backups set, and a restore to a chosen moment timed; a failover rehearsed, with every sign-in and job checked after; the slowest queries fixed; or reads from the last saved version tried for the reports that make saves wait.
What you receive is a written review and a proposal for that first step.
Questions worth asking
Can we upgrade without changing how the app behaves?
Yes, at first. Each database keeps the level it had after the server moves, so its queries run as they did. We raise each level once its plans are recorded, and keep any query that slows on its earlier plan while it’s fixed.
Why does the database log keep growing?
With the full recovery model, the log keeps every change until a log backup is taken. Backing it up on a schedule lets it reuse its space, and lets a restore reach a chosen moment. A long piece of work left open holds the log too, under every model, so we look for those as well.
What moves with a failover?
The databases in the group move. Sign-ins and scheduled jobs live on each server, so we keep them the same on every server, or keep them in a contained group, which carries them with it. The apps connect through one name, which leads them to the new primary when they reconnect.
Do long reports slow down the app?
They can make saves wait under the default setting. With row versioning on, reports read the last saved version of each row instead, so saves don’t wait for them. We try it on a copy first, since the versions take space of their own.
What does it cost to work with you?
It depends on the servers you run and the first step you choose. Before any work starts, you get a written proposal with the scope, the price or rate, and how you pay.
Can we hire developers for our own team?
Yes, through a dedicated team: Webair developers who stay with your product over time and work beside your own people.
Microsoft, Azure and SQL Server are trademarks of the Microsoft group of companies. Webair is independent of Microsoft, and isn’t sponsored by, endorsed by or affiliated with it.
Let’s build it better
Tell us which release your servers run, and what the next upgrade has to keep, and we’ll suggest where to begin, in a short call.
Thanks, we’ll be in touch soon.
{{ ctaStatus }}