July 5, 2004 at 11:40 am
I'm trying to decide what is the best way to reference a linked server when I know that the database is going to move to another server. In our developement environment, we have 2 servers (say A and B). My database on Server A needs read access to a database on Server B. So, I've linked them and that works great.
However, I will need to move both databases into production sometime soon. In production, they may or may not be on the same server ( I don't get to pick), but they will both definitely been on servers other than A and B, possibly C or C and D.
Short of having to go through with a Search and Replace function to replace the name of Server B in all my procedures/views/functions on Server A when I move into Production, what can I do?
Any help would be appreciated.
Thanks,
Jana
July 5, 2004 at 12:15 pm
Why not create Linked server names that you can re-create in your production environment. You can have multi. linked servers different names linked to the same database/server.
This way when you port the databases over to production and create the linked servers your databases should be none the wiser....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 5, 2004 at 12:23 pm
July 5, 2004 at 1:53 pm
As long as you can connect to them via linked server I really don't see why it would matter where they live as long as they can talk....
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 6, 2004 at 5:08 am
Unfortunately, a server cannot link to itself - at least, I couldn't get it to do it.
July 6, 2004 at 6:43 am
I posted a very simliar question last week and got the v.useful answer use sp_setnetname. see; http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=123539 for the full details
Jacko
July 6, 2004 at 7:05 am
I've found it very helpful to create your production linked server using the SQL Server radio button and the actual linked server you will be connecting to. Then in dev use the OLE DB provider for SQL Server, use the name of the production linked server, however change the server reference to point at whichever server you want, this is especially useful for a loopback linked server, allowing you to test remote functionality on a single server.
July 6, 2004 at 8:18 am
Another way to go on this one is to setup a server alias using Client Network Utility. It does not care what machine it points to and can be set to look the same on all machines regardless of configuration (single / multiple)
You do not want to use the same machine name or make the change in the hosts file because you may need to promote by copying.
We had a lot of problems moving from dev to prod because we had to have 3 different updates until one of our DBAs discovered this trick. Now we have 1 update and it always works even on machines where both systems are combined.
July 6, 2004 at 11:17 am
Before reading these posts, my recommendation would be to use linked server names that can be used in both production and test. For example, if you needed to access some sales data on MyCoSDW - create a linked server named "SALES" instead of naming it the server name MyCoSDW.
I think a happier medium could be reached using a combination of cliconfg and meaningful linked server names... using the example above, you could create everything in a dev environment, but with names ready for test or prod environments.. You would create a linked server to MyCoSDW, call it "SALES", and in test if you need it to point to a test server instead add a cliconfg record for MyCoSDW to MyCoSDWtest or something.. that way, you don't need to change anything when rolling out or up to another environment..
If you were to do this in EM, you'd need to choose the OLE DB adapter to SQL Server instead of the radio button. This is because EM will use the server name as the linked server name, other boxes would be greyed out..
-Ken
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply