Linked servers

you are misunderstanding me ... I should also mention that this is not my design (I am of the viewpoint that this sort of logic should not be in the database at all but happenning at the domain layer)

in development lets say there are two db servers

devdb1 and devdb2

devdb1 in sprocs is accessing devdb2 through a linked server.

in production there are two servers

proddb1 and proddb2

proddb1 in sprocs is asccessing proddb2 through a linked server



the problem is in synchronizing prod and dev ... since they have server names in the sprocs these server names need to be changed during the synchronization process. I am trying to come up with a way of working around this limitation in the interum while a more permanent solution (not using the link servers is under way). My initial thought is that since the server name seems to be the only alias available, one could use DNS to make proddb2 and devdb2 have a DNS alias db2 which points to the correct server dependent upon environment (i.e. in dev db1 points to devdb1 and in production db1 points to proddb1) it would atleast provide the use of the same names for the linked servers between the two environments.

What I am wonderring is if there is a better way of doing this, i.e. can I alias a link server's name in SQL itself as doing it through DNS is a bit kludgey.

Greg

Have you considered naming the servers the same way.  In other words on devdb1 call devdb2 db2 and on proddb1 call proddb2 also db2.  You can then access from devdb1 db2 (devdb2) and the same code can be used for proddb1.

I trust this is clear.  Otherwise I have no clue.

I guess that sums up my question

can I ALIAS a linked server in SQL server or does it have to be by name?

i.e. so I have a linked server named db1 that points to devdb1. everything I see uses the name as the alias. so my work around presented above was to use dns to alias the name.

Greg

>>can I ALIAS a linked server in SQL server or does it have to be by name?<<
It looks like I was wrong.  There does not appear to be a way to implement and alias for a server.

I am sorry I could not be more help.

ok thats what my research said (name appears to be both server name and internal name) which is what led me towards the DNS route.

Do you know of any other ways?

Greg

btw I was not being sarcastic in anyway, I really have no idea when it comes to sql server.

just in case I came accross that way.

>>Do you know of any other ways?<<
I am afraid not.  Using linked servers that way is not very performant and certainly not very secure.  We instead chose to use a third party tools to synchronize our servers, namely:

SQL Compare®
Compares and synchronizes SQL database schemas
http://www.red-gate.com/products/SQL_Compare/index.htm

and

SQL Data Compare™
Compares and synchronizes SQL database contents
http://www.red-gate.com/products/SQL_Data_Compare/index.htm

Your approach would also invalidate these tools, as we would not be able to make valid comparisons between the two environments.

>>just in case I came accross that way.<<
Not at all.  I know where you are coming from and I am sorry I could not help more.

ah no, they are not being used for synchronization.

the environments are replicas of one another for development/qa/staging ...

the problem is you can't just copy these sprocs from one to another you have to change them.

>>ah no, they are not being used for synchronization.<<
"synchronization" was probably the wrong term to use.  But I understood what you meant.

>>the problem is you can't just copy these sprocs from one to another you have to change them.<<
Exactly.  At our shop, when a stored procedure has been thoroughly tested we can promote as is from test to production using the tools I mentioned previously.  With your scenario we would in addition have to change the server names, which in a sense invalidates the whole "thou shalt not modify code directly in Test/QA/Staging or Production" edict.

you know I didnt realzie it took a connection string as an optional parameter, that works a charm. all the other methods use the server as the name and server name.

thats exactly what I was looking for, a way to alias it within sql server itself.

Cheers,

Greg

I havn't actually tried this yet but ...

USE master
GO
EXEC sp_addlinkedserver
   @server = 'WebDatabase',
   @srvproduct = '',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=prod02;UID=sa;PWD=sapassword;'
GO

etc

I will try it when I get in on monday and I will report back if there are issues. It sure would be nice if they made their parameters a _bit_ more meaningful :)

Cheers,

Greg