How to run T-SQL in another DB server?

Question

I want to select some data form another database in another server.

What I know is add a Linked Server, and run the T-SQL.

Is this way is a good choice, or is there any other way to run SQL in other database servers?

thanks.

Answer 1

Adding a linked server is fine.

You will then probably use the 4 part naming convention

select * from [server].[database].[Owner or Schema].[tablename]

Or you could look at OPENROWSET (Transact-SQL)

Includes all connection information that is required to access remote data from an OLE DB data source. This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB. For more frequent references to OLE DB data sources, use linked servers instead.

Answer 2

How

• use sp_addlinkedserver and then define security using sp_addlinkedsrvlogin

The upsides

• Can distribute load across multiple servers

The downsides

• Additional network / communication / security overhead
• Additional operational overhead - users, logins etc need to be added to both servers
• Fragility - you are coupling code in your local database to a named external server / database. However, you can use synonyms to loosen the coupling.

Alternatives

• Use Replication / Mirroring / Log shipping to get a 'local' copy of the database on the same server (although this also has the downside of additional IO and memory consumption on the local server or instance, plus additional issues dealing with data synchronisation)
• Use 2 connection strings in your app and do the data manipulation in the app.

But as astander says, there is no real technical issue in accomplishing cross server sql.