Opinion: Don’t Play Hide and Seek with T-SQL

Opinion: Don’t Play Hide and Seek with T-SQL

I spend most of my life in a variety of customer sites. Most of these nowadays tend to be large financial organizations. When I talk to developers in those organizations, and ask them about how they interact with their DBAs, I can’t tell you how often I hear that they try to avoid ever making schema changes, etc. as it takes too long and the process is painful.

When I talk to the DBAs at the same organizations, and I ask them why they resist making schema changes, I hear this:

If they change the database, they believe someone will scream at them, but they don’t know who because:

  • They have no visibility on the code that touches the database so
  • They do not know what they are about to break.

So where is this code that the DBAs can’t see or test?

  • Embedded in applications (or generated within the application dynamically) – often in widely-differing technology stacks (.NET, Java, etc.)
  • Embedded in Excel spreadsheets
  • Embedded in Reporting Services Reports
  • Embedded in Power BI Dashboards
  • Embedded in Analysis Services data sources
  • Embedded in SSIS Packages
  • Embedded in Access databases
  • And so on and so on.

What this means is that if the DBAs want to change the database, they have absolutely no way of knowing, at all, what they are about to break, unless they have tested every spreadsheet, have run every option in every report, have run every combination of SSIS packages, have reprocessed every analytic data model, have used every feature in every application, etc, etc.

That’s never going to happen, so what do they do? They push back on any changes.

image

If I talk to the developers, and ask them why they built this pile of cards, I’m invariably told that they had to be agile. Yet what they don’t understand is that what they have built is the complete opposite of agile. They have actually built a house of cards where no-one is game to touch anything. And we know where that all ends.

image

This is why I’m so much of a fan of having at least one layer of abstraction (yes that’s procs and views) within the database. Then at least, the DBAs have visibility in the code touching the database.

In a slightly better world, the data people would also have unit tests wrapped around those procs and views. Then, when they need to make a change, they could just re-run the tests, and at least know that they haven’t broken every report, spreadsheet, etc. in the organization.

I know that many might disagree, but I believe that if you are putting anything that looks like T-SQL logic directly into an application, into a spreadsheet, Reporting Services report, Analysis Services data source, Access database, etc., you’re building a problem faster than you’re building your applications.

2017-10-27