इसके माध्यम से साझा किया गया


sys.sp_rda_test_connection (Transact-SQL)

Applies to: SQL Server 2016 (13.x) and later versions

Tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.

Syntax

EXECUTE sys.sp_rda_test_connection
    @database_name = N'db_name'
    , @server_address = N'azure_server_fully_qualified_address'
    , @azure_username = N'azure_username'
    , @azure_password = N'azure_password'
    , @credential_name = N'credential_name'
[ ; ]

Arguments

@database_name = N'db_name'

The name of the Stretch-enabled SQL Server database. This parameter is optional.

Important

Stretch Database is deprecated in SQL Server 2022 (16.x) and Azure SQL Database. This feature will be removed in a future version of the Database Engine. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

@server_address = N'azure_server_fully_qualified_address'

The fully qualified address of the Azure server.

  • If you provide a value for @database_name, but the specified database isn't Stretch-enabled, then you have to provide a value for @server_address.

  • If you provide a value for @database_name, and the specified database is Stretch-enabled, then you don't have to provide a value for @server_address. If you provide a value for @server_address, the stored procedure ignores it and uses existing Azure server already associated with the Stretch-enabled database.

@azure_username = N'azure_username'

The user name for the remote Azure server.

@azure_password = N'azure_password'

The password for the remote Azure server.

@credential_name = N'credential_name'

Instead of providing a user name and password, you can provide the name of a credential stored in the Stretch-enabled database.

Return code values

In case of success, sp_rda_test_connection returns error 14855 (STRETCH_MAJOR, STRETCH_CONNECTION_TEST_PROC_SUCCEEDED) with severity EX_INFO and a success return code.

In case of failure, sp_rda_test_connection returns error 14856 (STRETCH_MAJOR, STRETCH_CONNECTION_TEST_PROC_FAILED) with severity EX_USER and an error return code.

Result set

Column name Data type Description
link_state int One of the following values, which correspond to the values for link_state_desc.

- 0
- 1
- 2
- 3
- 4
link_state_desc varchar(32) One of the following values, which correspond to the preceding values for link_state.

- HEALTHY
The between SQL Server and the remote Azure server is healthy.
- ERROR_AZURE_FIREWALL
The Azure firewall is preventing the link between SQL Server and the remote Azure server.
- ERROR_NO_CONNECTION
SQL Server can't make a connection to the remote Azure server.
- ERROR_AUTH_FAILURE
An authentication failure is preventing the link between SQL Server and the remote Azure server.
- ERROR
An error that's not an authentication issue, a connectivity issue, or a firewall issue is preventing the link between SQL Server and the remote Azure server.
error_number int The number of the error. If there's no error, this field is NULL.
error_message nvarchar(1024) The error message. If there's no error, this field is NULL.

Permissions

Requires db_owner permissions.

Examples

Check the connection from SQL Server to the remote Azure server

EXEC sys.sp_rda_test_connection @database_name = N'<Stretch-enabled database>';
GO

The results show that SQL Server can't connect to the remote Azure server.

link_state link_state_desc error_number error_message
2 ERROR_NO_CONNECTION <connection-related error number> <connection-related error message>

Check the Azure firewall

USE <Stretch-enabled database>
GO
EXECUTE sys.sp_rda_test_connection;
GO

The results show that the Azure firewall is preventing the link between SQL Server and the remote Azure server.

link_state link_state_desc error_number error_message
1 ERROR_AZURE_FIREWALL <firewall-related error number> <firewall-related error message>

Check authentication credentials

USE <Stretch-enabled database>
GO
EXECUTE sys.sp_rda_test_connection;
GO

The results show that an authentication failure is preventing the link between SQL Server and the remote Azure server.

link_state link_state_desc error_number error_message
3 ERROR_AUTH_FAILURE <authentication-related error number> <authentication-related error message>

Check the status of the remote Azure server

USE <SQL Server database>
GO
EXECUTE sys.sp_rda_test_connection
    @server_address = N'<server name>.database.windows.net',
    @azure_username = N'<user name>',
    @azure_password = N'<password>';
GO

The results show that the connection is healthy and that you can enable Stretch Database for the specified database.

link_state link_state_desc error_number error_message
0 HEALTHY NULL NULL