Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating alias for a database Expand / Collapse
Author
Message
Posted Tuesday, April 29, 2014 5:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:30 PM
Points: 76, Visits: 290
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.
Post #1566178
Posted Tuesday, April 29, 2014 8:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1566194
Posted Wednesday, April 30, 2014 12:21 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:25 PM
Points: 2,044, Visits: 3,059
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1566543
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse