sys.sp_rda_test_connection (Transact-SQL)

• 06/10/2016
• 2 minutes to read
• • 
  • 
  • 
  • 
  • 
  • +3

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

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.

@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 is not 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 Sets

Permissions

Requires db_owner permissions.

Examples

Check the connection from SQL Server to the remote Azure server

EXECUTE 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.

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.

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.

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.