Just Databases

Early on in my mainframe days I worked at the Kenedy Space Center for NASA here in Florida. My first foray into databases on a mainframe was IBM’s Information Management System (IMS). It was a hierarchical database and transaction manager that is still in use, even though it wa developed in the 1960s to track parts for NASA’s Apollo program. Yes, primitive now, but at the time it eventually became a staple for over 95% of the Fortune 1000. It’s still in use, processing billions of transactions every day, even now. 

I also used a data base software application called SQL/DS on the mainframe, which became DB2. I still have a book around my office somewhere with the “System R” logo on it where it was developed at IBM.

In all of those cases, data was just data. We moved from COBOL flat-files to an engine over relational tables, but it was just data. Applications (green-screen terminals, thank you very much) put data in, and took data out. The function of the “database” was to do that as quickly, efficiently, and securly as possible.

Surely You Can do Better than That

And then I disovered and managed and used Oracle. Oracle had *Forms*. It had a reporting system , all built in. And then they added Java! And all kinds of new data types outside text, dates and numbers. 

It was this kind of thing, pioneered by companies like Oracle, that allowed organizations to think about centering their data estate (that became a thing) around a database platform.

SQL Server – the Little Engine That Could

In the late 1980’s, in addition to the mainframes NASA used, we also had IBM’s OS/2. I joined the team that focused on this product, and one of the interesting applications it had was a command-line based database called “Microsoft SQL Server” – version 1.0. It was even client-server, meaning the database engine could run on one computer and another computer (the client) could access it over the (Token Ring) network. I was fascinated with the “Transact-SQL” dialiect of SQL it used. 

IBM and Microsoft broke up over OS/2, and I started my journey with Windows operating sytems, including “Windows NT Advanced Server”. By now I was using SQL Server version 6+. While Microsoft added Stored Procedures and other engine features, they were just that, engine features. For a database engine.

Except one: The opening of the system with the Replication feature. The ability to send some or all of the data in one database to another using a dedicated system. Data could now be used differently by entirely different application needs. And companies started thinking about the database engine a little differently.

SQL Server – Let’s Be a Platform

Then came SQL Server version 7 – a completely rewrite of the platform but with (mostly) backwards compatibility. It included not only a highly capable database engine with a wide suite of programability features, but Microsoft started adding things right in the box that drastically extended the ability of the system to host business-critical data estate functions. One of the most impressive was “OLAP Services”, which as the name suggests, was a full Business Intelligence (then a pretty new term) server. To fill, process and orchestrate that system, they added “Data Transformation Services” or DTS, a feature that was pushed by us administrators far past its intended purpose of Extract, Transform and Load (ETL). 

SQL Server – The Kitchen Sink

Fast forward to SQL Server 2005, and we got all the “S’s”: Analysis Services for BI, Reporting Services for, well, reporting, and Integration Services which is actually hard to pin down. Integration Services does so much that I’ve seen pure Oracle shops buy SQL Server just to use Integration Services for everything from data transforms and movement to a full job system.

Then I Joined Microsoft on the SQL Server team. What an eye-opener for me. This product was massive and had grown FAR beyond a database. You scaled out the data functions to multiple servers, with complicated disk arrangements and multiple network cards.

And from there, more and more features showed up in heavy and rapid fashion. We added every data type you can think of: you could store whole Microsoft Office documents, music, pictures, and search them all. Full text. In-memory.  We added not only geographic datatypes, but geo-spatial datatypes. I had one client using that feature to map out their entire warehouse, down to the shelf dimensions so they could find exactly which boxes would fit where for the most efficient storage.  In a database!

I moved from the SQL Server engineering team to work as a technical pro at Microsoft in the field, I then joined the team that built Azure, and then I joined the team that was implementing Machine Learning at Microsoft. And of course then that went into SQL Server. You could (and still can) run your own self-contained AI constructs and solutions and applications and never leave SQL Server. 

I was also part of the ill-fated “SQL Server Big Data Clusters” – a system ahead of its time that could scale from on-premsises to the cloud, with so many features running in Linux an Kubernetes no less.

Companies large and small, around the world, built their business logic, data processing and analysis, reporting, and now even AI into the SQL Server product.

But it got a bit, well, overwhelming. Having everything, and I mean everything, in the box can make for complex upgrades, complicated troubleshooting, and make it hard to keep up with what the system can really do. 

Adding to this, some of the features are more mature and stable than others. Some very much so. 

SQL Server – Just a Database

Things, as they always do, have changed. Developers are now using scale-out, cloud-first, micro-services, per-server data processing, which feeds much larger more spread-out systems. Data storage is now separated away from (as in my COBOL days of old) from the processing engines that used it. Vendors – even Microsoft – tended to focus on larger distributed sytems.

The expensive licenses of SQL Server doesn’t hold the same value if you’re just using it as a database. Postgres is “free” (not really, but that’s the mindshare) and if your data is small you can use SQLite (love me some SQLite) and the stunningly amazing DuckDB. DuckDB, for me, holds the same excitement level as the SQL Server version 7 days. The little engine that could. 

SQL Server – So Wither Forth?

In some clients I work with, they feel they are being left behind if they don’t move to the newest cloud analytics platforms currently battling for their data budget.

In some cases, after we do a deep audit of their needs, SQL Server still fills the bill quite well. Yes, it needs to be designed and implemented properly, but if you do, there’s a lot going for it. Yes, pricing is pretty high, but compared to cloud costs, it can actually be significantly cheaper.

So – will SQL Server be your “Platform in a Box” or cast aside for cheaper, smaller, faster “Just a Database” products? I’ll fall back here on the most famous saying in the Database Administrator (DBA) world: “It Depends”. Sorry, but there it is. I think for many, many shops, if you take the time to plan, implement and manage it well, SQL Server cannot be beat. It really can’t. 

For some workloads, however, distributed storage and compute is a great idea. If you haven’t investigated  with some of the major systems, I highly recommend you do. Oh, and while you’re at it, play around with DuckDB. It will amaze you with what it can do, directly over storage like Delta/Parquet. And while you’re at it, take a look at some of the newer systems that bypass the “Reporting” layer entirely. I’ve seen some lately that are an absolute sea-change. 

Everything old is new again. 

The post SQL Server: Database or Data Platform? appeared first on SQL Server Consulting - Straight Path Solutions.

For several versions of SQL Server, the maximum log file size was noted at 2 TB. If you ever reach that limit you could find yourself in a Disaster Recovery scenario.

Context

We received an emergency call from a client that noted that their SQL Server instances was unresponsive. (This was an Amazon RDS instance, although that didn’t play much into the ultimate root cause.) The client had some technical staff already looking at the issue, and when we joined the call we were informed that the transaction log for their main production database was completely full, and all transactional activity in the database had stopped.

For many versions, SQL Server has had a default maximum files size of 2 TB on transaction log files. Even though newer versions like SQL Server 2022 and 2025 may say “unlimited”, they aren’t truly unlimited. The current documentation notes that transaction log files still have a 2 TB maximum file size.

Investigation: Why log_reuse_wait_desc Showed REPLICATION

After a quick review, we confirmed that the transaction log for their main production database was completely full, and had reached the maximum size of 2 TB. The log reuse description in sys.databases showed “REPLICATION” as the cause for the log being full.

This instance did not have replication, but the client did have Change Data Capture (CDC) enabled on numerous tables, which also shows up as “REPLICATION” in the lof reuse information. We discovered the CDC capture and cleanup jobs had not been running for weeks, which caused the transaction log to hold transactions and not mark them for reuse.

Unfortunately, before we joined the call, the client had enabled CDC thinking that would “clean up the log”, but what that did in the immediate moment was fill up the log faster as it generated more transactions to update data in the CDC support tables.

So now the file was already at the 2 TB limit, meaning it couldn’t be grown further. But here’s the biggest problem: any other solution to reduce the log file size at this point would not work because…it would involve a logged transaction. And the log was full.

Possible solutions include:

• Disable CDC – that’s a logged transaction
• Add another log file – that’s also a logged transaction
• Declare it a disaster and restore the entire database from the most recent backups

The fix – kill another transaction and add another log file

We wanted to avoid restoring the database from backup, as that could mean further hours of downtime as well as more lost transactions. We determined the best possible solution was to try to Kill one of the smaller queries using the transaction log, and then quickly add another log file. It took several tries, but eventually we were able to kill one and add a second transaction log file, which allowed other transactions to start completing again.

Something like this:

BEGIN TRANSACTION;

