Testing On-Prem to Azure SQL Data Transfer

Question

Hi Guys,
my apologies if this is covered on a blog post somewhere.

Please just point me in the right direction and I will go read what must already be out here.

We are doing out first testing with our own ETL software with Azure SQL as a target.

I have just downloaded the adventure works InternetSalesFacts test table from a dev server and sent it to azure in western europe via the ODBC connection capability.

It is very slow. 60K records taking 41 minutes to send across.

Obviously we are just looking in to this and testing.

We tried linking the azure sql table just as a linked table so we could insert into it as a linked table but we couldn't get that to work. (Newbies to Azure that we are.)

So if anyone can point me to the blog posts I should be reading about linking on prem sql server databases to azure databases, or how unloading and sending data to azure sql I would much appreciate it.

I am thinking there will be loader commands that zip the files and send them across.

We have done this sort of thing before without cloud hosting where we use our ETL software to zip and ftp to the hosting service.

But we are brand new to Azure and just testing.

Thanks and Best Regards

Peter

Answer 1

Peter,

You can create a bacpac of your local database and store it in your local drive or on an Azure STorage Account, and then import it to Azure SQL Database using sqlpackage as explained here. That is if you want to move the whole database.

sqlpackage.exe /Action:Import /tsn:tcp:MyServer.database.windows.net,1433 /tdn:AdventureWorks /tu:AdminUser /tp:AdminPassword1 /sf:C:\temp\AW.bacpac /p:Storage=File  

You can also use Data Migration Assistant to migrate/copy your local databases to Azure SQL.

In addition you can use the linked servers to insert rows on an Azure SQL Database as explained here. This method has poor performance when loading a set of rows.

You can also copy tables and rows from on premises to Azure SQL using Azure Data Factory as explained here.

ALternatively, you can use the traditional SSIS packages to send data from on-premises databases to Azure SQL as explained here.

You can also have local tables sync with Azure SQL tables using SQL Data Sync tool.

Finally you can put CSV files on Azure Storage Account and have those CSV files imported as explained here. Azure Data Factory can help you do the same.

Hope this helps.