6. Skip to content

6. SCM Database Post Installation Steps and Maintenance Checklist

This checklist provides a list of tasks to complete after installation of Specification Compliance Manager (SCM) in order to set up a database connection. Refer to the following table for the list of tasks to complete and the recommended cadence for each task.

Task Cadence
Verify edition of SQL Server database One-time
Enable SQL Server Agent One-time
(Optional) Set up email notifications that contain maintenance reports One-time
Create backup location for database and maintenance files One-time
6.4 Create maintenance tasks One-time
6.5 SQL Server maintenance Twice per month
6.6 Verify available storage capacity on drive that contains database files Daily
6.7 Check Resource used by SQL server on VM One-time

6.1 Verifying SQL Server Database Edition

Maintenance tasks can be created only in Standard or Enterprise edition SQL servers. These tasks are not compatible with an Express edition SQL Server.

  1. Launch SQL Server Configuration Manager from the Start menu.

  2. Select SQL Server Services

  3. Right-click on the SQL Server instance used by SCM click Properties.

  4. Open the Advanced tab of the Properties window.

  5. Locate the Stock Keeping Unit Name field and verify that the value is either “Standard Edition” or “Enterprise Edition”.

6.2 Enabling SQL Server Agent

  1. Launch SQL Server Configuration Manager from the Start menu.

  2. Select SQL Server Services.

  3. Verify that the SQL Server Agent status is Running. If it is not, right-click SQL Server Agent and click Properties.

    1. In the Properties window, open the Service tab, expand the Start Mode drop-down menu, and select Automatic.

    2. Click Apply, then click OK.

    3. Right-click SQL Server Agent and select Start to resume running the SQL Server.

6.3 (Optional) Setting Up Email Notifications for Maintenance Reports

Reference Link: https://solutioncenter.apexsql.com/how-to-set-up-email-notifications-for-backup-jobs-in-sql-server/

You must complete the following tasks to enable the SQL Server Agent service to send email notifications for backup jobs and maintenance plans:

  1. Enable Database Mail.

  2. Configure SQL Server Agent to use Database Mail.

  3. Create an Operator.

6.3.1 Enabling Database Mail

You can use either the database configuration wizard or a template script to enable Database Mail.

6.3.1.1 Configuring Database Mail Using the Database Configuration Wizard

  1. Launch SQL Server Management Studio (SSMS) and connect to the SQL Server for SCM.

  2. Expand the node for the SQL Server that requires Database Mail configuration, open the Management folder, right-click Database Mail and select Configure Database Mail.

  3. In the Database Mail Configuration Wizard window, click Next.

  4. Select Set up Database Mail by performing the following tasks: and click Next.

    Note: If Database Mail has not been enabled previously, the wizard will generate a prompt to ask you to enable this feature. Click Yes to enable Database Mail.

  5. Enter the Profile name and, if needed, a description for the profile.

  6. Click Add…

  7. Enter the following information in the New Database Mail Account window in order to create a new Database Mail account that can send emails to an SMTP server.

    1. Enter the Account name for the new account

    2. If needed, enter a Description for the new account.

    3. Enter the E-mail address that will send the email notifications.

    4. Enter the Display name to specify the name for the email account that will send notifications.

    5. If needed, enter the Reply e-mail to specify the email address that will receive replies to the emails sent from the account you entered in E-mail address.

    6. Enter the SMTP address for the mail server in the Server name field. STMP addresses typically use the following format: smtp.\<address>.com.

    7. Enter the Port number for the mail server. In most cases, the port number is 25. NI recommends consulting with your mail administrator to confirm the correct port number for your server.

    8. If needed, enable This server requires a secure connection (SSL) to encrypt communication on the mail server using the secure sockets layer.

  8. Select the SMTP Authentication option that aligns with your mail server standards.

    1. If you select Basic authentication, enter a User name and Password (and confirm the password) to set login credentials for the mail server account.

  9. Click OK to finish creation of the new Database Mail account.

  10. If needed, create additional accounts to use as a backup for situations where the primary email account fails to send a notification.

  11. After you create all necessary email accounts, click Next.

  12. Use the lists on the Public Profiles and Private Profiles tabs of the window to select the accounts that public and private users with mail host database (msdb) access can use.

  13. Click Next.

  14. Enter the parameters for email notifications, then click Next.

  15. Review the information that you entered to confirm that everything is accurate, then click Finish.

  16. Verify that the configuration actions complete successfully.

  17. Close the Database Mail Configuration Wizard window.

