1

I am trying to install Sitecore 9.2 vanilla on Azure with SQL Managed Instance but I am getting the following error

{
"status": "failed",
"error": {
    "code": "ResourceDeploymentFailure",
    "message": "The resource operation completed with terminal provisioning state 'failed'.",
    "details": [
        {
            "code": "Failed",
            "message": ".Net SqlClient Data Provider: Msg 5008, Level 16, State 14, Line 5 This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.\r\n.Net SqlClient Data Provider: Msg 5008, Level 16, State 14, Line 5 This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.\r\nScript execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\nScript execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\n.Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.\r\n.Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.\r\nScript execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\nScript execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\nPackage deployment failed\r\nAppGallery Deploy Failed: 'Microsoft.Web.Deployment.DeploymentDetailedClientServerException: Could not deploy package.\r\nWarning SQL0: A project which specifies SQL Server 2016 as the target platform may experience compatibility issues with SQL Server 2014.\r\nWarning SQL72030: The project and target databases have different collation settings. Deployment errors might occur.\r\nError SQL72014: .Net SqlClient Data Provider: Msg 5008, Level 16, State 14, Line 5 This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.\r\nError SQL72045: Script execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\nError SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.\r\nError SQL72045: Script execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\n\r\n  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_METHOD. ---> Microsoft.SqlServer.Dac.DacServicesException: Could not deploy package.\r\nWarning SQL0: A project which specifies SQL Server 2016 as the target platform may experience compatibility issues with SQL Server 2014.\r\nWarning SQL72030: The project and target databases have different collation settings. Deployment errors might occur.\r\nError SQL72014: .Net SqlClient Data Provider: Msg 5008, Level 16, State 14, Line 5 This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.\r\nError SQL72045: Script execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\nError SQL72014: .Net SqlClient Data Provider: Msg 5069, Level 16, State 1, Line 5 ALTER DATABASE statement failed.\r\nError SQL72045: Script execution error.  The executed script:\r\nIF EXISTS (SELECT 1\r\n           FROM   [master].[dbo].[sysdatabases]\r\n           WHERE  [name] = N'$(DatabaseName)')\r\n    BEGIN\r\n        ALTER DATABASE [$(DatabaseName)]\r\n            SET ANSI_NULLS ON,\r\n                ANSI_PADDING ON,\r\n                ANSI_WARNINGS ON,\r\n                ARITHABORT ON,\r\n                CONCAT_NULL_YIELDS_NULL ON,\r\n                QUOTED_IDENTIFIER ON,\r\n                ANSI_NULL_DEFAULT ON,\r\n                CURSOR_DEFAULT LOCAL,\r\n                RECOVERY SIMPLE \r\n            WITH ROLLBACK IMMEDIATE;\r\n    END\r\n\r\n\r\n\r\n   at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()\r\n   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()\r\n   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)\r\n   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)\r\n   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)\r\n   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)\r\n   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)\r\n   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)\r\n   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)\r\n   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)\r\n   --- End of inner exception stack trace ---\r\n   at Microsoft.Web.Deployment.SqlReflectionHelper.ExecuteFunction(Object targetObject, String methodName, Object[] argArray)\r\n   at Microsoft.Web.Deployment.DacServices.ProcessDacpac(Stream packageStream, DacPacAction action)\r\n   at Microsoft.Web.Deployment.SqlDacPacProvider.Add(DeploymentObject source, Boolean whatIf)\r\n   at Microsoft.Web.Deployment.DeploymentObject.Add(DeploymentObject source, DeploymentSyncContext syncContext)\r\n   at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAdd(DeploymentObject destObject, DeploymentObject sourceObject)\r\n   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildren(DeploymentObject dest, DeploymentObject source)\r\n   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)\r\n   at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)\r\n   at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable, Nullable`1 syncPassId, String syncSessionId)\r\n   at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)\r\n   at Microsoft.Web.Deployment.WebApi.AppGalleryPackage.Deploy(String deploymentSite, String siteSlotId, Boolean doNotDelete)\r\n   at Microsoft.Web.Deployment.WebApi.DeploymentController.<DownloadAndDeployPackage>d__24.MoveNext()"
        }
    ]
}

}

However, I was able to deploy previously using SQL PaaS.

Is it because Sitecore does not support Azure SQL Managed Instance or something is changed recently?

I check the collation which is SQL_Latin1_General_CP1_CI_AS and COMPATIBILITY_LEVEL is 150.

I am using Sitecore Azure Toolkit for installation with some customisation to reference the correct resourceId for managed instance.

Any help, please.

Thanks

  • 1
    Check this out: sitecore.stackexchange.com/questions/23527/… – Dave Goosem Dec 20 '19 at 12:53
  • I tried to change compatibility level to 130 and it's still the same issue – Tech Learner Dec 22 '19 at 9:32
  • Please add more detail of how you are deploying. You've tagged SIF and PaaS which are not compatible with each other. You should be using Sitecore Azure Toolkit. – Mark Gibbons Dec 22 '19 at 21:19
  • @MarkGibbons you are correct, I am using the Sitecore Azure Toolkit and further customization was required for SQL MI to reference correct resourceId – Tech Learner Jan 5 at 22:48
  • Assuming changing the compatibility level still doesn't work, then probably sitecore support is a good next step. – Mark Gibbons Jan 6 at 0:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.