Linked Server View - Production vs Test

Question

Linked Server View - Production vs Test

brian.neumeier-603907

Old Hand

Points: 346
More actions
January 22, 2010 at 9:29 am

#215763

I have 2 servers one for accounting and one for business activity (orders,qc,etc...) These are considered production systems... I also have a server for a test environment for the business activity, for accounting the test environment is another database on the production server...
I have the production servers linked and then the test server linked to production accounting but only the accounting test database (user security)..
I have a view in my business activity environment that displays addresses from the accounting system...
CREATE VIEW [dbo].[vw_accounting_address]
AS
SELECT
[Customer].[CustID] AS [accounting_entity],
[Customer].[CompanyID] AS [accounting_company],
[Customer].[CustName] AS [customer_name],
[Address].[AddrLine1] AS [address_line_1],
[Address].[AddrLine2] AS [address_line_2],
[Address].[City] AS [city],
[Address].[StateID] AS [region],
[Address].[CountryID] AS [country],
[Address].[PostalCode] AS [postal_code]
FROM
[accounting_server].[accounting].[dbo].[Customer]
JOIN [accounting_server].[accounting].[dbo].[CustAddr] ON [Customer].[CustKey] = [CustAddr].[CustKey]
JOIN [accounting_server].[accounting].[dbo].[Address] ON [CustAddr].[AddrKey] = [Address].[AddrKey]
I have the same view in my test environment, but using a different database...
CREATE VIEW [dbo].[vw_accounting_address]
AS
SELECT
[Customer].[CustID] AS [accounting_entity],
[Customer].[CompanyID] AS [accounting_company],
[Customer].[CustName] AS [customer_name],
[Address].[AddrLine1] AS [address_line_1],
[Address].[AddrLine2] AS [address_line_2],
[Address].[City] AS [city],
[Address].[StateID] AS [region],
[Address].[CountryID] AS [country],
[Address].[PostalCode] AS [postal_code]
FROM
[accounting_server].[accounting_test].[dbo].[Customer]
JOIN [accounting_server].[accounting_test].[dbo].[CustAddr] ON [Customer].[CustKey] = [CustAddr].[CustKey]
JOIN [accounting_server].[accounting_test].[dbo].[Address] ON [CustAddr].[AddrKey] = [Address].[AddrKey]
Is there anyway to remove the difference between the 2 views {accounting} vs. {accounting_test} so we do not always have to manually address these discrepancies when syncing production environments to test... This is not the only view we have like this... But if we were to change the view, we would make the change in the test environments and perform testing... Once testing is complete, we just take script and execute against production, but for these views we have to remember to change accounting_test to accounting... Curious to hear others thought and ideas on how to make view source exactly same in all environments and not require manual change or additional scripts to handle...

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

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

Piotr.Rodak SSCrazy Eights Points: 9296 More actions · Answer 1

Hi Brian,

You can possibly use synonyms for all tables that you reference in views. If there are not too many of them, you could have one set of synonyms for test environment and another one on production. The code using them wouldn't change.

Regards

Piotr

...and your only reply is slàinte mhath

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Use a linked server with the same name in test and production.

--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)
Intro to Tally Tables and Functions

S t e f SSC Eights! Points: 917 More actions · Answer 3

Is there something stopping you creating a seperate instance on the test server for the accounting database and linking to that? That way Live and Test would be on seperate physical servers and you could still set up the linked servers as you would in Live, if you were to do this though and the machines remained on the same domain I would recommend using different security accounts just incase! Thats not too difficult to code round when you promote the scripts through the environments.

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 4

I think I like the last advice. Mimic the production environment and keep the names the same. A linked server also works, but be sure you use different accounts in test and production just to be safe.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com