How often are DB names ever referenced outside of connection strings?

  • Over the years here we’ve moved to using a separate instance for dev, qa, lab, prod etc, keeping DB names same so that code (including SQL scripts, which generally begin with a USE statement) migrates from one instance to the next with no code change; the only things that change are instance name and login.  This has worked great, and it's always slightly disturbing to inherit an environment where the DB names are appended with an environment tag, usually requiring us to manually edit SQL scripts whenever we need to manually migrate them to different environments.

    With Az SQL DB, and especially elastic pools, I think there is pressure to host multiple envs on a single “server”, thus requiring such tagging of our DB names (e.g. Commerce_Dev, Commerce_QA).  Seems like a step backwards.  But since there is no USE statement, and not much real reaching out of an Az SQL DB into other Az SQL DBs, maybe this is a moot point?  If DB names are not referenced anywhere except in connection strings, I think it is.

    My question is, in all the code you’ve written against Az SQL DB, do you ever reference DB name anywhere except in connection strings?  Do you think the argument against tagging DB names with environment indicators still holds in Az SQL DB?

    I'm curious to see if any of you have strong opinions one way or the other.

  • I think the USE statement gets us into trouble. It can be helpful to ensure you are in the right db, but I think when running scripts and making changes, you ought to have a script that deploys against a target, which is specified in the connection. I know there are case where you want to deploy in a transaction to two databases, but I'd rather handle that separately.

    For me, I find that often having the db name in a script creates a tight coupling, and it causes me issues. Especially if I want parallel work on a system (build/CI/QA), or I get to the place where I shard things out. In that case, I can't easily deploy the script to a database because it's locked to a name.

    My view is that depending on USE is an anti-pattern.

  • I've been living with similarly named databases that have different suffixes based on the environment for a very long time.  I say "living"... what I really mean is "enforcing".  We ONLY use two part naming in the databases.  In the old days before synonyms became available, we'd use "Pass-through" views and made the change to SYNONYMs when they came available.  We just don't ever have to change code to deploy from one environment to another and, if we have to copy a database, it's pretty easy to have a script that will change the synonyms based on the machine and database they've moved to.

    Nothing is a 100% panacea, though.  You still have to pay attention.   As with all else in SQL Server, "It Depends".

    And I know nothing about Azure and so have no clue if such a thing will actually work there.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks, Jeff.  Very interesting.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply