SQL Server Database Consulting
We get a variation of this request a LOT.
So now it’s time to do something about it!
Ad Hoc SQL Server help, at credit card friendly pricing.
SQL Server DBA Help – 4 Hours for $500 | Dallas DBAs
Thanks for reading!
— Kevin
I have a client right now debating how to handle SQL Server upgrades across all of their dev and test environments.
And it’s a good conversation, because the same logic people use for dev and test is often what sneaks into production later.
There are two paths. Both can work. Both can fail, but in very different ways.
An in-place upgrade modifies the existing SQL Server instance where it sits.
Why some people choose it
For dev and test, that simplicity is tempting.
The risk profile
Once SQL Server upgrades its system databases, you don’t roll back with a checkbox.
If something goes wrong late in the process, your options narrow quickly:
Downtime tends to be “whatever it takes,” not “whatever you planned.”
The safety net you must have
An in-place upgrade should never rely on SQL backups alone.
You need two rollback paths:
Platform-level rollback
SQL-level rollback
Add a captured baseline and a written stop/go decision, or you’re flying blind.
When in-place makes sense
Why teams choose this
This approach favors control over speed.
The tradeoffs
And data movement takes time – there’s no way around physics, but there are ways to mitigate this for short cutover windows.
Failures tend to be quieter and easier to reverse.
How to decide
If speed and simplicity matter most, in-place is attractive.
If rollback certainty and testing matter more, migration wins.
That’s why dev and test often lean towards in-place. Production should pause before copying that decision.
The first 24 hours matter
No matter which path you take:
Most upgrade failures don’t happen during setup.
They happen after users return.
If you don’t have a tested rollback plan, you don’t have an upgrade strategy.
Dev and test decisions shape production thinking, whether you intend them to or not.
Choose based on risk, not convenience.
Don’t let SQL Server make you miss your kid’s piano recital!
First month free for new clients
Replication was born for scale, not survival. It’s great for reporting, distributing data to remote sites, and keeping subsets of tables up to date. But if the publisher goes down? There’s no built-in recovery process. You’ll spend more time re-initializing and troubleshooting than restoring from a backup.
In addition, the subscribers are read-write. This means someone downstream can delete a row, causing updates at the publisher to not get moved, which can cause a huge issue.
Replication moves data, not databases. It’s not a safety net; it’s a delivery truck.
Log shipping is a solid, dependable form of disaster recovery. It keeps a warm copy ready for manual failover, which is perfect for a secondary data center or a standby VM in the cloud.
But it’s not high availability. There’s no automatic failover, no continuous read/write capability, and your secondary is always behind by at least one transaction log.
Think of log shipping as a lifeboat, not a bridge. It’ll get you safely across the storm, but only after you climb in.
A SQL Server database snapshot freezes a view of a database at a moment in time — handy for quick rollbacks or comparing changes. A VM snapshot, on the other hand, captures the state of a virtual machine. Neither one replaces a backup that can be restored independently to a new server.
If you are snapshotting the entire server, how does that help you restore to a point in time for one database without bringing all of them backwards? That’s a really awkward conversation to have with the CEO.
Snapshots are shortcuts, not insurance policies.
Even the shiniest Always On Availability Group can’t save you from data corruption, accidental deletes, or bad deployments that replicate instantly across every node.
Availability is not durability. You still need backups — tested, verified, and stored safely elsewhere.
An AG keeps you online, not immune.
SQL Server grew up in an enterprise world, full of legacy features that overlap and evolve. Microsoft rarely deprecates things outright, so we’re left with a toolbox that has five ways to move data and ten ways to misunderstand them.
Don’t feel bad. SQL Server’s feature naming team must have had a bet going to see how many words they could reuse. These don’t help:
That’s why it takes a DBA’s mindset. Understanding and translating these terms takes intentionality and years of exposure to them.
Fuzzy thinking is normal, but it’s dangerous when disaster strikes.
Replication moves data. Log shipping recovers data. Availability Groups keep you online.
Only backups and testing keep you safe.
When you know what’s really protecting you, you can make confident, budget-friendly choices, not guesses wrapped in acronyms.
We can focus on your server’s Reliability and Availability.
Want to see which databases are actually protected? This quick check shows the database name, recovery model, last full backup, and last log backup times:
-- This is code from the internet...test before you run
SELECT
d.name AS [Database],
d.recovery_model_desc AS [RecoveryModel],
MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS [LastFullBackup],
MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS [LastLogBackup]
FROM sys.databases d
LEFT JOIN msdb.dbo.backupset bs
ON bs.database_name = d.name
WHERE d.name <> 'tempdb'
GROUP BY d.name, d.recovery_model_desc
ORDER BY d.name;
Note: If a database is in FULL recovery model but has no recent log backup, your log file is probably growing and could eventually cause a disk-full outage.
Please send to your manager to help clarify the differences in SQL Server HA, DR, and distributed computing. They deserve a clear cheat sheet.
Why I Go to Conferences
I go for two big reasons:
A few favorite memories:
This year, I’ll be at PASS Data Community Summit in Seattle and next year SQLBits in Wales). If you’re attending, come say hi! I’ll be the over-caffeinated guy in a mountain-bike shirt talking about index maintenance.
A sample of conferences, most certainly not all inclusive of every event:
If You’re on the Fence
Here’s how to make a conference worth it:
If travel isn’t in the cards, start small: attend a local Data Saturday or User Group meeting. The ROI is incredible.
If your production SQL Servers are still running 2016 (or older) you’re basically banking on inertia. Sure, it’s been stable. But that doesn’t guarantee it’ll stay safe or compliant.
Microsoft shut off mainstream support for 2016 back in July 2022, and extended support ends in July 2026. Beyond that? You’re on your own for bug fixes, security updates, or emergency patches.
It’s easy to view upgrades as optional enhancements; in truth, staying current is about maintaining resilience. What you gain with 2019/2022 isn’t just bells and whistles. It’s reliability, defensive tools, and measurable performance.
Smarter Engines Under the Hood
“Better defaults” are no marketing fluff. From improvements in memory grants, parallelism, and hash joins, newer SQL Servers are tuned to make your workload more efficient out-of-the-box.
Adaptive Behavior Without Rewrites
Here’s where SQL Server 2019 and 2022 quietly earn their keep. Microsoft invested heavily in the Intelligent Query Processing (IQP) stack – features that make your existing code run better without touching a line of T-SQL (most of the time).
Older versions execute queries based on a single snapshot of estimated data volume, join paths, and parameter values. If those estimates are off (and they often are), the engine makes bad choices and never looks back. The newer engines don’t do that anymore.
Adaptive joins can switch between nested loop and hash join strategies while the query runs, based on how much data actually flows through. That means fewer “query plans from hell” when parameter values swing wildly between executions.
Interleaved execution gives the optimizer a second chance – especially for multi-statement table-valued functions. Instead of assuming a generic row count of “1,” SQL Server now runs the first statement, learns the real cardinality, and uses that for the rest of the plan.
Table variable deferred compilation fixes one of the longest-standing developer pain points. Instead of guessing that a table variable has exactly one row (which breaks most real-world queries), the engine waits until the table is populated, measures it, and builds an informed plan.
And if your code uses scalar user-defined functions , SQL Server 2019+ can inline them, turning what used to be a loop into a set-based operation. That alone can turn a 5-minute report into a 5-second one.
The beauty here is that you may not need to rewrite or refactor anything. You just get smarter plans, more consistent performance, and less time spent chasing parameter sniffing ghosts. All of the above have limitations. Do your homework and proper testing.
Faster Recovery & Safer Rollbacks
Ever had a long-running rollback or crash recovery hang your system? Accelerated Database Recovery (ADR) changes the game—making rollbacks and crash recoveries significantly faster, which is a safety net when things go sideways.
Query Store on Steroids
In older versions, you’d turn on Query Store, fiddle with settings, maybe capture plan regressions. In newer versions, it’s more mature, more integrated, and more automatic. You get insights, forced plan control, and regression protection with minimal overhead.
Security That Doesn’t Feel Optional
TDE, always encrypted, ledger capabilities (in 2022), granular auditing – these aren’t checkboxes anymore, they’re baseline expectations. Newer versions make it less painful to stay compliant and secure.
Hybrid & Cloud-Aware by Design
Backup to URL, cross-environment DR, and more. The newer SQL Server versions are built from the ground up to span on-prem, cloud, or hybrid without the constant “lift and re-architect” panic.
Predictability & Fewer Surprises
2019 and 2022 have been battle-tested at this point. Most of the early-stage regressions, bugs, or inconsistent behaviors have been discovered and addressed in the Cumulative Updates. That predictability is worth its weight in gold when you’re managing risk.
Stable isn’t the same as safe. SQL Server 2016 had a great run, but it’s time to let it retire gracefully.
Plan your move to 2019 or 2022 this quarter. Skip 2025 for now. You’ll sleep better.
———————————————————–
Need Migration Help?
Reach out to Dallas DBAs with code “Newsletter”
———————————————————–
Thanks for reading!
At some point every company faces it: the SQL Server that’s been quietly running for years is due for retirement. Maybe the hardware is old, the lease is ending, or your CIO wants to move workloads to the cloud.
From the outside, a SQL Server “migration” can sound like a simple lift-and-shift. Just copy the databases over, right? The reality is closer to moving offices. You don’t just grab every box and throw it into a new building. You measure the space, update the wiring, decide what gets upgraded, and make sure everyone can find their desk again on Monday.
Predict & Provision
The new environment needs to handle both today’s workload and tomorrow’s growth. Simply matching your old CPU, RAM, and storage can be a mistake if your business has grown since the last server was purchased. In the cloud, it’s even more important to right-size. Too small and you’ll choke performance, too large and you’ll bleed money. Planning capacity up front avoids both. For cloud VMs, provision low during testing and bump up the size as needed.
Install & Configure
SQL Server isn’t plug-and-play. A fresh installation with updated patches and best-practice settings sets the stage for stability. This is where you decide things like where to place (and separate) data and log files, how many tempdb files to allocate, and which default settings to avoid. A solid foundation here can prevent countless problems later.
Tune the Source Before the Move
One of the biggest mistakes in any migration is bringing old baggage (technical debt) into a new system. Giant log files, bloated indexes, and unnecessary jobs can cause just as much trouble on shiny new hardware as they did before. Tuning the source first is similar to decluttering your house before moving – you start fresh without dragging the junk along. Or cleaning the bathroom before your housekeeper shows up 😉
Move the Data
Databases aren’t the only things that need to come across. Logins, SQL Agent jobs, linked server definitions, and security settings are just as important. If you miss these, users may not be able to connect, backups may not run, or nightly jobs could fail. Successful migrations treat this as a holistic move, not just a database restore. There are multiple approaches to this, depending on your data size and cutover window.
Test, Test, Test
Once the new server is up, applications need to prove they can connect and perform. Something as small as a changed network name or a forgotten firewall rule can cause chaos. Testing gives you a safe window to discover what doesn’t carry over cleanly. It’s also a chance to capture new performance baselines so you can measure improvement.
Final Cutover
The actual “move day” should be planned, short, and closely monitored. Typically this means scheduling downtime, running one last backup and restore, and redirecting applications or DNS. The next 48 hours are critical: you’re confirming not only that the server is online, but that backups succeed, jobs run, and performance holds steady. With good prep, the cutover feels more like flipping a switch than rolling the dice.
For large databases in the TB+ range, a full backup/restore during the week with only a Differential needed on cutover day can reduce the amount of time dramatically.
Here’s the piece many companies miss: migrations are an opportunity to fix what wasn’t working. If you had jobs that failed silently, indexes that were never used, or security shortcuts, they’ll follow you into the new system unless you address them first. Treating the migration as a reset, or a chance to leave bad habits behind, means the business not only gets a new server, but a more reliable platform for the future.
A SQL Server migration is less about moving bits and more about moving confidence. With the right planning, you don’t just get a new server – you get a healthier, more reliable foundation for your business applications.
Free Disaster Readiness Quiz
I’ll trade you an email address for an honest assessment
Thanks for reading!
