1

I have a super small (no data, just a few single column tables, I'm just testing) on premise SQL server database that I'm trying to do an online migration for, to a SQL Managed Instance.

As far as I know I've configured everything as it should be - backup files are present in the file share, and the DMS is set up and can see both the SQL Server and the Managed Instance. However, it doesn't restore anything. It's stuck saying "log shipping in progress":

log shipping in progress

If I look at the managed instance itself, I can see a database has been created, and is currently in "Restoring" status.

My question is: how can I resolve this?

Maybe there are other logs I can look at, or there's some other permissions thing I don't know about, or something else?

I've tried creating a new project from scratch, but it had the same issue. And I've tried waiting... but I don't think it's working. As I mentioned, this is a DB with only a few tables (maybe 4), a single column in each table, no data at all.

David_001
  • 5,703
  • 4
  • 29
  • 55
  • Are you sure there's a problem? The target database will be in "Restoring" until you complete the migration. – David Browne - Microsoft Feb 22 '21 at 20:17
  • 2
    @DavidBrowne-Microsoft yes there's an issue because I can't click the "start cutover", and the Last Applied ... items are all blank - this indicates the data has not yet been restored into the target – David_001 Feb 22 '21 at 21:19

2 Answers2

1

Looking at your image, looks like there is no issue with DMS connecting to backup location and uploading backup and log files.

What is interesting is the field for last backup file applied and last applied LSN is empty. Makes me think there is some issue on the SQL MI machine.

I would recommend to open a case with MS Support on this.

One other thing you can try is to do a manual failover and your machine will failover to secondary node and then run the DMS job again.

https://techcommunity.microsoft.com/t5/azure-sql/user-initiated-manual-failover-on-sql-managed-instance/ba-p/1538803

Also try to take a look at what is going on with the SQL MI with any blocking.

You can use sp_whoisactive which is the latest version and it works on Azure SQL MI https://github.com/amachanic/sp_whoisactive/releases/tag/v11.35

Rizwan
  • 318
  • 1
  • 5
  • Thanks Rizwan - looking in the MI actually I can't see any blocking, but I can see in the logs that the restore operations completed successfully (both the full restore and the log restore). So now I think the issue is in how DMS is getting the status back from MI – David_001 Feb 22 '21 at 21:35
  • Did you get a chance to close the browser and log back into Azure and then check the status. Sometime it doesn't refresh automatically – Rizwan Feb 23 '21 at 04:51
  • I abandoned it in the end as despite started new jobs they all stalled with the "log shipping in progress" message and I could never start the cutover. I had left this for > 24 hours and viewed in different browsers etc. There was no option for me to create a support request on my subscription level, so I just clicked the link to email feedback, indicating that there's not enough error logging or reporting to diagnose errors, and the statuses are inadequately documented at https://learn.microsoft.com/en-us/azure/dms/how-to-monitor-migration-activity – David_001 Mar 04 '21 at 01:39
0

You need to assign permission to the service principal running the migration service at subscription level:

az role assignment create --assignee [YOUR SERVICE PRINCIPAL] --role contributor
Dharman
  • 30,962
  • 25
  • 85
  • 135
Alan Borsato
  • 248
  • 2
  • 13