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.
