How to collect information about SQL audits and database mail 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 mail. 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 mail 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:

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:

The new Audit Properties window will be opened:

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:

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:

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: 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 mail in SQL Server Management Studio

Database mail 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 Mail object so we will show how to collect it from there using SQL Server Management studio.

Collect database mail information using GUI

To review and collect information about database mail 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:

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:

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

The new Manage Existing Account window will be opened:

This window will now show all information about configured database mail 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:

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

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

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:

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

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:

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

Collect database mail information using scripting

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

EXEC msdb.dbo.sysmail_help_configure_sp;

This statement provides information about system parameters for database mail:

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

EXEC msdb.dbo.sysmail_help_account_sp;

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

EXEC msdb.dbo.sysmail_help_profile_sp;

And accounts association with one or more database mail profiles:

EXEC msdb.dbo.sysmail_help_profileaccount_sp;

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

EXEC msdb.dbo.sysmail_help_principalprofile_sp;

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:

From the Data sources and objects panel select 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:

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

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 mail 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:

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

Regarding the database mail, 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:

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:

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

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

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:

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:

Collect database mail information using ApexSQL Doc

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

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

• Mail accounts
• Configuration values
• Mail profiles

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:

Configuration values

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

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

• AccountRetryAttempts:

  

• AccountRetryDelay:

  

• DatabaseMailExeMinimumLifeTime:

  

• DefaultAttachmentEncoding:

  

• LoggingLevel:

  

• MaxFileSize:

  

• ProhibitedExtensions:

  

Mail Profiles

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

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

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 mail'

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:

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 mail on the test server:

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

This message means that database mail 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:

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:

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

Conclusion

With ApexSQL Doc all needed information regarding SQL audit sessions, and database mail 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.