Azure-rest-api-specs: SQL Data Sync: cannot create Sync Group with version 2015-05-01-preview

[manicminer]

Resource: https://management.azure.com/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/{databaseName}/syncGroups/{syncGroupName}

API Version: 2015-05-01-preview

Encountering an issue when creating a SQL Data Sync Group via API, that doesn't occur when creating via the Portal. When a Sync Group is created in the Portal, it's possible to PATCH it to add the schema property, but if the Sync Group is created via the published API, the PATCH operation fails.

Works via Portal

1. Create a SQL Database Server and two databases (a hub DB and a sync DB).
2. Via the "Sync to other databases" pane, create a new sync group and complete the first of 3 wizard pages. This will send the initial PUT request.
3. In pages 2 and 3, enter the DB credentials and select some tables and columns for the sync schema.
4. Note the corresponding PATCH requests to separately add the credentials and the schema.

To Reproduce

1. Send the observed PUT and 2 x PATCH requests from above to create and update a sync group via API version 2015-05-01-preview (noting the resource schema matches the swagger except for one property, see Other Observations below).
2. After the final PATCH request to add the schema, receive the following error:

{
    "error": {
        "code": "SyncOperation_GenericFailure",
        "message": "Failed to perform data sync operation: '' is invalid schema master"
    }
}

1. Back in the Portal, complete steps 1 and 2 from above, then attempt to send the 2 x PATCH requests to amend the sync group credentials and schema.
2. Observe no error upon updating the schema property.

Other Observations

When creating a sync group via the portal, it includes an undocumented property hubDatabaseId which is set to the resource ID of the hub database. The same database that forms part of the sync group resource ID. Including this property in the PUT request to the API doesn't appear to mitigate the issue.

Example Requests

PUT /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest-sql/providers/Microsoft.Sql/servers/aaatest/databases/test1/syncGroups/syncQ?api-version=2015-05-01-preview

{
    "properties": {
        "name": "syncQ",
        "interval": -1,
        "conflictResolutionPolicy": "HubWin",
        "syncDatabaseId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest-sql/providers/Microsoft.Sql/servers/aaatest/databases/sync",
        "hubDatabaseId": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest-sql/providers/Microsoft.Sql/servers/aaatest/databases/test1"
    }
}

```http
PATCH /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest-sql/providers/Microsoft.Sql/servers/aaatest/databases/test1/syncGroups/syncQ?api-version=2015-05-01-preview

{
"properties": {
"hubDatabasePassword": "Qwer5678",
"hubDatabaseUsername": "admino"
}
}

```http
PATCH /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest-sql/providers/Microsoft.Sql/servers/aaatest/databases/test1/syncGroups/syncQ?api-version=2015-05-01-preview

{
    "properties": {
        "schema": {
            "masterSyncMemberName": "",
            "tables": [
                {
                    "columns": [
                        {
                            "quotedName": "[ProductID]"
                        },
                        {
                            "quotedName": "[ProductNumber]"
                        },
                        {
                            "quotedName": "[Color]"
                        }
                    ],
                    "quotedName": "[SalesLT].[Product]"
                }
            ]
        }
    }
}

[msftbot[bot]]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @azureSQLGitHub.

[xccc-msft]

@manicminer Thanks for sharing the observations. Routing to SQL service team.

[fbuser1]

Any updates?

Same error:

    "message": "Failed to perform data sync operation: '' is invalid schema master"

even for 2019-06-01-preview api

[danikaZ]

Hi, the example request has masterSyncMemberName empty

"masterSyncMemberName": ""

Please refer to the REST API link to add masterSyncMemberName and retry.
https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/createorupdate#syncgroupschema

[ericshape]

@fbuser1 we will close this issue. If you have more feedback, feel free to reopen it. Thanks!

[manicminer]

@ericshape I am the issue reporter and this does not resolve the bug, please reopen the issue.

@danikaZ Thanks for your reply. For clarity, I have tried setting masterSyncMemberName to various values (the documentation is very unclear on what this is supposed to be) but it doesn't seem to make any difference.

I think it's worth nothing that when configuring a sync group using the Azure Portal, the request made by the portal also specifies a blank string for this property. For example (pulled directly from the portal):

