0

Hello folks first post in stack, btw wonderful community and helps out a lot.

like mentioned in the title what is the best way to copy such a large database? we got an ~ 500 GB Database and im currently moving this database from managed instance to a azure single database using smss:smss copy via deploy to microsoft azure sql database and it takes me right now 22 hours. i feel like im back in early 20s.

it's all in the same subscription and also in the same network configuration. afaik the process of that is that smss creates a bacpac file and then import it back to the single database. but 16 hours is just too long. so do you know any better option to do this quicker because i've a hell of more and partly larger databases to copy.

RedbeardIntegrator
  • 3
  • 1
  • When you say, "In the same network configuration", does that mean you're running a VM in that VNet? If so, have you looked at `sqlpackage.exe`? – WaitingForGuacamole Mar 11 '21 at 22:57
  • What Service Tier have you configured SQL Database to be? You will likely be seeing some Log Rate governance in play - more so at the lower tiers. I would look to boost the tier to the highest level while running this. Also as @WaitingForGuacamole has suggested, use a VM in Azure and run sqlpackage.exe rather than SSMS. Some more pointers at https://learn.microsoft.com/en-us/azure/azure-sql/database/database-import-export-hang?WT.mc_id=DP-MVP-5001259 – Martin Cairney Mar 11 '21 at 23:14
  • Did you think about using ETL tools, such as [Data Factory](https://learn.microsoft.com/en-us/azure/data-factory/introduction)? It has good performance to migrate the big data. – Leon Yue Mar 12 '21 at 05:54
  • not running a vm in that vnet, we have a different vm within the same subscription but in another vlan but as far as i know the subnet gots an service endpoint for direct sql traffic. the service tier is S3 so i should upgrade this to a higher tier and downgrade afterwards? About ETL i didnt know about that but im looking into it. – RedbeardIntegrator Mar 12 '21 at 08:35
  • Hi @RedbeardIntegrator, If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. – Leon Yue Mar 15 '21 at 06:31
  • @RedbeardIntegrator do you have any other concerns? – Leon Yue Mar 23 '21 at 00:27

1 Answers1

0

Did you think about using ETL tools, such as Azure Data Factory? It has good performance to migrate the big data. Ref this performance table: enter image description here

It supports SQL database and Azure SQL MI. Ref these tutorial:

  1. Copy and transform data in Azure SQL Database by using Azure Data Factory
  2. Copy and transform data in Azure SQL Managed Instance by using Azure Data Factory

It may takes some money but save much time. As we all know, time is money.

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23