/* Kill session 99 to release any locks/log space it may be holding */

 KILL 99;

/* Add a second log file to DatabaseName on a different drive */

ALTER DATABASE [DatabaseName]

ADD LOG FILE (

NAME = N'DatabaseName_log2'

, FILENAME = N'L:\ Logs\DatabaseName_log2.ldf'

, SIZE = 64MB

, MAXSIZE = 2TB

, FILEGROWTH = 64MB);

 COMMIT TRANSACTION;

After we had the second log file we were able to monitor the progress for CDC and the drive space used by all the transactions recorded. This took a few hours but eventually caught up, and afterwards we emptied and removed the extra transaction log file and resized the original log file to reduce the massive amount of Virtual Log Files (VLFs). We also added a job to alert us in case the transaction log became more than 50% full to avoid another occurrence of this issue.

We were very fortunate, because had we not found a tiny windows to add another log file we would have had no choice but restore from backups. While you’re reading this, make sure your backups are actually ready for restores – check out our free tool, sp_CheckBackup to make sure.

The post SQL Server Log File Full at 2 TB Max with CDC: How We Recovered appeared first on SQL Server Consulting - Straight Path Solutions.

This month we have way more updates than in March, including recommendations from the Issues section of GitHub, so thank you to all who contributed!

We also have been working on the content pages referenced by the URLs in the stored procedures. We’ve been going through them all to make sure the content was more helpful. Lots of the previous content pages have had simple sentences that weren’t very descriptive, so we’ve been filling those out to provide more explanations.

This month the updates are mostly corrections, although there were additional checks added to sp_CheckBackup and sp_CheckTempdb.

We hope these new updates can help you, especially if you’ve never used these stored procedures. Here are the updates for April 2026, with links to the GitHub repositories where you can download the latest versions.

sp_CheckAG

• CORRECT omission of DTC Support check for SQL Server 2016
• CORRECT CPU configuration details (CheckID 113)
• CORRECT IP Address details for case sensitivity (CheckID 119)
• CORRECT check for recent events to account for AlwaysOn_health XE being stopped (CheckID 426)
• CORRECT incorrect logic for determining Seeding Mode
• ADD check for AlwaysOn_health XE being stopped  (CheckID 429)
• UPDATE check for Endpoint Owner to use SID in case sa login renamed (CheckID 406)
• UPDATE check for AG Owner to use SID in case sa login renamed (CheckID 407)
• UPDATE check for replica database not joined for clarity (CheckID 413)
• UPDATE check for session timeout to 2 checks – one at default, one below (CheckID 415)
• UPDATE check for offline listener to include IP and state (CheckID 417)
• UPDATE check for replicas not failover ready to include Server Instance (CheckID 421)
• ADD EndpointOwnerSID to Endpoints result set
• ADD AGOwnerSID to Availability Group result set
• CORRECT typos in CheckID 410, 415

sp_CheckBackup

• ADD EncryptionType column to @Mode = 2
• ADD CompressedBackupSizeInMB and CompressionPercent column to @Mode = 2
• ADD check for integrity checks (CheckID 503)
• ADD check for backup history not purged (CheckID 216)
• ADD check for high VLFs (> 200) (CheckID 217)
• ADD check for backups to NUL (CheckID 218)
• CORRECTED Check for password protected backups (CheckID 215)
• Corrected typo in CHECKID 211

sp_CheckSecurity

• CHANGE check for most recent version for SQL Server security updates
• CHANGE Issue, Details, and ActionStep data type in #Results to VARCHAR(MAX)
• CHANGE Failed Login check to suppress error message (CheckID 330)
• CHANGE Number of Error Logs check to suppress error message (CheckID 801)
• CHANGE Hide Instance check to suppress error message (CheckID 348)
• CHANGE Extended Protection check to suppress error message (CheckID 348)
• CHANGE Force Encryption check to suppress error message (CheckID 349)
• CORRECTED logic for creating #InvalidLogins table
• CORRECTED incorrect ActionStep for Contained Databases (CheckID 344)
• CORRECTED typos in ChecIDs 304, 305, 307, 310, 313, 314, 316, 321, 323, 324, 327, 330, 351, 622
• CHANGE CheckID 801 (error log retention is at default) to CheckID 622

sp_CheckTempdb

• ADD description of @Mode = 99 to @Help
• CORRECT usage of @Size in @Help
• CORRECT checks for Slow reads and Writes (CheckIDs 718 and 719)
• CORRECT redundant check for tempdb file with no growth allowed (CheckID 605)
• CORRECT redundant check for tempdb file with percentage growth (CheckID 711)
• CORRECT redundant check for tempdb file with growth < 64 MB (CheckID 712)
• CORRECT redundant check for tempdb file with high usage (CheckID 714)
• CORRECT logic for finding more than 8 cores (CheckID 707)
• REMOVED redundant code for @UsagePercent
• ADD code to avoid negative values for minimum @AvgReadStallMs and @AvgWriteStallMs
• ADD drop of #TraceFlg before it is created
• ADD check for more than 16 data files (CheckID 730)

The post Updates for all sp_Check tools – April 2026 appeared first on SQL Server Consulting - Straight Path Solutions.

But here’s the kicker: your CIO doesn’t care about page life expectancy. They care about data monetization.

The Problem: SQL Server Isn’t the Whole Playground Anymore

Once upon a time, uptime was the crown jewel. If the server was online, you were the hero. But now? Data is flowing into cloud warehouses like Snowflake and Databricks – maybe even Microsoft Fabric. Meanwhile, Marketing spins up SaaS apps faster than you can say “linked server.” And they’ve hired or subbed out an entire analytics team that’s building Spark or Data Warehouse with Dashboards or Jupyter Notebooks that bypass you entirely.

And when those silos don’t talk to each other and the data doesn’t work for what they are doing? Guess who gets blamed. Yep, the DBA. You. Suddenly, you’re not the guardian of uptime; you’re the bottleneck on the road to AI progress or whatever Next Big Thing™ has your CEO’s ear.

The Pivot: Stop Being the Mechanic, Start Being the Architect

So what do you do? Sure, you can hunker down and work even harder on T-SQL and internals, but will that help? Think of it this way: Mechanics keep the engine running. Architects design solutions so that the engine powers the business.

In larger companies, the Chief Data Officer (CDO) role exists for one reason: to turn data into business value. But you don’t need a fancy title to start acting like one. What you do need is a strategy. That strategy is up-leveling your talents, and maybe bringing in some help.

But Code Though (Because You’re Still a DBA at Heart)

Let’s be honest: you’re not going to stop focusing on the servers and keeping everything up and running. But let’s take a look at a couple of queries to illustrate the mindshift you need.

The first one is typical of a report from a monitoring tool. It says, “Hey boss, the server’s been up for 47 days.” You’ve shown your value – your systems are up and running when needed. Except….

-- Old world: prove uptime SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

That doesn’t mean anything to your Leadership. I’ve always said, unless you’re in a small company, people two levels above you have no idea what you do. So, how do you map the things you do to the things Leadership cares about?

Well, while the monitoring Dashboards you use are probably pretty useful, they don’t tell the boss why they should care about your work. A better approach is to map the result of your work to something that drives revenue for the company. I know that sounds marketing-y. Don’t make that face. This is what they care about.

How about this instead:

SELECT s.sqlserver_start_time, COUNT(*) AS ‘CustomerPurchasesLast3Months’ FROM sys.dm_os_sys_info s CROSS JOIN dbo.Customers c JOIN dbo.Orders o ON c.CustomerID = o.CustomerID WHERE o.OrderDate >= DATEADD(month, -3, GETDATE());

———

sqlserver_start_time CustomerPurchasesLast3Months

2025-09-01 08:15:00 12,345

This says, “Hey boss, here’s how many customers bought something in the last quarter on that server that I keep running for you.” Guess which one gets you invited to the strategy meeting with the Spark-ly people.

Be the Data Advocate

Being a “Data Advocate” is the new job security; some here at Straight Path say it’s always been the job. The DBA who only cares about uptime is replaceable. The DBA who champions data as a business asset? That’s the one who probably sticks around, maybe even gets a promotion from time to time. So the next time you’re tempted to brag about your deadlock troubleshooting skills, pause. Ask yourself: How is this data making the company money?

That’s the straight path to data leadership.

The post From “Keep it Running” to “Make it Valuable”: Why Your SQL Skills Aren’t Enough Anymore appeared first on SQL Server Consulting - Straight Path Solutions.

The Investigation

