I’m on the hook to do a session at the Manchester, NH Sitecore User Group next month and the topic will be a deep-dive into diagnostics for Azure PaaS Sitecore implementations. This is an absolutely huge surface area for a one hour talk, so I have a lot of flexibility in where to focus. One decision I made was to use a Sitecore Commerce PaaS sandbox as the foundation for the session. We’ll dive into the Azure Redis, Azure SQL, Azure Search, Azure App Services, and all the other PaaS goodness that comprise this Sitecore 9 Commerce in PaaS implementation I create.
I haven’t worked with the Azure Marketplace for a Sitecore PaaS implementation in about a year, so I worked through that interface to create a shiny new Sitecore Commerce environment . . .
. . . it’s a straight-forward process and after a few minutes I was patiently waiting for my new Azure PaaS environment to be ready.
Issue #1
Then some bad news: Azure’s interface reported that the Sitecore PaaS deployment had failed after about 30 minutes of work.
DeploymentFailed: At least one resource deployment operation failed. Please list deployment operations for details
I’ll post the full error at the end of the post, in case it helps a web searcher to find the resolution. For brevity, I scanned the details of the failure in JSON format and picked out this nugget:
ERROR_SQL_EXECUTION_FAILURE. ---> System.Data.SqlClient.SqlException: Incorrect syntax near 't'.
I’ve done enough Sitecore and PowerShell automation for this to set off familiar alarm bells. This looked like a character encoding or invalid escape sequence issue — like where an & (ampersand) or another “special” character was invalidating the script. Several of the inputs in the Sitecore Marketplace forms are password fields, and like a good computer worker I used a strong password (like from https://www.grc.com/passwords.htm for example). One of my special character’s was a single quote (‘), to it really had me wondering about the password fields.
Regardless, I tried a second time, thinking maybe my Azure resources had a transient issue; I picked different Azure regions for the resources, because occasionally I’ve seen the same operation succeed with one Azure region but fail for another. Unfortunately, this second attempt also failed with the same type of exception.
For my third attempt, I tried using a weak password, just a simple alphanumeric like “123abc”– and the provisioning succeeded. It took almost 2 hours to run to completion, but I didn’t hit any failures. I was able to update the passwords to something more secure afterwards, since the automation creating the PaaS resources is a one-time execution, so I don’t have to live long term with the rather weak password.
I reached out to Sitecore Support and they confirmed it’s a defect they’re tracking under reference number 320324. I suggested the put a bit of validation in the marketplace module, then, because surely everyone is running into this?
Issue #2
The next issue I encountered was the Sitecore Commerce Catalog wasn’t properly bootstrapped into the PaaS environment. The Sitecore CM showed no catalogs in any of the usual places:
Sitecore support confirmed this behaviour and explained it’s an oversight of the installation routines. In the Azure Marketplace setup, I correctly selected the sample Habitat catalog with the SXA components etc. To fix this problem, I needed to “clean an initialize” the environment myself to include the sample catalog. With IaaS deployments, this magic is handled by SIF routines (go down that rabbit hole at https://github.com/CommerceMinion/Sitecore-Commerce-v902-Scaled-Installation if you’re brave).
These two blog posts were particularly helpful as I worked through the manual PostMan initialization process:
- https://tothecore.sk/2018/07/25/setting-up-development-environment-with-postman-and-sitecore-experience-commerce-sxc-9/
- https://naveed-ahmad.com/2018/02/21/sitecore-experience-commerce-xc9-getting-started-with-postman/
Many thanks to the authors https://tothecore.sk/about/ and https://naveed-ahmad.com/!
Finally, from Issue #1, here’s the full details of the provisioning failure message related to the password:
{“code\”: \”Conflict\”,”message\”: \”{\\r\\n “status”: “failed”,\\r\\n “error”: {\\r\\n “code”: “ResourceDeploymentFailure”,\\r\\n “message”: “The resource operation completed with terminal provisioning state ‘failed’.”,\\r\\n “details”: [\\r\\n {\\r\\n “code”: “Failed”,\\r\\n “message”: “Package deployment failed\AppGallery Deploy Failed: ‘Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: “1” and “43”. The verbose log might have more information about the error. The command started with the following:\”declare @ApplicationName nvarchar(256) = ‘sitecore”\ Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’. http://go.microsoft.com/fwlink/?LinkId=178587 Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. —> System.Data.SqlClient.SqlException: Incorrect syntax near ‘t’.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘xs’ has already been declared. Label names must be unique within a query batch or stored procedure.\The label ‘sql’ has already been declared. Label names must be unique within a query batch or stored procedure.\Unclosed quotation mark after the character string ‘\ ) from (select @Salt as [bin] ) T \execute [dbo].[aspnet_Membership_SetPassword] \ @ApplicationName\ ,@UserName\ ,@EncodedHash\ ,@EncodedSalt\ ,@CurrentTimeUtc\ ,@PasswordFormat\’.\ at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\ at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\ at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\ at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)\ at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)\ at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ — End of inner exception stack trace —\ at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sqlStatement, DeploymentBaseContext baseContext, Int32 timeout)\ at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)\ at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)\ at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\ at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)\ at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)\ at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)\ at Microsoft.Web.Deployment.WebApi.AppGalleryPackage.Deploy(String deploymentSite, String siteSlotId, Boolean doNotDelete)\ at Microsoft.Web.Deployment.WebApi.DeploymentController.<DownloadAndDeployPackage>d__17.MoveNext()
Sitecore Architecture 