Connecting a Remote Sql Server & Accessing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Connecting a Remote Sql Server & Accessing

Connecting a Remote Sql Server & Accessing

Rate Topic

Display Mode

Topic Options

Author

Message

Mohan Kumar VS

Posted Wednesday, December 16, 2009 6:13 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

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

Bru Medishetty

Posted Wednesday, December 16, 2009 7:04 AM

SSCommitted

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

Grant Fritchey

Posted Wednesday, December 16, 2009 8:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375, Visits: 25,159

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

Bru Medishetty

Posted Wednesday, December 16, 2009 9:09 AM

SSCommitted

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

Mohan Kumar VS

Posted Wednesday, December 16, 2009 10:18 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 04, 2012 10:13 PM
Points: 283, Visits: 311

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 »

Permissions