Access primary database instance from read replica instance

Mathankumar Rajendran 20 Reputation points
2023-01-27T12:04:45.9166667+00:00

Since we are starting to use read replica in our projects, we are going to migrate the our data retrieval procedures to read only database. But, we are doing the performance track inside that procedures that we are moving to read only DB and it is inserting a row in table for every execution of the stored procedures. Now, Our question is whether it is possible to make entry to the database from read replica instances or can we access the primary instance's database table in read replica instance & insert a data as per our requirements?

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 32,886 Reputation points MVP
    2023-01-27T13:48:34.0666667+00:00

    To my knowledge what you are trying to accomplish is not possible, Instead of trying to create your own performance tracking solution for read-only replica, please consider using Dynamic Management Views (DMVs)available to monitor query performance. Take a look at those DMVs on this documentation.

    0 comments
  2. Erland Sommarskog 101K Reputation points MVP
    2023-01-27T23:02:20.5533333+00:00

    It's unclear what you mean with "read replica". I would take it to mean a readonly secondary in an availability group, but you have included Azure SQL Database in your tags, and Availability Groups is nothing you have in Azure SQL Database.

    For the rest of the post, I will assume that you have an AG.

    In theory you could make a call over a linked server to write to the primary replica. but don't do that. It will definitely add overhead, and you don't want your performance monitoring be your bottleneck. A somewhat better idea would be to write to a database outside the AG, if that is possible.

    Yet an idea is to use Trace with user-define events which you can fire with sp_trace_generateevent.

  3. Seeya Xi-MSFT 16,436 Reputation points
    2023-01-30T05:38:45.02+00:00

    Hi Mathankumar Rajendran,

    Please use this DMVs to monitor AG:

    select * from sys.dm_hadr_cluster

    select * from sys.dm_hadr_cluster_members

    select * from sys.dm_hadr_cluster_networks

    select * from sys.availability_groups

    select * from sys.availability_groups_cluster

    select * from sys.dm_hadr_availability_group_states

    select * from sys.availability_replicas

    select * from sys.dm_hadr_availability_replica_cluster_nodes

    select * from sys.dm_hadr_availability_replica_cluster_states

    select * from sys.dm_hadr_availability_replica_states

    select * from sys.dm_hadr_auto_page_repair

    select * from sys.dm_hadr_database_replica_states

    select * from sys.dm_hadr_database_replica_cluster_states

    select * from sys.availability_group_listener_ip_addresses

    select * from sys.availability_group_listeners

    select * from sys.dm_tcp_listener_states

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments