Method to moving SQL Monitor database to Azure SQL Database

fbanville

We would like to move our Redgate SQL Monitor to Azure SQL Database, but it's pretty huge.

What is the simplest method?

fbanville

Additonnal info:

I tried Redgate SQL Data Compare but had connection breakups on the large tables. And it's super slow.

I tried Microsoft Data Migration Assistant and had some strange errors : "The results of a row range query could not be written to the table [Table Name] due to an invalid operation exception raised while writing the batch."

Alex B

Hi @fbanville,

Normally for going from one on premises instance to another it would be taking a backup and restoring it to the new instance, then pointing the Base Monitor service at that.  So perhaps doing this process to restore an on premises SQL Server database to an Azure SQL Database may work https://learn.microsoft.com/en-us/answers/questions/653840/how-to-restore-a-on-premises-sql-server-db-to-azur  

If it's a matter of data size and you are OK losing the data in favour of keeping the configuration and settings, you could first take a backup and then try truncating the largest tables in the data schema and then shrinking the database to see if it will then work.

Otherwise, it would just be creating a new Azure SQL Database and pointing your Base Monitor service at that and starting over as there's not a really good way to migrate settings (either monitored entities or other settings) from one base monitor to another.

This page has more information on moving the repository including what file to update to point the Base Monitor at the new repository location.

Kind regards,
Alex

fbanville

Thank you for your response, Alex.

Unfortunately, your link is to deploy the schema (DACPAC) only and not the data (BACPAC).

The problem really is the quantity of data being moved. I'll see if we can afford to lose the data tables.

Thanks.