The error message from Azure Managed Instance told the story right away: “The restore plan is broken because firstLsn (12345000000456700001) of log backup XXXXX is not equal to the lastLsn (12345000000123400001) restore point in time failed.” If you’ve worked with SQL Server backups, you know what the LSN (Log Sequence Number) is. It’s how SQL Server knows where the last backup left off and where to start the next one, so every transaction is captured. When those numbers don’t line up, SQL Server won’t let you restore, and for good reason.

My client had a process in place since they were running the migration, and we were there to catch them if needed. They were going to stop backups with their third-party backup tool, then use native SQL Server backups pointed to Azure Blob Storage to build their restore chain into Managed Instance. The plan made sense.

I quickly grabbed our trusty community tool sp_CheckBackup and ran this on the source:

SP_CheckBackup @Mode = 3, @DatabaseName = 'TheDBTHatIsErroringEvenWithTHeirPlan'

Mode 3 checks for split backup chains, backups going to different locations, anything that could complicate recovery. Even though they had that plan. this sure felt like a split backup chain… My gut was right. Some log backups were taken outside of the Blob Storage backup plan, after the initial full, differential, and logs. “Rogue Backups,” we call them at Straight Path (though sometimes it gets typoed to rouge backups – so we’ve started calling them by either name.)

Here’s what the timeline looked like:

• 12:00 PM – Full backup to Blob Storage (restored successfully)
• 6:00 PM – Differential backup to Blob Storage (restored successfully)
• 6:10 PM – Transaction log backup to Blob Storage (restored successfully)
• 6:20 PM – Rogue transaction log backup taken by the third-party backup tool (not in Blob Storage)
• 7:10 PM – Next scheduled log backup to Blob Storage (FAILED to restore, LSN mismatch)

That rogue backup at 6:20 PM was the problem. Even though the client had removed the database from their third-party backup policy, the backups didn’t actually stop running. That third-party log backup broke the chain that had been established in Blob Storage because it advanced the LSN past what our next Blob-based log backup expected.

The Fix

Stop the third-party backup solution entirely before initiating backups to Blob Storage. Don’t just remove the database from the policy. Actually confirm the backups have stopped. Run our free community tool, sp_CheckBackup to verify no rogue backups are sneaking in before you start your restore chain.

The Lesson

In this case, my client took a fresh full and differential during the migration activity and was eventually successful, but it was painful. And this was for a 1-2GB database. Imagine this with your 2TB or even 24TB database at GO LIVE. Quivers in chair.

Here’s what you should be doing if you’re planning a migration to Managed Instance (or any backup/restore migration):

1. Confirm all third-party backup tools are fully stopped, not just “removed from the policy.” Verify they are no longer touching the database. Run sp_CheckBackup to confirm.
2. Validate your backup chain before you start restoring. Use sp_CheckBackup @Mode = 3 to check for split chains and unexpected backup sources. If someone or something else took a backup you weren’t expecting, you want to know before you’re mid-migration, not after.
3. Bring your DBA team into migration planning early. With a little coordination on a planning call, gotchas like broken backup chains can be spotted before the big day. This one could have been solved immediately if we’d been on the call when it happened. And during cutover planning, we could have flagged the risk before it became a problem with a bit more involvement from the start.

Rogue backups are one of the most common issues we see across new client environments. Multiple tools taking backups of the same database, often without anyone realizing the other exists. Mike Walsh wrote about this exact pattern in SQL Server Backups and the Illusion of Safety, where he points out that when everyone is responsible for backups, sometimes no one is actually accountable for recoverability.

The Straight Path Team and Skills

Backup chain integrity is one of the first things we train any new DBA on at Straight Path. sp_CheckBackup is one of our free community tools, largely built by Jeff Iannucci and David Seis. I’m confident that anyone on our team would have done the same troubleshooting here. Backups and recoverability are DBA skills 101, and the majority of our clients perform routine restore testing and integrity checks to make sure data is safe and ready for when it matters most.

Straight Path Solutions is a team of nearly 20 SQL Server experts trusted by 120+ clients. We treat our clients’ environments as if they were our own because our job depends on your success. Making your life pain-free in the land of SQL is the rewarding part of our work. If you’re planning a migration, dealing with backup concerns, or just want to sleep better knowing someone’s watching your environment, reach out to us.

This post is part of our Case of the Week series, real SQL Server issues and lessons from the field.

The post SQL Server Case of the Week: A Broken Backup Chain Blocks Migration to Azure SQL Managed Instance appeared first on SQL Server Consulting - Straight Path Solutions.

A businessperson pulls a report from a data warehouse, runs the same query they’ve used for two years, and gets a number that doesn’t match what the finance team presented at yesterday’s board meeting. Nobody changed the report. Nobody changed the dashboard. But somewhere upstream, an engineering team renamed a field, shifted a column type, or quietly altered the logic in a pipeline, and nobody thought to mention it because there was no mechanism to mention it.

While we think of this as an engineering failure, it’s more of an implied contract failure. More precisely, it’s the absence of a formal contract. Data contracts are one of the most practical tools a data organization can adopt, and one of the most underused. The idea is not complicated: a data contract is a formal, enforceable agreement between the team that produces data and the team that consumes it. It defines what the data looks like, what quality standards it must meet, who owns it, and what happens when something changes. Think of it as the API layer for your data, the same guarantee a software engineer expects from a well-documented endpoint, applied to the datasets and pipelines your business depends on. This post is about why that matters at the CDO level and how to get them put in place.

The Problem with Good Data Intentions

Most data teams operate on informal contracts. A producer team knows that the analytics team uses a certain table. The analytics team assumes that the table will always have the same fields in the same format. It’s not written down, agreed to, or formalized it. It just became an unspoken convention.

Unspoken conventions are fragile. When the producing team moves to a new schema, the consuming team finds out when the dashboard breaks. When a definition shifts, the downstream metric drifts without warning. When a new consumer starts using a dataset, they’re inheriting assumptions they didn’t know existed. Relying on informal agreements to manage data quality at scale is relying on chance and time. Those two things are not working for you, in my experience…it’s quite the opposite.

The result is a cycle that most data leaders know by heart: an incident surfaces, a postmortem happens, someone adds a note to a Confluence page, and the cycle repeats six months later with a different field and a different team. The documentation exists, but it isn’t enforced. Documentation without enforcement is just a polite suggestion.

Data contracts break this cycle by making the agreement explicit, visible, and machine-enforced. They move quality governance from a reactive discipline, where you discover problems after the fact, into a proactive one, where the pipeline validates expectations before data ever reaches a consumer.

What a Data Contract Actually Contains

A data contract isn’t a document you put in a SharePoint folder and review annually. It’s a structured, version-controlled specification that travels with the data and can be executed automatically as part of your pipeline. The components vary by implementation, but the core elements are consistent across mature approaches.

Schema and Structure

This is the foundation. The contract defines the fields, data types, allowed values, and nullability rules for a given dataset or data product. It’s the equivalent of an API schema, and it enforces the same kind of compatibility guarantees. A consumer who relies on a field being a non-nullable string should not have to discover at 2 a.m. that it’s now an optional integer.

Semantics and Business Meaning

Schema alone isn’t enough. Data contracts include definitions, the actual business meaning of each field. What does customer_id represent? Is it the account-level identifier or the contact-level identifier? Which definition of ‘active customer’ does this table use? These are governance questions, and they belong in the contract, not in tribal knowledge held by whoever has been on the team longest.

Service Level Agreements

A contract defines the quality commitments the producer is making. How fresh will this data be? What’s the acceptable error rate? When does the data land, and what does ‘on time’ mean? These are commitments you can monitor, alert on, and hold producers accountable for, because they’re written down and agreed upon rather than assumed.

Ownership and Stewardship

Every data contract should identify the team or individual responsible for the data product, the escalation path when something goes wrong, and the process for requesting changes. Ownership without a documented path creates the same orphaned-dataset problem that governance programs have struggled with for decades. The contract makes ownership explicit and findable.

Change Management and Versioning

This is where contracts earn their value in the long run. When a producer needs to change the schema, the contract defines the process: how much notice is required, how breaking changes are communicated, and how version deprecations are managed. Versioning lets producers evolve their data products without blindsiding the teams that depend on them.

Producer-Defined vs. Consumer-Defined Contracts

Here’s where implementation gets nuanced, and it’s worth being direct about what works and what doesn’t in practice. The obvious approach is to have producers define the contract. They know the data best. They know what it contains, how it’s generated, and what guarantees they can realistically make. This is what I see most often, and to bue clear, Producer-defined contracts are a significant improvement over no contracts at all.

But there’s a structural problem: producers often don’t know how downstream teams are using their data. They optimize for operational concerns. They make changes that seem reasonable from their perspective because they don’t have visibility into the analytical models, reports, and decisions that depend on the data they produce.

A contract defined only by the producer reflects what the producer thinks the consumer needs. A contract that includes consumer expectations shows what the business department actually needs.

Consumer-defined contracts, or contracts co-developed with meaningful consumer input, solve this issue. When the team building the analytical model has a hand in defining the schema and quality expectations, the contract reflects the actual downstream requirements rather than upstream assumptions. It also creates a feedback loop that a ticket queue will never produce, because consumers are involved in defining the standard before something breaks rather than after.

Here’s my recommendation: start with consumer-defined awareness. Let consumers document the expectations they rely on. Use that to generate awareness among producers about the downstream impact of their changes. Then move toward formal contracts that are collaboratively defined and enforced in the pipeline. The maturity curve is awareness, then ownership, then governance.

Enforcement Is the Difference Between a Contract and a Wish

The single most important thing to understand about data contracts is that a contract that can’t be enforced automatically isn’t a contract. It’s documentation with good intentions, and documentation without enforcement.

Enforcement means the validation logic lives in the pipeline, not in a spreadsheet. When data arrives from a producer, the pipeline checks it against the contract before making it available to consumers. If the data doesn’t meet the defined expectations, it doesn’t pass through. The issue is surfaced at the point of production, not discovered downstream when a dashboard breaks or a model produces nonsense output.

This is sometimes called shift-left”. Instead of catching problems at the point of consumption, you catch them at the point of creation. The cost of a data quality issue found at the source is a fraction of the cost of the same issue found after it has propagated through six downstream pipelines and influenced a business decision.

Patterns that work

So let’s dive in to the practical aspects of creating a data contract. They have a few features that I see used with success. Take a look at these (the What), and then decide the physical implementation you will use (the How):

Quality as code: Contract specifications written in YAML or similar formats, checked into version control, and executed directly in the pipeline. The contract is treated like any other code artifact: reviewed, tested, and deployed through standard engineering workflows.

CI/CD integration: Contracts are validated as part of the deployment process. A schema change that would break a downstream contract fails the build before it reaches production. Breaking changes require an updated contract version and advance notice to consumers.

Automated monitoring: Once the contract is live, the pipeline continuously validates incoming data against the specification. Violations trigger alerts, not just log entries. Producers are notified when they’re breaking a commitment their consumers depend on.

Lineage integration: Contracts embedded in metadata platforms connect ownership, definitions, and quality expectations to the data catalog. Any consumer who queries a dataset can see the contract, understand the quality commitments, and identify the owner without opening a single ticket.

The CDO Case for Data Contracts

As a CDO, you’re probably not writing YAML specifications. But you are accountable for the organizational conditions that make data contracts work, and you’re the executive who can remove the barriers that prevent their adoption.

There are four things the CDO function needs to own in a data contract program:

1. Data contracts only work when producers take responsibility for the downstream impact of their changes. That cultural shift requires executive reinforcement. It doesn’t happen from the bottom of the engineering hierarchy. Mandate the culture of explicit ownership.
2. If producer teams are measured on deployment velocity and nothing else, they’ll deprioritize the upstream coordination that contract maintenance requires. The metrics that support a contract culture include time to detect quality issues, rate of downstream incidents caused by upstream changes, and consumer satisfaction with data reliability. Measure what you want to see. Align the measurement model.
3. Contracts need a place to live that both producers and consumers can access. A metadata platform or data catalog that supports contract specifications, version history, and automated validation is a prerequisite. This is a platform investment, not a policy investment, and it requires CDO-level sponsorship to get funded. Invest in the shared infrastructure.
4. You don’t need to contract every dataset all at once. Identify the five to ten data products that drive the most critical business decisions, or that have caused the most downstream incidents in the past year. Contract those first. Build the muscle, prove the value, and expand from there. Start with the data products that matter most.

What “Good” Looks Like in Practice

When data contracts are working in an organization, a few things change in ways that are immediately visible. Engineers stop finding out about breaking changes from an angry Slack message. The pipeline tells them first, before the change reaches production. The conversation shifts from incident response to change coordination.

Analysts stop spending time reconciling conflicting numbers. They know which dataset is authoritative, they know when it was last updated, and they know what quality checks it passed. They spend that time on analysis instead of archaeology.

Data leaders stop measuring quality by the absence of complaints. They have instrumented visibility into contract compliance rates, SLA adherence, and the velocity of quality issues by domain. They can report on data reliability the same way engineering reports on system reliability.

And the business relationship with data changes. When business leaders can trust that a number in a dashboard has been validated against a known specification, they don’t waste meeting time questioning the data. They make the decision. That’s the definition of decision velocity, and it’s what a data organization is actually for.

Getting Started: A Practical Sequence

If you’re starting from zero, here’s a sequence that has worked in practice without requiring a rearchitecture of your entire data platform.

1. Which datasets power your most important decisions? Which ones have caused the most downstream incidents? Start there. You don’t need to tackle everything at once. Audit your highest-impact datasets.
2. Talk to your consumers. What do they expect from these datasets? What would break their work if it changed? This documentation becomes the foundation of the formal contract. Document the informal contracts that already exist.
3. Work with both producers and consumers to codify expectations: schema, semantics, SLAs, ownership, and change management process. Put it in version control. Define the contract specification.
4. Encode the contract’s quality rules into the pipeline. Automate the checks. Route violations to the producer before data reaches consumers. Implement pipeline validation.
5. Make contracts discoverable. If a consumer can’t find the contract, the governance value is lost. A data catalog that links datasets to their contracts, owners, and quality history is the operational infrastructure that makes the program scale. Connect contracts to your metadata layer.
6. Once the first set of contracts is working, identify domain owners who can drive contract adoption within their area. The CDO’s office sets the standard. The domains implement it. Expand by domain.

Data quality is not a technical problem. It’s an accountability problem. The technical tooling, pipelines, catalogs, validation frameworks, is a layer of infrastructure. But infrastructure doesn’t create accountability. Agreements do.

Data contracts make the agreement visible, enforceable, and real. They transform the implicit expectations that exist in every data organization into explicit commitments that can be monitored, measured, and acted upon.

Your business is already relying on informal contracts. The only question is whether you’ll formalize them before the next incident, or after it.

The post Data Contracts: The Agreement Your Pipeline Has Been Missing appeared first on SQL Server Consulting - Straight Path Solutions.

My goal was to grab every dbatools discovery command I could find and shape it into a usable health check for you to use. There are 700 dbatools commands, and portion of that is geared for discovery and testing. I did not include every possible command and as you can imagine there are a lot of moving parts. I’ve got it in a good state for you to take, look at, modify, and make your own. This is the dbatools powered checkup that made sense to me, I hope it is useful and exciting for you!

You can access all the source code and follow along in this post and the subsequent posts walking through this on our github. Like our family of sp_check* community tools, this is free for you to explore, understand, and use to improve your own environment.

Boilerplate

As a reminder, I created a multi-subnet lab with three developer edition SQL Server instances, one SQL Express and a network share based on this blog post by Jonathan Kehayias. I will likely need to rebuild it after the beating it has taken through this blog series, however that makes it all the more useful for this round. If you are not familiar with dbatools yet, now is the perfect time to grab a test instance and learn! On your test environment you can open PowerShell and run the following scripts. These will install Dbatools and add it to the path for future projects as well as import it into the current session for the purpose of testing the scripts of this blog post.

Install-Module Dbatools

Import-Module Dbatools

The Core Design Principle: Read-Only

This SQL Server Health Check engine is designed to report, not add or change. The star of the show is meant to be DBAtools. I chose many of the GET and TEST cmdlets, some measures, and a few invoke-dbaquery. I will dig into the design and organization for your own testing and review, but this process is meant to read and report within your environment only. Each finding is linked back to the original dbatools function to point out how useful they are in your day-to-day DBA administration and review tasks!

SQL Server Health Check Architecture Overview

The suite follows a clean hub-and-spoke architecture with four layers:

Menu → Engine → Spokes → Output

