Documentation forDatabase Performance Analyzer

Register an Azure SQL Managed Instance

Complete the following tasks to register a single Azure SQL Managed Instance (ASMI) for DPA to monitor.

You can also use mass registration to registrations to register multiple database instances, or you can register database instances using scripts that call the DPA API.

For DPA to collect metrics from a monitored SQL Server instance, Azure SQL instance, or ASMI, the SQL option NUMERIC_ROUNDABOUT must be set to OFF.

Registering an ASMI is slightly different than registering other types of monitored database instances:

  • You cannot use the wizard to create the DPA monitoring user. Do one of the following:

  • If the DPA repository is an Oracle database, DPA stores performance data for monitored ASMIs in the default tablespace of the repository user. You cannot change the default tablespace in the Register Instance Wizard. If you need to change the default tablespace, register the instance using mass registration.

Create the monitoring user

If you are not using a Microsoft Entra service principal as the DPA monitoring user, you must create a user account to serve as the DPA monitoring user. DPA uses this account to register and monitor the instance.

DPA ignores data on the monitored database instance from the monitoring user. Make sure the monitoring user will not cause load on the monitored instance.

The monitoring user account must have the SYSADMIN role during registration. After you have registered the ASMI, you can (optionally) remove the SYSADMIN role from the DPA monitoring user. If you remove the SYSADMIN role, DPA functionality is limited. For example, the SQL Server Log Has Many Virtual Logs alert does not work.

  1. Connect as a SYSADMIN user to the ASMI database instance you want to monitor.

  2. Run the following SQL statements against the database you are registering. Replace username and password with the credentials for the user account. Replace the default database and language values if needed.

    --Create DPA login

CREATE LOGIN username WITH
  PASSWORD=N'password',
  DEFAULT_DATABASE=master,
  DEFAULT_LANGUAGE=us_english,
  CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

--Give SYSADMIN rights to the DPA user
EXEC sys.sp_addsrvrolemember @loginame = N'username', @rolename = N'sysadmin'
GO

Prepare to authenticate with a Microsoft Entra ID password

If you plan to use a Microsoft Entra ID (formerly Azure AD) password to authenticate the DPA monitoring user, follow the instruction in the support article Use Microsoft Entra ID (formerly Azure AD) authentication in DPA.

Prepare to authenticate with a service principal

If you plan to use a service principal to authenticate the DPA monitoring user, complete the following steps:

  1. In the Azure portal (https://portal.zaure.com) under Azure services, click App registrations. Then register an app.

    You can use an existing app if you already have one.

  2. Use the SQL Server Management Studio (SSMS) to connect to the ASMI database instance at the server level, and run the following SQL statements to create a user and give it the db_owner role:

    CREATE USER appName FROM EXTERNAL PROVIDER;
ALTER ROLE db_owner ADD member appName;

  3. Run the following SQL statements to grant the sysadmin role. 

    CREATE LOGIN appName FROM EXTERNAL PROVIDER WITH
  DEFAULT_DATABASE=master, 
  DEFAULT_LANGUAGE=us_english
ALTER SERVER ROLE sysadmin ADD MEMBER appName

Complete the Register Instance Wizard

  1. On the DPA home page, click Register DB Instance for Monitoring.

  2. Under Microsoft Azure, select Azure SQL Managed Instance. Then click Next.

  3. On the Enter Monitored Database Instance Connection Information panel, enter the server name or IP address of the ASMI and the port number.

  4. Under Authentication method, select the type of authentication to use for the monitoring user account:

    • SQL User
    • Microsoft Entra Password (formerly Azure Active Directory password)
    • Microsoft Entra Service Principal (formerly Azure service principal)

    Do not specify the authentication method in the Connection Properties field.

  5. Specify the monitoring user credentials, and then click Next:

    • To specify an account, enter the user name and password for the monitoring user account that you created previously.

    • To specify a service principal:

      1. In the Username field, enter the service principal application ID.

        You must enter the application ID, not the display name or object ID.

      2. In the Password field, enter the value of the service principal secret.

  6. Specify the following Instance options.

    The instance name and group membership can be changed after registration.

    1. Enter the name that DPA will display to identify this database instance.

      The Display name field defaults to the ASMI name retrieved from the instance.

    2. (Optional) If you have manually created instance groups, you can assign this database instance to one of the groups.

      If no manual groups exist, this option is not shown.

    3. (Optional) If you have existing alert groups, you can assign this database instance to one or more groups.

      If no alert groups exist, or the existing groups do not match this instance's database type, this option is not shown.

    4. Click Next.

  7. Review the information on the Summary page. Click Back if you need to make changes. When the information is correct, click Register.

The scripts are not supported under any SolarWinds support program or service. The scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.