SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating alias for a database


Creating alias for a database

Author
Message
SQLCurious
SQLCurious
SSC-Addicted
SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)SSC-Addicted (451 reputation)

Group: General Forum Members
Points: 451 Visits: 452
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 Sad That's how they are, and apparently it's a big deal to change since a lot of other factors are involved. Sad

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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210043 Visits: 41973
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19185 Visits: 7410
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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search