Register an Azure SQL Database

Complete the following steps to register an individual Azure SQL Database for monitoring with DPA.

To register multiple Azure SQL databases using the Mass Registration feature, follow the instructions in this KB article.You can also register databases 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.

1. In the upper-left corner of the DPA home page, click Register DB Instance for Monitoring.

2. Under Microsoft Azure, select Azure SQL Database. Then click Next.

   The Enter Monitored Database Instance Connection Information panel opens.

3. Enter the logical server name, port, and database name.

   You cannot enter an IP address in the Server Name field.

4. Create or specify the account that DPA will use to gather information (the monitoring user).

   DPA gathers information through this user from the monitored database. You can create a new monitoring user or use an existing user, such as for read-only replica databases.

   To register a read-only geo-replica, you must create a monitoring account through the primary server first.

   You can create the monitoring user account yourself using the script provided, or you can let DPA create or configure the monitoring user.

   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.

   • 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 configured 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. Click Monitoring User Creation Script.

     3. Copy the script to a file and edit it per the instructions.

     4. Run the edited SQL statements on the Azure SQL database.

        The monitoring user is created.

     5. Enter this user's credentials in the Username and Password fields.

     6. Click Next.

   If your repository database is Oracle, the Oracle Repository Tablespace panel opens. If not, continue with step 6.

5. 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.

6. (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

7. Review the information and click Register Database Instance.

8. 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.

1. 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;

2. Connect to the read-only replica database.
3. Click Extended Events > Sessions.
4. 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.