6.3.1.2 Configuring SQL Server Agent to use Database Mail

Note: Only users with sysadmin permissions can configure SQL Server Agent to use Database Mail.

  1. Expand a SQL Server instance in the Object Explorer, right-click on SQL Server Agent and select Properties.

  2. Open the Alert System page.

  3. Click the Enable mail profile checkbox.

  4. Expand the Mail system drop-down menu and select Database Mail.

  5. Expand the Mail profile drop-down menu and select the mail profile that you created previously.

  6. Click OK.

  7. Restart the SQL Server Agent service.

6.3.1.3 Creating an Operator for SQL Server Agent

Note: Only users with sysadmin permissions can create an operator for SQL Server Agent.

  1. Expand SQL Server Agent node in the Object Explorer, right-click Operators and select New Operator…

  2. Enter the name and email address for the operator in the Name and E-mail name fields on the General page of the New Operator window.

  3. Ensure that the Enabled checkbox is selected.

  4. Click OK.

6.4 Create Maintenance Tasks to Monitor Database Performance

Database performance can degrade over time due to several issues which you can monitor with maintenance tasks. Create the following scheduled and on-demand maintenance tasks to ensure that database performance remains optimal.

6.4.1 Create a Database Backup

NI recommends creating a daily backup of the database on a remote location outside the database server machine. You can use these backup files to restore the database on the event of database server machine corruption or downtime.

Create a scheduled daily maintenance task to perform database backups. Refer to Creating Scheduled Maintenance Tasks for more information.

Monitor Disk Space Usage of Database Log Files
Specification Compliance Manager store database log files while continuous database processes are ongoing. Ensure that the storage location for these files has enough capacity for database log files. NI recommends creating a backup of the database file and condensing the database log files for storage in the backup location.

Create a scheduled daily maintenance task to perform database log file management. Refer to Creating Scheduled Maintenance Tasks for more information.

Rebuild the Database Index
The database index can become fragmented as a result of delete, update, or insert (in some cases) operations performed on database tables that contain columns with an index. NI recommends rebuilding the index on columns to improve the speed of database table operations.

Create an on-demand maintenance task to rebuild the database index. Refer to Creating On-Demand Maintenance Tasks for more information.

Update column statistics
SQL Query compilation uses column statistics to create an optimized plan for query execution. NI recommends frequent updates to column statistics to ensure that the query compiler recompiles execution plans with up-to-date information.

Create an on-demand maintenance task to update column statistics. Refer to Creating On-Demand Maintenance Tasks for more information.

6.4.2 Creating Scheduled Maintenance Tasks

Scheduled maintenance tasks can be performed in parallel with other database operations without a negative effect on performance.

Complete the following steps to create a scheduled maintenance task.

Note: To enable email notification for execution report please complete Setting Up Email Notifications for Maintenance Tasks procedure before creating maintenance tasks.

  1. Enter the server name and authentication details to connect to the database using the SQL Server Management Studio (SSMS) and click Connect.

    Note: If SSMS is installed on the database server machine, you can also use the Windows authentication credentials.

  2. Expand SQL Server Agent in the Object Explorer section of the SSMS interface.

  3. Right-click Jobs and select New Job…

  4. Enter the following values in the fields on the General page of the New Job window.

    1. Name—Shrink_Log_File

    2. Owner—Enter a username with db_owner/sysadmin access

    3. Category—Database Maintenance

  5. Open the Steps page and click New.

  6. Enter the following values in the fields on the New Job Step window.

    1. Step name—Shrink_log_file

    2. Type—Transact-SQL script (T-SQL)

    3. Run as—Leave field empty.

    4. Database—Select the name of the SCM database.

    5. Command—Enter the following syntax:

ALTER DATABASE [<database_name>]
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE ('<database_log_file_name>')
GO

Tip: The default log file name is \<database_name>_log.

