Sql-server – SP_whoisactive capture on Alwayson Secondary Replica issue

availability-groupssql serversql-server-2016sql-server-agentssms

I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column.
This happens when I run the code below as a sql job. However, when I have my SSMS connection set to use APPLICATIONINTENT = READONLY and execute it manually it stores the execution plan for the queries running on the server.

How do we set APPLICATIONINTENT = READONLY for SQL Agent jobs running on secondary readonly replicas. Thanks!

EXEC dbo.sp_WhoIsActive
@get_plans = 1,
@destination_table = 'dbo.WIA_Output'

error message="The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

Best Answer

Since you're running sp_WhoIsActive, which looks at all running queries on the server, you can run it from any database. Just choose your database context as master or tempdb in your Agent job step.

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

There are several steps to configuring a server to accept ReadOnly traffic. The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER01' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON
N'COMPUTER02' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.

Sql-server – Change Data Capture for Asynchronous Secondary Replica

Change Tracking and Change Data Capture are, while having similar goals, actually separate features. For AlwaysOn Availability groups, the limitations indicate that you can't use the synchronous Change Tracking feature at all, though the asynchronous Change Data Capture is possible to enable: It's just not a feature Microsoft will support for Active Secondaries.

Generally, things like that aren't supported because there exists a supported way to do so. In this case, enabling CDC on the primary node of an AlwaysOn Availability Group is completely supported, allowing the captured changes to be replicated along to the Active Secondaries, so would likely be the path you should explore for your SSIS needs on the secondaries.

You can read more about the differences between Change Tracking and Change Data Capture here, and more about setting up CDC on the primary replica here.

Best Answer

Related Solutions

Sql-server – ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available

Sql-server – Change Data Capture for Asynchronous Secondary Replica

Related Question