How to collect information about SQL audits and database mails using SQL documentation

Manual replication of a SQL Server is still nothing out of the ordinary, so keeping some SQL documentation about the ‘original’ SQL Server is recommended to use as a reference. SQL Documentation can come in handy especially when server level objects have to be reconstructed manually on the new instance, for example, SQL audit settings or database mails. Having the T-SQL scripts also with this documentation for recreating those objects will certainly simplify the actions needed for the task.

As we know the databases are easy to replicate to another SQL Server, but the information related to the SQL Server requires a lot of time to collect and recreate.

When a database administrator receives a task to recreate, for example, SQL audit settings or database mails on some test server, based on the current production server, the first step in that process is to collect all necessary information from the current SQL Server.

All needed information can be collected using SQL Server Management Studio, so let’s see how it’s done.

SQL Audits in SQL Server Management Studio

Changes that are made to a specific server, database or its objects can be monitored using the SQL Server audit feature that can be configured using the SQL Server Management Studio.

In the following examples, it will be explained how to collect information related to SQL audits that should be propagated to the test server using GUI and SQL script with SQL Server Management Studio.

Collecting SQL Audits information using GUI

To find SQL audits for the databases, expand the Audits folder which is located under the Security folder in the Object Explorer panel:

SQL Audit sessions list

All created SQL audit sessions will be shown. Right-click over the desired SQL audit session and =from the context menu select Properties. For this example, the object called Audit-AdventureWorks2017, representing one auditing session, will be used:

How the see the SQL Audits Properties

The new Audit Properties window will be opened:

The SQL audits Properties wizard

From this window, all valuable information about the SQL audit session settings, like Audit name, Queue delay, On Audit Log Failure, etc., can be collected so that they can be used to setup SQL audit on the test server.

Collect SQL audit information using scripting

Another method for extracting information about audit sessions is to script them out. From the Object Explorer panel, click the Security folder and choose Audits folder:

SQL Audits

As explained before, all created SQL audit sessions will be stored in the Audits folder and from here the SQL script can be extracted.

Right-click on the created audits, then from the context menu expand the Script Audit As sub-menu, then expand the CREATE To sub-menu, and select the New Query Editor Window:

Create the Query for SQL audits

Inside SQL Server Management Studio new tab will be opened which contains the following SQL script:

USE [master]
GO

/* Object:  Audit [Audit-AdwentureWorks2017]    Script Date: 10/27/2020 10:30:17 AM */
CREATE SERVER AUDIT [Audit-AdwentureWorks2017]
TO FILE
(      FILEPATH = N'C:\backup\'
      ,MAXSIZE = 1024 MB
      ,MAX_FILES = 2147483647
      ,RESERVE_DISK_SPACE = OFF
)
WITH
(
      QUEUE_DELAY = 1000
      ,ON_FAILURE = CONTINUE
      ,AUDIT_GUID = 'e9bc09bc-ba6e-4a7c-9183-77d5f3341293'
)
ALTER SERVER AUDIT [Audit-AdwentureWorks2017] WITH (STATE = ON)
GO

The database administrator can now use this SQL script and create a copy of the SQL audits for the database on the test SQL Server.

In the created script necessary information about SQL audit session will be shown:

  • FILEPATH = N’C:\backup\’ – where session files with audited data will be stored
  • MAXSIZE = 1024 MB – the maximum file size per session file
  • MAX_FILES = 2147483647 – the maximal number of session rollover files
  • RESERVE_DISK_SPACE = OFF – reserve disk space for upcoming session file creation
  • QUEUE_DELAY = 1000 – delay (in millisecond) before SQL audit processing intervals
  • ON_FAILURE = CONTINUE – continue SQL operations in case auditing fails
  • ,AUDIT_GUID = ‘e9bc09bc-ba6e-4a7c-9183-77d5f3341293’ – Audit_GUID ID

Quick tip icon

Quick tip:

More information regarding the setup of SQL audits for databases can be found in the following article: Various techniques to audit SQL Server databases

Database mails in SQL Server Management Studio

Database mails serve as a solution for sending e-mails from the SQL Server database engine to configured recipients. In this way, people of interest, like database administrators will be familiar with the changes in a database.

This information is stored in the Database Mails object so we will show how to collect it from there using SQL Server Management studio.

Collect database mails information using GUI

To review and collect information about database mails start the SQL Server Management Studio and from the Object Explorer navigate to the Management folder and expand it. Then right-click on the Database Mail and from the context menu select Configure Database Mail:

Configure database mail

The Database Mail configuration wizard will be opened. To access current configuration select the Manage Database Mail accounts and profiles, then click the Next button:

Manage database mail accounts and profiles wizard in the SQL Server Management Studio

In the next step select the View, change or delete an existing account, then click the Next button:

View, change, or delete an existing account wizard in the SQL Server Management Studio

The new Manage Existing Account window will be opened:

Manage Existing Account wizard in the SQL Server Management Studio

This window will now show all information about configured database mails so a database administrator can use it to recreate it on another SQL Server.

All saved mail accounts can be reviewed in the following manner. Select the Account name from the drop-down list:

Select Account name for database mails

Then, when the Account name is selected, gather information from chosen account about Email address, Display Name, Reply email, etc.

See the Email addresses for the database mails

From the SMTP Authentication section, the administrator needs to check the type of authentication that will be used on the test server:

Type of the SMTP Authentication for the database mails

Quick tip icon

Quick tip:

If there are more accounts, this process must be repeated for every account.

Now is time to collect information about account security profiles. This can be done by selecting a Manage profile security then clicking the Next button:

Manage profile security for database mails

The wizard will show which profile is public or private, along with all profiles:

Select Public profiles for the atabase mails

The final step is to see the configurations, this can be done by selecting a View or change system parameters, followed by the Next button:

View or change system parameters for database mails

The wizard will show all system related parameters with ther values:

Configure System Petameters for database mails

Collect database mail information using scripting

Another way to collect information related to database mails is to use the following statement:

EXEC msdb.dbo.sysmail_help_configure_sp;

This statement provides information about system parameters for database mail:

Information about configuration settings for database mail

Next statement provides information about the mail accounts (except passwords):

EXEC msdb.dbo.sysmail_help_account_sp;

Information about database mail accounts

Information about one or more mail profiles can be obtained with:

EXEC msdb.dbo.sysmail_help_profile_sp;

Information about one or more mail profiles

And accounts association with one or more database mail profiles:

Information about the accounts associated with one or more database mail

And final information about associations between database mail profiles and database principals:

Information about associations between database mail profiles and database principals

Quick tip icon

Quick tip:

More information regarding the setup of SQL Database Mail can be found in the following article How to configure Database Mail in SQL Server.

SQL documentation software

The entire procedure previously explained can be done in one action using SQL documentation software like ApexSQL Doc. This is a SQL documentation software that allows documenting of Analysis Service databases (SSAS), SQL Server Integration Service (SSIS) packages, multiple SQL databases, Tableau sites, and SharePoint farms easily and automatically.

Let’s demonstrate how SQL audit and Database Mail objects can be documented using this tool.

When the application is started, the first step is to click the New button from the Home tab:

Home tab in ApexSQL Doc

From the Data sources and objects panel select Server engine:

SQL Server engine

Then click the Add button and the new Add SQL server window will be shown. Select the desired Server and type of the Authentication from the drop-down list, and click the OK button:

Connection to SQL Server using ApexSQL Doc

The selected server will be loaded and shown on the left side in the server panel.

SQL Server listing

Quick tip icon

Quick tip:

This process can be done with more SQL servers at the same time

When the SQL Server is loaded, select the Server objects under the Server engine tab in the SQL documentation software. In the Object type grid available server objects types will be listed for documentation. For the purpose of this example, the Audits and Database mails should be selected.

When the Audits are selected on the right side of the main window all objects of Audits type will be shown, select the desired one by check on the check box near the name of the desired SQL audits.

In this example, the Audit-AdwentureWorks2017 will be used:

SQL Server objects available  for documentation

Quick tip icon

Quick tip:

SQL documentation software supports documenting multiple numbers of SQL Audits at the same time.vvv

Regarding the database mails, use the check box next to the Database Mail item to select it. The SQL documentation software will automatically check the SqlMail object in the right grid:

Database Mail object type

Before generating the SQL documentation, the administrator can select the desired output file format.

ApexSQL Doc supports the six most popular output formats:

  • Compiled HTML (.chm)
  • Linked THML (.html)
  • Markdown (.md)
  • Word document 97-2003 (.doc)
  • Word document 2007 (.docx)
  • Portable Document Format (.pdf)

For the demonstrative purpose of this article, the CHM output file format will be used:

Select output file formats

When the desired output file format is selected click on the Generate button from the Home tab:

ApexSQL Doc

When the SQL documentation is generated, Audits, and Database mails will be shown under the Server objects in the content tree:

Server objects

Collect SQL Audits information using ApexSQL Doc

By clicking the Audits item, in the generated SQL documentation, the additional information regarding SQL audits will be shown on the right side of the documentation. In the Audit properties section in the SQL documentation we can see that documentation generated with ApexSQL Doc contains more pieces of information like Created date, Date last modified, etc.

The administrator from this point now can take information on how to recreate the SQL audits on the test SQL Server using the SQL Server Management Studio:

Documented SQL database audits using ApexSQL Doc

Alternatively, scroll down in the SQL documentation to the Audit script section where the T-SQL script can be found which can be executed in SQL Server Management Studio to recreate the SQL audits on the test SQL Server in one go:

Generated SQL script  for SQL database audits