• Start-Checkup.ps1 (Menu): The single entry point. You configure all thresholds, toggles, and paths here — nothing is buried elsewhere. It builds the canonical $Settings hashtable and calls the engine once.
• Checkup.Engine.ps1 (Engine): The orchestrator. It loads targets, discovers check modules, runs each module against each instance, and hands off to the output layer. It contains zero check logic itself.
• Spoke.*.ps1 (Spokes): Individual check packs covering specific domains. Each is self-contained and communicates with the engine through a strict contract — it receives a $Target, a $Config, and a [ref]$Findings list. Nothing else.
• JSON + HTML Output: Findings are first written to a timestamped JSON file (the system of record), then projected into a self-contained HTML report via Report.HtmlBuilder.ps1.
• Checkup.Catalog: The check catalog is the backend check organization tool. Instead of digging though the Spoke files individually to change the priority and category of a check, you can configure both for each check in one place. You can also edit the Label that shows up in the report, if desired.

This separation means you can add a new check pack without touching the engine, update the report template without touching any check logic, and trust that the contracts between layers stay stable.

What DBA Best Practices and Configs are Checked?

It will be a lot. This first iteration will ship with the database spoke. Each pack will be covering many of the areas that matter most in day-to-day SQL Server operations for DBAs. The planned spokes are as follows:

• Instance: Build version currency, startup parameters, sp_configure settings, surface area configuration, error log scanning, and SQL Server Agent status.
• Database ( you are getting this one today!): Backup currency (full, diff, log), VLF counts, auto-growth events, space utilization, anti-pattern detection (Auto-Shrink, Auto-Close), TDE inventory, Query Store status, and owner compliance.
• Maintenance: Last good CHECKDB age, statistics staleness, duplicate/unused/disabled indexes, wait statistics analysis, and identity column capacity.
• HADR: Availability Group health, replica connectivity, log shipping status, and legacy database mirroring.
• Security & Audit: Server-level and database-level audit specifications, SA account status, and surface area exposure.
• TempDB: File count, equal sizing, growth configuration, and space utilization.
• Networking: TCP/IP configuration, named pipes, SPN compliance, static vs. dynamic port, and round-trip latency.
• Host: Power plan compliance, disk space, and OS-level configuration.
• Misc: Database Mail health, Extended Events sessions, and notable trace flags.

Every check produces one of three actionable statuses — Pass, Attention, or Fail — plus an Informational category for inventory-style output that doesn’t require action.

Scoring and the Health Check Report

Findings aren’t just a flat list. Each check carries a configurable weight, and scores roll up by category and across the entire instance into a single health percentage — visualized as a gauge in the report header.

We use a similar scoring and multiple category analysis approach in our professional SQL Server scorecard engagements, where we go deeper with expert analysis and a prioritized action plan. We use the same approach with the proactive dashboard we maintain across the 2,000+ SQL Server instances we manage for our more than 120 DBA-as-a-Service clients.

The HTML report is fully self-contained (no external dependencies, no server required). It supports:

• An overview dashboard showing all instances, category breakdowns, and aggregate scores
• A drill-down per-instance view with a full findings table
• An all-findings view filterable by status and category
• Import/Export JSON/Excel Workbook buttons for sharing or archiving results
• Sortable tables and color-coded heat cells (red → amber → green)

You can email the HTML file, drop it in a SharePoint, or store it alongside your deployment artifacts — it works anywhere a browser does.

Configuration: Everything in One Place

One of the deliberate design choices is that all configuration lives in a single file: Start-Checkup.ps1. The goal was that there should be no hidden config files, no registry keys, no scattered settings. Every threshold — from the percentage that triggers an identity column warning to whether Named Pipes being enabled is a Fail or just an Attention — is documented inline with a comment explaining the intent.

# Free space thresholds (percent free)

FreeSpacePctAttention = 15.0

FreeSpacePctFail      = 5.0

You configure your targets in a Targets.json file, supporting Windows integrated auth, per-target SQL credentials, or a single shared credential for bulk runs.

Get Started Building Your Own SQL Server Health Check and Score Report

Prerequisites are minimal: PowerShell 5.1+ and the dbatools module. The suite uses dbatools exclusively for SQL connectivity — only read-only cmdlets (Get-*, Test-*, Invoke-Dba*Diagnostic*).

1. Clone or download the suite to a local folder.
2. Edit 3. Helpers\Targets.json to list your SQL Server instances.
3. Review thresholds in Start-Checkup.ps1 — the defaults felt reasonable to me, but your environment will have different baseline configurations.
4. Run Start-Checkup.ps1 from PowerShell.
5. Once completed, the html report will automatically open, otherwise you can open the HTML report from the 4. Output\Reports folder.
6. You can export the findings to json (if for some reason you keep the report but not the json result file) or to an excel workbook. The excel workbook has the overview table and the all-findings table. You can also import json if you are wanting to look at a previous run’s data during testing.

The first run will create settings.json from your definitions. Subsequent runs reuse those settings unless you pass -OverwriteSettings. More How-To guides and what to expect will be added to the repo as I release more spokes and as you all have questions. Also, the report has a “?” icon in the top right corner that opens a user guide to help you navigate the report results:

Conclusion

This is not in its final state; in fact, it is meant to be picked up by you and modified to fit your own needs. I worked on this for longer than I ever expected, and there is more I would do if I had the time. With so many moving parts, I can guarantee you that something has been overlooked.

Please take a look at it, respond here, or log bugs on GitHub if you notice any problems.

Look to our blog here or subscribe to our newsletter to find out when I drop the next posts, when I will be back with the additional spokes I promised above, and will let you all know about the updates we make periodically in response to bug fixes.

Hopefully, in just a few months, this will be an awesome health-check tool for you to keep tabs on your environment, featuring dbatools!

The post Free SQL Server Health Check with dbatools appeared first on SQL Server Consulting - Straight Path Solutions.

This month we did change the “ReadMore” URL links in the results of all tools to not use redirects. This might have been causing issues for folks who just wanted some more info about the findings in our tools, and we apologize if you were one of those folks.

Speaking of URLs, we found a bunch that didn’t work because the wording was wrong. We’re not sure how this happened, but we will get these fixed in the April updates.

This month though there are mostly a few small updates for the tools, as next month’s updates should also include many of the issues noted in GitHub (thanks for those!) Most of this month’s updates were to sp_CheckSecurity, although we did add “Initial File Size” to the output of sp_CheckTempdb since that had been requested by a few folks.

We hope these new updates can help you, especially if you’ve never used these stored procedures. Here are the updates for March 2026, with links to the GitHub repositories where you can download the latest versions.

sp_CheckAG

• CHANGE all Straight Path URLs to not involve redirects
• Change High HADR_SYNC_COMMIT waits to avoid arithmetic overflow (CheckID 422)

sp_CheckBackup

• CHANGE all Straight Path URLs to not involve redirects
• ADD check for password protected backups (CheckID 215)

sp_CheckSecurity

• CHANGE all Straight Path URLs to not involve redirects
• CHANGE check for most recent version for SQL Server security updates
• CORRECT inconsistent recommendation for Database owner discrepancy (CheckID 306)
• CHANGE Database owner discrepancy (CheckID 306) to only check online databases
• CHANGE Service Account using local elevated account (CheckID 329) to include the SQL Agent service.
• CHANGE Service Account in sysadmin role (CheckID 350) to include the SQL Agent service.
• ADD check for SQL Server services in local Administrators group (CheckID 351)

sp_CheckTempdb

• CHANGE all Straight Path URLs to not involve redirects
• Add InitialFileSize to @Mode = 1
• Remove ordinal values from ORDER BY in @Mode = 1

The post Updates for all sp_Check tools – March 2026 appeared first on SQL Server Consulting - Straight Path Solutions.

The Investigation

After receiving the alert, I verified that there were multiple databases on the same instance that were in the “Recovery Pending” state. A review of the SQL Server error log showed that the folder on the L drive was inaccessible, so transaction log files could not be read.

Those errors were not current though, and the L drive now appeared to be accessible. Moreover, it did not appear to be out of space (which could have been a possible root cause of the error.)

Reviewing the Windows System error log on the server showed there was a storage level issue for that drive only at the time of the errors in the SQL Server error log, and that those errors were no longer persisting.

The Fix

Often times a database is in the “Recovery Pending” state because there is corruption in the transaction log file. In that case, the database may need to be set to EMERGENCY state, set to SINGLE_USER, and then have DBCC CHECKDB run with REPAIR_ALLOW_DATA_LOSS. As with most situations involving corruption, you’re better off restoring from a backup then trying the “database surgery” method, since you never know how much the data will be lost with the former method.

