About linked servers

Linked servers give SQL Server the ability to read data from remote data sources and execute commands on remote databases. This page provides information about Cloud SQL instance permissions, things to consider when implementing linked servers, and the limitations of using linked servers.

Linked server instance permissions

To set up linked servers, you need the following instance and user permissions:

  • ALTER ANY LINKED SERVER: Cloud SQL grants this permission when you add the cloudsql enable linked servers flag to your instance.
  • ALTER ANY LOGIN: Cloud SQL automatically grants this permission to the administrative user for the Cloud SQL instance. This permission allows you to create a mapping between a login on Cloud SQL and a security account on a linked server.

For a list of the default permissions available for Cloud SQL instances, see Default SQL Server users.

For information about how to grant the ALTER ANY LINKED SERVER or ALTER ANY LOGIN permission to additional accounts, see the example at Granting server permissions.

Considerations when using linked servers

This section has recommendations for handling security, performance, and reliability on linked servers.

Security

To mitigate potential security vulnerabilities, you need to ensure that linked servers have strong security protocols in place, such as the following:

  • Implement appropriate authentication and authorization mechanisms, such as SSL encryption.
  • Implement strict password policies.
  • Ensure that only authorized users have access to the linked server.
  • Ensure that sensitive data is properly encrypted and secured.
  • Ensure that logins only have access to the resources that you use during the configuration.

Performance

There are a few factors that might impact performance on linked servers:

  • Linked servers can introduce latency and degrade performance when queries are complex, depending on the volume of data each server transfers. This can cause problems for applications that require real-time data synchronization.
  • When you use linked servers to run a query, a database engine creates a new connection and fetches data for every query you run. Data isn't cached and the server might copy the entire table across the network every time you use a linked server.
  • Cloud SQL can't create an optimized execution plan for the remote server because it doesn't know how the remote server handles indexes and statistics.

To optimize performance, do the following:

  • Carefully design your queries and ensure that only the necessary data is being transferred between servers.
  • Leverage the SQL Server distributed queries feature.
  • Distribute queries across multiple servers.
  • Regularly monitor and optimize your linked server configuration to help ensure that it's running smoothly.

Reliability

Any interruptions or failures in the link between servers can lead to significant downtime and potentially cause data loss.

To ensure reliable operation, it's important to implement appropriate failover and redundancy mechanisms. This can include the following:

  • Implement a backup linked server for failover.
  • Monitor server availability and performance.
  • Regularly test and validate your linked server configurations.

Limitations

Linked servers for Cloud SQL have the following limitations:

  • Linked servers can't use data sources other than SQL Server.
  • Active Directory Authentication isn't available for linked Servers.

For a full list of limitations for linked servers, see Unsupported features & services.

What's next