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 ?
asked Feb 28, 2019 at 16:19
Add a comment
|
2 Answers
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.
answered Feb 28, 2019 at 16:30
-
Thank you for clearing the confusion. As a DBA I dont want my developer to have access to the Primary Replica, instead of me deploying the Stored procedure for them is there any workaround ? Feb 28, 2019 at 16:32
-
2Typically the developer writes the code, tests it in a development environment, and then provides the script for the DBA to run in production. Either that or install code management software that manages approvals and deployments ($$$). Feb 28, 2019 at 16:35
A possible workaround would be; Just create a database in the secondary instance and then create the stored procedures accessing the data from the databases being replicated.
EDIT: But this database wouldn't be replicated.
answered Feb 28, 2019 at 16:36
