Register an Azure SQL Database

See the following sections to register an individual Azure SQL Database for monitoring with DPA.

Options for creating the monitoring user
Prepare to authenticate with a Microsoft Entra ID password
Prepare to authenticate with a service principal
Complete the Register Instance Wizard
Enable deadlocks for read-only geo-replicas

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.

If you register a database instance within the 14-day trial period, DPA begins monitoring the instance immediately. After the trial period, you must activate a license to monitor the database instance.

Options for creating the monitoring user

DPA requires an account with certain privileges to gather information from the database instance. This account is called the monitoring user. When you identify and authenticate the monitoring user in the registration wizard, you have the following options:

Allow DPA to create the monitoring user or configure an existing user to give it the required privileges.

If you let DPA create the monitoring user, you must enter the credentials of a privileged user. These credentials are not saved after the registration is complete.
Create the monitoring user manually by running a script.
If you don't want to create, manage, and update user credentials, you can use a Microsoft Entra service principal (previously Azure service principle). See the prerequisites below.

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:

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

You can use an existing app if you already have one.
Use the SQL Server Management Studio (SSMS) to connect to the Azure SQL Database at the database 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;
```

Complete the Register Instance Wizard

In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.
Under Microsoft Azure, select Azure SQL Database. Then click Next.

The Enter Monitored Database Instance Connection Information panel opens.
Enter the logical server name, port, and database name.

You cannot enter an IP address in the Server Name field.
Create or specify the monitoring user account. (See the options for the monitored user account above.) Then click Next.

To register a read-only geo-replica, you must create a monitoring account through the primary server first.
- To let DPA create or configure the monitoring user:
  1. Select Let DPA create a new contained user.
  2. In the Username and Password fields, enter the credentials of a privileged user account. This account must be assigned the db_owner role.
  3. Click Next.
    
    DPA verifies the connection information and the credentials.
  4. Enter the credentials of the monitoring user. This can be either:
    - An account that DPA creates.
    - An existing account that DPA configures to assign the required privileges.
  5. Click Next.
- To create the monitoring user yourself:
  1. Select I'll create the contained user or login.
  2. Under Authentication Method, select either SQL User or Microsoft Entra Password (formerly Azure Active Directory password) as the type of authentication to use for the monitoring user account.
  3. Click Monitoring User Creation Script.
  4. Copy the script to a file and edit it per the instructions.
  5. Run the edited SQL statements on the Azure SQL database.
    
    The monitoring user is created.
  6. Enter this user's credentials in the Username and Password fields.
  7. Click Next.
- To specify a service principal:
  1. Select I'll create the contained user or login.
  2. Under Authentication Method, select Microsoft Entra Service Principal.
    
    Do not specify the authentication method in the Connection Properties field.
  3. In the Username field, enter the service principal application ID.
    
    You must enter the application ID, not the display name or object ID.
  4. In the Password field, enter the value of the service principal secret.
If your repository database is Oracle, the Oracle Repository Tablespace panel opens. If not, continue with step 6.
If your repository database is Oracle, choose the tablespace in the repository database to store DPA performance data for this monitored instance. Then click Next.

By default, the performance data is stored in the default tablespace of the repository user. However, data for monitored instances can be stored in separate tablespaces.

If your repository database is not Oracle, the wizard skips this step.
(Optional) Select the alert groups you want the new database instance to join. Then click Next.
- If no alert groups exist, or the existing groups do not match the database type of this instance, the wizard skips this step.
- Group membership can be changed after registration
Review the information, and click Register Database Instance.
When the registration is complete, click Finish to return to the DPA home page.

Enable deadlocks for read-only geo-replicas

To enable the deadlock feature for read-only geo-replica Azure SQL Databases, you must create and enable an Extended Event Session (EES).

If you registered the primary server first, an EES is already created and synced. Skip to step 2.

Otherwise, connect to the primary server first to create an EES.

Run the following SQL statement:

CREATE EVENT SESSION [dpa_deadlock_capture] ON DATABASE
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.ring_buffer(SET max_events_limit=(1000), 
  max_memory=(256))
WITH (MAX_MEMORY = 256KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = ON);
-- ALTER EVENT SESSION [dpa_deadlock_capture] ON DATABASE STATE = START;

Connect to the read-only replica database.
Click Extended Events > Sessions.
Enable the dpa_deadlock_capture session.

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.