DallasDBAs.com

SQL Server Database Consulting

Kevin3NF

SQL Server Reliability: Clearing the Fuzzy Thinking

by Leave a Comment

The terminology around reliability is a mess

If you’ve ever said, “We’re covered, it’s replicated,” you’re in good company.

 

SQL Server is a massive, 35+ year-old product that has changed, renamed, and re-imagined its features so many times that even seasoned IT pros get tripped up. Between log shipping, mirroring, replication, snapshots, Availability Groups, clustering, and VM backups, it’s no wonder managers end up with fuzzy expectations about what keeps their data safe.This isn’t a failure of leadership; it’s the side effect of a product that’s both deep and deceptively familiar. You’ve got “copies,” “replicas,” and “backups” everywhere, but they don’t all mean the same thing.Let’s clear the fog.

Replication Isn’t Disaster Recovery

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 DR, But Not HA

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.

Snapshots Aren’t Backups

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.

Availability Groups Don’t Replace Backups

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.

Why It Gets Fuzzy

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:

  • Replication sounds like redundancy.
  • Availability sounds like recovery.
  • Snapshots sound like backups.

That’s why it takes a DBA’s mindset. Understanding and translating these terms takes intentionality and years of exposure to them.

What can you do?

  1. Inventory your protection layers. List what you’re using (replication, AGs, log shipping, backups) and define what each one really does.
  2. Map to business needs. High availability ≠ disaster recovery ≠ data protection. You probably need all three, in different forms.
  3. Test the ugly scenarios. Shut down primaries, take databases offline, pull power cords (in dev). You’ll learn fast which “solution” is just a hope.
  4. Document the real RTO/RPO. Your CFO doesn’t care if it’s log shipping or AGs — they care how long you’ll be down and how much data you’ll lose.

The Bottom Line

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.

Reach out for a free basic Health Check

We can focus on your server’s Reliability and Availability.

Contact Us

SQL TidBit:

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.

Link Party

  • Erin Stellato: “The Accidental DBA Day 23 of 30: SQL Server HADR Features”
  • Redgate / Simple-Talk: “SQL Server Backup and Restore for the Accidental DBA”
  • SQLShack: “Understanding Database Snapshots vs Database Backups in SQL Server”</li >
  • Brent Ozar: “Transactional Replication doesn’t solve your HA or DR pains”
  • Microsoft Docs: “Log Shipping and Replication (SQL Server)”

Please send to your manager to help clarify the differences in SQL Server HA, DR, and distributed computing. They deserve a clear cheat sheet.

 

Data Conferences – Worth Every Dollar

by Leave a Comment

Some of the best career enhancers you can buy.

 

Why I Go to Conferences

I go for two big reasons:

  1. Learning from the best. The folks teaching at PASS Summit or SQLBits aren’t reading from slides. They’re the ones writing the scripts, blog posts, and tools we all use such as the First Responder Kit, Ola Hallengren’s maintenance solution, and countless others. You get to learn how the creators think.
  2. Community. I’ve built friendships at these events that turned into collaboration, mentorship, and yes, a few “help me right now” text messages at midnight. You can’t get that on YouTube.

A few favorite memories:

  • Watching Brent Ozar and Pinal Dave tag team with Pinal asking questions the audience should be asking.
  • That time Kalen Delaney saw ‘Kevin3NF’ on my badge and said “I know you!” (We had never met offline)
  • Late-night lounge war story sessions with strangers who became friends over adult beverages.
  • Game night at PASS Summit

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:

  • Plan ahead. Pick sessions that fill your knowledge gaps, not just what sounds cool.
  • Talk to people. Even if you’re introverted, one hallway conversation might change your career.
  • Bring something back. Document 3–5 takeaways to justify the trip (and remind your boss why it’s valuable).

 

If travel isn’t in the cards, start small: attend a local Data Saturday or User Group meeting. The ROI is incredible.

Always On Availability Group

by Leave a Comment

 

Always On Availability Group

Official Definition

An Availability Group (AG) provides HA/DR by hosting a set of databases that fail over together between replicas.

Simplified Explanation

Availability Groups are SQL Server’s premier high-availability solution that lets you maintain multiple synchronized copies of your databases across different servers. Unlike older solutions that worked with single databases, AGs group related databases together so they all fail over as a unit – crucial for applications that depend on multiple databases staying in sync.

