Linked Servers

Unlike remote servers, linked servers have two simple setup steps:

1. Define the remote server on the local server.

2. Define the method for mapping remote logins on the local server.

Notice that the configuration is performed on the local server. The mapping for the local user to the remote user is stored in the local SQL Server database. In fact, you don't need to configure anything in the remote database. This setup allows SQL Server to use OLE DB to link to datasources other than other SQL Servers.

OLE DB is an API that allows COM applications to work with databases as well as other datasources such as text files and spreadsheets. This lets SQL Server have access to a vast amount of different types of data.

Unlike remote servers, linked servers also allow distributed queries and transactions.

Truly Linked

Keep in mind that when you complete linked servers, SQL Server really keeps these data resources linked in many ways. Most importantly, it keeps the schema definitions linked. In other words, if the schema of a remote table on a linked server changes, any server that has links to it also knows the change. Even when the linked server's schema comes from something such as Excel, if you change the Excel spreadsheet in any way, that change will be automatically reflected back at the local SQL Server. This is extremely significant from a metadata (schema's) integrity point of view. This is what is meant by "completely linked!"

Distributed Queries

Distributed queries access data stored in OLE DB datasources. SQL Server treats these datasources as if they contained SQL Server tables. Due to this treatment, you can view or manipulate this data using the same basic syntax as other SQL Server SELECT , INSERT , UPDATE , or DELETE statements. The main difference is the table-naming convention. Distributed queries use this basic syntax when referring to the remote table:

  linked_server_name.catalog.schema.object_name  

The following query accesses data from a sales table in an Oracle database, a region table in a Microsoft Access database, and a customer table in a SQL Server database:

 SELECT s.sales_amount  FROM access_server...region AS r, oracle_server..sales_owner.sale AS s, sql_server.customer_db.dbo.customer AS c where r.region_id=s.region_id and s.customer_id=c.customer_id and r.region_name='Southwest' and c.customer_name='ABC Steel' 

Distributed Transactions

Distributed transactions are supported if the OLE DB provider has built in the functionality. This means it is possible to manipulate data from several different datasources in a single transaction. For example, suppose two banks decide to merge. The first bank (let's call it OraBank) stores all checking and savings accounts in an Oracle database. The second bank (let's call it SqlBank) stores all checking and savings accounts in a SQL Server 2000 database. A customer has a checking account with OraBank and a savings account with SqlBank. What would happen if a customer wants to transfer $100 from the checking account to the savings account? You can do it simply using the following code while maintaining transactional consistency. The transaction is either committed or rolled back on both databases:

 BEGIN DISTRIBUTED TRANSACTION  -- One hundred dollars is subtracted from the savings account. UPDATE oracle_server..savings_owner.savings_table  SET account_balance = account_balance - 100 WHERE account_number = 12345 -- One hundred dollars is added to the checking account. UPDATE sql_server.checking_db.dbo.checking_table  SET account_balance = account_balance + 100 WHERE account_number = 98765 COMMIT TRANSACTION; 

For more information on distributed transactions, see Chapter 32, "Distributed Transaction Processing."