Click here to monitor SSC
  • Av rating:
  • Total votes: 17
  • Total comments: 4
Robert Chipperfield

Database Documentation Using SQL Doc

26 January 2007

Robert Chipperfield is a member of the development team at Red Gate Software. In this article, he describes the key features of Red Gate's new database documentation tool, SQL Doc, and provides insight into the development process and some of the design philosophies that underpin the tool.

In a recent article describing Red Gate's SQL Refactor tool, András Belokosztolszki, observed that it is rare for a developer or DBA to work with a database that they also designed. It is much more common that they are required to work on established databases that have been built by a previous developer or a colleague.

What is largely true for developers and administrators is almost by definition true for support staff. One of the first tasks for anyone taking over support duties on a given database is to attempt to gain at least some understanding of its functionality – how it is structured, where different types of data are stored, and how the different entities relate to each other. On a large schema, this can be a formidable challenge.

In the past, I have spent many hours working through schemas and constructing relationship diagrams by hand, then having to update these whenever changes are made to the database later on. Whilst tools such as Management Studio and Query Analyser are able to provide some information about the structure of the database, they are geared more towards working with individual objects at the fine-grained level, rather than gaining a fast overview of the whole database.

This is the gap we hoped to bridge when we developed Red Gate's new automated database documentation tool, SQL Doc. With it, we sought to provide developers and DBAs alike with a means of quickly documenting and understanding the structure of their databases without the need to keep hand-crafted documentation up-to-date.

Self-documenting your database

SQL Doc is designed to make it as simple as possible to generate a comprehensive documentation set for your databases. Of course, the usefulness of this documentation generated will be greatly enhanced if the database itself provides some meaningful indicators of the role and intent of its various component parts.

A large part of designing an intuitive database is the use of consistent and logical naming conventions for you tables (e.g. "CustomerAccounts" instead of "cas") and keys ("pk" prefix for primary keys "fk" for foreign keys, and so on). However, there is a lot more that you can do. SQL Server 2005 introduced the concept of schemas – effectively allowing you to segment your database into different logical groups. In the classic AdventureWorks database, all the HR data is stored in HumanResources schema. This is often seen as a security feature – it allows sections of the database to be quickly locked down rather than having to set permissions on each object individually, not to mention solving the problem of what to do when the person who owns objects in a database leaves an organisation – but it is also very useful as a descriptive feature

One of the most useful things that a developer /DBA can do in helping provide useful documentation is to make use of SQL Server's built in extended properties. These are essentially descriptive attributes that can be added to database objects by the designer. For example:

-- we add the extended property to provide a description to the
-- dbo.Customer.InsertionDate column
sp_addExtendedProperty 'MS_Description',
                       'the date at which the row was created', 
                       'user', 'dbo', 'table', 'Customer',
                       'column', 'InsertionDate'

Unfortunately, the standard toolset provides no easy way to work with these extended properties – in Management Studio, viewing the extended properties of a column on a table means drilling down through the object explorer to the column level, and then using the properties dialog to select the "Extended Properties" page. Consequently, extended properties tend to be an underused feature of SQL Server, which is a pity given that the number of objects to which they can be attached has been considerably extended in SQL 2005. It was important to us to include full support for extended properties in SQL Doc.

Developing SQL Doc

Some of the existing documentation tools tend to be little more than documentation editors, with the ability to import from a database schema "tacked on". With SQL Doc, we wanted to achieve something different. First and foremost, it had to be fast and simple to use; in fact, once installed, we wanted to transform the process of generating documentation from being a substantial task to one that could be done in seconds, whenever required.

The project was split into three components – the UI, an "engine", and the output renderer. Keeping the UI separate from the main application logic is fairly standard practice for most large applications, but the second split – between the engine and the renderer – is perhaps less common and more interesting.

Our aim was to ensure that all output format-specific code was entirely separate from the more general code for, say, determining which objects should be documented, and calculating the dependencies between objects. This approach proved its worth when developing version 1.1 off the tool – we received a large number of requests for compiled help (CHM) format output, and could thankfully achieve this moderately easily by changing only the rendering code.

The engine assembly builds on other Red Gate technologies with which you may already be familiar – SQL Compare for providing an abstraction of the database schema, and SQL Dependency Tracker for calculating most of the dependencies. It combines these with further logic for features such as the extended property manipulation.

The renderer itself is modular and extensible, making it easy to support any database object types that may appear in future versions of SQL Server. For example, each type of database object is rendered by a corresponding class, implementing a standard "renderer" interface. Given the type of the database object, an appropriate renderer is retrieved, which takes care of producing the final output for that object. Common page elements are rendered by methods in a further class, ensuring consistent styling of features such as the breadcrumbs and quick links.

Early in development, it became clear that we were seeing severe performance problems when documenting large databases – demonstrated perfectly by our tester who decided to throw a 100,000 schema database at SQL Doc. It sat there overnight, churning away, and then crashed and burned beautifully.

At this point I needed to investigate why things weren't scaling as well as they needed to – time to bring out the profiler. A few bottlenecks were immediately obvious: generating the left-hand navigation tree was expensive, and was being done for every single output page. The original thought behind this was that the relative page links depended on the location of the output file, and so each page would have a different set of links. However, with a little thought, we ended up caching these navigation sections, keyed on the directory of the output file – this meant that once it had been generated for one file in a given folder, all future files would just re-use the cached version.

Working on a few more similar problems for just a couple of days resulted in huge improvements in performance – we ended up being able to profile the same database in less than fifteen minutes.

In the following sections I describe some of the key features of the tool and provide insight into some of the design decisions behind them.

Usability: a familiar object explorer

If you've tried SQL Doc, you'll be familiar with the user interface – an object hierarchy on the left, and a preview on the right. You'll probably also notice that the hierarchy is rather similar to that used in Management Studio.

We have had requests from some people for alternative categorisations of objects, such as grouping by schema, or a Query Analyser style flat view, but we've resisted "improving" it, because it's currently a good match for the world that most of our users work in – that of Management Studio. Sure, it might not be The Best Possible Organisation Ever, but it's familiar.

If a group of people who had never seen or used a computer before were asked to design the best possible input mechanism, given all the technology available to us now, the chances are that they would come up with something rather different to the keyboard and mouse that is currently ubiquitous. Their solution might be a technically better way of doing it, but familiarity counts for a lot. If you don't believe me, try switching between a QWERTY and Dvorak keyboard on a regular basis.

Accessibility: HTML documentation

Our documentation is generated in HTML format. There were many reasons for this, some of which are worth noting here:

  • Once generated, the documentation can easily be disseminated. It can be uploaded to a company web server, and instantly shared among a team of people on one project, or viewed from any network connected machine.
  • No special viewer is required: if you need to ship a database to a contractor, shipping the documentation alongside it is simple. There is no need for the other party to install any software other than a web browser – not exactly uncommon these days!
  • Simple and intuitive navigation throughout the schema: as well as the hierarchical navigation system provided, navigation between related objects is also simple. Foreign keys on table columns link to the tables they refer to, user defined types similarly, and the new SQL Server 2005 CLR-based types instantly show the assemblies they were deployed from.

Everyone nowadays is familiar to some extent with the wonders that can be achieved with technologies such as AJAX and ASP.NET – not to mention the up-and-coming Atlas.

However, the majority of these techniques assume some kind of intelligence at the web server end of things, and some of it assumes an awful lot. If we had chosen to go down this route with SQL Doc, we would have had to impose the restriction on our users that they would need to be running a certain web server, configured in a certain way, and in general made the requirements much more onerous. Instead, we opted for almost entirely pure static HTML (apart from a little JavaScript for expanding and collapsing the tree view navigation). This means that the documentation can be viewed directly from the local filesystem, or uploaded to just about any web server with no further configuration.

This choice did impose some limitations, some of which are a little irritating – like the tree view being unable to maintain state when navigating between pages on a "no frames" document – but overall, I feel this was definitely the correct choice.

Exploiting extended properties

In SQL Doc, we have integrated Extended Properties – in particular MS_Description – into the core of the documentation, making it easy to add human-readable descriptions to your database. Objects show their description in the page title, and these can be edited with the click of a button while previewing the documentation within SQL Doc. No more need to search Books Online for the correct type0, type1 and type2 parameters just to edit the description for a column!

Scheduled documentation

The team-based development described earlier can be further enhanced with the additional features found in SQL Doc Pro – the command line interface allows documentation to be generated on a schedule, ensuring that developers always see the latest version, with no effort required on their part.

Customisability

Along with accessibility and usability, we also considered customisability. Whilst the default style tries to be as clean and clear as possible, some companies will have house styles for documentation, and the is especially likely to be of concern when shipping documentation to outside parties.

Our solution here was to use the features made available to us by CSS. This means that by editing a single file, the look and feel of the entire documentation can be quickly, simply, and consistently updated. For example, if you would prefer the list of object dependencies to be a vertical list rather than horizontal, simply change the appropriate CSS style to "display: block;", as shown below:

Using CSS, we were able to provide a high degree customisability of the output style to those requiring it, whilst ensuring that the product remained easy to use.

Conclusion

Database documentation has always been an immensely useful resource to developers and administrators alike, but unfortunately keeping this updated has traditionally been a time-consuming and error-prone process, and as such one that has often been neglected due to time constraints.

Automated documentation tools such as SQL Doc aim to remove this barrier, increasing productivity and reducing administrative burden.