The primary replica handles all the work while secondary replicas can serve read-only queries and provide automatic failover protection. You can have up to 8 replicas total, with some synchronous (guaranteed identical data) and others asynchronous (faster but slight data lag).

This flexibility makes AGs suitable for everything from local high availability to disaster recovery across continents, though they do require Windows clustering for automatic failover.

Further Reading

See the official Microsoft documentation on “Always On Availability Group” for a more detailed explanation.

SQL Server Versions: Out With the Old, In With the Supported

by Leave a Comment

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.

 

What You’re Missing

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.

 

The Bottom Line

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”

Contact Us

———————————————————–

Thanks for reading!

SQL Server Migration Overview

by Leave a Comment

It’s Not Just Backup / Restore

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.

The Big Picture

 

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.

Why Preparation Matters

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.

The Bottom Line

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

DR Quiz – Are you ready?

 

Thanks for reading!

SQL Server Alerts

by Leave a Comment

Don’t Let Trouble Sneak Up on You

 

Most SQL Servers run quietly. Until they don’t. By the time someone notices an application outage or a failed backup, you’re already behind. That’s why SQL Server’s built-in alerts exist – they give you an early warning before small problems become major outages.

There are a bunch of great 3rd party tools and community scripts available, but not every firm is going to make that investment or allow open-source code on their servers.

SQL Server Alerts are Microsoft-supported, built into the product, and rely on Database Mail for notifications. Configure them once, and you’ll have a safety net that runs 24/7. But like smoke detectors, too many false alarms and you’ll start ignoring them.

 

Step 1: Create an Operator

An operator is just the person (or distribution list) that gets notified.

In SSMS:

  • SQL Server Agent >> Operators >> (right-click) New Operator
  • Fill in a name and email address (use a group if possible). 100 character limit.

 

T-SQL Example:

USE msdb;

EXEC msdb.dbo.sp_add_operator  

    @name = N'DBA On Call',  

    @enabled = 1,  

    @email_address = N'[email protected]';

 

 

Step 2: Define the Alert

Alerts can fire on:

  • Specific errors (e.g., error 823 = disk I/O issue)
  • Severity levels (e.g., all severity 20+ errors)
  • Performance conditions or WMI Events

In SSMS:
SQL Server Agent >> Alerts  >> (right-click) New Alert  >> choose type and scope.

 

T-SQL Example:

USE msdb;

EXEC msdb.dbo.sp_add_alert  

    @name = N'Error 823 Alert',  

    @message_id = 823,  

    @severity = 0,  

    @enabled = 1,  

    @delay_between_responses = 300, -- 5 minutes 

    @include_event_description_in = 1,  

    @notification_message = N'Disk I/O error (823) detected!';

 

Step 3: Tie It Together

Link the alert to the operator so someone actually gets notified.

In SSMS:
Open the alert >> Response >> “Notify Operators”

 

T-SQL Example:

EXEC msdb.dbo.sp_add_notification  

    @alert_name = N'Error 823 Alert',  

    @operator_name = N'DBA On Call',  

    @notification_method = 1; -- Email

 

 

Step 4: Enable the Mail Profile

Emails won’t get sent without this often-overlooked step.

In SSMS:

SQL Server Agent >> (right-click) >> Properties >> Alert System >> Check “Enable Mail Profile” and pick a profile from the drop down. This required Database mail to be configured and working.

 

Step 5: Cut the Noise

Not every warning deserves an email at 3 a.m. Start with the essentials:

  • Possible Corruption (823, 824, 825)
  • Critical Job failures (Agent jobs)
  • Severity 19+ errors (fatal errors, serious resource issues)
    • Severity 20 may give false positives if you are using vulnerability testing software
  • HADR role changes for unexpected AG failovers

 

Then, test and adjust. If the alerts are noisy, you won’t trust them when it matters.

 

The Bottom Line

Setting up alerts in SQL Server is one of the easiest wins for DBAs. They’re built in, supported by Microsoft, and once tied to Database Mail and operators, they can catch serious issues before your phone rings. Just be selective, as too much noise, and the real signals get lost, or emails get “ruled” into a folder rather than acted on.

 

Free Disaster Readiness Quiz

I’ll trade you an email address for an honest assessment

DR Quiz – Are you ready?

 

Thanks for reading!

 

— Kevin

Home Blog About Privacy Policy