SQL Studio Management - How to run queries across multiple servers

Question

My 2 server both use SQL Server 2008 R2

I have my local SQL server and also an Amazon machine running an instance of SQL-Server there.

I'm able to connect from my local machine to that Amazon SQL using the standard 10.10.10.10, 1433 connection from my local Management Studio.

What i need to do now is to run a query that says ..tells me what records I have locally that are not on the Amazon server right now.

Something like:

 SELECT * 
 FROM [LOCAL].dbo.Table1  
 WHERE Field1 NOT IN   
       (SELECT Field1 FROM [AMAZON].Database1.dbo.Table1)

================================ Question:
I don't know how to write the "AMAZON" location on the Query window itself, since it's running on a different server.

Any help is truly appreciated !!!

Solution 1

You have to configure AMAZON Server as LINKED Server on your local machine. If you name it "AMAZON" - you query will work exactly as you wrote.

Solution 2

In SSMS, \Server Objects\Linked Servers. Right click, 'new linked server'. Name your server, and choose 'SQL server' radio button. Because I was authorized user on both machines with windows credentials, I selected 'Be made using the login's current security context' radio button under the security tab, and did not even have to fool with the local/remote user mappings.

Solution 3

In order to be able to run queries across multiples servers, a link (linked Server) must be established between the 2 Servers. To create a linked server,

1. Navigate to the Linked Server Sub-folder under the Server Object folders
2. Right Click on the Linked Server Folder
3. Click on New Linked Server
4. Supply the Connection Strings for the Server
5. Name your Linked Server.

You can now use the full object qualification (LinkedServer.Database.tableOwner.Table) to access the objects.

Good Luck !