How to redirect SQL connection to different server name?

Question

Post reply

How to redirect SQL connection to different server name?

wodom

Hall of Fame

Points: 3813
More actions
May 2, 2011 at 4:18 pm

#239397

I did some searching but didn't find anything on this. This is sort of an Intranet environment. What I want to do is run the same web page on two different web servers, but the copy on one server uses a different SQL Server database instance -- even though the code specifies the same instance in each case.
E.g., ProductionWeb uses databases on productionDB. But when we copy the exact same web pages to TestWeb for development and testing, we want it to use TestDB instead of ProductionDB.
I believe something similar can be done by adding a CNAME entry (ProductionWeb) in DNS, which points to TestWeb, but that would apply to the entire LAN. Our test server doesn't have its own DNS. DNS is supplied by Active Directory, and everything on our LAN is in the same domain. I don't think the Hosts file on the server will work for this, since it maps names to IP addresses, not other names like a CNAME does.
Is there a way to do this without manually having to change the DB names in all the different files for testing, and then change them back for production? (Besides being error-prone, there are 2 production servers that map to the 1 test server, so changing the names back is harder.)
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

You can configure an alias on the test server using SQL Server Configuration Manager - see screenshot.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

wodom Hall of Fame Points: 3813 More actions · Answer 2

Hmm, interesting but there seems to be a piece missing. If I understand you correctly, there would then be two ProductionDBs advertised on the network (one real, one alias) -- so how would anyone be able to choose which one they want?

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

wodom (5/2/2011)
Hmm, interesting but there seems to be a piece missing. If I understand you correctly, there would then be two ProductionDBs advertised on the network (one real, one alias) -- so how would anyone be able to choose which one they want?

No, it is a client alias on the test server only.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

Very good recommendation from Mister Magoo. That's exactly the thing that you were looking for.

Thank You,

Best Regards,

SQLBuddy

wodom Hall of Fame Points: 3813 More actions · Answer 5

mister.magoo (5/2/2011)
wodom (5/2/2011)
Hmm, interesting but there seems to be a piece missing. If I understand you correctly, there would then be two ProductionDBs advertised on the network (one real, one alias) -- so how would anyone be able to choose which one they want?
No, it is a client alias on the test server only.

Sorry, but still, what does that buy youi? You're saying the test server now has an extra name -- an alias whereby it is now known as ProductionDB in addition to its regular name, but then that conflicts with the "real" ProductionDB which is elsewhere on the network.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 6

wodom (5/3/2011)
mister.magoo (5/2/2011)
wodom (5/2/2011)
Hmm, interesting but there seems to be a piece missing. If I understand you correctly, there would then be two ProductionDBs advertised on the network (one real, one alias) -- so how would anyone be able to choose which one they want?
No, it is a client alias on the test server only.
Sorry, but still, what does that buy youi? You're saying the test server now has an extra name -- an alias whereby it is now known as ProductionDB in addition to its regular name, but then that conflicts with the "real" ProductionDB which is elsewhere on the network.

No, you are misunderstanding what this does.

When you create a client alias, it only exists on the specific client on which you create it. No other machines on the network can use it.

It is similar to putting an entry in your windows "hosts" file, except that using an alias you can tell it which port (and thereby which instance) to connect to as well as the machine.

Creating a client alias on the TEST web server would allow the TEST web server to use the name ProductionDB to connect to the TestDB sql server. No other machine would be able to do that.

You need to have the configuration tool on the TEST web server to do this AFAIK, but once it is set up it will do exactly what you require.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

wodom Hall of Fame Points: 3813 More actions · Answer 7

Well if it's something that exists solely on the client side then I think I follow what you're saying, but your screen shot clearly says it uses the SERVER Configuration Manager, not a CLIENT Configuration.

Maybe this is some sort of terminology change between SQL Server 2000 and 2008 (most of my experience has been on the former). But either way it appears I need to install a portion of SQL Server 2008 onto the TestWeb server. So I'll see what I can get to happen going down that road.

Thanks!

mister.magoo SSC-Forever Points: 47174 More actions · Answer 8

Sorry for short answer...on phone...http://msdn.microsoft.com/en-us/library/ms190445.aspx

Read the community content on that page to give you a pointer.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

mister.magoo SSC-Forever Points: 47174 More actions · Answer 9

Ok, here is a fuller reply - now that I am at a keyboard...

Open a command prompt on the TEST server and type

%WINDIR%\system32\cliconfg.exe

This will open the SQL Server Client Network Utility

Open the second tab entitled "Alias" and click the "Add" button.

Fill in the details of your SQL server (TestDB) and give the Alias a name (ProductionDB), choose the relevant options for connectivity and click OK.

This will write the details of the Alias to the Windows Registry on the Test server.

You can now use the name ProductionDB from the Test server and it will connect to the TestDB instead.

Please remember to test this carefully before using it for real!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

wodom Hall of Fame Points: 3813 More actions · Answer 10

It turns out there was already an install of SQL 2000 on TestWeb, and I ran its Client Config, and sure enough, that old version has aliases too. So now that I could tell what you were actually talking about (client side vs. server side) I set up an alias last night and it works. That's even before I read your latest post.

Actually there are two production DBs served by the one test DB, so we need two aliases, but they appear to work just fine. As an extra complication there turns out to be one "test" web site that actually modifies real data instead of test data, but that's easily solved by modifying that site to use a different new alias, configured on both web servers, which ONLY points to the "live" database no matter which web server you're using.

This is cooler than cool. Thanks so much for telling me about this feature.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

wodom (5/4/2011)
It turns out there was already an install of SQL 2000 on TestWeb, and I ran its Client Config, and sure enough, that old version has aliases too. So now that I could tell what you were actually talking about (client side vs. server side) I set up an alias last night and it works. That's even before I read your latest post.
Actually there are two production DBs served by the one test DB, so we need two aliases, but they appear to work just fine. As an extra complication there turns out to be one "test" web site that actually modifies real data instead of test data, but that's easily solved by modifying that site to use a different new alias, configured on both web servers, which ONLY points to the "live" database no matter which web server you're using.
This is cooler than cool. Thanks so much for telling me about this feature.

You're welcome, glad you got it sorted.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

How to redirect SQL connection to different server name?

Cookies on SQLServerCentral