How dbForge Documenter is involved in the DevOps process

There are many reasons why documentation is a valuable part of the CI process. Indeed the list of benefits of up-to-date documentation is endless. But its main functional purpose is reflecting the current state of the database.

Documenter for SQL Server is a quick and simple tool that automatically generates database documentation as HTML, Markdown or pdf files. The generated documentation contains information about database objects, their creation scripts, and objects dependencies. After the documentation has been created, it can be included in the NuGet package along with database scripts. The general outline of Documenter for SQL Server involvement in the CI process is shown in the figure below:

Documenter Scheme

There are two ways of using Documenter for SQL Server in the CI process

1. Generating documentation on the fly based on a database connection. In this case, the information about database objects will be generated by default.
2. Creating a * .ddoc project via the Documenter GUI. In this case, you can add additional descriptions to your objects. You can also control which objects (tables, stored procedures, functions, etc.) are included in the documentation and which are not.
Documenter for SQL Server

Script to use dbForge Documenter in PowerShell

The PowerShell script below shows how to create detailed and accurate documentation for the database on the fly using the database connection string:

# Variables
$serverName = "SQLEXPRESS15"
$databaseName = "AdventureWorks2019"

# Create database connection
Write-Host "Creating database connection..."
$connection = New-DevartSqlDatabaseConnection -Server $serverName
-Database $databaseName -WindowsAuthentication $true

# A file path to an output file
$outputFileName = "C:\Test\Out\database.pdf"

New-DevartDatabaseDocumentation -Connection $connection -Format Pdf -Output
$outputFileName

Below is the PowerShell script, that shows how to create documentation for a database based on an existing * .ddoc project:

# A file path to a project file
$projectFile = "C:\Test\Doc\ProjectFile.ddoc"

# A file path to an output file
$outputFileName = "C:\Test\Out\database.pdf"

New-DevartDatabaseDocumentation -ProjectFile $projectFile -Format Pdf -Output
$outputFileName

Depending on your needs, you can choose the method that suits you better. However, if you are developing a database from scratch and it does not have a stable structure as the new objects are actively appearing or the existing ones are changing, then it is recommended to use the first method of organizing your database documentation. In this case, the documentation will be generated on the fly and all database objects will fall into the resulting file.

When the database is well-elaborated and provides a fairly stable structure, you can create a * .ddoc project and customize which objects to include or exclude when generating a report. You can also add comments to the objects in the “Description” section as shown in the figure below:

Description section

The rendered documentation report file can be included in the NuGet * .nupkg file along with database scripts. The PowerShell script for creating the documentation file and including it into the NuGet package is shown below.

# A database connection string
$projectFile = "C:\Test\Doc\ProjectFile.ddoc"

# A file path to an output doc file
$outputFileName = "C:\Test\Out\database.pdf"

# A path to an output nuget file
$publishOutputFolder = "C:\Test\Out\"

$migrationScriptsFolder = "C:\Test\Scripts"

# Create a doc file
New-DevartDatabaseDocumentation -ProjectFile $projectFile -Format Pdf -Output $outputFileName

# Create a nuget package which contains both a database script and a doc file.
$project= New-DevartDatabaseProject -SourceScriptsFolder $migrationScriptsFolder
Set-DevartPackageInfo -Project $project -Id "Test.DevOpsAutomation.Database"
-Version 3.0.0 -Documentation $outputFileName
Export-DevartDatabaseProject -Project $project -Format NuGet -OutputDirectory
$publishOutputFolder -Overwrite

Conclusion

Conclusion

Using Documenter for SQL Server and PowerShell, you can easily get comprehensive up-to-date database documentation. The generated documentation can be then included in the resulting NuGet package along with database scripts. Later on, the documentation will be valuable not only for administrators and business analysts but also for employees who are just starting to work on that database development project.

Learn about other dbForge tools involved in collaborative
database development and deployment