Error when creating azure sql managed link from sql server

Question

   "statusMessage": "{\"status\":\"Failed\",\"error\":{\"code\":\"ResourceOperationFailure\",\"message\":\"The resource operation completed with terminal provisioning state 'Failed'.\",\"details\":[{\"code\":\"InternalServerError\",\"message\":\"An unexpected error occured while processing the request. Tracking ID: '5634d03d-6390-406f-84ea-cf9d3def20f4'\"}]}}",

trying to create a link between sql server and azure managed instance link and getting the above issue

in the past, restarting the azure managed instance allowed it to work again however i have several azure linked servers already on it and they would need to be deleted first before retarting and redone which isnt an option right now

any help would be appreciated

Answer 1

@Chris Riley

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer. Accepted answers show up at the top, resulting in improved discoverability for others.

Issue: Issue with creating a link between SQL Server and Azure SQL Managed Instance due to an internal server error

Solution:

1. Creating Availability Groups (AG) and Distributed Availability Groups (DAG):
   • Manually create the AG on the primary SQL server using a script:

          CREATE AVAILABILITY GROUP [AG_] WITH (CLUSTER_TYPE = NONE) FOR DATABASE [] REPLICA ON N'' WITH (ENDPOINT_URL = N'tcp://:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SEEDING_MODE = AUTOMATIC);
     

GO ```

• Create the DAG on the primary SQL server:

       CREATE AVAILABILITY GROUP [DAG_] WITH (DISTRIBUTED) AVAILABILITY GROUP ON N'AG_' WITH (LISTENER_URL = N'tcp://:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC), N'AG__MI' WITH (LISTENER_URL = N'tcp://:5022;Server=[]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC);
  

GO ```

1. Upgrading Azure Console PowerShell:
   • Upgrade to the latest version of Azure PowerShell (version 13):

          Install-Package -Name Az -force
     

2. Executing Azure Console PowerShell Script:
   • Use the New-AzSqlInstanceLink command to create the link:

          New-AzSqlInstanceLink -ResourceGroupName "" -InstanceName  -Name $DAGName -Database  -InstanceAvailabilityGroupName  -PartnerAvailabilityGroupName  -InstanceLinkRole "Secondary" -PartnerEndpoint $SourceIP -FailoverMode "Manual" -SeedingMode "Automatic"
     

This approach successfully seeded the primary database to the managed instance secondary and started the replication process.

Answer 2

the error occurs when using SSMS trying to link a primary sql database to an azure managed instance secondary

after many hours and hundreds of failed attempts using SSMS, we figured out how to do it thru the azure console

example

1. creating the ag and dag on the primary manually on the primary SQL server via script:

CREATE AVAILABILITY GROUP [AG_<databasename>] WITH (CLUSTER_TYPE = NONE) FOR DATABASE [<databasename>] REPLICA ON N'<servername>' WITH (     ENDPOINT_URL = N'tcp://<serverip>:5022',     FAILOVER_MODE = MANUAL,     AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,     SEEDING_MODE = AUTOMATIC);

GO

CREATE AVAILABILITY GROUP [DAG_<databasename> WITH (DISTRIBUTED) AVAILABILITY GROUP ON   N'AG_<databasename>' WITH (LISTENER_URL = N'tcp://<databaseserverip>:5022', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC),   N'AG_<databasename>_MI' WITH (LISTENER_URL = N'tcp://<managedinstanceurl>:5022;Server=[<managedinstancename>]', AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL, SEEDING_MODE = AUTOMATIC);

GO

2. upgrading the azure console powershell to the latest version (version 13) with :

Install-Package -Name Az -force

3. executing the azure console powershell script/command:
   New-AzSqlInstanceLink -ResourceGroupName "<managedinstancegroup>" -InstanceName <ManagedInstanceName> -Name $DAGName -Database <databasename> -InstanceAvailabilityGroupName <AGNameOnSQLMI> -PartnerAvailabilityGroupName <AGNameOnSQLServer> -InstanceLinkRole "Secondary" -PartnerEndpoint $SourceIP -FailoverMode "Manual" -SeedingMode "Automatic"

this then seeded the primary database to the managed instance secondary and started the replication