Linked Server Connection String to utilize as pass through to remote database

I have been asked to replace an existing SQL Server 2005 with a new SQL Server 2012 in a plant that has recently been purchased.  The existing and running SQL Server 2005 resides on the previous owners network and can be accessed by clients.  The new SQL Server is not allowed to be connected to the previous Owners network until a later date.  I have been asked to migrate 30 + databases to the new SQL Server 2012 and then add the New Server as a linked Server on the Old Server which is still connected to the previous owners network and try to set up the existing server as a pass through server so clients can access.  There is a second network both servers are connected to that is internal and unregulated.  I have used linked servers quite a bit in the past by using OpenQuery and the 4 part identifier for individual code.  There is no way I will be able to change all code to reference a linked server versus a local server.  All of 56 apps have 1 main connection string that is reused through all code in the application.  I was wondering if there was a way I could modify the connection string to connect directly to the linked server and actually use the existing server as a true pass through server to allow client access.  Is there a way to accomplish this with linked servers or another method?  My initial research looks bleak.  Thanks
rockman44Asked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
No links ready, sorry.

The simplest way is to install/activate RRAS (Routing and Remote Access Services) on the "bridging" server, and setting its "Routing" option (fairly straight-forward in the setup). This will allow bidirectional routing for the two NICs, and hence both networks.

Next, all clients needing access need to get the route to the secondary network. That can be done by creating a static route on each client - but I wouldn't do that. Better IMHO is to set the route on the existing default gateway, which will either propagate the route temporarily and on demand to the clients, or just do the routing.
The route in either situation needs to define the secondary network with the SQL Server 2005 as gateway.

Last, the new SQL Server needs to know the route back - again, setting a static route to the primary network on that machien (using the 2005 as gateway) will do that.

For example, if you have 192.168.123.0/24 as the "old" network and 192.168.124.0/24 as the "new" one:
MSSQL 2005, NIC 1: 192.168.123.123
MSSQL 2005, NIC 2: 192.168.124.123
MSSQL 2012, NIC 1: 192.168.124.124

A client needs to know the route: 192.168.124.124 mask 255.255.255.255 gateway 192.168.123.123
MSSQL 2012 needs to know the route: 192.168.123.0 mask 255.255.255.0 gateway 192.168.124.123

This is most restrictive, of course you can set up the complete network in the first route instead of a single IP.

If you then set up a connection string using the IP of 192.168.124.124, it should work. If you want to use the name, you will have to set up DNS or WINS appropriately, since it will not work automatically.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The only way I can think of is to create a new schema consisting of synonyms using the linked server. Provided the applications do not use a 2- or 3-part name for DB objects ...

It is not possible to make a linked server fully transparent, so there will still be a difference thru the added Distributed Query feature. And having to create synonyms for each and every object to use is a PITA, of course, even if doing it per script.

IMHO it would be much better to allow for full routing, and provide the real server name/IP in the apps' connection strings.
0
 
rockman44Author Commented:
I agree with you.  Can you direct me to any links that would provide information how to implement full routing so I can provide the New Servers name in the apps connection string?  Thanks
0
 
rockman44Author Commented:
Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.