Fortunately, this was not an issue of corruption, but rather one of the drive with the transaction log files being temporarily unavailable. Since the drive was now available, the databases could be brought online by simply taking them offline, then bringing them online.

ALTER DATABASE [DatabaseName] SET OFFLINE;

GO

ALTER DATABASE [DatabaseName] SET ONLINE;

GO

Since the transaction log files were now readable, the online process was able to successfully complete recovery.

As a side note, since these databases were in a state where there could be no connections we didn’t need to ROLLBACK IMMEDIATE to account for any activity.

One final step was needed, though. Although we did not suspect that the “Pending Recovery” state was due to corruption, we still ran DBCC CHECKDB for each database to verify there were no consistency errors.

The Takeaway

Finding one or more databases in the unusable “Recovery Pending” status can be shocking, but the solution may be shockingly simple.

1. Check the SQL Server and Windows error logs. In this case we were able to quickly identify that the cause was a temporary and not persistent storage level error. Since it the issue only affected some but not all databases, we had hope the quick fix noted above would work.
2. Try the solution of least resistance first. Someone with less experience might have tried to solve this by asking a search engine or AI chatbot what to do. I did a quick survey and those solutions said to go the way of repairing with data loss. That should be a last resort, and maybe not even a necessary option if you…
3. Always have a backup. Backups are the most important thing you can have for your databases. For “oops” moments, for ransomware, for corrupted data files, and for times like this. If our method had not worked, we still verified that recent backups were available if needed.
4. You still need an integrity check. Any time you have a database recovered from an unusual state, you should always run an integrity check. It’s better to know now if you have an issue that requires a database to be restored from backup.

Seriously, go check your backups now. We even have a tool to help you check SQL Server database backups quickly.

The Straight Path Team and Skills

Andrew Kelly and I collaborated on troubleshooting this issue. All thanks to him for suggesting this simple fix in this case.

Troubleshooting like this is a team effort, and having multiple perspectives and experience with the problem helped us come to a solution in just a few minutes.

This post is part of our Case of the Week series—real SQL Server issues and lessons from the field.

If you are inheriting SQL Server environments and want a team that knows how to dig into the details, reach out to us, and let’s talk about managing your SQL Server environment together. Our team is 20 people deep, and we have MVPs, speakers, bloggers, and authors. We know a lot because we’ve been exposed to a lot.

The post SQL Server Case of the Week: Databases in a “Recovery Pending” state don’t always require surgery appeared first on SQL Server Consulting - Straight Path Solutions.

This is a pattern I’ve seen repeated across organizations of every size and industry, and it almost always traces back to the same root cause: leadership thinks infrastructure investment just has an inherent capability benefit. They’re not the same thing, and no amount of marketing features close that gap.

There’s a persistent belief I sse that the right platform will, on its own, change how an organization thinks, acts and just makes things better. But the food you see in the fast-food commercial isn’t always what you get at the counter. A dashboard placed in front of someone who doesn’t understand what they’re looking at produces exactly one outcome: confusion dressed up as confidence. People nod knowingly at the chart, return to their desks, and do what they were going to do anyway. I’ve been in those rooms. We’ve all been in those rooms.

This is expected behavior. When organizations invest heavily in data infrastructure but lightly in evaluating the real data capability, they create an asymmetry that no software release can fix. Closing the gap between “we have data” and “we use data well” is fundamentally a human problem, and it requires a human answer. I’m very passionate, especially in these data-rich, context-poor times, about Data Literacy. I’ve created and taught courses and workshops on Data Literacy from high-schools to colleges to C-level staff meetings.

What We Mean When We Say Data Literacy

The term “Data Literacy” gets used loosely enough that it’s worth being precise about what it means in practice. Data literacy means equipping people at every level with the ability to read data critically, ask the right questions about what they’re seeing, and understand its limitations well enough to avoid being misled by it. Writing SQL or building regression models is specialty work. Understanding data correctly is everyone’s job. Anyone who uses data should be trained in Data Literacy.

Think about financial literacy as a parallel. A financially literate executive doesn’t personally perform audits. But they know how to read a balance sheet, they recognize when a number doesn’t make sense in context, and they know which questions will surface the real story beneath a tidy summary. Data literacy is that same capability applied to the information your organization produces and consumes every single day.

In practical terms, this looks like a sales manager who can examine a pipeline report and understand why a conversion rate trending upward during a seasonally slow period deserves skepticism rather than celebration. It looks like an operations director who can distinguish between a process that actually improved and a measurement methodology that changed. It looks like a finance partner who understands why an average can be technically correct and deeply misleading at the same time. None of these are advanced analytical skills. They’re foundational ones, and most organizations have never deliberately built them.

Data Literacy is the CDO’s Job

Chief Data Officers (or a group of people tasked in that capacity) tend to inherit a mandate that’s heavily weighted toward infrastructure: modernize the stack, migrate to the cloud, build the lake, implement governance. These are legitimate priorities and they deserve real attention. But they’re enabling work. The actual outcome the business is paying for is better decisions, made faster, with greater confidence. Infrastructure creates the conditions for that outcome. Literacy delivers it. The CDO who hands data literacy off to a learning and development team with a catalog of e-learning modules will consistently underdeliver on the promise of the data function. Literacy is a culture, and culture gets shaped by what leadership measures, rewards, and models, not by what it assigns.

The signals are usually obvious once you know to look for them. If the CDO is the only person in the room asking critical questions about data quality, that’s a signal. If business reviews treat dashboards as authority rather than as evidence to be interrogated, that’s a signal. If the phrase “the data shows” ends a conversation rather than starts one, that’s a signal. These are indicators of an organization where data fluency hasn’t been made a shared expectation, and shared expectations are set by leadership.

Building Literacy Into the Work

The organizations that do this well embed literacy into the work itself rather than running it as a separate program alongside the business. The approach is far more surgical than most people expect.

It starts by identifying the handful of decisions that drive the most value in the business, the ones that, if made faster or more accurately, would compound meaningfully over time. Then you audit those decisions: what data is actually being used, who’s interpreting it, what assumptions are baked into the framing, and what questions nobody’s asking yet. That audit almost always reveals that the bottleneck is the shared vocabulary and critical framework needed to act on what the data is saying, with access rarely being the real constraint.

From there, the work gets targeted. You build decision-specific fluency in the people closest to the choices that matter most, rather than spreading broad training across the whole organization. A supply chain team making weekly replenishment decisions needs to understand the difference between a forecast and a plan, and why treating those two things as synonymous is a quiet way to accumulate risk. That’s the level of fluency that changes behavior.

One of the most effective accelerators I’ve seen is embedding a data practitioner, whether that’s an analyst, an engineer, or a dbusiness analyst, directly into a business unit. When someone with real data fluency is present in the daily conversation, literacy transfers through proximity and practice. It’s a fundamentally different mechanism than coursework, and it works faster than anything else.

Measuring What Matters

The correct goal is to measure how many decisions that used to be made on instinct are now being made on evidence. Like most things that are strategic, that’s a harder goal to reach than tracking completions on a training module, but it’s the measurement that connects data literacy to business performance rather than to HR reporting.

Some leading indicators are more tractable. You can track how often data products are actually opened before decisions get made, not after. You can observe whether leadership teams start requesting clarifying data rather than accepting the first number presented. You can listen for whether the vocabulary in business reviews shifts toward probabilistic language, words like “likely” and “indicates” and “given the assumptions,” rather than the false certainty that tends to dominate when nobody feels empowered to push back on a number.

These are cultural signals, and they accumulate slowly. But they’re the real evidence that an organization is actually becoming data-driven, rather than simply claiming to be.

CFO Alignment

When your CFO signed off on the data platform budget, they were approving a bet that better information would produce better outcomes. Every dollar you’ve spent on tooling, talent, and cloud compute is in service of that bet. Data literacy is the part that makes it pay off.

When the people closest to your most important decisions can read data critically, question its assumptions, and use it to challenge their own intuitions, the entire data function starts compounding. Dashboards actually get used. Models get trusted because people understand their boundaries. Governance gets adopted because people can see the value of reliable data, rather than experiencing it as a compliance burden handed down from somewhere above them.

The organizations that win with data are the ones where the most people, at the most levels, know how to think with data, and are expected to. That’s a leadership decision before it’s a technology one, and it belongs squarely in the CDO’s lane.

Not sure where Data Literacy stands in your organization? Straight Path’s Data Estate Audit is a fixed-scope, four-week engagement led by Chief Data Officer Buck Woody that inventories your data and AI landscape, scores your maturity across key dimensions, and delivers a prioritized roadmap tied directly to your business goals, and delivers a ready-made, across-the-company Data Literacy course. The result is an executive-ready deliverable you can take to your board with confidence.

The post Data Literacy is a Strategic Opportunity appeared first on SQL Server Consulting - Straight Path Solutions.

A few years ago, we built a duplex. In-laws moving into the downstairs units, my office going upstairs. About a month out from moving in, I called the cable company to get service set up. Figured a couple of weeks, no big deal.

That’s when I learned about the pole permit...

Turns out, the cable was already on the shorter telephone pole. It just needed to be attached to the new taller one right next to it. Simple, right? Nope… Minimum 90 days to get approval from the power company. For a cable that needed to move about 2 feet, two companies that already worked together… Who’da thunk.

I didn’t know what I didn’t know. And I found out way too late.

I’m watching companies make the same kind of mistake with AI right now, except the price tag is a lot bigger than a cable bill.

The $250K Discovery Project Disguised as a New Hire

Everyone is rushing to hire a Head of AI, called by different titles; it makes sense and can be a good hire for many. I’m seeing these postings everywhere. $200K-$300K salary, VP or C-level title, and a job description that reads like someone fed ChatGPT the phrase “we need AI” and asked it to write a wish list. SQL Server, Fabric Databricks, PySpark, SQL, PostgreSQL, ETL pipelines, Delta Lake, Spark optimization, Data Engineering – all in one person. That’s not a job description. That’s a unicorn wishlist, and it signals something important: the company doesn’t actually know what they need yet.

They’re listing every data and AI buzzword they’ve heard, maybe even all the data sources and sinks they use and know about, and hoping someone shows up who can figure it out for them.

Here’s the thing. Gartner predicted that through 2026, 60% of AI projects will be abandoned because the data isn’t AI-ready. Not because the models are bad. Not because the team isn’t talented. Because nobody did the homework on what data they actually have, where it lives, whether it’s governed, and whether any of it is connected to anything else.

Your shiny new Head of AI isn’t going to walk in and start building machine learning pipelines on day one. They’re going to spend their first six months asking “where does this data actually live?” and “who owns this?” and “why is this table called sales_rollup_final_v2_REAL_final?”

Six months of a $250K salary is $125K. Spent mapping your data landscape. Before a single AI solution gets built.

Before You Hire Your Head of AI, Consider This

Before you write that job description, try answering a few questions first:

• What data do you actually have? Not what you think you have. What’s really there. What’s in the warehouse, what’s in the ERP, what’s in Julie’s “CFO actual data” spreadsheet on the shared drive?
• Where does it live? On-prem? Cloud? Hybrid? Bob’s laptop?
• What’s connected and what’s siloed? Can your systems actually talk to each other, or are you moving data between them on thumb drives and email attachments? Don’t laugh. I’ve seen it at companies with nine-figure revenue.
• What’s governed and what’s a mess? Do you have data ownership? Retention policies? Access controls? Or is it the wild west? What will the [insert regulatory or audit framework people of choice here – NCUA examiner, State examiner, SOC2 auditor, HIPAA auditor, etc.] say when they realize that we don’t even know who owns these key data sources?
• What’s your actual business problem? “We need AI” is not a business problem; it’s sort of a panicked plea to not miss out. “We’re losing 15% of customers in the first 90 days and we don’t know why” is a business problem. “The monthly financial snapshot we deliver to the board each month isn’t trusted by anyone because the data in it keeps changing.”

What Changes When You Do the Homework First

When you can answer those questions, three things change.

Your job description gets specific and realistic instead of being a buzzword buffet. Maybe you need a data engineer before you need a data scientist. Maybe you need a data governance framework before you need either one.

Your hiring priorities shift. You stop looking for a unicorn who can do everything and start looking for the right person for the actual problem you have.

And your odds of success go way up. Because your new hire walks into clarity instead of chaos, and they can start building on day one instead of spending six months figuring out what century your data infrastructure is in.

Our CDO, Buck Woody, does exactly this kind of work. He calls them Data Estate Audits, and they’re designed to give companies a clear picture of what they actually have before they start writing big checks for AI strategy. It’s the kind of thing that turns a six-figure guessing game into an informed decision.

Whether you work with someone like Buck or figure it out internally, just do the work first. Map what you have. Name what’s broken. Understand the connections and the gaps. Then hire.

The companies that will win with AI aren’t the ones that hired a Head of AI the fastest. They’re the ones who gave that person something real to work with when they showed up.

The post Before You Hire a Head of AI appeared first on SQL Server Consulting - Straight Path Solutions.

The Investigation

This client was migrating their SSRS environment from a SQL Server 2016 Enterprise Edition instance to a SQL Server 2022 Standard Edition instance, both running on AWS EC2. The new server was built from an AWS template rather than a manual install. The client said they had tested things, but hadn’t checked subscriptions in the portal or tried creating one.

When I opened the SSRS web portal on the new server, the subscriptions section was empty. No data-driven subscriptions at all.

On the original server, the subscriptions were all there and accounted for.

When I tried to create a new subscription on the new server, the data-driven subscription option was completely missing from the menu.

I went through a long list of troubleshooting steps. Since I wasn’t the one who originally migrated the SSRS databases (ReportServer and ReportServerTempDB), I started by verifying that all the SSRS tabs matched between the two servers. I confirmed the encryption key was loaded and all settings were in place.

I checked permissions in the SSRS web portal. Under Manage folder > Security, I noticed the original server had BUILTIN\Administrators set to Content Manager. On the new server, the service account configured in the Subscription Settings tab wasn’t in the local Administrators group, and I didn’t want it there. I gave that service account Content Manager permission directly.

Under Site Settings, BUILTIN\Administrators had System Administrator permission on the old server, so I gave the service account System Administrator permissions on the new server as well. Restarted SSRS. Still no data-driven subscriptions.

I checked the SSRS logs and fixed some email errors along the way, but nothing pointed to why the data-driven subscriptions weren’t showing. Standard email subscriptions were visible, just not the data-driven ones. I compared the RSReportingServer.config on both servers. Nothing stood out.

Talk about pulling your hair out in frustration.

The Fix

After all of that, I found the answer: SSRS Standard Edition does not support data-driven subscriptions. You need Enterprise Edition.

The original server was running SQL Server 2016 Enterprise. The new server, built from the AWS template, was SQL Server 2022 Standard. That edition difference is why the subscriptions disappeared from the portal and why the option to create new ones wasn’t there, even though the subscription data still existed in the underlying database tables.

The client needed to move to an Enterprise Edition instance. Once that was done and I also reconfigured the FileShareStorageLocation in RSReportingServer.config to use a separate drive (the Standard Edition instance had been filling up the C:\ drive with report output), the data-driven subscription option reappeared.

This was just the first of three issues I hit on this single SSRS migration. The full story, including subscription Agent jobs not being created and a permissions issue with xp_sqlagent_notify, is covered in my full blog post on SSRS migration troubleshooting.

The Takeaway

1. Know your edition features before you migrate. Data-driven subscriptions are an Enterprise Edition-only feature in SSRS. If you’re changing editions as part of a migration or upgrade, check the SQL Server feature comparison by edition before you start. The subscriptions data will survive in the database tables, but the portal won’t show them and you won’t be able to create new ones on Standard.
2. Test subscriptions specifically. It’s easy to validate that reports render and data sources connect, and call the migration “tested.” But subscriptions, especially data-driven ones, need their own validation step. Create a test subscription. Verify it fires. Don’t skip this.
3. Watch out for AWS templates and pre-built images. When you’re using a template to spin up a new EC2 instance, you don’t always get to choose every detail of the install. In this case, the template delivered Standard Edition when the client’s environment needed Enterprise. If you’re using templates for your SQL Server builds, verify the edition and patch level before you start migrating anything onto it.

Straight Path Solutions helps over 120 clients manage, migrate, and upgrade their SQL Server environments. SSRS migrations are among the many tasks our team handles as part of our DBA-as-a-Service offering. If you’re planning an upgrade or migration, especially from older versions nearing the end of life (like SQL Server 2016 in 3 and a half months), our team has a methodology and experience to get it right the first time.

The post SQL Server Case of the Week: SSRS Data-Driven Subscriptions Missing After Migration to SQL Server 2022 appeared first on SQL Server Consulting - Straight Path Solutions.

Data-Driven Subscriptions Disappeared After SSRS Migration to SQL 2022 Standard

After migrating SSRS from SQL 2016 Enterprise Edition to SSRS SQL 2022 Standard Edition Data-driven subscriptions disappeared from within the SSRS web portal. However, I could see the subscriptions in the ReportServer.dbo.Subscriptions table.

SSRS was migrated from an EC2 instance where SQL and SSRS, etc. was installed by a DBA to an EC2 instance that was built using a template where all components were installed. When this was originally built out, we asked that they test. They said they did some testing, but they did not look at subscriptions in the portal nor did they create a subscription.

What the web portal looked like on the new SQL 2022 Standard Edition server:

What we were expecting:

If you tried to create a new subscription on the new SSRS server, you do not get the Data-driven subscription option:

The type of subscription that was missing:

Troubleshooting steps that did not help:

• Since I was not the one to do the migration of the SSRS DBs (reportserver and reportservertempdb), I verified that all tabs within SSRS looked the same on both servers, I also checked that the encryption key was loaded and all settings were set.
• I looked at permissions within SSRS web portal.
  • Manage folder, Security – I noticed on the original SSRS server it had BUILTIN\Administrators permissions set to Content Manager.  I checked who was in the local administrators’ group on both servers. On the new server, the service account that was set within Subscription Settings tab was not added to the new server local administrators’ group nor did I want it in there. I changed security settings on the new server to so that account had Content Manager permission since that service account did not have it set but the BUILTIN\Administrators group did.
  • Site settings – BUILTIN\Administrators had System Administrator permission so again I set the service account that was set in Subscription Settings to have System Administrator permissions. Restarted SSRS and again no Data-Driven Subscriptions.
• I looked at SSRS logs and fixed some email errors, but nothing stood out on why they were not shown. We could see email subscriptions.
• I compared the RSReportingServer.config on both servers to see if anything was off. Nothing that stood out besides what I changed.

Talk about pulling your hair out in frustration!

The fix for the missing Data-driven subscriptions:

I found out Standard Edition for SSRS does NOT allow you to create data-drive subscriptions!!!! You must have Enterprise Edition!

Again, we used the AWS template to create a new EC2 instance. No, I do not like templates, but they wanted pay-as-you-go, so you must use a template. With this new version\server, I asked to get new drives added and I moved the databases to that new data and log drive as well as moved tempdb onto their own drive.

I set FileShareStorageLocation within the RSReportingServer.config file to use the D:\ drive instead of C:\ since reports that were running on the Standard Edition server were filling up the C:\ drive which in and of itself caused issues. This change allows reports to use a different drive for report runs. I also disabled any SQL services within SQL Server Configuration Manager that were not wanted\needed. With this change, I could now see Types of subscriptions.

You would have thought my issues were done… NO they were not.

Subscription Agent Jobs Not Created After Upgrading to Enterprise Edition

Once the original problem was “fixed”, I noticed a new issue.  On the new Enterprise Edition server, I noticed that the subscription jobs were NOT automatically created. We do not copy those over, on a new SSRS server restarting SSRS “should” create those new subscription jobs if they do not already exist.  We want SSRS to create them, so I know all is well when a new subscription is created then a corresponding Agent job gets created via SSRS. They have over a thousand subscriptions; I would NOT easily notice if the test subscription was created and I did not want to spend the time determining which test subscription was tied to which Agent job.

Not the fix but told me a lot:

• I changed the account that started SSRS from using a Virtual Service Account that did not have sysadmin rights to my domain account (yes, I have sysadmin rights within SQL). Restarted SSRS within Report Server Configuration Manager and lo and behold the subscription jobs were created. I deleted all 1000+ of them and changed the account back to Virtual Service Account. I wanted to use a Domain account, but the client insisted on using the Virtual Service Account since that is what was set on the server we migrated from.
• Why I did the above change… I was troubleshooting an SSRS issue for a different client and an SSRS job would not run without changing the owner to SA and no changes were made to SQL or SSRS. Since this client server was an AG, failovers would change that owner back to the account starting SSRS since I have a mechanism where SSRS jobs get deleted from primary and the restart of SSRS adds that job back to the new primary server which brought me back to square one. I ended up changing the account that started SSRS to my domain account then back to a new domain account that did not have special privileges. The original domain account had DOMAIN ADMIN rights! Yes, you heard that right. I was mortified. I did not build these servers out. I had the client create a new domain account with no special privileges. I added that account to the primary server with no special privileges and removed the old SSRS account. I let SSRS set the permissions for that account which is done when you change accounts within Report Server Configuration Manager. I restarted SSRS and ran the job with the owner not changed and it ran. I used DBATools to copy that account to the secondary server. I even ran code to set all permissions for role RSExecRole just for giggles. Failed over and ran the newly created subscription job and again it worked. Sometimes thinking out of the box works but sometimes it doesn’t.

The fix:

I checked the virtual service account permissions within SQL. I checked to see what was set within MSDB since I knew that is where permissions are set for accounts that we do NOT want to have sysadmin rights to create jobs. I noticed that role SQLAgentOperatorRole was not set. I set that permission and restarted SSRS. Reporting Server subscription jobs were now created!

We do NOT want to change the owner of those jobs to something else, especially if we do not give sysadmin rights to that account. Again, why would you give that access? Everyone and their mother should NOT have sysadmin rights.

When you migrate to a new server, lock things down and have them test before the final migration. The account that starts SSRS must be the owner of those jobs so that the service account can delete that Agent job if that subscription is removed from within the portal.

At this point, I released the server back to the client, thinking I was done. BUT no…

New Subscriptions Failing with Timeout and ‘An Error Has Occurred’

After releasing the server, the person who creates reports and supports SSRS tried creating a new data-driven subscription. He reported back that the subscription creation would time out, and the “An error has occurred.” message appeared. What a not-so-helpful error! I tried creating a basic email subscription, and I too got the same error.

Troubleshooting steps that did not help:

• This template had an outdated patch level of SSRS installed. For SSRS starting with SQL 2017, you must patch differently. It is no longer included in SQL patching. To find out what build of SSRS that is running open Report Server Configuration Manager. Login and the first window Report Server Status page will tell you the Product Version. Go to this website to find out what the latest build is – https://sqlserverbuilds.blogspot.com/2020/09/sql-server-reporting-services-ssrs.html. Find the version of SSRS you have and want to patch. Look for the green Download  button and click it. That button will redirect you so you can download the latest build of SSRS. I patched SSRS and tried creating a new email subscription. Same timeout issue.
  • Patching did not help my issue, but it needed to be done.
• Again, I looked at everything – SQL logs, SSRS logs, configuration files, etc.

At this point, I am so frustrated and want to quit, but that is not who I am. Sometimes you have to throw in the towel and bring someone else in for a fresh set of eyes. I contacted a coworker and went over everything with him again.

The fix:

• About those fresh set of eyes…. Apparently, that is the key to finding something you looked at over and over again. After looking at different things on our own again, then coming together on a screen share, he found the issue. But prior to that, we commented out the Oracle and Teradata data sources in the configuration file to help “clean up” errors in the SSRS log. You must restart SSRS to verify they no longer appear. He found a permission issue in the SSRS log that I missed. Maybe because I was searching for the word ERROR, and with so much information in the SSRS log, finding something like that can be easily overlooked. At least that is what I am telling myself to make me feel better in this whole debacle. It was an EXECUTE permission denied on the object ‘xp_sqlagent_notify’. This issue should NOT have existed since permissions should have been set when the role RSExecRole was created via SSRS when I pointed SSRS to use that restored database. Those roles existed in both master and msdb. There are blogs out there that provide the code to grant EXECUTE permissions in master and msdb. He ran it, and I recreated a subscription, and hallelujah, it created the subscription. I verified both the subscription and the job. I deleted the subscription in the portal and again verified the job deleted.

• I asked my coworker who restored the original DBs on the Standard Edition server whether he created the DBs first using Report Server Configuration Manager, then followed our migration process, or restored them and then set things up in Report Server Configuration Manager. He mentioned the databases were already created and set up, then he just followed our normal process and restored, etc. The only thing I did differently was I restored the two DBs first then used Report Server Configuration Manager to point to an existing SSRS database. I have done this before and did not have these issues, so I’m not sure why it happened here.

This time, when I released the server back to the client, everything worked as we expected. They officially migrated to this new server and dropped the other server from the AWS portal. 

The post SSRS Data-Driven Subscriptions Missing After Migration to SQL Server 2022 -Troubleshooting Guide appeared first on SQL Server Consulting - Straight Path Solutions.