0

I need to copy data from an on-premise SQL Server to Azure SQL Server (on existing database).

I've tried through a .bacpac file (no good, it create a new database) and with SQL Server Management Studio (generate script, tables..) but it requires 30 minutes for about for 300 MB data.

Is there a faster way?

Improve this question
2
  • You can backup from SQL Server directly to Azure DB or managed instance, could you do that and then use it as a data source to merge your target tables.
    – sTTu
    Commented Apr 17, 2023 at 14:33
  • You you just want to "upsert" then Azure Data Factory can do it by connecting to the on-prem as the source and Azure SQL Database as the target.
    – Zorkolot
    Commented Apr 18, 2023 at 14:54

2 Answers 2

Reset to default
1

You have several options for migrating from on premises SQL Server to Azure SQL Database. The main ones are:

  1. the Azure SQL Migration extension in Azure Data Studio - I believe this is the currently recommended option but from experience I can tell you it doesn't handle schema or data type changes well
  2. Azure Migrate
  3. Azure Data Factory - this is the best option I've found if you have schema changes

However for all options, you'll want to time them to determine if they're fast enough for your requirements by testing them.

If you have tight deadlines in which to do the upgrade, e.g. a short maintenance window on a production application database, you could also consider transactional replication as this would allow you to minimise the time taken to switch from on premises to Azure.

Microsoft has a comprehensive Migration guide which covers a number of options, considerations & limitations.

Improve this answer
1
  • You can create a linkedserver & just use 4 part naming
    – Stephen Morris - Mo64
    Commented Apr 26, 2023 at 5:43
0

You could also use the copy activity in Azure synapse to copy data from an on-premises SQL Server to Azure SQL Server (on existing database). Please follow the below tutorial in the doc https://learn.microsoft.com/en-us/azure/data-factory/tutorial-hybrid-copy-data-tool

Is there a faster way?

It depends on the SHIR configuration (for on-prem) and the Azure SQL region.  But 300 MB is small data. it will be fast.  If it is for prod and you want very minimum down time, then you will need to choose other options. You can set up replication between on-prem and Azure SQL.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.