{
  "properties": {
    "schema": {
      "tables": [
        {
          "quotedName": "[SalesLT].[Product]",
          "columns": [
            {
              "quotedName": "[ProductID]"
            },
            {
              "quotedName": "[ProductNumber]"
            },
            {
              "quotedName": "[Color]"
            }
          ]
        }
      ],
      "masterSyncMemberName": ""
    }
  }
}

I'm not convinced this is the source of the actual issue, but I'm open to any help you could provide in supplying the expected values since the documentation is unclear.

[manicminer]

It has been awhile since I've worked on this, but I've resurrected my earlier test setup and can confirm I have tried the following values for masterSyncMemberName:

• `` (a blank string, just as the portal specifies)
• test1 (the name of the hub DB)
• aaatest/test1 (servername/dbname)
• aaatest/aaatest/test1 (resourcegroup/servername/dbname)
• aaatest/aaatest/databases/test1 (resourcegroup/servername/databases/dbname)
• /subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/aaatest/providers/Microsoft.Sql/servers/aaatest/databases/test1 (resource ID of the hub db)

Each results in the error:

{
    "error": {
        "code": "SyncOperation_GenericFailure",
        "message": "Failed to perform data sync operation: 'XYZ' can't be found as sync group member"
    }
}

where XYZ is the attempted value for masterSyncMemberName.

Note again the reproduction steps in the initial report, where if you first create the sync group in the portal, it can then be updated without error via the API.

[danikaZ]

Hi, I agree with doc is very confusing. The same API is shared by Create and Update sync group.

Let me start from the beginning for creation steps:

1.Use template to create sync group and member. When creating sync group, do not pass in the sync schema (tablecolumn), do not pass in masterSyncMemberName, because at this time sync group does not have tablecolumn information yet.
(This is equivalent to Portal create sync group.)

1. After sync group is created correctly, refresh schema (either from hub or member),
   For hub
   https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/refreshhubschema
   for member
   https://docs.microsoft.com/en-us/rest/api/sql/syncmembers/refreshmemberschema

3 Update sync group by specifying the sync schema. (here pls include your schema and masterSyncMemberName, masterSyncMemberName is the name which holds the schema you want to use)
https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/createorupdate

[manicminer]

Hi @danikaZ, many thanks that's really helpful.

I've tried to sequence my API requests as you described, but I'm getting a 500 error when trying to set the schema (after successfully refreshing the schema from the hub DB). I'm using the AdventureWorksLT sample. Note that I've tried the second PUT request having the schema, both with masterSyncMemberName omitted and set to "" (blank string) - with the same 500 error either way.

I've made a gist of the API conversation, with requests and responses in order: https://gist.github.com/manicminer/27e2c4dfdb809bc4303c4a19a2716d7b

Note that I ensured the LRO polls returned 200 after each request. I left in the correlation IDs in case it helps you trace it.

[manicminer]

3 Update sync group by specifying the sync schema. (here pls include your schema and masterSyncMemberName, masterSyncMemberName is the name which holds the schema you want to use)
https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/createorupdate

Can you clarify what masterSyncMemberName is supposed to be? Is it the name of the hub database? The name of one of the members? The sync DB? When administering this through the portal, I only see this set to a blank string.

[danikaZ]

Hi, based on your description and example, assuming you already finished step 1 and step 2.

If so, in step 3, "masterSyncMemberName is the name which holds the schema you want to use".
If you want to use the schema from your hub db, (guessed based on u mentioned refreshed schema from hub db), then pls put down your hub db name in "masterSyncMemberName". So "syncHub210227163016265419" in your example.

I'm having trouble finding the request trace with request id: 646e83fd-ff1e-1fde-6033-be602ea10873. Also seems like the server does exist either.
ping acctestsqlserver210227163016265419.database.windows.net
Ping request could not find host acctestsqlserver210227163016265419.database.windows.net. Please check the name and try again.

If you need further help, please add your server name and region.
If you have security concern, please create a support ticket (should be free :D) with your server name, region, sync group name.

[ericshape]

@manicminer we are closing this issue. if you have more questions, please open a new one. Thanks!

[manicminer]

