Linked servers - SQL Server to Aurora MySQL Migration Playbook
SQL Server UsageMySQL Usage

Linked servers

This topic provides reference information about linked servers in Microsoft SQL Server and their absence in Amazon Aurora MySQL-Compatible Edition. You can understand the functionality and benefits of linked servers in SQL Server, including their ability to connect to external data sources and run distributed queries.

Feature compatibility AWS SCT / AWS DMS automation level AWS SCT action code index Key differences

No feature compatibility

No automation

Linked Servers

Data transfer across schemas only, use a custom application solution to access remote instances.

SQL Server Usage

Linked servers enable the database engine to connect to external Object Linking and Embedding for databases (OLE-DB) sources. They are typically used to run T-SQL commands and include tables in other instances of SQL Server, or other RDBMS engines such as Oracle. SQL Server supports multiple types of OLE-DB sources as linked servers, including Microsoft Access, Microsoft Excel, text files and others.

The main benefits of using linked servers are:

  • Reading external data for import or processing.

  • Running distributed queries, data modifications, and transactions for enterprise-wide data sources.

  • Querying heterogeneous data source using the familiar T-SQL API.

You can configure linked servers using either SQL Server Management Studio, or the system stored procedure sp_addlinkedserver. The available functionality and the specific requirements vary significantly between the various OLE-DB sources. Some sources may allow read only access, others may require specific security context settings, and so on.

The linked server definition contains the linked server alias, the OLE DB provider, and all the parameters needed to connect to a specific OLE-DB data source.

The OLE-DB provider is a .NET Dynamic Link Library (DLL) that handles the interaction of SQL Server with all data sources of its type. For example, OLE-DB Provider for Oracle. The OLE-DB data source is the specific data source to be accessed, using the specified OLE-DB provider.

Note

You can use SQL Server distributed queries with any custom OLE DB provider as long as the required interfaces are implemented correctly.

SQL Server parses the T-SQL commands that access the linked server and sends the appropriate requests to the OLE-DB provider. There are several access methods for remote data, including opening the base table for read or issuing SQL queries against the remote data source.

You can manage linked servers using SQL Server Management Studio graphical user interface or T-SQL system stored procedures.

  • EXECUTE sp_addlinkedserver to add new server definitions.

  • EXECUTE sp_addlinkedserverlogin to define security context.

  • EXECUTE sp_linkedservers or SELECT * FROM sys.servers system catalog view to retrieve meta data.

  • EXECUTE sp_dropserver to delete a linked server.

You can access linked server data sources from T-SQL using a fully qualified, four-part naming scheme: <Server Name>.<Database Name>.<Schema Name>.<Object Name>.

Additionally, you can use the OPENQUERY row set function to explicitly invoke pass-through queries on the remote linked server. Also, you can use the OPENROWSET and OPENDATASOURCE row set functions for one-time remote data access without defining the linked server in advance.

Syntax

EXECUTE sp_addlinkedserver
    [ @server= ] <Linked Server Name>
    [ , [ @srvproduct= ] <Product Name>]
    [ , [ @provider= ] <OLE DB Provider>]
    [ , [ @datasrc= ] <Data Source>]
    [ , [ @location= ] <Data Source Address>]
    [ , [ @provstr= ] <Provider Connection String>]
    [ , [ @catalog= ] <Database>];

Examples

Create a linked server to a local text file.

EXECUTE sp_addlinkedserver MyTextLinkedServer, N'Jet 4.0',
    N'Microsoft.Jet.OLEDB.4.0',
    N'D:\TextFiles\MyFolder',
    NULL,
    N'Text';

Define security context.

EXECUTE sp_addlinkedsrvlogin MyTextLinkedServer, FALSE, Admin, NULL;

Use sp_tables_ex to list tables in a folder.

EXEC sp_tables_ex MyTextLinkedServer;

Issue a SELECT query using a four-part name.

SELECT *
FROM MyTextLinkedServer...[FileName#text];

For more information, see sp_addlinkedserver (Transact-SQL) and Distributed Queries Stored Procedures (Transact-SQL) in the SQL Server documentation.

MySQL Usage

Amazon Aurora MySQL-Compatible Edition doesn’t support remote data access.

Connectivity between schemas is trivial, connectivity to other instances will require an application custom solution.