Creating alias for a database

  • I have 3 databases that my OLTP application uses

    SalesDB

    MarketingDB

    AccountingDB

    I have about 50 ETL stored procedures that extracts data by joining tables from all 3 databases. My Development, QA, and UAT environments all have these databases created under different names to reflect the environment.

    Dev

    SalesDB_Dev

    MarketingDB_Dev

    AccountingDB_Dev

    QA

    SalesDB_QA

    MarketingDB_QA

    AccountingDB_QA

    UAT

    SalesDB_UAT

    MarketingDB_UAT

    AccountingDB_UAT

    Production

    SalesDB

    MarketingDB

    AccountingDB

    Please don't ask me why they are being created that way 🙁 That's how they are, and apparently it's a big deal to change since a lot of other factors are involved. 🙁

    Coming back to the point...because of the way the DBs are named, I am forced to change my scripts, using search and replace, every time I deploy them to a different environment. Is there a way I can create an alias for each of these databases so that all of them can be referred without their environment suffix? That will help me to deploy my changes to production without making any changes after they are certified in QA and UAT.

    If creating an alias is not possible, what other ways can I tackle my problem? I appreciate your valuable inputs.

  • They created the database names with the environment suffix to make sure that applications under test don't accidently point to production. It's a real PITA for DB folks, especially during the promotion of code, but it's a REAL lifesaver if something goes wrong... goes wrong... goes wrong... (whack!) goes wrong.

    I say it's a "real PITA" but it actually isn't. You just need to do a little more work as a DBA but you do NOT want to "re-touch" any code that has already been tested because YOU will be at fault if anything goes haywire.

    How do you get around that?

    1. Enforce a 2 part naming convention for all SQL Objects in ALL code... frontend or backend. Be a bitch about it, too! It will eventually make everyone's life a whole lot easier.

    2. Setup SYNONYMs for each environment.

    Here's an example... this sets up 4 databases, 2 "Dev" and 2 "QA" that start with my initials in an attempt to avoid collisions on your test box. I don't drop the databases at the end. When you're done, you can drop all 4 easily enough using the {f7} key (follow your nose when you get there) because they'll all be grouped together by name.

    The rest is in the comments in the code that follows. It's a full demonstration that runs womb-to-tomb. Just make sure you read the comments so that you understand how easy this is. The only thing this type of thing won't help you with is if you have identically named objects in the two databases. If that's the problem, post back because I have a trick even for that using SYNONYMs.

    USE tempdb; --Just a safe place to start

    GO

    --====================================================================

    -- Create the "Dev" environment.

    -- This is NOT a part of the solution. We're just setting up

    -- the simulated environment here.

    --====================================================================

    --===== Create the databases for the Dev enfironment

    CREATE DATABASE JbmMain_Dev;

    CREATE DATABASE JbmDB01_Dev;

    GO

    --===== Create a View on the "JbmDB01" database

    USE JbmDB01_Dev;

    GO

    CREATE VIEW dbo.SomeView AS SELECT ThisDBName = DB_NAME();

    GO

    --====================================================================

    -- Create the "QA" environment.

    -- Normally, this would be on a different server but this will do.

    -- This is NOT a part of the solution. We're just setting up

    -- the simulated environment here.

    --====================================================================

    --===== Create the databases for the Dev enfironment

    CREATE DATABASE JbmMain_QA;

    CREATE DATABASE JbmDB01_QA;

    GO

    --===== Create a View on the "JbmDB01" database

    USE JbmDB01_QA;

    GO

    CREATE VIEW dbo.SomeView AS SELECT ThisDBName = DB_NAME();

    GO

    --====================================================================

    -- Now, let's create a synonym on the "JbmMain" database of each

    -- of the two environments that will allow us to look at the

    -- correct database

    -- This is a major part of the solution.

    --====================================================================

    --===== Setup the synonym in the "Dev" environment using a 2 part

    -- naming convention.

    USE JbmMain_Dev;

    CREATE SYNONYM dbo.SomeView FOR JbmDB01_Dev.dbo.SomeView;

    GO

    --===== Setup the synonym in the "QA" environment using a 2 part

    -- naming convention.

    USE JbmMain_QA;

    CREATE SYNONYM dbo.SomeView FOR JbmDB01_QA.dbo.SomeView;

    GO

    --====================================================================

    -- Let's now pretend that you're promoting code to both

    -- environments. The key here is that we don't have to change

    -- the code just because we change environments.

    --====================================================================

    --===== Create a stored proc on the "JbmMain" database that will

    -- select from the dbo.SomeView on the "JbmDB01" database.

    -- This one is for the "Dev" environment.

    USE JbmMain_Dev;

    GO

    CREATE PROCEDURE dbo.GetNameFromOtherDB AS

    SELECT * FROM dbo.SomeView; --This is actually a synonym.

    GO

    --===== Create a stored proc on the "JbmMain" database that will

    -- select from the dbo.SomeView on the "JbmDB01" database.

    -- This one is for the "QA" environment. Note that is identical

    -- to the one in the "Dev" environment. The difference is that

    -- the "dbo.SomeView" synonym is pointing to the other database

    -- in the "QA" environment.

    USE JbmMain_QA;

    GO

    CREATE PROCEDURE dbo.GetNameFromOtherDB AS

    SELECT * FROM dbo.SomeView; --This is actually a synonym.

    GO

    --====================================================================

    -- Let's see how they run. Let's simulate an app calling the

    -- stored procedure from each environment.

    --====================================================================

    --===== First, we'll execute the proc in the "Dev" environment.

    USE JbmMain_Dev; --App wouldn't need this because of connection string

    EXEC dbo.GetNameFromOtherDB;

    --===== Now, we'll execute the proc in the "QA" environment.

    -- Notice that we even EXEC the proc in exactly the same way.

    USE JbmMain_QA; --App wouldn't need this because of connection string

    EXEC dbo.GetNameFromOtherDB;

    --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)

  • I'd try to do this in the most straightforward way possible. Therefore, I'd go with db snapshots unless the modification activity on the dbs during the ETL process was too high.

    You'd write your code with dummy db names, say "SalesDB__Local", etc..

    Your ETL processing would have three basic phases:

    1) dynamic code to create the relevant db shapshots with the dummy names;

    2) run the procs as normal;

    3) dynamic code to drop the db snapshots.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

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

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