Applies to
Summary
This article explains how to deploy a custom SQL audit template against multiple SQL Servers or databases.
Details
It is not uncommon that multiple SQL servers and databases have the same SQL audit requirements. Having that fact in mind it is possible to use the option to apply the same SQL audit filter configuration to many instances thus reducing the total time needed to set up auditing in larger environments. This article describes the process of auditing configuration deployment against multiple servers and/or databases.
Setting the custom template
The first action required for multiple auditing deployment is to create a custom operations filter template. For this action, it is required to go to the Configure tab and add one SQL Server instance for auditing:
The SQL Server addition and configuration process is completely explained in the article: Auditing SQL Server databases – initial configuration
When the initial server is added, use the Operations grid view to set the filters for the operations required to be audited. This will be the template used later to apply it to other SQL servers:
When the filter configuration is set by the requested auditing standard, it can be considered as a custom template. To confirm the setup, click the Apply button as shown below:
This server can now be used to propagate audit filtering configuration to other servers. It is possible to create one custom template on one selected (sample) SQL Server and different custom templates using other SQL servers as a sample.
Applying the custom template to multiple instances
If the SQL auditing filter template is applied to the initial SQL Server, it is time to proceed with adding the rest of the SQL servers for auditing. Repeat the procedure to add a server for each required instance and leave the auditing configuration empty:
With all required instances added, click back on the initially configured SQL Server in the server tree and click on the Templates drop-down menu. From there, click on the Apply to many option and the dialogue with the list of currently present SQL servers will be shown where the instances, that require custom template, can be selected with checkboxes. From there, click on the Apply button to confirm the configuration:
Note that the order of actions is arbitrary. It is possible to first add all of the SQL servers for auditing, i.e. create a list of servers, then configure one of the servers to create a custom template and, in the end, use the Apply to many option.
Editing a custom template
In the case where the SQL audit requirements are changed, which means that the filter configuration will have to be changed, performing multiple updates will also require a quick solution. This is where the multiple configuration deployment can be used again. First, select one of the SQL servers that has the custom template applied. Change the filter settings in the Operations grid view and confirm the changes with the Apply button in the bar:
Now the custom template is updated, so to propagate the updates, use the Apply to many option from the Templates drop-down menu. The SQL Server selection dialogue will again show the list of present SQL servers. At this point, it is possible to select instances to apply the new configuration to. To confirm the update on selected instances, click the Apply button:
If no configuration editing is done, using the Apply to many option will provide a preview of instances with the same configuration applied by labeling them with checkmarks automatically. This can be used to create an inventory of instances grouped by their respective configurations.
Applying the custom template to multiple databases
Analog to the explanation about multiple SQL Server configurations, databases can also be configured in one go. First, for each SQL Server in the server tree, add databases that need to be audited. When the database list is created, pick one database to use it as a sample, and configure the auditing filters in the Operations grid view. To confirm the setup, click the Apply button in the bar:
Keep the configured database selected and use the Apply to many option from the Templates drop-down menu, and the dialogue with the list of databases, grouped by their respective hosts, will be shown. Pick those databases that require the same auditing template and confirm the configuration with the Apply button:
By selecting any database in the server tree, the audit filter configuration can be confirmed as it will be shown in the Operations grid view:
Again, analog to the SQL Server explanation, the SQL audit configuration can be also updated on multiple databases at once.
The explained method allows multiple custom SQL audit templates to be saved throughout the sample SQL Servers/databases and applied against any required combination in one action.