Collect database mails information using ApexSQL Doc

Now, it is time to collect information created by SQL documentation software to recreate database mails on the test SQL Server.

In the generated documentation three separates sections are located under the SqlMail item, which is stored under the Database mails item in the content tree:

  • Mail accounts
  • Configuration values
  • Mail profiles

List of database mails

Mail accounts

The Mail accounts contains the information about database mail account properties. As it is stated before, , the T-SQL script is present which can be executed and new mail profile created using SQL Server Management Studio:

Database mail account properties

Configuration values

In the SQL documentation, the Configuration values section contains the configuration properties with their values:

List of configuration values for database mails

These parameters and their respective representation in the generated SQL documentation are:

  • AccountRetryAttempts:

    Value and properties regarding the Account Retry Attempt for database mails

  • AccountRetryDelay:

    Account Retry Delay for database mails

  • DatabaseMailExeMinimumLifeTime:

    Database Mail Exe Minimum Life Time  SQL script

  • DefaultAttachmentEncoding:

    Default Attachment Encoding

  • LoggingLevel:

    Logging Level script documented using ApexSQL Doc

  • MaxFileSize:

    Max File Size for database mails

  • ProhibitedExtensions:

    Properties regarding Prohibited Extensions

Mail Profiles

The database mail profiles can be found stored in the Mail profiles section:

List of database mail profiles

The administrator can now select the desired mail profile and collect the information from the SQL documentation:

SQL script for creating database mail profiles

Let’s review all T-SQL scripts, regarding the default mail account, from the SQL documentation in one place. These scripts can now be used and run with the SQL Server Management Studio to finish the replication task:

USE [master]

IF NOT EXISTS (SELECT profile_id FROM msdb.dbo.sysmail_profile WHERE name = N'Admin')
BEGIN
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'Admin'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = N'Admin'
  ,@account_name = N'Admin',@sequence_number = 1
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name = N'guest'
  ,@profile_name = N'Admin',@is_default = 0

END

USE [master]
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysmail_account WHERE name = N'Default Account')
EXEC msdb.dbo.sysmail_add_account_sp @account_name = N'Default Account'
    ,@email_address = N'sql.myserver@mycompany.com'
    ,@display_name = N'SQL - myserver'
    ,@replyto_address = N'dbas@mycompany.com'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'AccountRetryAttempts'
  ,@parameter_value = N'1' ,@description = N'Number of retry attempts for a mail server'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'AccountRetryDelay'
  ,@parameter_value = N'60' ,@description = N'Delay between each retry attempt to mail server'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'DatabaseMailExeMinimumLifeTime'
  ,@parameter_value = N'600' ,@description =N'Minimum process lifetime in seconds'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'DefaultAttachmentEncoding'
  ,@parameter_value = N'MIME',@description = N'Default attachment encoding'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'LoggingLevel'
  ,@parameter_value = N'2'
  ,@description = N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'

USE [master]
EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'MaxFileSize'
  ,@parameter_value = N'1000000',@description = N'Default maximum file size'

USE [master]

EXEC msdb.dbo.sysmail_configure_sp @parameter_name = N'ProhibitedExtensions'
  ,@parameter_value = N'exe,dll,vbs,js'
  ,@description = N'Extensions not allowed in outgoing mails'

USE [master]
IF NOT EXISTS (SELECT profile_id FROM msdb.dbo.sysmail_profile WHERE name = N'Default Profile')

BEGIN
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name = N'Default Profile'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name = N'Default Profile' ,@account_name = N'Default Account' ,@sequence_number = 1

END

Previous explanation targets solution for just one mail profile. In case all accounts should be reconstructed that can be done in a bulk. To do that,, click on the SqlMail item in the document:

SQL Mail content

As shown in the image below, the complete T-SQL script is saved in the SQL documentation, which can be executed using SQL Server Management Studio to recreate the database mails on the test server:

Database mails in SQL Documentation

In ideal conditions, this script will be executed successfully, but there is a possibility to receive the following information:

Database mail is not activated

This message means that database mails are not activated, this is very easy to solve.

Open the Management folder from the Object Explorer panel then right-click over the Database Mail and select the Configure Database Mail option from the context menu:

Configure Database Mail

The wizard will be opened, along with an information message: “The Database Mail feature is not available. Would you like to enable this feature?” click the Yes button, then go back to SQL Server Management Studio and execute the SQL script:

Enable database mail

If everything goes well the following information message should be shown in SQL Server Management Studio that “Commands completed successfully

Executed script created using ApexSQL Doc

Conclusion

With ApexSQL Doc all needed information regarding SQL audit sessions, and database mails can be collected in one SQL documentation created in a few clicks. Using ApexSQL Doc for this process the time will be saved, and the created documentation can be stored locally or on the cloud for everyone to use if needed to recreate documented SQL Server objects.

 

November 17, 2020