New-AzSqlSyncMember command fails

Question

New-AzSqlSyncMember command fails

Aparna Manoharan 40

Im trying to create a sync member for an on-premises SQL Server database , Im using the command -

New-AzSqlSyncMember -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -SyncGroupName "SyncGroup01" -Name "SyncMember01" -SyncDirection "OneWayMemberToHub" -MemberDatabaseType "SqlServerDatabase" -SqlServerDatabaseId "dbId" -syncAgentResourceGroupName "syncAgentResourceGroupName" -syncAgentServerName "syncAgentServerName" -syncAgentDatabaseName "syncAgentDatabaseName" -syncAgentName "agentName" | Format-List

This command fails with message unrecognised GUID format.

But None of parameters in command expect GUID as paramter.

Is there any paramater Im missing?

Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2024-11-04T18:28:33.2933333+00:00
Hi @Aparna Manoharan,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

It looks like the issue might be related to the SqlServerDatabaseId parameter. This parameter expects a valid GUID format for the SQL Server database ID. If the value provided is not in the correct GUID format, it could trigger the “unrecognized GUID format” error.

Here are a few steps you can take to troubleshoot and resolve the issue:

Ensure that the parameters you are providing, particularly -SqlServerDatabaseId, have valid and correctly formatted values. Incorrectly formatted string representations can lead to GUID-related errors.

Make sure that all the parameters related to the sync agent (-syncAgentResourceGroupName, -syncAgentServerName, -syncAgentDatabaseName, -syncAgentName) are also correct and that they correspond to existing resources.

If you're unsure about the -SqlServerDatabaseId, you can retrieve it by using the Get-AzSqlDatabase command to see if you can obtain the correct ID for the database.

Double-check the Azure PowerShell documentation for New-AzSqlSyncMember to ensure there are no additional parameters or requirements that may be affecting your command.

For more information, please refer the document: https://learn.microsoft.com/en-us/powershell/module/az.sql/new-azsqlsyncmember?view=azps-12.4.0

I hope this information helps. Please do let us know if you have any further queries.
Aparna Manoharan 40 Reputation points

2024-11-04T19:50:47.97+00:00

Im using this command to create sync member for on prem sql db.

I assume SqlServerDatabaseId should be the id of on-prem sql server db.

please help on how to find SqlServerDatabaseId for on Prem sql server

Also is it mandatory to set SyncAgentId parameter? If yes , please help on finding the SyncAgentId on azure portal
Aparna Manoharan 40 Reputation points

2024-11-04T19:51:21.56+00:00

Im using this command to create sync member for on prem sql db.

I assume SqlServerDatabaseId should be the id of on-prem sql server db.

please help on how to find SqlServerDatabaseId for on Prem sql server

Also is it mandatory to set SyncAgentId parameter? If yes , please help on finding the SyncAgentId on azure portal
Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2024-11-04T20:32:49.3366667+00:00

Hi @Aparna Manoharan,

Thanks for the response.

please help on how to find SqlServerDatabaseId for on Prem sql server

Please refer the document: https://www.tsql.info/sql-server/how-to-get-database-id.php

Also is it mandatory to set SyncAgentId parameter? If yes , please help on finding the SyncAgentId on azure portal

Yes, it is mandatory to set the SyncAgentId parameter when configuring Azure SQL Data Sync for an on-premises SQL Server.

For more information, please refer the document: https://learn.microsoft.com/en-us/entra/identity/hybrid/verify-sync-tool-version

I hope this information helps. Please do let us know if you have any further queries.
Aparna Manoharan 40 Reputation points

2024-11-04T21:06:32.0966667+00:00

Yes , I tried to get db_id from on-prem sql server , and it is an integer value

In SQL Server, each database is assigned a unique identifier known as the "database ID" or "DBID". This identifier is an integer value

But you mentioned, SqlServerDatabaseId should have valid and correctly formatted values. Incorrectly formatted string representations can lead to GUID-related error .

what is the correct format value when used in New-AzSqlSyncMember Command?

SqlServerDatabaseId “2”

Is this correct format?
Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2024-11-05T16:27:54.1733333+00:00

Hi @Aparna Manoharan,

Thanks for the response.

The SqlServerDatabaseId parameter in the New-AzSqlSyncMember command does not use the integer database ID from your on-premises SQL Server. Instead, it requires a specific format that uniquely identifies the database in Azure.

For on-premises SQL Server databases, the SqlServerDatabaseId should be the fully qualified domain name (FQDN) of the database server along with the database name, formatted as a string. This is typically in the format "ServerName.DatabaseName".

For example, if your on-premises SQL Server is named MyOnPremServer and the database is named MyDatabase, the SqlServerDatabaseId should be "MyOnPremServer.MyDatabase".

I hope this information helps. Please do let us know if you have any further queries.
Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2024-11-06T17:07:38.0666667+00:00

Hi @Aparna Manoharan,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Mahesh Kurva 10,520 Reputation points Microsoft External Staff Moderator

2024-11-07T17:50:04.5133333+00:00

Hi @Aparna Manoharan,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.