Database Management

Create the DPA Monitoring Login for SQL Server AG Listener Registrations

If you are monitoring a SQL Server availability group (AG), you must manually create SQL Server logins on secondary replicas when the following conditions are met: 1) You specified an AG listener during DPA registration, and 2) The DPA monitoring login does not exist on all replicas. In most cases, the monitoring login will not exist if DPA created the monitoring user during registration. If you used a monitoring login that already exists on all replicas, these instructions do not apply.

First published date

10/30/2018 2:45 PM

Last published date

10/30/2018 2:45 PM

Overview

If you are monitoring a SQL Server availability group (AG), you must manually create SQL Server logins on secondary replicas when both of the following conditions are met:

  1. You specified an AG listener during DPA registration, and
  2. The DPA monitoring login does not exist on all replicas.

In most cases, the monitoring login will not exist on the replicas if you chose to have DPA create the monitoring user during registration. If you are using a monitoring login that you know already exists on all replicas, these instructions do not apply.

For information about the options for monitoring SQL Server AGs, see Registration and licensing options for clustered environments.

Background 

SQL Server logins are stored on the [master] database, and therefore logins created on the primary replica are not automatically propagated to secondary replicas. Database users, however, are replicated to secondary replicas because replication occurs at the database level.  

This is important when you have registered using an AG listener because the DPA monitoring login needs to continue to work when a primary failover occurs. The login is created on the primary replica during registration. However, because the login is not replicated to other AG replicas, the login will not exist when the primary fails over, and DPA will not be able to monitor. Therefore, you must manually create the login on each secondary replica that the listener's AG can fail over to.

Product section

Database Performance Analyzer

Resolution

Get the list of AG replicas

To display a list of secondary replicas that the AG could fail over to:

  1. In DPA, select a SQL Server instance from the DPA home page.
  2. Click on the AG Status tab in the upper-right corner.
  3. Click on the listener's AG (the one in bold).
  4. The Replicas section lists the replicas that need the DPA monitoring login and permissions.

Create the monitoring login

Create the login on each secondary replica using the same credentials as the monitoring login on the primary replica.

  1. On each secondary replica, log in as a SYSADMIN user.
  2. Run the following commands. Substitute the correct monitoring login and password.
USE [master]
GO

-- Create DPA monitoring login on each Secondary replica.
CREATE LOGIN [<DPA monitoring login>] WITH
  PASSWORD=N'<DPA monitoring login password>',
  DEFAULT_DATABASE=[master],
  DEFAULT_LANGUAGE=[us_english],
  CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Give rights to monitor the replica.
ALTER SERVER ROLE [sysadmin] ADD MEMBER [<DPA monitoring login>]
GO

-- Scripts are not supported under any SolarWinds support program or service.
-- 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.