February 29, 2012 at 2:18 am
Hi guys,
I have a 2005 instance with both a DEV and LIVE database with a stored procedure which references a Linked server.
Because the linked server resides at instance level the LS for DEV is different to the LS for LIVE. What is the best process for designing the SQL so that it references the correct LS depending on its parent database.
e.g. in Dev it needs to do
SELECT someField from [DevLinkedServer].[myDatabase].[dbo].[someTable]
in Live is needs to do
SELECT someField from [LiveLinkedServer].[myDatabase].[dbo].[someTable]
Things I have considered - none of which I like:
An input parameter to take Dev or Live and construct a Dynamic SQL statement to be executed using exec sp_execute. This means no caching of query plans
Interrogate the schema to find out the active database Dev or Live and run a different SQL statement based on the result
Create two stored Procedures and choose which one to call: I am ultimately going to be calling them from an SSIS package so can dynamically choose which one to use at run-time. This runs the risk that the sps do not get updated in the same way (there is no source code control here either:w00t:)
Unfortunately I am not in a position to separate the dev and live systems into different instances - which is the 'proper' way of doing it
February 29, 2012 at 2:27 am
Personal experience says just create a dedicated script for LIVE and perform the deployment.
The reason...
Interrogating the server list , or dynamic sql etc are by nature subject to change. A change in Server name or a change in the server list could be handled automatically and you wont be aware of it.
I would rather have my object fail during deployment because it couldn't find the linked server than go about picking one on its own especially when I am not in control of the server names etc.
February 29, 2012 at 12:23 pm
The problem with that is that every tme you make changes to the script in DEV you have to modify it again once it has passed UAT so that you can deploy it to live. If in the future they move to automatic deployment using RedGate or similar then the script will fail at deployment.
February 29, 2012 at 2:51 pm
how many objects are being accessed via the linked server?
you might consider creating synonyms for the objects. if there is a zillion of them, or dynamic SQL is being used,t hat might be a problem.
CREATE SYNONYM REMOTETABLE FOR [DevLinkedServer].[myDatabase].[dbo].[someTable]
the the code will not change int eh stored procs, but each server would have a slightly different synonym definition;
SELECT * FROM [REMOTETABLE]
Lowell
February 29, 2012 at 3:12 pm
you can test whether the synonym exists in the proc and bailout if necessary.
Create Procedure DoStuff
AS
BEGIN
IF (SELECT OBJECT_ID('REMOTETABLE','SN')) IS NULL
RAISERROR('Required Synonym [REMOTETABLE] does not exist!',16,1)
--do stuff
END --PROC
Lowell
March 1, 2012 at 11:04 am
aaron.reese (2/29/2012)
Hi guys,I have a 2005 instance with both a DEV and LIVE database with a stored procedure which references a Linked server.
Because the linked server resides at instance level the LS for DEV is different to the LS for LIVE. What is the best process for designing the SQL so that it references the correct LS depending on its parent database.
e.g. in Dev it needs to do
SELECT someField from [DevLinkedServer].[myDatabase].[dbo].[someTable]in Live is needs to do
SELECT someField from [LiveLinkedServer].[myDatabase].[dbo].[someTable]Things I have considered - none of which I like:
An input parameter to take Dev or Live and construct a Dynamic SQL statement to be executed using exec sp_execute. This means no caching of query plans
Interrogate the schema to find out the active database Dev or Live and run a different SQL statement based on the result
Create two stored Procedures and choose which one to call: I am ultimately going to be calling them from an SSIS package so can dynamically choose which one to use at run-time. This runs the risk that the sps do not get updated in the same way (there is no source code control here either:w00t:)
Unfortunately I am not in a position to separate the dev and live systems into different instances - which is the 'proper' way of doing it
I would think that having stored procs check to see which DB they are running in would be a bad idea. You would be making the production processes do that work every time they are called, just to support the development process - the tail wagging the dog, IMHO.
I think your only feasible choice in this case is to change the linked server name in the code after it has passed UAT and is ready to promote to production. I would suggest doing this with some sort of automated find and replace function to reduce the chance of human typing errors and ease the workload when you have to change dozens or hundreds of object names. This will be a serious hassle in code maintenance, but as you pointed out, you lack the influence to do this the proper way by putting DEV and LIVE on different instances, so you're just stuck with it. Document the shortcomings of the current system and the suboptimal processes that it necessitates, share the documentation with those above you in the reporting chain, and forge ahead. :crazy:
Jason Wolfkill
March 1, 2012 at 11:11 am
Lowell (2/29/2012)
how many objects are being accessed via the linked server?you might consider creating synonyms for the objects. if there is a zillion of them, or dynamic SQL is being used,t hat might be a problem.
CREATE SYNONYM REMOTETABLE FOR [DevLinkedServer].[myDatabase].[dbo].[someTable]
the the code will not change int eh stored procs, but each server would have a slightly different synonym definition;
SELECT * FROM [REMOTETABLE]
I like the synonym idea. I think that even if you had to create and maintain synonyms for a few hundred objects in the remote DB, you would have a single point of maintenance if the schema of the remote server changes - change the synonym definitions rather than having to seek out all object references throughout the stored proc/UDF code base. I think you could probably automate the creation of the synonyms with just a little thought and effort. You also would be able to promote code from DEV to LIVE without any changes to the object names - the synonyms in each DB would point to the appropriate linked server for that environment.
Jason Wolfkill
March 1, 2012 at 11:19 am
another possibility could be that the linked servers on DEV and LIVE have the same names, but different definitions.
it's not obvious, but you can create a linked server with any name, and point it to SQL instance;
i do that especially because i hate a lined server named things like [DEV223\SQL-2008R2]
--#################################################################################################
--Linked server Syntax for SQL Server With Alias
--#################################################################################################
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'DEV223\SQL2008R2', @provider = N'SQLOLEDB';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
Lowell
March 1, 2012 at 11:52 am
Lowell (3/1/2012)
another possibility could be that the linked servers on DEV and LIVE have the same names, but different definitions.it's not obvious, but you can create a linked server with any name, and point it to SQL instance;
i do that especially because i hate a lined server named things like [DEV223\SQL-2008R2]
--#################################################################################################
--Linked server Syntax for SQL Server With Alias
--#################################################################################################
EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',@srvproduct = N'', @datasrc = N'DEV223\SQL2008R2', @provider = N'SQLOLEDB';
-- Add Default Login (if applicable)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MyLinkedServer',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa',
@rmtpassword = 'NotARealPassword';
I'm no expert in setting up linked servers, but aren't they created at the instance level? The OP has a DEV DB and a LIVE DB on the same instance (and also wishes this weren't the case but cannot change it), if I read his post correctly. I assume you couldn't set up two different linked servers with the same name - am I right?
Jason Wolfkill
March 1, 2012 at 1:20 pm
Lowell beat me to the SYNONYM point. I prefer to use that, the code never changes and the SYNONYM is defined at the database level given you the ability to use different locations per database..
CEWII
March 1, 2012 at 7:32 pm
Elliott Whitlow (3/1/2012)
Lowell beat me to the SYNONYM point. I prefer to use that, the code never changes and the SYNONYM is defined at the database level given you the ability to use different locations per database..CEWII
I'll "third" that.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply