Database name in linked services

Question

Database name in linked services

TRACEY-320982

SSChampion

Points: 13653
More actions
August 27, 2011 at 5:37 pm

#390291

I have test and prod environments.
DB are TEST_APP on TEST_SQL and PROD_APP on PROD_SQL
If the developers use
select * from TEST_SQL.TEST_APP.SCHEMA.TABLE
then we they issue this to production they have to change TEST throughout to PROD
select * from PROD_SQL.PROD_DB.SCHEMA.TABLE
Some of these run across linked servers.
Using Synonyms Takes care of teh PROD_DB_SCHEMA.TABLE as
I can set this up on each TEST and PROD Servers.
The SQL Server names can be set up with DNS
The problem comes when trying to access table with Synonyms as this is not show up when you set up linked servers.
Any one know of a way so the developer does not have to change the DATABASE NAME from PROD_DB to TEST_DB throughout their scripting when going from TEST to PROD and rolling out software.
Thanks

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Synonyms are the right answer - I am not clear why you say this doesnt work. On the test system, you would create the synonyms as:

CREATE SYNONYM MySchema.Table FOR TEST_SQL.TEST_APP.Schema.Table;

Repeat for every object that you need to access through the linked server. Code should now only reference the synonym.

Your live system would have the following:

CREATE SYNONYM MySchema.Table FOR PROD_SQL.PROD_APP.Schema.Table;

Now, if you are writing code in DB1 that is accessing objects in DB2 - and that can be a linked server in production but is not a linked server in test, then you would have this:

CREATE SYNONYM MySchema.Table FOR DB2.Schema.Table; -- Test System

CREATE SYNONYM MySchema.Table FOR SRVA.DB2.Schema.Table; -- Prod System

Again, code now only uses the synonym and will work in either environment with no changes needed.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

This is what I did

SQL 01

DATABASEA

CUST.ORDERS

DATABASEB

CUST.ORDERS

CREATE SYNONYM [DBO].[ORDERS] FOR [DATABASEA].[CUST].[ORDERS]

CREATE SYNONYM [DBO].[ORDERS] FOR [DATABASEB].[CUST].[ORDERS]

SQL 02

---SET UP THE LINKED SERVER called TEST_LINK which is for SQL 01

-- When you do this you cannot see the SYNONYM Called ORDERS you can see the real table CUST.ORDERS

-- Only Views or sp are viewable.

If i do

SELECT * FROM [Test_LINK].[DATABASEA].[DBO].[ORDERS]

--This errors

--If i create a view and call it MY VIEW this consists of

SELECT * FROM DATABASEA.DBO.ORDERS

The on my SQL02 I can do - The data is returned.

SELECT * FROM [Test_LINK].[DATABASEA].[DBO].[MYVIEW]

If i want to then get data from DATABASEB i would have to change my to

SELECT * FROM [Test_LINK].[DATABASEB].[DBO].[MYVIEW]

If you want switch between databases i.e DATABASEA and DATABASEB this means changing the views each time.

A little bit of overhead in having to do this just to switch between difference databases and something the DBA would need to do

Is there a way to open up the TEST_LINK (Linked Server - where you expand and see tables, views to actually see the SYNONYMS.

I have selected the user securables and put select, view definitions on login but still not see them. Is there even possible?

Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

Tracey,

You have put the synonyms on the wrong server. What you want to have is a single synonym on SQL02:

CREATE SYNONYM dbo.Orders FOR TEST_LINK.DATABASEA.Cust.Orders;

Then, your code on SQL02 would use this synonym:

SELECT {...} FROM dbo.Orders;

Now, if you want to access the Orders from DatabaseB - then you do this:

DROP SYNONYM dbo.Orders;

GO

CREATE SYNONYM dbo.Orders FOR TEST_LINK.DATABASEB.Cust.Orders;

Now, the above code will get the data from database B.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

Didn't think about having the SYNONYM on the SQL02 and the databases on SQL01. That makes sense.

Now i can do the SELECT * FROM TABLE....

All works just as I expected.

Thanks for the time to stick in there with me.