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

Posted on 2012-09-16
Last Modified: 2012-09-17
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
Question by:rockman44
    LVL 68

    Expert Comment

    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.

    Author Comment

    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
    LVL 68

    Accepted Solution

    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 as the "old" network and as the "new" one:
    MSSQL 2005, NIC 1:
    MSSQL 2005, NIC 2:
    MSSQL 2012, NIC 1:

    A client needs to know the route: mask gateway
    MSSQL 2012 needs to know the route: mask gateway

    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, 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.

    Author Closing Comment

    Thanks for your help

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now