Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Connecting a Remote Sql Server & Accessing Expand / Collapse
Author
Message
Posted Wednesday, December 16, 2009 6:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
Hi

I need to connect two different sql servers across different geographical locations (For eg. one server in UK & other in US).

After the servers are connected, the users should be able to join 2 or more tables from those two remote servers for querying.

Kindly let me know the method.

Rgds
Mohan Kumar VS
Post #835024
Posted Wednesday, December 16, 2009 7:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947
You need to setup Linked Server and use 4 part Notation, ex: LinkedServer.Database.Schema.Table in your queries.

Search Google for Linked Server in SQL Server and you will find plenty of example how to setup.



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #835047
Posted Wednesday, December 16, 2009 8:28 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:39 PM
Points: 15,618, Visits: 27,998
However, joining tables across servers, let alone across oceans, can be very problematic from a performance standpoint. Frequently with linked servers you'll see all the data from the first server moved across to the second as part of the join operation. You might want to reevaluate the solution to determine if you can move the data between the servers in some fashion in order to avoid having to join between them.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #835135
Posted Wednesday, December 16, 2009 9:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:27 AM
Points: 1,735, Visits: 1,947
Agree with Grant's opinion. Linked Servers tend to slow down the query. But it is the only way where you can connect 2 different SQL Servers across different machines.

Depending on your requirement, you may need to evaluate other ways of moving the data to local system and performing the required task.



Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.com\LearnSQLWithBru

Twitter -- BruMedishetty
Post #835211
Posted Wednesday, December 16, 2009 10:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 1, 2013 2:51 AM
Points: 283, Visits: 312
Thanks for the suggestions. I understand that this has to be done through linked servers (sp_addlinkedsrvlogin) . But I'm unable to get exact example for connecting to remote server (at a different location). I searched BOL & Google. Kindly give me an example how to connect it.

Rgds
Mohan Kumar VS
Post #835558
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse