In my previous post, “Automating Deployments with SQL Compare command line” I looked at how teams can automate
the deployment and post deployment validation of SQL Server databases using
the command line versions of Red Gate tools. In this post I’m looking at another use for
the command line tools, namely using them to generate up-to-date documentation with every database change.
There are many reasons why up-to-date documentation is valuable. For example when somebody new has to work on or administer a database for
the first time, or when a new database comes into service. Having database documentation reduces
the risks of making incorrect decisions when making changes. Documentation is very useful to business intelligence analysts when writing reports, for example in SSRS. There are a couple of great examples talking about why up to date documentation is valuable on this site: Database Documentation – Lands of Trolls: Why and How? and Database Documentation Using SQL Doc.
The short answer is that it can save you time and reduce risk when you need that most!
SQL Doc is a fast simple tool that automatically generates database documentation. It can create documents in HTML, Word or pdf files.
The documentation contains information about object definitions and dependencies, along with any other information you want to associate with each object.
The SQL Doc GUI, which is included in Red Gate’s SQL Developer Bundle and SQL Toolbelt, allows you to add additional notes to objects, and customise which objects are shown in
the docs. These settings can be saved as a .sqldoc project file.
The SQL Doc command line can use this project file to automatically update
the documentation every time
the database is changed, ensuring that documentation that is always up to date.
The simplest way to keep documentation up to date is probably to use a scheduled task to run a script every day. However if you have a source controlled database, or are using a Continuous Integration (CI) server or a build server, it may make more sense to use that instead.
If you’re using SQL Source Control or SSDT Database Projects to help version control your database, you can automatically update
the documentation after each change is made to
the source control repository that contains your database. To get this automation in place, you can use
the functionality of a Continuous Integration (CI) server, which can trigger commands to run when a source control repository has changed. A CI server will also capture and save
the documentation that is created as an artifact, so you can always find
the exact documentation for a specific version of
the database. This forms an always up to date data dictionary.
If you don’t already have a CI server in place there are several you can use, such as
the free open source Jenkins or
the free starter editions of TeamCity. I won’t cover setting these up in this article, but there is information about using CI servers for automating database tasks on
the Red Gate Database Delivery webpage. You may be interested in Red Gate’s SQL CI utility (part of
the SQL Automation Pack) which is an easy way to update a database with
the latest changes from source control.
The PowerShell example below shows how to create
the documentation from a database. That database might be your integration database or a shared development database that is always up to date with
the latest changes.
$serverName = "server\instance"
$databaseName = "databaseName" # If you want to document multiple databases use a comma separated list
$userName = "username"
$password = "password"
# Path to SQLDoc.exe
$SQLDocPath = "C:\Program Files (x86)\Red Gate\SQL Doc 3\SQLDoc.exe"
$arguments = @(
"/server:$($serverName)",
"/database:$($databaseName)",
"/username:$($userName)",
"/password:$($password)",
"/filetype:html",
"/outputfolder:.",
# "/project:$args[0]", # If you already have a .sqldoc project file you can pass it as an argument to this script. Values in
the project will be overridden with any options set on
the command line
"/name:$databaseName Report",
"/copyrightauthor:$([Environment]::UserName)"
)
write-host $arguments
& $SQLDocPath $arguments
There are several options you can set on
the command line to vary how your documentation is created. For example, you can document multiple databases or exclude certain types of objects. In
the example
above, we set
the name of
the report to match
the database name, and use
the current Windows user as
the documentation author. For more examples of how you can customise
the report from
the command line please see
the SQL Doc command line documentation
If you already have a .sqldoc project file, or wish to further customise
the report by including or excluding specific objects, you can use this project on
the command line. Any settings you specify on
the command line will override
the defaults in
the project. For details of what you can customise in
the project please see
the SQL Doc project documentation.
In
the example
above,
the line to use a project is commented out, but you can uncomment this line and then pass a path to a .sqldoc project file as an argument to this script.
Conclusion
Keeping documentation about your databases up to date is very easy to set up using SQL Doc and PowerShell. By using a CI server to run this process you can trigger
the documentation to be run on every change to a source controlled database, and keep historic documentation available.
If you are considering more advanced database automation, e.g. database unit testing, change script generation, deploying to large numbers of targets and backup/verification, please email me at
[email protected] for further script samples or if you have any questions.
