How to Document SQL Server for the Beginner DBA

How many times has this happened to us? Your DBA managers often come round and ask you for the latest schema for the Customer database that was recently updated during a maintenance window that occurred on Jan 5th 2009.

Where do you go next? You’ve forgotten how to create a database diagram that your instructor at DBA School told you so much about and you didn’t do it when the database was last changed. Now if you could only generate the scripts and issue them to your manager, he/she would say “Where is Table X related to Table Y, I need all the details! Now!” You could either cry for help or you probably follow some of these steps that helped me become a Productive and Document-Oriented DBA.

Often documentation is not a very favorite topic of one and all in the industry, only designed for technical authors and business analysts alike. Nowadays documentation is becoming a vital part of the DBA profession. Documentation helps improve one’s understanding of the Line of Business (LOB) applications.

Best Practices

Everyone has been in this kind of situation; here are some of the best practices that you can follow:

1. Document

During the kick-off meetings, always pay attention to the architecture that is being related to the application. Document it even though the project manager and business analysts are doing the same.

2. Visualize

When you go back to your desk start visualizing on how you should design your database. One of the best tools that has helped me document is either SQL Server Management Studio’s Database Diagram option; another one of my favorites is SQLDoc (Short for SQL Documenter) from Red Gate Software.

3. Accessibilty

Maintain a Database Documentation folder in the server and make it accessible only for authorized management such as CTOs, CIOs and Senior Architects / Project DBA Managers.

4. Organizing

Database documentation should be split into two sections:

• A descriptive yet flexible section that is easy to understand for the general user of the document, which might include technical authors, who would be interested in adding a few comments at a later stage for improvements.
• A technical section meant for top level, senior and execution level groups for better understanding on how each table is related to each other and which table, when modified, could impact the other tables in the database etc.

5. Visual Appeal

Many of us are drawn towards learning and grasping things visually, at least I have managed to do so in my career so far. Make the documentation highly presentable and to the point.

6. Backups

Backup the documentation when new schema changes occur: whenever new schema changes take place within the database, to maintain a replica in the event, certain things need to be rolled back. You could re-use the old documentation and restore your database to a consistent state using the previously scripted objects.

7. Document Problems

Documentation is not just about creating and maintaining database schema information, it also involves documenting steps to resolve a particular problem. For example: Yesterday a database was in Suspect Mode. What did you do in order to bring it back online again?

• You first checked if the files were moved to another location
• You discovered that they were moved; now you go and re-attach your database pointing to the new file location
• You brought the database online again!

Attention to Detail

By the time you have implemented steps 1 to 7, I am positively certain, you would have reached DBA Doc expertise to a certain level.

Make documentation a regular habit. You will love it as much as you love jogging early in the morning or hitting the gym, you get the idea 🙂

Communication

This makes you a very good communicator in both written and verbal areas. You will be the next DBA Hero/Heroine of your company.

Happy Documenting DBAs!