<img src="../media/image28.png" style="width:5in;height:2.8125in" />

  1. Open the Advanced page of the New Job Step window.

  2. Open the On success action drop-down menu and select Quit the job reporting success.

  3. Click OK to save the step and close the New Job Step window. You can view the new job step on the Job step list within the New Job window.

  4. Click OK to save the job and close the New Job window. You can view the new job under the Jobs object for the SQL Server Agent.

  1. Expand the Management folder for the SQL Server, right-click on Maintenance Plans and select Maintenance Plan Wizard.

  2. Click Next on the SQL Server Maintenance Plan Wizard window.

  3. Enter the following values in the fields on the Maintenance Plan Wizard window.

    1. Name—BackupAndCleanMaintenancePlan

    2. Run as—SQL Server Agent service account

    3. Select Single schedule for the entire plan or no schedule.

  4. Click Change to update the Schedule field.

  5. Enter the following values for the fields on the New Job Schedule window.

    1. Occurs—Daily.

    2. Recurs every—1 day

    3. Occurs once at—Set to a time when the database is not actively in use. NI recommends setting this field to early in the morning, local time. For example, 03:00.

    4. Enter a Start date and, if needed, enter an End date.

    5. Click OK.

  6. Click Next in the Maintenance Plan Wizard window.

  7. Select the following options in the Select Maintenance Tasks list, then click Next.

    • Execute SQL Server Agent Job

    • Back Up Database (Full)

    • Maintenance Cleanup Task

  8. Select the order to perform the tasks in the following order Move Up… and Move Down… buttons, then click Next.

    1. Back Up Database (Full)

    2. Execute SQL Server Agent Job

    3. Maintenance Cleanup Task

  9. In the Define Back Up Database (Full) Task window, open the Database(s) drop-down menu, select the required database, and click OK.

  10. Open the Back up to drop-down menu and select the type of destination location for the backup as either Disk or URL. This example will configure the task create a backup to a shared folder on disk.

  11. Open the Destination tab and select the Create a backup file for every database and Create a sub-directory for each database options.

  12. Enter the backup folder location in the Folder field

Note: Enter a remote location as the destination in the Folder field.

  1. Click Next.

  2. In the Define Execute SQL Server Agent Job Task window, select the Shrink_Log_File SQL Server Agent job and click Next.

  3. Select and enter the following values for the fields on the Define maintenance Cleanup Task window.

    1. Delete files of the following type—Select Backup files.

    2. File location—Select Search folder and delete files based on an extension.

    3. Folder—Enter the location of the shared folder for the backup files.

    4. File extension—bak

    5. Enable Include first-level subfolders.

    6. Enable Delete files based on the age of the file at task run time.

    7. Select the length of time to keep backup files. NI recommends setting this to 1 week.

  4. Click Next.

  5. In the Select Report Options window, select Write a report to a text file, enter the shared backup folder location, and click Next.

    Note: You can select E-mail report if you have created an operator email. Refer to Creating an Operator for SQL Server Agent for more information.

  6. Click Finish.

  7. Verify that scheduled maintenance task setup is successful, then click Close.

  8. Refresh the Maintenance Plans folder to view the new maintenance plan in the Object Explorer.

6.4.3 Creating On-Demand Maintenance Tasks

On-demand maintenance tasks typically require database resources for execution, which can be detrimental to other database operations. Run on-demand maintenance tasks during database server downtime to optimize database resource allocation.

Tip: NI recommends at least two periods of database server downtime per month for on-demand maintenance tasks. It is ideal to schedule downtime during holidays or other periods of time when the database server is not actively in use.

Complete the following steps to create on-demand maintenance tasks to update column statistics and rebuild the database index.

Note: To enable email notification for execution report please complete Setting Up Email Notifications for Maintenance Tasks procedure before creating maintenance tasks.

  1. Create a job for the index rebuild and column statistic update tasks.

    1. Expand the Management folder in the Object Explorer pane, right-click Maintenance Plans and select Maintenance Plan Wizard.

  2. Click Next.

  3. Enter the following values in the fields in the Select Plan Properties window.

    1. Name—RebuildAndUpdateStatsMaintenancePlan

    2. Run as—Select SQL Server Agent service account.

    3. Select Single schedule for the entire plan or no schedule.

    4. Schedule—Set to Not Scheduled (On Demand).

  4. Click Next.

  5. Select the following tasks from the list on the Select Maintenance Tasks window.

    1. Rebuild index

    2. Update statistics

  6. Click Next to confirm the task selection.

  7. Arrange the order of the tasks as needed, then click Next to confirm the order.

  8. Define the parameters for the Rebuild Index task.

    1. Databases—Select the database which you are using to connect to SCM. (The default database is NI_Spec_Mgmt.)

    2. Object—Select Table.

    3. Selection—Select All objects.

    4. Fragmentation—Enter the following condition values:

      1. Optimize index only if Fragmentation > 10% and Page Count > 1000

  9. Define the parameters for the Update Statistics task.

    1. Databases—Select the database which you are using to connect to SCM. (The default database is NI_Spec_Mgmt.)

    2. Object—Select Tables and views.

    3. Select Update only column statistics.

    4. Scan type—Select Full scan.

  10. Click Next.

  11. Select the destination folder for the report file that is created by the on-demand maintenance task. You can use the same folder as scheduled maintenance task reports.

  12. Click Next.

    Note: Choose E-mail report if you have created an operator email. Refer to Creating an Operator for SQL Server Agent for more information.

  13. Click Finish.

  14. Verify that on-demand maintenance task setup is successful, then click Close.

