Split-merge tool error. MSDN resolution does not help

Zaman Bakshi 101

Hi,

We would want to move shardlets from one database to another and would like to use split-merge tool. We were able to successfully deploy the split-merge tool using cloud service (classic) as mentioned here: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-configure-deploy-split-and-merge

We are facing the following issue: [Exception] System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'dbo.InsertRequest'.

And the resolution you have provided in the same MSDN web page "In this case, check your configuration file, in particular the setting for WorkerRoleSynchronizationStorageAccountConnectionString. This error typically indicates that the worker role could not successfully initialize the metadata database on first use." does not work. I literally copy and paste the storage account connection string from Azure portal, so the connection string is not a problem.

Moreover, I see that the split-merge tool does not create any such stored procedure on start, nor can I find this stored procedure in the elastic scale library source code.

Can you please help?
At least can you please list the sequence of elastic scale client library method calls that we need to move a shardlet from one database to another.

Zaman Bakshi 101 Reputation points

2021-08-19T07:05:08.447+00:00

HI Saurabh,
Thanks for reply.
I do not see any such stored procedure.
Also I found out that the tool is using a deprecated version for storage library to get the blob (for locking). I tried writing a simple console application with the same code and it fails. I am interested to know how are you able to run this?

The error I get is that the tool is unable to create a blob because the library (SDK) is sending a BAD REQUEST to the Azure Storage API.

Can you please tell me the format of storage account connection string you are using?
Zaman Bakshi 101 Reputation points

2021-08-19T07:44:10.157+00:00

@Saurabh Sharma are you using storage emulator to test this?

The sharding database environment was created using the powershell scripts that are provided as part of the shrading library .
Zaman Bakshi 101 Reputation points

2021-08-19T09:19:54.307+00:00

IIn addition, the source code of the elastic scale client library has no mention of the stored procedure. I have looked in code files and resource files.
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-19T19:34:39.827+00:00

Hi @Zaman Bakshi ,
Thanks for sharing details. I already had the shard databases available which I have created using the Elastic Database Tools for .NET. I have not tried using the PowerShell scripts for Sharding environment preparation but I will try to check if it works or not.

Thanks
Saurabh
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-19T19:35:05.72+00:00

Hi @Zaman Bakshi ,
Thanks for sharing details. I already had the shard databases available which I have created using the Elastic Database Tools for .NET. I have not tried using the PowerShell scripts for Sharding environment preparation but I will try to check if it works or not.

Thanks
Saurabh
Zaman Bakshi 101 Reputation points

2021-08-20T08:43:19.493+00:00

HI Saurabh,
It still does not work using the ElasticScaleStarterKit. I get the same error.
Just to check I also created "classic" storage account. But it was of no help.
Are you using storage emulator to test by any chance instead of actual storage account on Azure?

I hope Microsoft releases the source code for this tool so that we can rebuild it.
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-20T17:59:40.563+00:00

Hi @Zaman Bakshi ,

ok, strange. I am not using storage emulator and using an Azure Storage account for diagnostics output.
I am checking internally on this one and get back to you.

Thanks
Saurabh
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-20T18:15:24.067+00:00

Hi @Zaman Bakshi ,

Also, you are looking at the wrong database. Please check the Database which you have used in Split-Merge Service configuration. (see screenshot below)

Are you using the shared server as your split merge db server ?

Thanks
Saurabh
Zaman Bakshi 101 Reputation points

2021-08-20T18:50:33.263+00:00

Hi Saurabh,
Thank you for being so patient with me.
Yes I am using the same database where there is shardmanager as the split merge database. Is there a requirement to create a separate database?
I believe the tables and stored procedure names for both the manager and the tool are different so there should not be any conflicts !!
Also, after taking your advice I ran the sample application but it did not create insertrequest stored procedure. Also, Do you mean to write that this stored procedure is created by the tool and not the shardmanager?
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-20T18:59:14.307+00:00

Hi @Zaman Bakshi ,
No, I think that should be fine but wanted to ask so that I can also try to replicate how you are currently using the tool.
I believe the procedure is created while using the split-merge service creation process (probably deploying the cloud service) and not with the ElasticScaleStarterKit tool which creates shard databases and shardmanager. If this procedure is not available then I suspect your deployment had some issues.
Can you please check Operation log of your cloud service for any errors.

Thanks
Saurabh
Zaman Bakshi 101 Reputation points

2021-08-20T19:20:04.027+00:00

I have checked it. No errors, all green :-)
But two days back when I RDP-ed into worker role and looked at event logs I could see the blob creation errors I mentioned earlier
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-20T22:02:17.18+00:00

@Zaman Bakshi ok. Looks like some issues while setting the worker role. Can you please check if are taking connection string of storage account from portal as per the screenshot and passing it WorkerRoleSynchronizationStorageAccountConnectionString.

<Setting name="WorkerRoleSynchronizationStorageAccountConnectionString" value="DefaultEndpointsProtocol=https;AccountName={ACCOUNT_NAME};AccountKey={KEY};EndpointSuffix=core.windows.net" />

Also, can you try creating another cloud service instance and try to deploy your split-merge service.

Thanks
Saurabh
Zaman Bakshi 101 Reputation points

2021-08-23T07:22:36.44+00:00
Hi Saurabh,
I am pretty sure that I have the same format connection string; in addition I copy pasted from the portal.

Can you tell me which of the following you chose to deploy your service?

Storage Account or Storage Account (Classic)

Cloud Service or Cloud Service (Extended Support)

I will redeploy me cloud service.
Zaman Bakshi 101 Reputation points

2021-08-23T08:18:45.873+00:00
Hi,
This is what I did.
Deployed two Azure Cloud Services, one using storage account and other with storage account classic/legacy
I receive same error in both scenarios:

With classic storage account

With normal storage account
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-23T20:53:40.533+00:00

@Zaman Bakshi This is strange, I am using Storage Account and using Cloud service (classic) and it works fine. We probably need to open a support ticket so that someone could look into your environment. Also, if you have any limitations opening support ticket please let me know.

Thanks
Saurabh

Accepted answer

Zaman Bakshi 101 Reputation points

2021-09-20T07:43:08.557+00:00

Hi Saurabh,
Thanks. The resolution given to change the TLS version to 1.0 works and worker role is able to create the necessary stored procedures.
Will I be able to deploy this tool as cloud service (extended support) instead of cloud service (classic) which is going to be deprecated in Aug 2024?
Br,
Zaman
Please sign in to rate this answer.

1 person found this answer helpful.
Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-09-20T16:15:43.763+00:00

Hi @Zaman Bakshi ,

Great. Thanks for sharing the resolution over here.

Thanks
Saurabh
Sign in to comment

1 additional answer

Saurabh Sharma 23,671 Reputation points Microsoft Employee

2021-08-18T22:52:50.043+00:00

Hi @Zaman Bakshi ,

Thanks for using Microsoft Q&A !!
You can find [dbo].[InsertRequest] stored procedure in your Elastic Scale Metadata database which you have configured in the ServiceConfiguration.cscfg file under <Setting name="ElasticScaleMetadata" settings. (Please find below the screenshot)

ServiceConfiguration.cscfg

Config Database

Can you please check if your metadata database has the stored procedure created ?

I have tested in my environment using the steps mentioned in the document and I could see Shardlet Move has completed successfully for me.

Thanks
Saurabh
Please sign in to rate this answer.

0 comments No comments
Sign in to comment