Get Started with the SQL Server On-Demand Assessment - Microsoft Engage Center

The SQL Server assessment allows you to diagnose potential issues with your SQL Server environment running on-premises, on Microsoft Azure Virtual Machines (VMs), or on Amazon Web Services (AWS) VMs. Assess a single server, multiple servers, or failover cluster running SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017 or SQL Server 2019 instances, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019 failover cluster, or standalone server installations are supported.

The recommendations cover areas such as change control, monitoring, disaster recovery, service-level agreements, configuration items, and the proper function of the major components of the SQL environment.

This assessment is designed to provide you specific guidance grouped in Focus Areas to mitigate risks to your SQL Servers and your organization.

The SQL Server Assessment focuses on several key pillars, including:

  • SQL Server configuration
  • Database design
  • Security
  • Performance
  • Always On
  • Cluster
  • Upgrade readiness
  • Error log analysis
  • Operational Excellence

Run the SQL Server Assessment

Prerequisites

To take full advantage of the On-Demand Assessments available through Microsoft Engage Center, you must:

  1. Have an active Azure Subscription with deployed Engage Center Connector, Log Analytics workspace, collector server , plus the SQL Server assessment extension added. For more information, see Get started with On-Demand Assessments.

  2. A domain account (User or Managed Service Account) with the following rights:

    • Member of the local Administrators group on all servers in the environment
    • SysAdmin role on all Microsoft SQL Servers in the environment.

  3. Review the Pre-Requisites document for the SQL Server Assessment. This document explains the detailed technical documentation of the SQL Server Assessment and the server preparation needed to run the assessment. It also documents the various types of data collected by the assessment.

Note

On average, it takes an hour to initially configure your environment to run an On-Demand Assessment. After you run an assessment, you can review the data in Azure Log Analytics. This will provide you with a prioritized list of recommendations, categorized across six focus areas. This allows you and your team to quickly understand risk levels, the health of your environments, act to decrease risk, and improve your overall IT health.

Setup the SQL Server Assessment

Note

You will need to setup your Engage Center Connector first to configure On-Demand Assessments. Follow these directions to setup your Engage Center Connector, connect to a Log Analytics workspace and then add the On-Demand Assessments that you want to configure.

  1. On the data collection machine create the following folder: C:\LogAnalytics\SQLServer (or any other folder besides C:\ODA which is reserved by the system).

  2. If you are using a User Domain Account Open regular PowerShell (not ISE) in Administrator mode and run the following cmdlet:

    Add-SQLAssessmentTask -SQLServerName <YourServerName> –WorkingDirectory <Directory>-Provide the required user account credentials that satisfy the requirements mentioned in this article earlier

    where YourServerName is the fully qualified domain name (FQDN) or the NetBIOS name of single server or failover cluster running SQL Server environment.

    <WorkspaceId> – provide id for the Log Analytics workspace that will be used to store the uploaded data

    If more than one environment is assessed, “;” is used between the environments. For failover cluster, check failover cluster virtual network name. Directory is the path to an existing directory used to store the files created while collecting and analyzing the data from the environment(s). Provide the required user account credentials that satisfy the requirements mentioned in this article earlier.

  3. If you are using a Group Managed Service Account Open regular PowerShell (not ISE) in Administrator mode and run the following cmdlet:

    Add-SQLAssessmentTask -SQLServerName <YourServerName> –WorkingDirectory <Directory> -RunWithManagedServiceAccount $True

    where YourServerName is the fully qualified domain name (FQDN) or the NetBIOS name of single server or failover cluster running SQL Server environment. When prompted for password just press enter, as you are using a gMSA you don't need to provide a password as it is handled by the system

    <WorkspaceId> – provide id for the Log Analytics workspace that will be used to store the uploaded data

    If more than one environment is assessed, “;” is used between the environments. For failover cluster, check failover cluster virtual network name. Directory is the path to an existing directory used to store the files created while collecting and analyzing the data from the environment(s). Provide the required user account credentials that satisfy the requirements mentioned in this article earlier.

  4. Data collection is triggered by the scheduled task named SQLAssessment within an hour of running the previous script and then every 7 days. The task can be modified to run on a different date/time or even forced to run immediately from the task scheduler library > Microsoft > Operations Management Suite > AOI*** > Assessments > SQLAssessment.

  5. During collection and analysis, data is temporarily stored under the Working Directory folder that was configured during setup.

  6. After a few hours, your assessment results will be available on your Log Analytics and Microsoft Engage Center Dashboard. You see the results by going into Microsoft Engage Center > IT Health > On-Demand Assessments and then selecting View all recommendations against the active assessment.

  7. If you wish to get a Microsoft Accredited Engineer to go over the issues about your SQL Server environment with you, you can contact your Microsoft Representative and ask them about the Remote or Onsite CSA Led Delivery.

Agreement Remote Engineer Onsite Engineer
Premier SQL Remote Datasheet SQL Onsite Datasheet
Unified SQL Remote Datasheet SQL Onsite Datasheet
  • Last updated on