Not Defined as Subscriber Error

In SQL 2019, if you create a transnational push subscription to a database that participates in an Availability Group, using the subscriber name format <AGListener>, <port number> then it will fail with the error:

Msg 20032, Level 16, State 1, Procedure distribution.dbo.sp_MSadd_subscription, Line 175 [Batch Start Line 0]
'AGLISTENER,54321' is not defined as a Subscriber for 'SERVER1\INSTANCE1'.
Msg 14070, Level 16, State 1, Procedure sys.sp_MSrepl_changesubstatus, Line 1249 [Batch Start Line 0]
Could not update the distribution database subscription table. The subscription status could not be changed.
Msg 14057, Level 16, State 1, Procedure sys.sp_MSrepl_addsubscription_article, Line 384 [Batch Start Line 0]
The subscription could not be created.
Msg 20021, Level 16, State 1, Procedure sys.sp_MSrepl_addpushsubscription_agent, Line 258 [Batch Start Line 0]
The subscription could not be found.

Similar error will happen if you upgrade the remote distributor to SQL 2019. It is a bug fixed in CU5 released last week.

Assuming you have a replication environment like this

• Publisher: SERVER1/INSTANCE1
• Distributor: SERVER2/INSTANCE2
• Subscriber: AGLISTENER,54321

The script to reproduce the issue is:

-- Publisher
create database pub_test
go

use pub_test
create table test(a int primary key, b varchar(255))
go
exec sp_replicationdboption @dbname = N'pub_test', @optname = N'publish', @value = N'true'
exec sp_addpublication @publication = N'test', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true'
exec sp_addpublication_snapshot @publication = N'test'
exec sp_addarticle @publication = N'test', @article = N'test', @source_owner = N'dbo', @source_object = N'test'
go

-- Susbcriber
create database sub_test
go

-- Publisher

exec sp_addsubscription @publication = N'test', @subscriber = N'AGLISTENER,54321', @destination_db = N'sub_test', @subscription_type = N'Push'
exec sp_addpushsubscription_agent @publication = N'test', @subscriber = N'AGLISTENER,54321', @subscriber_db = N'sub_test'

Listing Azure Databases

If you are a data professional and need to list all the databases in your organization, across all subscriptions and resource providers, then you can easily accomplish this task by using Azure Resource Graph. We will start with writing the Kusto query in the Azure portal and later we will use PowerShell to execute the query programatically.

Azure Resource Graph Explorer

The easiest way to write a Kusto query is using the explorer. In the Azure portal search box, type Resource Graph Explorer and click on it. On the left pane search for sql, and you will see all the resource types that contain sql in the name. Click on resources, then click on the resource type microsoft.sql/servers/databases, both will show in the query editor in the right like this:

resources
 | where type == "microsoft.sql/servers/databases"

Next click on Run query and that's it, you wrote your first Kusto query!

In this example you will notice that master shows as a third database, you can filter it out by adding another where operator:

 | where name != "master"

It is a good practice to only show the columns needed (less data travel over the wire), you can select the columns with the project operator:

 | project type, name, kind, subscriptionId

Since the query will search in all subscriptions, you can create another query to find the subscriptions in your organizations using resourcecontainers of type microsoft.resources/subscriptions, and you will use the project operator to select what is needed such as subscriptionId and susbcriptionName:

 resourcecontainers
 | where type == "microsoft.resources/subscriptions"
 | project subscriptionId, subscriptionName = name

Now you can join both queries using the join operator with the column subscriptionId, and selecting again only the columns needed with the project operator:

resources
 | where type == "microsoft.sql/servers/databases"
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName

Azure PowerShell

Now that you have the Kusto query under your belt, you can call it from a PowerShell script using the cmdlet Search-AzGraph. But first you will need the Az modules, then connect to Azure:

Connect-AzAccount

Next you can run the same query from above. In addition, you can add other database resource types (MySQL and PostgreSQL) using the in operator

$query = '
resources
 | where type in (
    "microsoft.sql/servers/databases"
    ,"microsoft.sqlvirtualmachine/sqlvirtualmachines"
    ,"microsoft.sql/managedinstances"
    ,"microsoft.dbforpostgresql/servers"
    ,"microsoft.dbformysql/servers"
   )
 | where name != "master"
 | project type, name, kind, subscriptionId
 | join kind = inner (
   resourcecontainers
   | where type == "microsoft.resources/subscriptions"
   | project subscriptionId, subscriptionName = name
   ) on subscriptionId
 | project type, name, kind, subscriptionName
'
Search-AzGraph -Query $query

This was just a peek of the capabilities that Azure Resource Graph provides and hopefully it sparked your interest to learn more about it.