Business Central 140 CU upgrade not support Azure SQL Managed Instance

quocdunginfo · 2021-01-20

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

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
---------------------------

quocdunginfo · 2021-01-20

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)

kriki · 2021-01-27

[Topic moved from 'NAV/Navision Classic Client' forum to 'SQL General' forum]

quocdunginfo · 2021-02-08

Using BACPAC to backup and restore Db => OK

Howdy, Stranger!

Quick Links

Categories

Business Central 140 CU upgrade not support Azure SQL Managed Instance

Answers