@ericshape Please can you reopen this issue? Why did you close it when the issue isn't resolved? I am still getting a 500 error from the API, I would hope that this is considered a valid bug report since I'd hope not to see that unless the service had an issue.

@danikaZ Thanks for your reply, sorry for the few days delay in getting back to you. I have retried as per my last message, and also with masterSyncMemberName set to the name of the hub database, but I am still getting a 500 error just as in my gist link above.

This time I have left the databases intact and have not submitted any further API requests against those database since receiving the 500 error. Here's the response complete with headers if it helps you isolate it.

HTTP/1.1 500 Internal Server Error
Cache-Control: no-cache
Pragma: no-cache
Content-Length: 162
Content-Type: application/json; charset=utf-8
Expires: -1
x-ms-request-id: 9f1bea4a-553b-4304-b988-4149358191d5
Server: Microsoft-HTTPAPI/2.0
x-ms-failure-cause: service
x-ms-ratelimit-remaining-subscription-writes: 1197
x-ms-correlation-request-id: 29d3441c-b51c-c1e8-aaba-8ca2aa49d28f
x-ms-routing-request-id: UKSOUTH:20210308T182356Z:3e85645b-28cd-4f64-aeb2-5e190b37d824
Strict-Transport-Security: max-age=31536000; includeSubDomains
X-Content-Type-Options: nosniff
Date: Mon, 08 Mar 2021 18:23:56 GMT
Connection: close

{
  "error": {
    "code": "InternalServerError",
    "message": "An unexpected error occured while processing the request. Tracking ID: '9f1bea4a-553b-4304-b988-4149358191d5'"
  }
}

EDIT:

Location: West Europe
Resource group: acctestRG-mssql-210308181838699406
Server name: acctestsqlserver210308181838699406

[danikaZ]

@manicminer Hello again! No worries, take your time, and thanks for chasing this :)
I took a look at your sync group and didn't see any schema on hub. (meaning step2 failed or not done)

Can you please try step 2 first again, before going to step3? (more details below)

1. After sync group is created correctly, refresh schema (either from hub or member),

Our PM is still working on a REST API example page, meanwhile, I find the steps in Powershell (PS) example very useful: https://docs.microsoft.com/en-us/azure/azure-sql/database/scripts/sql-data-sync-sync-data-between-sql-databases (please ignore all the private link related commands in PS for now, let's get your happy path to work first :) )

Let me extend the three steps including the corresponding PS commands (truncate some PS parameters for better formatting) for easier understanding:

1. Use template to create sync group and member. When creating sync group, do not pass in the sync schema (tablecolumn), do not pass in masterSyncMemberName, because at this time sync group does not have tablecolumn information yet.
   (This is equivalent to Portal create sync group.)

   -- Equivalent to PS command
   New-AzSqlSyncGroup -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -Name $syncGroupName

2. After sync group is created correctly, refresh schema (either from hub or member). Please do not continue until _list hub schema_ return successfully.
   For hub (you need this link)
   https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/refreshhubschema

   --Equivalent to PS command
   Update-AzSqlSyncSchema -ResourceGroupName $resourceGroupName -ServerName $serverName -DatabaseName $databaseName -SyncGroupName $syncGroupName

   To check refresh schema completed successfully (pls give it 90 seconds after refresh schema command before checking, refresh schema can take some time). For _list hub schema_
   https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/listhubschemas

   --Equivalent to PS command
   Get-AzSqlSyncSchema -SyncGroupName $syncGroupName -ServerName $serverName -DatabaseName $databaseName -ResourceGroupName $resourceGroupName

   Rest API for member (in case you use schema from a member in the future. Ignore for now.)
   https://docs.microsoft.com/en-us/rest/api/sql/syncmembers/refreshmemberschema
   https://docs.microsoft.com/en-us/rest/api/sql/syncmembers/listmemberschemas

3. Update sync group by specifying the sync schema. (here pls include your schema and masterSyncMemberName, masterSyncMemberName is the name which holds the schema you want to use)
   https://docs.microsoft.com/en-us/rest/api/sql/syncgroups/createorupdate

   --Equivalent to PS command
   Update-AzSqlSyncGroup -ResourceGroupName $resourceGroupName -ServerName $serverName `
   -DatabaseName $databaseName -Name $syncGroupName -Schema $tempFile