A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
Linked servers offer the following advantages:
1. Remote server access.
2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
3. The ability to address diverse data sources similarly.
With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.
-------------------------------------------------------------------------------
What are Linked Servers?
A Linked Server is a Link to an external (remote) data source. The remote data source can be Oracle, Access, Excel or any other data source that uses an OLE DB provider.
What are Remote Servers?
- Remote servers do not use OLE DB providers
- A remote server configuration allows a client connected to one instance of SQL Server to execute a stored procedure on another instance of SQL Server without establishing another connection
- Both stored procedures and distributed queries are allowed against linked servers; however, only stored procedures are allowed against remote servers.
- Use Linked Servers instead of Remote Servers. Remote servers are for backward compatibility only.
Note: Linked servers can run remote stored procedures for SQL Server and cannot run remote stored procedures for Oracle.
Adding Linked Servers
Syntax
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]
Please refer BOL for syntax and more info
Example: To add Oracle as a linked server to SQL Server:
This example creates a linked server named OraLinkServ that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is OraProduction.
USE master GO EXEC sp_addlinkedserver @server = 'OraLinkServ', @srvproduct = 'Oracle', @provider = 'MSDAORA', @datasrc = 'OraProduction' GO
Adding Linked Server Login
Sp_addlinkedsvrlogin
Creates or updates a mapping between logins on the local instance of SQL Server and remote logins on the linked server.
Syntax
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
Please refer BOL for syntax and more info
Querying Linked Server
The following are the various methods of querying different linked servers.
LinkedServerName.Dbname.Owner.ObjectName
Select * from LinkSqlServ.Northwind.dbo.Employees
Select * from OraLinkServ..SCOTT.Stores
Select * from LinkMdb...customers
OpenQuery: - Executes the specified pass-through query on the given linked server, which is an OLE DB data source.
Syntax:
OpenQuery ( linked_server, 'query' )
Examples:
SELECT * FROM OPENQUERY(OraLinkServ, 'SELECT Name, Title FROM Scott.Titles') INSERT OPENQUERY(LinkSqlServ, 'select * from pubs.dbo.jobs') values (15, 'Technical Editor', 100, 300)
OpenRowSet: - This method is an adhoc method of connecting and accessing remote data using OLE DB. It creates linked server on the fly.
Syntax
OPENROWSET ( 'provider_name'
, { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query' }
)
Removing Linked Server and its Logins
Sp_dropserver - Removes a server from the list of known remote and linked servers on the local SQL Server.
Sp_droplinkedsvrlogin: Removes an existing mapping between a login on the local server running SQL Server and a login on the linked server.
Obtaining Meta data
Please refer BOL for complete syntax.
Sp_tables_ex: Returns table information about the tables from the specified linked server.
sp_columns_ex: Returns the column information, one row per column, for the given linked server table(s). sp_columns_ex returns column information only for the given column if column is specified.
sp_table_privileges_ex: Returns privilege information about the specified table from the specified linked server.
sp_column_privileges_ex: Returns column privileges for the specified table on the specified linked server.
Sp_linkedservers:Returns the list of linked servers defined in the local server.
Sp_helpserver: Reports information about a particular remote or replication server, or about all servers of both types. Provides the server name, the server's network name, the server's replication status, the server's identification number, collation name, and time-out values for connecting to, or queries against, linked servers.
Sysservers: Contains one row for each server that SQL Server can access as an OLE DB data source. This table is stored in the master database.
Sysoledbusers: Contains one row for each user and password mapping for the specified linked server. This table is stored in the master database.
xp_enum_oledb_providers: Provides information about OLEDB providers.
Sp_serveroption: Sets server options for remote servers and linked servers.
Sp_serveroption has been enhanced with two new options, use remote collation and collation name, that support collations in linked servers.
Sp_setnetname: Sets the network names in sysservers to their actual network computer names for remote instances of SQL Server. This procedure can be used to enable execution of remote stored procedure calls to computers that have network names containing invalid SQL Server identifiers.
What is a Linked Server?
Think of a Linked Server as an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC--including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a "Link Table."
Why use a Linked Server?
With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.
While it would be convenient to have all of our business data in one place, there are too many obstacles such as Vendor applications built for a specific data store, data sets too large for one server, legacy flat file applications that are cost prohibitive to recreate and changing business standards, preventing this from happening.
"Replication Manager" has made moving data from one SQL Server to another on a regular basis relatively easy. However, duplicating data to an application server is not always the best solution. If your source is large, and you cannot predict what subset of data you will need, then a linked server may be a better solution.
If you have a very large data set, there may be performance benefits to splitting your data into pieces, and moving those pieces onto different servers. Then using distributed partitioned views to present the data as one source. If so, linked servers are the technology that makes it possible.
Why not use a Linked Server?
If the remote data is not yours, and the owning department will not allow you remote access, then a linked server is out. You will have to rely on some type of scheduled pickup and exchange.
When absolute, best possible performance is required, local data will out perform a linked server.
If the physical link between your SQL Server and the remote data is slow, or not reliable, then a linked server is not a good solution.
History
Linked servers are a superset of "remote servers." Remote servers allowed the running of stored procedures on distributed SQL Server machines. SQL 2000 BOL states, "Support for remote servers is provided for backward compatibility only. New applications that must execute stored procedures against remote instances of SQL Server should use linked servers instead." Support for remote servers may be discontinued in the future. In addition, remote servers only allowed stored procedures to be run. Linked servers allow both stored procedures and ad hoc queries.
Distributed Transaction Coordinator (DTC)
Before starting the examples, we need to start the Distributed Transaction Coordinator. The DTC manages the committing of transactions when there are several different data sources involved. For Windows 2000, service pack 1 is required.
+ Open the services MMC, locate and start the Distributed Transaction Coordinator using the default settings.
I am making assumptions that remote server is already set up as linked server with proper permissions in application and network is arranged.
Method 1 : Remote Stored Procedure can be called as four part name:
Syntax:
EXEC [RemoteServer] .DatabaseName.DatabaseOwner.StoredProcedureName
'Params'
Example:
EXEC [DEVSQL] .AdventureWorks.dbo.uspGetEmployeeManagers '42′
Method 2 : Use Distributed Query functions (OpenQuery())
Syntax: SELECT *
FROM OPENQUERY([RemoteServer],DatabaseName.DatabaseOwner.StoredProcedureName)
Example: SELECT *
FROM OPENQUERY([DEVSQL],AdventureWorks.dbo.spAdmin_GetAllUsers)
Let me know if you want me write about above two methods in detail.
No comments:
Post a Comment