You should be documenting your database schemas. 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.
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 pretty 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 (and if not, why not?), it can be pretty easy.
Add the ‘include database documentation’ option to your build process (for example in the TeamCity plugin as below), and send the information to your DLM Dashboard server, and you’ll have up-to-date schema docs for no extra effort.
Whenever that same schema is recognized across your environments, the documents will be available and up-to-date.
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.
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!
Also in DLM Automation
Sudden performance issues in SQL Server can have many causes, ranging all the way from malfunctioning hardware, through to simple misconfiguration, or perhaps just end users doing things they shouldn'...
Also in DLM Dashboard
The Redgate DLM Automation team that we’re part of recently took time out from development to explore some of the issues our customers face when automating database deployment. Following on from las...
Also in Hub
When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a bat...
Also in Product learning
Deploying schema changes to SQL Server databases can be tricky when you’d like to automate parts of your workflow. For instance, how do you go about version controlling your schema changes? In appli...
Also about DLM Automation
While the practice of Continuous Integration (CI) started with application code, you can apply the same principles to databases. Database CI is the process by which we build, test and deploy the datab...
Also about DLM Dashboard
As a DevOps engineer at Redgate, I’m one of those responsible for keeping the systems going behind the scenes. That’s everything from the internal systems and databases to the infrastructure that ...