Applies to
Summary
This article gives a comprehensive look into the automation process of the SQL Server instance documentation.
Introduction
Maintaining documentation for dedicated SQL Servers is a repetitive task that could benefit from some kind of automatization. Keeping important parameters for a SQL Server, saved in a form that is easy to review and to retrieve when needed, should be done periodically as SQL best practices indicate. At least two major effects could be achieved that way, archiving SQL Server parameter history in case of frequent changes, and keeping working set of parameters in one place so they can be easily applied on another instance.
It is possible to achieve this level of automatization using ApexSQL Manage tool’s built in operation scheduler which will be explained in this article.
Create a documentation schedule
To create scheduled tasks to generate the SQL Server documentation, head over to the Schedules tab and from the Create drop down menu opt for the Documentation schedule:
This action will initialize the Documentation window with the Schedule tab shown by default. Here, provide the name of the schedule along with the specific day and time this scheduled task will be triggered. For instance, the screenshot bellow illustrates schedule set for every Friday at 6AM:
Now, let’s move on to the Data sources tab, where all SQL Server instances currently managed by the tool, along with their previous snapshots will be displayed. From here, choose instance(s) and/or snapshot(s) to document:
To choose what SQL Server properties should be documented, switch to the Filters tab, and choose properties to include into documentation. This selection is easily made by ticking the checkbox of desired property:
In the Output tab, configure the documentation output. Choose a file format from the drop-down list, select the destination folder, the one that will store this document, and define the template for the filename. In case it is required to keep the history of documentation, use the proposed Date and Time system variables for automatic filename generation. Additionally, in the Custom text section, customize header and footer sections in documentation pages by tweaking the available settings:
- More information about the scope of options available for documentation task can be found in the How to create detailed documentation for SQL Server instances and snapshots article
Since the documentation task will be executed unattended it is recommended to use the notification option to be able to receive the status of the executed task. Go to the Notification tab, and in the Documentation notify events section choose the desired task status to be notified about:
- Success
- Failure
- Skipped
Additionally, choose whether to include report in the notification email, by ticking the checkbox next to the Attach report on finish. In the end, enter the email address of the recipient by clicking the Add button:
- For more information on configuring email notifications, please consult the How to configure email notifications for core features in ApexSQL Manage guide
In the Summary tab, an overview of chosen parameters of the Documentation window are presented: chosen SQL Server instance(s) as data sources, property filters, name of the document, schedule description, etc…Click the OK button to confirm the schedule creation:
In the next step, the Running tasks completed window will be shown to inform that all operations completed successfully. Click the Finish button to exit the setup:
An overview of the Schedule tab
The new schedule is displayed in the main grid of the Schedules tab, waiting to be performed based on the set date and time parameters:
To edit the schedule, click the Edit button and change any of the previously configured settings:
To see the SQL Server instance documentation schedule details, click the Details button. This action will open the Schedule details window when all schedule parameters are conveniently sorted:
Furthermore, to disable the schedule, i.e. to prevent the task from running based on its schedule, click the Disable button. Enable it back when convenient using the Enable button:
To manually run the scheduled task, click the Run button. This will run the documentation generation outside of its set date/time frequency and it will not disrupt the current schedule:
Once the task is performed, manually or automatically, the email notification will arrive with previously configured notify events:
With a clean documentation management like this it will be possible to preserve a SQL Server instance parameters and re-enter them in case of a need to revert some changes or use them for new instance.
