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

7
  • 1
    Check this out: sitecore.stackexchange.com/questions/23527/… Dec 20 '19 at 12:53
  • I tried to change compatibility level to 130 and it's still the same issue 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. 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 Jan 5 '20 at 22:48
  • Assuming changing the compatibility level still doesn't work, then probably sitecore support is a good next step. Jan 6 '20 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.