June 19, 2006 at 12:50 pm
Does sql server 2000 support linked server aliases or names other than the machine name the instance is running on? so instead of 'physical server name.database.owner.object' naming convention in my codee
I can use 'physical server name alias'.database.owner.object'
The advantage is in porting code to a DR site or moving code through a developement lifecycle across vaious environments without having to change the code. What solutions are out there? Surely this is a common problem.
SQL 2012 Standard VPS Windows 2012 Server Standard
June 19, 2006 at 2:07 pm
Hi,
My favorite solution for SQL Server 2000 is to use a client network utility to create the alias. Then when creating a linked server, you may use this alias for the name. The Client Network Utility Alias should be created on the machine where the parent SQL Server runs. By parent I mean where the linked server should be created.
Another solution is to ask your network administrator for DNS aliases.
Let us know if it worked for you.
Regards,Yelena Varsha
June 19, 2006 at 3:06 pm
Your response gives me confidence it can be done. What exactly do you mean by 'client network ustility'? Can you give me an example of one?... perhaps the one you use?
I ended up making a linked server using OLEDB source instead of a native SQL Server source. This way I was able to make my own name for it. I'm concerned there are limitations with this type of connection.
SQL 2012 Standard VPS Windows 2012 Server Standard
June 19, 2006 at 3:37 pm
Start->Programs->Microsoft SQL Server -> Client Network Utility
OR
Start ->Run ->cliconfg (pay attention there is no letter i in confg)
Go to Aliases tab, click New (or Add, whatever you have there) and enter Alias name and server name. Also click on the protocol you want to be used: TCP/IP or Named Pipes.
Do it on the Server machine.
Regards,Yelena Varsha
June 19, 2006 at 4:54 pm
oh my... this is the way to do it. Thank you
SQL 2012 Standard VPS Windows 2012 Server Standard
June 20, 2006 at 11:10 am
I disagree ... using the SQL Server network utility for aliasing is a multiple computer maintenance nightmare potentially. The easiest (and probably best) method is to use DNS aliasing - after all that is what it was intended to do. This way the change is isolated to one place and one place only. Talk to your network admins about this.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 20, 2006 at 11:31 am
Okay I see your point. It occured to me last night that when both machines are up and running on the same domain/network. I dont know which one will get the DB connection!
the primary one with the real windows machine name 'machineA' or machineB with the SQL server network utility TCP/IP alias 'machineA'.
Or, the solution I have in place, is an OLE linked server that allows for a linked server name. Two questions about that... how concerned should I be about this backup server being implamented OLE instead of native AND when the backup server is up and running for testing on the same network/domain as our live server.. how is a query with 'machineA.database.owner.object' resolved?
SQL 2012 Standard VPS Windows 2012 Server Standard
June 20, 2006 at 2:43 pm
Rudy,
I did mention network_admin- managed DNS aliases too.
I don't think that managing Network Utility aliases takes a lot of maintenance, but it rather creates a lots of confusion for the end users. I guess I have to mention this because it is important: you have to be careful if you create an alias with your production server name with connection to the development server. It is right, you will not have to change code when moving code to production, but the RISK is VERY BIG that you will forget that you are connecting to development server and start putting production things there. Or other people who don't know about your setup will think that they are connected to production while they are connected to development. But it goes for both Network Utility and DNS aliases
Regards,Yelena Varsha
June 20, 2006 at 3:00 pm
Yelena,
Sorry I missed a part of your post (one of my clusters was and still is misbehaving). I agree with you on being careful and the risks involved. Whenever we use DNS for such an activity it's usually a real disaster of some type or a DR test scenario. Both of which have defined and documented steps for carrying out all of the needed activities. Well, have a great day ! As for me back to the present cluster locking /performance issues.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 20, 2007 at 5:55 pm
I realize this is an older thread...but came across while looking up some info about Aliases and Linked Servers. One of the main reasons to use this approach was as you quoted before...moving applications through different environments: Dev, Staging, Production.
Let's say we have 6 SQL Servers (DevA, DevB, StagingA, StagingB, ProdA, & ProdB). The Application runs on the "A" server in each environment but must access the "B" server using Linked Servers to query some extra data.
Using the Client Network Utility you would create 1 Alias (called SQLServerB) on each of the DevA, StagingA, and ProdA Servers. This Alias would point to the appropriate "B" Server depending on each environment:
DevA --> Create "SQLServerB" Alias which Points to DevB and then Create Linked Server "SQLServerB" using TCP/IP
StagingA --> Create "SQLServerB" Alias which Points to StagingB and then Create Linked Server "SQLServerB" using TCP/IP
ProdA --> Create "SQLServerB" Alias which Points to ProdB and then Create Linked Server "SQLServerB" using TCP/IP
This way your code in a Stored Procedure on DevA could use [SQLServerB].[MyDatabase].[dbo].[MyTable] when accessing data on DevB. This exact same code would work on StagingA and ProdA without the need to change any code.
So, I don't think you would use this technique if you wanted your Alias to be the same name as another existing server. You would use this technique when you wanted to use the same code to reference the same "secondary" server in different environments. Also you get the added benefit of a single quick change if the "B" Server ever changed in any of the environments, just update the Alias using the Client Network Utility on the appropriate "A" Server. To me, DNS Aliases shouldn't even factor into this discussion.
December 18, 2007 at 12:14 pm
Yes, use :
sp_addlinkedserver 'Alias', '', 'SQLOLEDB', 'ServerName’
Now instead of servername.catalog.dbo.table
you can use alias.catalog.dbo.table
😀
May 29, 2008 at 5:43 pm
This worked for me! Thank you!!!
May 29, 2008 at 6:08 pm
Instead of messing around with DNS, client network utility, etc... I use the following, which is much easier to manage.
Declare @server nvarchar(50)
,@source nvarchar(50);
Set @server = 'aliasname';
Set @source = 'servername';
-- Drop existing server, will error if doesn't exist but does not cause a problem
Execute master.dbo.sp_dropserver @server, 'droplogins';
Execute master.dbo.sp_addlinkedserver
,@srvproduct = N'SQLServer OLEDB Provider'
,@provider = N'SQLNCLI'
,@datasrc = @source
Execute master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = @server
,@useself = N'False'
,@locallogin = NULL
,@rmtuser = N'remote_user'--CHANGE TO VALID USER
,@rmtpassword = 'remote_password'--CHANGE TO VALID PASSWORD
-- Test the new linked server
Declare @sql varchar(max);
Set @sql = 'Select * From ' + @server + '.master.dbo.spt_values'; Execute(@sql);
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply