30 December 2015
30 December 2015

We don’t need no documentation – automating schema docs in SQL Change Automation


“Understanding the existing product consumes roughly
30 percent of the total maintenance time.”
Facts and Fallacies of Software Engineering by Robert L. Glass.

You should be documenting your database schema. I know it, you know it. Having current, accurate documentation available accelerates time-to-resolution for faults, aids tech-to-business conversations, and is a regulatory requirement for a great number of firms. Yet a majority of 214 respondent to our survey admitted that while they didn’t always have up-to-date docs, they knew they should.

schema docs survey response

Do you work in a US-listed organization? Sarbanes-Oxley (SOX) clearly requires up-to-date documentation on where the financial data resides within your firm and how it’s managed. There’s a nice summary on MSDN ;

Compliance to SOX has historically come down to the opinions within external auditor statements attesting that the proper controls are in place to ensure that financial data moves through the organization unaltered and is only exposed to the correct people in the organization. Part of the auditing process is to trace the flow of financial information inside the organization. For most companies, most of this information flow takes place through IT systems. This means that IT needs to provide assurance that this data:

– Cannot be altered by unauthorized individuals.
– Cannot be viewed by unauthorized individuals.
– Is available when needed by authorized individuals.

It also ensures that any material changes to IT infrastructure that touch this data are documented and reported immediately to management.

Financial Services firms in any other jurisdiction will find they have similar requirements.

But what does it mean to have documentation – does a list of tables and columns do it? I recall a mapping table for managing foreign exchange positions at a hedge fund. We’d agreed a standard (cleverly, we thought) that such tables would include the names of both the tables where we wanted a logical many-to-many join.

Every time we needed to work with CounterpartyPermittedCurrencyProxyCustomerExposureLimit (if my memory serves me right), there was quite a delay while we mined our collective memory for what exactly we intended it to do. We needed to know what was where and why, so adding some explanatory text on the business context was invaluable.

That’s when you need to reach for extended properties, if you’re using SQL Server. You could use database diagrams to help as well, and export to a wiki or MS Word doc for sharing. Tools like SQL Doc help for sure. There’s only one problem with documentation though. It’s almost always out of date. It’s just not human nature – among most developers anyway – to implement the urgent change, the 11th hour hot fix, then re-generate the schema docs.

As with many other things, automation saves the day. If you’re source-controlling your database, and building as part of your Continuous Integration process , it can be pretty easy.

With Redgate’s SQL Change Automation, it comes as standard. Add the ‘include database documentation’ option to your build process (for example in the TeamCity plugin as below), and you’ll have up-to-date schema docs for no extra effort. If you sourced-controlled the database using SQL Source Control, you also have the option to send the information to your and DLM Dashboard products.

tc

This maintains a tight association between the documents and the database being built from source code. What’s more, the extended properties that provide your business context are not only surfaced, but are also seen as change to be promoted through your environments.

Dashboard doc with xp

No more last-minute refreshes of your documents (when the auditor’s at the door), faster fixes, and shared knowledge of what is where, and why.

Are you doing schema docs? Where does documentation fit into your IT infrastructure? Share your thoughts in the comments!

Tools in this post

DLM Dashboard

Track your SQL Server databases and be the first to know about schema changes.

Find out more

SQL Change Automation

Continuous integration and automated deployments for your SQL Server database.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Transaction-handling techniques in T-SQL deployments

    How are transaction handled when deploying databases with SQL Change Automation? For the most part, we have resisted putting excess structure around the way that changes are deployed to your database. Unlike database projects that use the declarative-style of deployment, which work by synchronizing a source-controlled model of your schema to a target database, we

  • Article

    SQL Code Analysis from a PowerShell Deployment Script

    Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in ‘temporary’ development code, by mistake, so it is always good to have a way of flagging up SQL Code issues and ‘smells’ that are agreed to be incompatible with ‘production’

  • Article

    Dealing with production database drift

    For the SQL Change Automation team, it’s important that we take time out from development, occasionally, to explore some of the issues our customers face when automating database deployment. Following on from previous posts about cross-database and cross-server dependencies, this article shares some of our thoughts about how to deal with production database drift. If you’re

  • Article

    Sometimes the tool just fits - using SQL Change Automation and Octopus Deploy for Data Change Control

    From a business risk perspective, data change can be just as significant as code or schema change. Sometimes even more so; an incorrect static (or reference, or master) data change can drive your software’s behaviour more dangerously askew than pretty much any bug can. Imagine treating a retail customer for an investment fund as a corporate by

  • University

    Take the SQL Provision course

    In this course, you’ll learn about the challenges of masking data and moving it around, and how to overcome them by using a combination of SQL Clone and SQL Data Masker.

  • Forums

    SQL Change Automation Forum

    Continuous integration and automated deployments for your SQL Server database