Piotr Kononow
The problem
It is important for your whole team to have access to up to date documentation of your database elements: tables, views, columns etc. along with their descriptions. When databases are under development documentation quickly becomes out of date.
Solution
You can solve this with Dataedo in just three steps. First, set up Dataedo documentation repository and import database documentation schema to it. Then provide the developers and DBAs access to the Dataedo editor so they can edit descriptions and metadata of your database. And lastly, set up automated daily tasks to update schema and generate documentation.
That will give you:
- Global repository with metadata (potentially for all databases)
- Framework to gather documentation
- Automated documentation generation and publishing process
The process
1. Designing the database
Developers apply changes to the database as usual. They create new tables and columns with tools of their choice.
2. Updating documentation schema [Automatic]
You can set up an automatic task to update schema stored in your documentation - all changes to tables and other objects will be imported and applied in the repository. New tables and columns will be added. Deleted ones will be marked as such, so no descriptions will be lost.
3. Describing database objects
Developers and architects can access documentation in the repository and check if all elements are described. New elements with missing descriptions will show less than 100% in progress column. This information can be updated through a convenient editor.
4. Exporting and publishing documentation [Automatic]
It is possible to schedule automatic documentation export from the repository to a HTML or PDF. The document can be saved in shared location so your team members always have access to the most recent documentation.
Open sample Dataedo HTML export in new window
Setting up the tool
1. Install
Download and install Dataedo on workstations of the team members responsible for keeping the documentation up to date, and the machine where the tasks will be scheduled.
You also need to get a trial key (or enter your Pro license key), as functionality described here is not available in free edition.
2. Set up repository
Dataedo can store its metadata in a file or an SQL Server database. For teams we recommend using the server repository. You can create it on any existing instance or just install free SQL Server Express Edition (download).
To create a repository open Dataedo, select the Create new repository option and click Next button.
Then provide the connection details and click Create button.
Online documentation: creating repository.
3. Import database schema
Now that you have a repository you need to connect to and import your database's schema. This will create a new documentation in the repository. To do this, click the Add documentation button on the ribbon.
4. Create update command file
To schedule Dataedo tasks you need to create a command file - an XML file specifying the tasks to be performed. To get an update command file click the Update documentation button on the ribbon, then in the last step click the save the file link.
Online documentation: updating schema
5. Create export command file
Now you need a similar file for the export task. Just click Export documentation, choose format and location, then save the file in the last step.
Online documentation: exporting
6. Schedule tasks
Now that you have the command files you can set up a Windows Scheduler task that run Dataedo with a file as a parameter. More on this here.
Online documentation: executing command file
Conclusion
You have set up a repository and scheduled automatic documentation update and export tasks. Make sure all people responsible for documenting have Dataedo editor installed and everyone who may need access to documentation have links to the exported files. You may want to repeat this process for the rest of your databases. Your process is ready to go – your team can now enjoy being always up to date with your database design.