Business Central 140 CU upgrade not support Azure SQL Managed Instance

quocdunginfoquocdunginfo Member Posts: 8
edited 2021-01-27 in SQL General
I am going to upgrade BC140 CU 11 to CU 20 (In order to apply some platform bugs fixed by microsoft)
Error occured when open Db in step of converting Db to new CU version
"ALTER DATABASE ... SET SINGLE_USER is not supported"
Database is hosted in Azure SQL Managed Instance (aka SQL MI)
After a few research, I realized that SQL MI does not support mode differ than MULTI_USER
Ref link: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#alter-database-statement
6bq8ekdcqtzp.png

With the same process, SQL Server get success. So SQL MI is the point
I think upgrade toolkit had embeded "ALTER DATABASE ... SET SINGLE_USER" in function flow which could not be touched
In this case, my proposed solution look like:
1. Restore Db to SQL Server
2. Upgrade new CU
3. Re-attach to SQL MI

It would be graceful if anyone else faced or share experiences when working with SQL MI, thank much!

Full error msg:
---------------------------
Microsoft Dynamics NAV Development Environment
---------------------------
The following SQL Server error or errors occurred: 5008,"42000",[Microsoft][SQL Server Native Client 11.0][SQL Server]This ALTER DATABASE statement is not supported. Correct the syntax and execute the statement again.
5069,"42000",[Microsoft][SQL Server Native Client 11.0][SQL Server]ALTER DATABASE statement failed.

 

SQL:
ALTER DATABASE [Db_Name] SET SINGLE_USER
---------------------------
OK
---------------------------

Answers

  • quocdunginfoquocdunginfo Member Posts: 8
    Restore Db from SQL MI to SQL Server raise error
    Restore of database 'Db_Name' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
    
     
    
    ------------------------------
    Program Location:
    
     
    
       at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
       at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
       at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)
    
     
    
    ===================================
    
     
    
    System.Data.SqlClient.SqlError: The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 15.00.2000. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.SmoExtended)
    
     
    
    ------------------------------
    For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.44091.28+(SMO-master-A)&LinkId=20476
    
     
    
    ------------------------------
    Program Location:
    
     
    
       at Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
       at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
    
  • krikikriki Member, Moderator Posts: 9,033
    [Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • quocdunginfoquocdunginfo Member Posts: 8
    Using BACPAC to backup and restore Db => OK
Sign In or Register to comment.