Skip to main content

Implement well-architected database configurations in Workload Factory

Contributors netapp-rlithman
PDFs

Using configuration analysis insights and recommendations, leverage NetApp Workload Factory to implement best practices for your database configurations with Microsoft SQL Server and Oracle. You can easily review the well-architected status, learn about issues with database configurations, and fix configurations that aren't optimized for reliability, security, efficiency, performance, and cost.

You can also dismiss the analysis of specific configurations that don't apply to your environment to avoid unnecessary alerts and inaccurate optimization results.

Learn about the configuration analysis and well-architected status in Workload Factory.

About this task

Workload Factory analyzes database configurations daily. The daily analysis provides well-architected status, insights, and recommendations. You can automatically fix configuration issues to meet best practices.

You can review the recommendations for configuration issues and fix those issues from the Databases inventory within the Workload Factory console.

For SQL Server resources, you can perform a one-time assessment to review best practices for your database configurations. You don't need to register resources or store credentials to perform the one-time assessment.

What is analyzed

Workload Factory analyzes the well-architected status of the following configurations:

For Microsoft SQL Server instances:

  • Storage sizing: includes storage tier, file system headroom, log drive size, and TempDB drive size

  • Storage layout: includes user data files placement, log files placement, and TempDB placement

  • Storage configuration: includes capacity management, thin provisioning, tiering policy, snapshots, Microsoft Multipath I/O (MPIO) status, and MPIO timeout setting

  • Compute: includes rightsizing, operating system patches, and network adapter settings like Receive Side Scaling (RSS), TCP offloading, and MTU alignment

  • Applications: includes Microsoft SQL Server licenses, Microsoft SQL Server patch, and MAXDOP settings

  • Resiliency: includes local snapshots, FSx for ONTAP backups, cross-region replication (CRR), and Microsoft SQL High Availability.

  • Clones: includes options to refresh and delete clones (sandboxes) that were created in or outside of Workload Factory and are older than 60 days

For Oracle databases:

  • Storage sizing: includes swap space allocation and file system headroom

  • Storage configuration: includes capacity management, thin provisioning, tiering policy, snapshots, storage efficiencies, and operating system configurations for deployments using NFS or iSCSI with or without Automatic Storage Management (ASM) including Microsoft Multipath I/O (MPIO) status and settings, and the following dNFS settings: dNFS enablement, dNFS consistent IP resolution, dNFS configuration file, and dNFS nosharecache

  • Storage layout: includes redo log placement, temp tablespace placement, data files placement, archive log placement, control files placement and binaries placement, ASM disk groups LUN count

  • Compute: includes operating system patches

The well-architected analysis for Oracle notifies you when a configuration depends on others and lists actions to optimize linked configurations.

Perform a one-time assessment of SQL Server database configurations

Perform a one-time assessment to find out the well-architected status of your Microsoft SQL Server instances without storing credentials or registering instances.

You can run the script and upload results as many times as you want. Each time you upload, the well-architected status and insights are updated based on the latest results.

Steps

  1. Log in using one of the console experiences.

  2. Select the menu The hamburger menu icon is used to navigate to workloads like storage Then select Databases.

  3. In the Inventory, select One-time assessment > Learn about assessment. Review the information about the one-time assessment.

  4. Select Download script.

    1. Copy the script to your Microsoft SQL Server host.

    2. Run the script in PowerShell. One script collects metadata from all instances.

  5. Select One-time assessment > Upload. Find the file and upload it to the Workload Factory console.

  6. View the recommendation for any configuration. Recommendations include best practices and problems you might have if configurations are not optimized.

Before you begin fixing, dismissing, and re-activating configurations

  • You must grant operations and remediation permissions in your AWS account.

  • The resource must be registered in Workload Factory and the storage type must be FSx for ONTAP. Learn how to register resources.

  • Review each recommendation before fixing a setting or configuration. For RSS and MAXDOP, test recommended settings to check performance before changing your production environment.

Note Fixing a configuration may cause instance downtimes or service interruptions. Make sure you review the recommendation carefully before you choose to fix a configuration.

Fix a configuration issue

Fix configuration issues for SQL Server or Oracle environments running on FSx for ONTAP storage.

Note The remediation process may cause instance downtimes or service interruptions. Make sure you review the recommendation carefully before you choose to fix a configuration issue.
Steps

  1. Log in using one of the console experiences.

  2. Select the menu The hamburger menu icon is used to navigate to workloads like storage Then select Databases.

  3. In the Inventory, select the engine type: Microsoft SQL Server or Oracle.

  4. Select resources to fix for specific configurations.

    • If you selected Microsoft SQL Server, select the Instances tab.

    • If you selected Oracle, select the Databases tab.

  5. Select View and fix to view configuration issues for the instance.

  6. On the Well-architected status page for the instance, review the findings from the analysis.

    You can filter configuration issues by categories, subcategories, status, severity, and tags.

    Select Export PDF to download a report of the findings.

  7. Select the dropdown arrow to view the recommendation for any configuration. Recommendations include best practices, potential pitfalls of unoptimized configurations, and important considerations. Make sure you review the recommendation carefully.

  8. Select to View and fix configuration issues when the option is available.

    Select all is the default, but you can select specific resources to fix.

    1. For all configurations except clone cleanup, review the recommendation details to learn what will happen if you choose to fix the issue. Some remediation operations may cause instance downtimes or service interruptions.

    2. For clone cleanup, select cloned databases (sandboxes) to refresh or delete.

      • Refreshing a clone synchronizes it with its source database. Refresh is available only for clones created in Workload Factory.

      • Deleting a clone removes it permanently, frees up storage space, and reduces costs. You can delete clones created in and outside of Workload Factory.

  9. Select Continue to fix the configuration issue.

Result

Workload Factory begins fixing the issue(s). Select the Job monitoring tab to view the status of the operation.

Dismiss the analysis of database configurations

Dismiss the analysis of specific database configurations that don't apply to your database environment to avoid unnecessary alerts and inaccurate optimization results. You can reactivate a dismissed configuration analysis at any time.

Steps

  1. Log in using one of the console experiences.

  2. Select the menu The hamburger menu icon is used to navigate to workloads like storage Then select Databases.

  3. From the Databases menu, select Inventory.

  4. In the Inventory, select the database engine type: Microsoft SQL Server or Oracle.

  5. Navigate to the SQL Server instance or Oracle database with the configuration to postpone, select the action menu, and then select Well-architected.

  6. On the Well-architected analysis page, scroll down to the configuration to postpone, select the action menu, and then select Dismiss.

  7. In the Dismiss configuration dialog, select the Dismiss option and then select Dismiss to confirm dismissal.

Result

The configuration analysis stops.

You can reactivate the analysis at any time. The configuration is no longer included in the total optimization score.

Reactivate a dismissed configuration analysis

Reactivate a dismissed configuration analysis at any time. You can select one or more configurations to reactivate.

Steps

  1. Log in using one of the console experiences.

  2. Select the menu The hamburger menu icon is used to navigate to workloads like storage Then select Databases.

  3. From the Databases menu, select Inventory.

  4. In the Inventory, select the database engine type: Microsoft SQL Server or Oracle.

  5. Navigate to the SQL Server instance or Oracle database with the configuration to postpone, select the action menu, and then select Well-architected.

  6. On the Well-architected analysis page, select Dismissed configuration to display only dismissed configurations.

  7. Select Reactivate to restart the configuration analysis for the postponed or dismissed configuration.

Result

The configuration analysis is reactivated and runs every day from now on.