We have a read-only reporting server that is part of the AlwaysOn availability group. Our developer wants to create some stored procedures. My question is where should the stored procedure be created–will that be on the primary since the secondary is read only ?
SQL Server – Stored Procedure on Read-Only AlwaysOn Availability Group Replica
availability-groupshadrsql serversql server 2014stored-procedures
Related Question
- Sql-server – Read only replica part of always on availability group for Reporting
- SQL Server 2014 Listener Port 1433 for Always On Availability Group
- SQL Server Read-Only Replica – Does Read-Only Activity on Synchronous Secondary Replica Slow Down Primary Replica?
- SQL Server Availability Groups – Removing Database from AlwaysOn
- Sql-server – SELECT query in a SP in AlwaysOn Availability Group Read Only Routing
Best Answer
Stored procedures are contained in databases, so if the database you are creating the stored procedure on is in the availability group, you will create it on the primary--it would be impossible to create it on the secondary since the replica there is read-only.
Once the stored procedure is created on the primary, it is then part of the database and available on the secondary replicas.
The same principles apply when making any modifications to a database in an availability group. You can't add tables, views, stored procedures, modify permissions, etc. on the secondary because the database there is read-only.