6.5 Perform Routine Server Maintenance

NI recommends at least two periods of database server downtime per month for on-demand maintenance tasks. It is ideal to schedule downtime during holidays or other periods of time when the database server is not actively in use. Refer to Confirming Database User Activity for steps to confirm that the database is not in use.

Complete the following steps to perform routine server maintenance.

  1. Run all on-demand maintenance tasks to rebuild the index and update statistics.

  2. Restart the SQL Server service to clear space occupied by tempdb.

6.5.1 Confirming Database User Activity

Complete the following steps to determine if an SQL Server is actively in use.

  1. Connect to SSMS.

  2. Click New Query.

  3. Enter the following query and click Execute.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

If the query returns a single row in the Results tab, the SQL server is not in use. If the query returns multiple rows in the tab, the SQL server is currently in use. Execute this query multiple times before performing on-demand maintenance to validate that there really are no active database users.

Notice: Do not terminate ongoing database operations in order to perform on-demand maintenance tasks.

6.5.2 Running an On-Demand Maintenance Task

Complete the following steps to run an on-demand maintenance task.

  1. Expand the Management folder in the Object Explorer pane.

  2. Expand the Maintenance Plan folder.

  3. Right-click RebuildAndUpdateStatsMaintenance plan (the on-demand maintenance task which you created previously) and select Execute.

6.5.3 Restarting the SQL Server

You can use the Services window or SSMS to restart the SQL Server.

Restarting the SQL Server from the Service Window

  1. Open the Services window.

  2. Select the SQL Server you want to restart.

  3. Click Restart this service.

  4. Click Yes in the pop-up window that appears.

  5. Ensure that the SQL Server and the SQL Server Agent are running after the restart is complete.

6.5.3.1 Restarting the SQL Server from SSMS

  1. Launch SSMS and connect to the SQL Server.

  2. Right-click on the SQL Server and select Restart.

  3. Click Yes in the pop-up window, then click Yes again.

  4. Ensure that the SQL Server and the SQL Server Agent are running after the restart is complete.

6.6 Verify Disk Space Availability for Database Files Storage

NI recommends an allocation of 30% of database file size or 100GB (whichever is larger) as available disk space in the storage location for SQL Server files. Verify that the minimum required space is available at least once per day.

Tip: NI recommends creating an automated task to check disk space availability daily. Act immediately if available disk space becomes insufficient to ensure that issues do not occur.

6.6.1 Checking Disk Space Availability for Database File from SSMS

  1. Launch SSMS and connect to the SQL Server instance where the database is present.

  2. Right-click on the database in the Object Explorer pane and select Properties.

  3. Open the Files page of the Database Properties dialog box.

  4. Review the .mdf file row in the Database files table that shares the name of the database. (In the following example, NI_Spec_Mgmt.mdf). The file size is denoted in megabytes (MB)

6.7 Check Resource Usage by the SQL Server on a Virtual Machine (VM)

This step is required only if the SQL Server is running on virtual machine (VM). If this is not the case, you can skip this checklist item.

For reference: https://www.mssqltips.com/sqlservertip/4801/sql-server-does-not-use-all-assigned-cpus-on-vm/

Take care when using a VM for an SQL Server database. It is possible that the SQL Server database on the VM will not be able to use all available CPU cores and might experience performance degradation. Refer to the Compute Capacity Limits by Edition of SQL Server document on the Microsoft SQL Docs wiki for SQL Server edition capabilities.

Use the following query statement to check the available number of CPU cores and the number of CPU cores that the SQL Server is using. Refer to the Results tab to view the outcome of the query.

DECLARE @OnlineCpuCount int
DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',
CASE
WHEN @OnlineCpuCount \< @LogicalCpuCount
THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
ELSE 'You are using all CPUs assigned to O/S. GOOD!'
END as 'CPU Usage Desc'

Last update: October 10, 2022