As per my understanding Azure SQL MI runs SQL Server under the hood. So a backup from MI can be restored on latest version of SQL Server (2019). I have a task to find a rollback strategy if the move to MI is called off after the migration.
-
Took backup from MI
-
Tried restore to SQL Server 2019 (15.0.2070.41)
I expected it a 50/50 chance of success but it failed. The error however is a bit misleading :
Msg 3169, Level 16, State 1, Line 2 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.2070. Either restore the
database on a server that supports the backup, or use a backup that is
compatible with this server. Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally
If we can't go back smoothly to server we got the backup from then how do we plan the rollback. The bacpac/dacpac method is a it of pain when db size is 30GB and you get loads of errors.
-
Why is that error coming up? Need to know whats running in MI
-
Shall we reconsider moving to MI, with the pain of not having an easy/smooth rollback.
PS: AWS RDS provides versions which makes it much better as you have the option to just rollback if something goes wrong in the cutover.
.
Best Answer
Currently the only way to restore a database from Azure to OnPrem is the bacpac method.
Also FYI: I am currently looking at Azure MI migration but due to the fact you can't point a DNS CNAME at it I am unable to Migrate.