Database Documentation

Question

Database Documentation

dj1202

SSCommitted

Points: 1829
More actions
July 18, 2012 at 7:35 am

#278046

Hey everyone, I was wondering if I could get some insight from some people here who've had experience manually documenting a database. I've recently joined a company and have been tasked with preparing documentation on their database. I know there are third party tools available for this relatively time-consuming process, but I do not have any available to me, nor am I permitted at the moment to download and install such applications. I have begun by documenting all tables along with their fields, data types, null/not null properties, and descriptions. I have also documented all indexes and all types of constraints. Lastly, I have documented the stored procedures currently in the database, along with their believed purpose. From here, I think I will use the Track Changes feature on my Word Doc to notate potential concerns and recommendations that I may have on the database. Does this seem comprehensive to those who have experience with documentation? Is there anything in particular that I am leaving out which may be helpful for those who review the documentation in the future? Thanks kindly!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I would get a full understanding of the state of the backups. In addition, what are the maintenance routines currently in place. After that, I'd want to work on establishing a performance baseline, especially focused on what procedures are called most frequently, run the longest, use the most resources.... Add that right to the database documentation. As far as the documentation goes, sounds OK, but I'd also want to know about the foreign key constraints and whether or not they are enforced (created with CHECK or with NOCHECK). Nothing else jumps out.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 2

To add to what you have and what Grant said, I like to look through the SQL agent jobs as well. Figure out when they run, how often they run, how long they normally take to run, are there SSIS packages involved, where those packages reside, maybe a brief description of the purpose of the job and who supports it when it fails etc.

I didn't see security mentioned either. I would document that as well. Might find some scary stuff in there....:-)

Lowell SSC Guru Points: 323518 More actions · Answer 3

documenting all users and roles, any CLR assemblies, and mail settings for database mail is handy too(even though the mail settings are in msdb)

linked servers and synonyms and custom user types also come to mind to track as well.(if they exist/are set up)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dj1202 SSCommitted Points: 1829 More actions · Answer 4

Thank you all for the incredibly informative replies. Greatly appreciate the wealth of insight offered and have began the long process of incorporating all things mentioned into the document. Unfortunately, as I have just joined the company, I have not yet been granted an admin or writer role as of yet, so have been unable to check the agent jobs, as well as attain useful maintenance routine info. On the other hand, it appears that most of the data is manually imported or inserted using custom apps. Thanks again!

Mickle Old Hand Points: 382 More actions · Answer 5

I've had to do the same thing for my databases in my company when I took over a project a few years ago. I used MS Excel and its been helpful to me and many others in our organization. I used excel rows for each sql column notation and added excel columns for relevant sql column names, properties, alias's and field descriptions. We have some tables with 300+ columms which are difficult to navigate when you need to query and return all rows. I added a column for table column location counts also. We have found it a great help to be able to sort on various columns depending on what we are looking for. As field changes are made, I add an Excel column to denote when a field became available. I put each table into its own Excel tab. We call it a "field map". Hope this helps.