CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-database
Champ
Champ

Reputation: 750

Data movement from SQL on-premise to SQL Azure

I have migrated my database schema to SQL Azure, but I have huge(millions) data records to be migrated please suggest me an approach to move data

Approaches I have tried.

  1. SQLAzureMW tool (but it takes 14 hours time, its not feasible for me)
  2. Import export on SQL server(even this is taking time)

Any other approaches ..need help..!!

Upvotes: 1

Views: 208

Answers (2)

user3113204
user3113204

Reputation: 91

If 14 hours using SQLAzure Migration Wizard and your database is Azure compatible, you have 4 other choices:

  • export locally to BACPAC, upload BACPAC to Azure, and import BACPAC to Azure
  • export BACPAC directly to Azure and then import BACPAC to Azure
  • Use SSMS migration wizard with the most recent version of SSMS (includes a number of functional and performance enhancements)
  • Use SQL Server transaction replication - see additional requirements for this option. This last option enables you to incrementally migrate to SQL DB and then when SQL DB is current with your on-premise database, just cut your application(s) over to SQL DB with minimal downtime

For more information, see https://azure.microsoft.com/en-us/documentation/articles/sql-database-cloud-migrate/#options-to-migrate-a-compatible-database-to-azure-sql-database

Upvotes: 0

Michael B
Michael B

Reputation: 12228

For large datasets you usually have to take a more imaginative approach to migration!

One possible approach is to take a full data backup. Ensuring that transaction logs are committed and cleared at the same time.

  • upload, or use Azure Import / Export to get the backup into Azure blob storage
  • syncronise your transaction logs with Azure blob storage
  • Create an Azure SQL database, import backup
  • replay transaction logs
  • Keep in sync with transaction logs until you are ready to switch over.

Upvotes: 2

Related Questions