Link server and Distributed Query

The linked server provides SQL server with the ability to access data from a remote data source. By using the linked server, you can perform queries, modify data, and execute remote process calls. Remote Data sources can be similar (that is, the data source is another SQL server instance) or heterogeneous (from other relational database products and data sources, such as DB2, access, Oracle, Excel, and text files ). Cross-platform queries are used to connect or retrieve data across multiple platforms. Cross-platform queries allow you to access legacy database systems without merging and migrating existing data sources.

Use the ole db access interface to connect to a remote data source. Developed by Microsoft, ole db is a set of COM (Component Object Model) interfaces that provide consistent access to different data sources. To establish access from an SQL Server 2005 instance to another data source, select an appropriate OLE DB access interface. The design of the ole db access interface depends on the Distributed Query (Select,Insert, Update, or delete stored procedure execution.

In summary, the linked server means establishing a connection to a remote data source. The dependency is the ole db driver used to set the link server. You can perform distributed queries to retrieve data or perform operations in a remote data source.

Distributed queries can also run without defining the linked server. For example-The SQL function OpenRowSet. OpenRowSet is enhanced in SQL Server 2005 to allow bulk to read ASCII, Unicode, and binary files. OpenRowSet and bulk can be used to read Table-like data from text files, or use it to import files of the ASCII, Unicode, or binary type to a single big data type column and a single row (such as varchar (Max),Nvarchar(Max) Or varbinary (Max)).

It is also a new feature in SQL Server 2005. Microsoft introduces the synonym object, which allows you to reference long name objects with short names. It is usually useful when using long identifiers, but it is particularly useful when using short names to replace data sources in Distributed queries that reference 4-part linked server names.

This chapter includes these tips: create a link server, execute distributed queries, use OpenRowSet and bulk to read from text files, and use new synonym objects.

27.1Link server Basics
The following tips show you how to use a linked server. Specifically, I will show you how to do the following:

L create a linked server to connect to another SQL Server 2005 instance.

L configure the properties of the linked server.

L view the configuration link Server Information on the SQL server instance.

L Delete the linked server.

I will first discuss how to use the system stored procedure sp_addmediaserver to create a new linked server.

27.1.1Create a linked server for another SQL server instance
The linked server allows you to query external data sources from an SQL server instance. External data sources can be different SQL Server instances or non-SQL server data sources, such as Oracle, MS Access, DB2, or MS Excel.

Use the system stored procedure sp_addmediaserver to create a linked server. Syntax:

Table 27-1 describes the system stored procedure parameters.

Table 27-1Sp_addmediaserver Parameters

Parameter
Description

Server
The local name of the linked server. You can also use the Instance name, for example, myserver \ sql1.

Product_name
The product name of the ole db data source. For SQL Server instances, product_name is ' SQL Server '
 
Provider_name
This is the unique programmable identifier of the ole db access interface. If it is not specified, the access interface name is the SQL server data source. The explicit provider_name of SQL Server is sqlncli (Microsoft SQL native client OLE DB Provider ). Oracler is msdaora, and Oracle 8 or later is oraoledb. Oracle. MS access and MS Excel are Microsoft. Jet. oledb. 4.0 . Db2oledb for IBM DB2 and msdasql for ODBC Data sources
 
Data_source
This is the data source interpreted by the specific ole db access interface. For SQL Server, This is the network name of SQL Server (servername or servername \ InstanceName. For Oracle, this is SQL * . Net alias. For MS Access and MSExcel, This is the complete path and name of the file. For ODBC data sources, this is the system DSN name
 
Location
Location explained by the specific ole db access interface
 
Provider_string
The connection string specified by the ole db access interface. For ODBC connections, this is an ODBC connection string. For MS Excel, this is Excel 5.0
 
Catalog
The definition change of catalog is based on the implementation of the ole db access interface. For SQL Server, this is an optional database name. For DB2, this directory is the database name.
 

In the network environment of Multiple SQL Server instances, the linked server provides a convenient way to share SQL server data, instead of sharing data by physically pushing or pulling back data and copying the architecture.

Note: In this chapter, I explain the examples of communication between SQL Server instances. Parameters for heterogeneous data sources, such as DB2, access, and Oracle, will change significantly. For a more comprehensive explanation of the sp_addmediaserver option, see the topic "sp_addmediaserver (TRANSACT-SQL )".

The configuration used to connect to a heterogeneous data source changes based on the ole db access interface. If you connect to different SQL Server instances, Microsoft makes it easy. In this example

In this example, I created a linked server to connect to another SQL server instance:

You can also create a connection server that connects to the SQL Server naming instance, for example:

Analysis

Add a connection server to external data sources to allow distributed queries (Distributed queries will be introduced later in this chapter ). When you add an SQL server connection server to an SQL server instance, Microsoft makes it simple, regardless of whether it is a default or a named instance. You only need the server and product_name values.

What is the security method for connecting to an SQL server instance? When creating a new linked server, use the current user's login name Security credential (SQL or Windows) to link to the linked server. You can also create the displayed remote login name ing for the linked server, which will be discussed later.

27.1.2Configure link server Properties
After creating a linked server, there are many different settings that can be used to configure it. Table 27-2 describes these settings.

Table 27-2Link server Properties

Parameter
Description

Collation compatible
If you confirm that the SQL server instance and remote SQL server have the same sorting rule, enable this setting. Since SQL Server no longer needs to compare character columns between data sources, it assumes they are the same sorting rules, which can improve performance

Collation name
If use remote Collation is enabled and is not a data source of SQL Server, collation name specifies the name of the remote server sorting rule. The sorting rule name must be supported by SQL Server.

Connect timeout
Specify the number of seconds to connect to the linked server before the timeout occurs. If the value is"0 ", SP _ configure's remote query timeout server value is used as the default value
 
Data Access
If enabled, Distributed Query access is allowed.
 
Lazy Schema Validation
If it is set to true, the architecture does not detect remote tables at the beginning of the query. Although this reduces the load on remote queries, if the architecture changes and you do not perform architecture detection, for example, the objects referenced in the query cannot communicate with the query command, an error is generated.
 
Query timeout
Specifies the timeout value (in seconds) for the query wait ). If the value is 0, the query wait option uses the sp_configure value.
 
RPC
Enable remote process call on the slave server
 
RPC out
Enable Remote Procedure Call to the server
 
Use Remote collation
Whether to use the remote server sorting rule (true) or the local server sorting rule (false)
 

Use the system stored procedure sp_serveroption to modify the link server attributes. Syntax:

Table 27-3. describes the system stored procedure parameters.

Table 27-3Sp_serveroption Parameter

Parameter
Description
 
Server
Name of the linked server in the Configuration Attribute
 
Option_name
Options to be configured
 
Option_value
New Value of the option
 

In this tip, the query timeout setting of the linked server joeprod \ node2 will be changed to 60 seconds:

Analysis

In this tip, the query timeout limit for the linked server joeprod \ node2 is changed to 60 seconds. The first parameter called server specifies the name of the linked server. The second parameter option_name specifies the option to be configured, and the third parameter option_value configures the new value.

27.1.3View linked server information
You can use sysservers in the system directory view to view the linked servers defined in the SQL server instance. For example:

It returns:

Analysis

Sysservers can be used to retrieve information about linked servers defined in SQL Server instances. Other options that can be viewed from SYS. servers include product, provider, data_source, location, provider_string, catalog, is_linked, delimiter, delimiter, is_data_access_enabled, delimiter, use_remote_collation, and collation_name. In the query, the is_linked column only returns the linked server (excluding the local SQL server instance settings ).

27.1.4Delete linked server
The sp_dropserver is used to delete the linked server. The syntax of sp_dropserver is as follows:

Table 27-4. describes the system stored procedure parameters.

Table 27-4Sp_dropserver Parameters

Parameter
Description
 
Server
Name of the linked server deleted from the SQL server instance
 
Droplogins
If droplogins is specified, You need to delete the login name ing before deleting the linked server (described later in this chapter)
 

This tip shows how to delete a linked server:

Analysis

This tip shows how to use the system stored procedure sp_dropserver to delete a linked server from your SQL server instance. The droplogins option specified in the second parameter will delete the existing login name ing before deleting the linked server (I will introduce the link server logon in the following tips ). If you try to delete the linked server before deleting the login, you will get the following message:

There are still'Joeprod'.