Robert Chipperfield

Author profile:

Robert is a software engineer at Red Gate, where he's worked since September 2006 on a wide range of products, including SQL Doc, SQL Data Compare, SQL Log Rescue, SQL Multi Script, and ANTS Profiler. He is currently working on the new Exchange Server Archiver tool, which should be heading towards release at the start of 2009. Outside of work, he enjoys amateur radio, electronics, and of course the usual assortment of computer-related technologies, from hardware all the way through to high-level software

Search for other articles by Robert Chipperfield

Rate this article:   Avg rating: from a total of 17 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: SqlSpec
Posted by: Anonymous (not signed in)
Posted on: Saturday, February 03, 2007 at 6:17 PM
Message: one alternative to SQL Doc is SqlSpec from Elsasoft.

It costs less, has more features, and supports many more platforms than SQL Doc: in addition to SQL Server, it also does Oracle, MySQL, Analysis Server, and Access.

check it out here: http://www.elsasoft.org

SqlSpec also has very good reviews - they are well deserved. :)

Subject: re: SqlSpec
Posted by: TomC (view profile)
Posted on: Sunday, February 04, 2007 at 1:51 PM
Message: Every documentor seems to get good reviews. I, personally, rather like DBDesc, mainly because of its XML. It is even cheaper than SqlSpec and its output is prettier.
There are a number of documenting utilities out there, and I reckon that the one you choose is all down to personal preference, and depends on the features that are important to you. SqlSpec might be a good choice if cross-platform uniformity in documentation is important to you but that isn't of any interest to me. I want a product that makes the best use of extended properties, and provides a complete range of output possibilities with scope for modification of the output styles. So many of these utilities give you masses of information just because it is there, rather than because you want it. I'd love a way of paring this down to the essentials, which for me is the DDL, the extended properties, and the dependencies.

Subject: Other Documentation tools
Posted by: David Connell (view profile)
Posted on: Tuesday, February 06, 2007 at 9:36 AM
Message:

My name is David Connell and I work for Red Gate and, specifically, I worked on the SQL Doc project. Firstly, if you want to document anything other than Microsoft SQL Server 2000 or 2005 then SQL Doc is not for you and you may want to evaluate one the alternatives mentioned in the previous comments.

What we did with SQL Doc was spend a lot of time making sure that it was a) easy-to-use and b) offered complete support for SQL 2000 and 2005 objects.

We put a lot of effort into constructing a really straightforward user interface that allows you to specify what objects etc. you want in a very simple manner. When we say we support SQL Server then we mean we *really* support SQL Server. Take SQL 2005 assemblies, for example. Not only do we document the basic types etc in each assembly, we also give you complete access to the assembly so that you can use cool tools on it, such as Lutz Roeder's .NET Reflector [URL: http://www.aisto.com/roeder/dotnet/]. If you have deployed by Visual Studio then we will give you the exact source code that went with your assembly and access to the PDB. The list goes on and on, but I hope that you can see where I am coming from.

We can do this because SQL Doc uses the same underlying technology as Red Gate's SQL Compare, and this SQL Compare technology allows us to parse and understand the SQL directly. It is obviously a non-trivial exercise to read system tables, to understand how Microsoft have built their technologies etc, but the benefits are enormous as only then do you begin to understand what is really going on. SQL Doc is built upon these technologies because we have found that this is the only way to truly understand a database and its relationships, and therefore generate totally accurate and complete documentation for that database.

Whatever bells and whistles other tools might offer, I believe that, for the reasons above, SQL Doc provides the most complete support for SQL Server of any documentation tool on the market. That's not to say it's perfect of course, and if you feel that it's limited in some way then please write to either the [URL: http://www.red-gate.com/MessageBoard/viewforum.php?f=55] SQL Doc forum or myself. If we cannot address your issues straight away then I can promise you we will put your comments forward to be considered for the following product releases.

I look forward to hearing from you in the near future.

David Connell


Subject: LiveDoco
Posted by: Zar Shardan (view profile)
Posted on: Saturday, April 28, 2012 at 11:41 AM
Message: Check out my new web browser based SQL Server documentation tool - LiveDoco [URL: http://www.livedoco.com] It does not generate any help files or documents. Instead it provides a search-enabled web based interface to your SQL Server database's metadata. It allows editing extended properties and uses them to store the notes/comments (MS_Description) so it is compatible with Red Gate's SQL Doc, probably SQLSpec and other documentation generators. Currently it supports tables, views, stored procedures and user defined functions so its output is not as comprehensive as SQL Doc's however it wasn't my goal either. Its primary use (as I see it) is to give SQL Server developers and DBA's an easy way to quickly explore, search and comment their databases. LiveDoco is not a direct replacement for documentation generators because sometimes we actually need a help file or a pdf document.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.