0

Is it good practice to keep all stored procedures in separate database which has no data and all tables with real data in separate database(s)? Main reason: to be able to upgrade stored procedures with simple restore which does not affect the production data. Second (optional) reason: 2+ databases can be processed by the same set of stored procedures.

closed as primarily opinion-based by mustaccio, SqlWorldWide, Tom V - Team Monica, Erik Darling, RDFozz Feb 8 '18 at 16:15

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Re reason #1, I don't think restore is a good practice for deployments. Consider DACPACs. – Dan Guzman Feb 8 '18 at 13:57
  • 1
    Don't downvote the guy just because you don't like what he is asking. Just tell him why its a bad idea people! – Anthony Genovese Feb 8 '18 at 15:06
  • 2
    As well as other objections you would lose ownership chaining and have to deal with cross database security. – Martin Smith Feb 8 '18 at 15:12
  • I've done something similar in a weird situation, only with ad-hoc reporting views that referenced a secondary replica of an AG database. They were a mess and I didn't want them in the production database, so it worked okay. Definitely not best, or even good, practice and it doesn't sound like you have a strong use case for it. – LowlyDBA Feb 8 '18 at 15:29
  • 1
    @i486 it sounds like you're asking permission to do something that is a bad practice and do not like the answers provided, warning you not to hurt yourself. You won't find permission here, we are just a giant group of professionals that work in this space daily screaming at the guy on the ledge not to jump. This is horrible from a management stand point, i.e. youre hurting the company you let implement this, and it will hurt you when you have to fix it. There are more alternatives, list out the larger goal you desire, or restrictions that you are concerned with. – Random_User Feb 8 '18 at 15:46
2

I have never seen that approach used. If you are keeping your database schema in a source control system, then you can generate deployment scripts. Such tools can compare previous versions of your schema and deploy the changes. SQL Server Data Tools is available in Visual Studio.

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