Link servers through proxy

My main development computer, XP Pro, (call it DEV) has 2 Ethernet
adapters. Adapter A goes out to a router, and can see the world, and
can see a remote SQL server (SQLREMOTE) that I have access to.
I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number.
Adapter B on my DEV computer is cabled directly to my SQL test computer
(SQLTEST) which only has one network adapter. On my DEV computer, using
the same three tools, I can see the databases on the SQLTEST computer
just fine.
This has worked for years. The DEV computer runs ProxyPlus to give the
SQLTEST computer some access to the Internet... for Windows Update and
such.
After all that was set up and working fine, I recently got a VPN
connection to the same network that the SQLREMOTE server lives on.
(Actually there are three SQL servers on that remote system.) This
might be an irrelevant detail.
Now I would like to link the two SQL servers together. I need to
occasionally get data from SQLREMOTE into SQLTEST. Since my DEV
computer can see both, it seems like this is doable.
I have read BOL and searched Google and haven't figured out how to do
this yet.
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
Q2: Do I need a "route" statement on SQLTEST so that it can at least
ping the remote IP address that the SQLREMOTE server lives on?
Q3: Knowing "where" things actually run is a little confusing. When I
have a query window open on SQLREMOTE, it seems as if I am "at" the
remote location: I can't do a Bulk Insert using local drive letters,
for example. This is never made quite clear in the documentation that I
have read.
(If I use the tool SQLDelta on my DEV computer, I can link to both
SQLTEST and SQLREMOTE and synchronize data or schemas. This apparently
makes two separate connections and acts as the middleman.)
Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
Thanks for any advice or pointers.
David WalkerHi David,
I understand that you would like to fetch data from SQLREMOTE into SQLTEST
via your DEV computer since it can see both SQLREMOTE and SQLTEST. You
would like to know how to do that.
If I have misunderstood, please let me know.
You can use DTS to export the data from SQLREMOTE to SQLTEST. Open your SQL
Server Enterprise Manager, expand your server and Data Transfermation
Services, create a new package,
drag two Microsoft OLE DB Provider for SQL Server, one for SQLREMOTE as
data source, the other for SQLTEST as data destination, and use Transform
Data Task to link them. After you define the Transform Data Task to
transfer which tables, you can save the package execute it.
Also, if the business logic is complex, you may write a T-SQL statement to
transfer the data. First run sp_addlinkedserver and sp_addlinkedsrvlogin to
add SQLREMOTE and SQLTEST as linked servers in your SQL Server on the DEV
computer. After that, you can query the remote tables on SQLREMOTE and
SQLTEST from DEV like this:
INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3 FROM
SQLREMOTE.testdb.dbo.tableA
Ofcourse, you can also use OPENQUERY for distributed queries. For more
information, please refer to SQL Server BOL.
For your three questions,
Q1: Are "Linked Servers" replacing "Remote Servers"? Do I have to worry
about "Remote Servers" at all?
You just need register a linked server via sp_addlinkedserver and
sp_addlinkedsrvlogin in your SQL Server on DEV. Is this your meanning of
replacing "remote servers"?
Q2: Do I need a "route" statement on SQLTEST so that it can at least ping
the remote IP address that the SQLREMOTE server lives on?
No need. SQLTest has no need to know the existance of SQLREMOTE since DEV
knows.
Q3: Do I actually need for SQLTEST to link to (or "see") SQLREMOTE in order
to perform distributed queries that use data from both servers?
No.
Hope this helps. If you have any other questions or concerns, please feel
free to let me know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:zmDIGfjsHHA.2528@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
> I understand that you would like to fetch data from SQLREMOTE into
> SQLTEST via your DEV computer since it can see both SQLREMOTE and
> SQLTEST. You would like to know how to do that.
> If I have misunderstood, please let me know.
>
[...]

> Also, if the business logic is complex, you may write a T-SQL
> statement to transfer the data. First run sp_addlinkedserver and
> sp_addlinkedsrvlogin to add SQLREMOTE and SQLTEST as linked servers in
> your SQL Server on the DEV computer. After that, you can query the
> remote tables on SQLREMOTE and SQLTEST from DEV like this:
> INSERT INTO SQLTEST.testdb.dbo.tableA SELECT column1, column2, column3
> FROM SQLREMOTE.testdb.dbo.tableA
>
This is the approach I want to take.
BUT, I don't have a SQL server on the DEV computer -- I'm just running
the SQL client tools on the DEV server.

> Hope this helps. If you have any other questions or concerns, please
> feel free to let me know.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
I would like to know how to get the SQLTEST SQL server to see the
SQLREMOTE SQL server.
If you could help me with that, it would be great.
Thanks.
David Walker|||Hi David,
Sorry for the misunderstanding.
I had noticed that you said:
"I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
Management Studio to access several database on the SQLREMOTE server
(default instance) using TCP/IP and an alias that I have set up, with a
specific port number."
I had thought that there was a SQL Server on your DEV computer.
If you do not want to install a SQL Server on your DEV computer, you need
to at least allow one direction of the network communications accessible
from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume that you can
access SQLREMOTE from SQLTest, you can run the T-SQL statement from SQLTest
to fetch data from SQLREMOTE.
Of course you can also develop an application for extracting data from
SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to know
each other.
Appreciate your understanding that our SQL Server newsgroup is focused on
break/fix issue of SQL Server. It seemed that this is a network
configuration issue, if my suggestions here are not your appropriate for
your situation, I recommend that you contact your network administrator or
have a new post at the community microsoft.public.windows.server.networking
for the most appropriate assistance.
If you have any other questions or concerns, please feel free to let us
know.
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||changliw@.online.microsoft.com (Charles Wang[MSFT]) wrote in
news:MbNRiawsHHA.3896@.TK2MSFTNGHUB02.phx.gbl:

> Hi David,
> Sorry for the misunderstanding.
> I had noticed that you said:
> "I can use SQL 2000 Enterprise Manager, Query Analyzer, and SQL 2005
> Management Studio to access several database on the SQLREMOTE server
> (default instance) using TCP/IP and an alias that I have set up, with
> a specific port number."
> I had thought that there was a SQL Server on your DEV computer.
> If you do not want to install a SQL Server on your DEV computer, you
> need to at least allow one direction of the network communications
> accessible from SQLTest to SQLREMOTE or SQLREMOTE to SQLTest. Assume
> that you can access SQLREMOTE from SQLTest, you can run the T-SQL
> statement from SQLTest to fetch data from SQLREMOTE.
> Of course you can also develop an application for extracting data from
> SQLREMOTE to SQLTest. In this case, SQLREMOTE and SQLTest need not to
> know each other.
> Appreciate your understanding that our SQL Server newsgroup is focused
> on break/fix issue of SQL Server. It seemed that this is a network
> configuration issue, if my suggestions here are not your appropriate
> for your situation, I recommend that you contact your network
> administrator or have a new post at the community
> microsoft.public.windows.server.networking for the most appropriate
> assistance.
> If you have any other questions or concerns, please feel free to let
> us know.
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ========================================
=============
Thanks, I got the connection to work by the correct setup of the port
forwarding through the proxy server on my DEV machine.
I have a somewhat-related problem which I will put in a new post...
since I don't think it's a proxy issue.
Thanks for your help.
David Walker