1

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 ?

1

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.

  • 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 ? – SQL_NoExpert Feb 28 at 16:32
  • 2
    Typically 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 ($$$). – Tony Hinkle Feb 28 at 16:35
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.