Creating alias for a database

Question

Creating alias for a database

SQLCurious

SSCrazy

Points: 2339
More actions
April 29, 2014 at 5:15 pm

#307096

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.

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

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)

ScottPletcher SSC Guru Points: 101099 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".