1

I have a couple of stored procedures that will be used only for maintenance tasks (facilitating truncation of tables, rebuilding indexes etc). In my mind the natural location for these would be the master DB (or any other central DB) so that other DBs would be able share this functionality.

This would also clarify the intent of these procedure and excludes these from DACPAC deployments. However, in Azure SQL cross-database queries are not allowed so I do not find any natural place to put these procedures inside.

Where would you put these kinds of stored procedures that are outside the actual business needs?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
FinneVirta
  • 374
  • 1
  • 4
  • 14
  • 2
    Given the constraints of Azure SQL Databases, I'd put them in a separate schema of the DB. You can then use the schema to set appropriate permissions to stop users accidentally rebuilding your core indexes in the middle of the day. – TZHX Sep 22 '22 at 10:02
  • 1
    Don't exclude them from dacpac deployments. Instead you can create an SSDT project with these objects and have your other projects reference that as "same database same server" to include these objects. The core project could define its own database schema and put all its objects in that dedicated schema too – Martin Smith Sep 22 '22 at 10:15
  • @MartinSmith What do you mean with having the projects referencing it as "same database same server"? Do you mean that I would have a CoreSSDT that holds the [admin]-schemas with Database1SSDT & Database2SSDT somehow referencing the CoreSSDT? – FinneVirta Sep 22 '22 at 11:57
  • 1
    yes. You add a "Database Reference" - either to another SSDT project in the same solution or to a dacpac of CoreSSDT – Martin Smith Sep 22 '22 at 15:00
  • @MartinSmith Cool, did not know of this feature, thanks for the tip. For anyone looking at this, here is a [MSFT link on this](https://learn.microsoft.com/en-us/sql/ssdt/add-database-reference-dialog-box?view=sql-server-ver16#to-create-a-reference-to-a-database-on-the-same-server) – FinneVirta Sep 23 '22 at 06:58

1 Answers1

2

You can always create a schema, e.g. called something like admin and put them in there.

See Create a Database Schema on Microsoft docs for more details.

Then you can execute them from within the database itself, and a schema is also a great mechanism to regulate permissions - giving execute permissions on these stored procedures only to your DBA staff and now users of the regular database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Alright, that sounds fair. But is there no way to avoid having to duplicate the same procedures in several databases even if they exist on the same server? – FinneVirta Sep 22 '22 at 11:52
  • @FinneVirta: as you said yourself - cross-database queries are not allowed in Azure SQL - so no, you cannot have them in a central database - you need to put them into each and every database where you want to use them. – marc_s Sep 22 '22 at 11:53
  • 1
    Was hoping that there was another way for this. But I'll mark this as the answer as it the best alternative considering the limitations of Azure SQL. – FinneVirta Sep 22 '22 at 11:58