Business Central 140 CU upgrade not support Azure SQL Managed Instance

quocdunginfo
Member Posts: 8
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:
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 ---------------------------
0
Answers
-
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)
0 -
[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!0 -
Using BACPAC to backup and restore Db => OK0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions