There are number of solutions available to transfer data from one location to another location, I was trying to narrow down to some good practices, and I find them between SQL Sever Integration Service and BACPAC File Export/Import.
SQL Server Integration Service (SSIS)
It only imports data into target schema, and especially if you would like to sync particular schema or limited data, then SSIS would provide you greater control. SSIS - Data Flow would enable you with some interesting patterns, i.e. append only, schema transformation, modified since, OLTP to OLAP (DW or DM). If you just need simple schema mapping, then you can leverage Data Import/Export wizard in SQL Server Management Studio .
Import/Export BACPAC in/from SQL Azure
This will transfer data (including Schema Objects) to Azure Blob Storage as BACPAC thus you would require Azure Storage Service instance. If the target database is on the premise than process could vary based on compatibility (i.e. 2012, 2014 or 2016). You can read Microsoft’s guide to Import a BACPAC File to Create New User Database . To export the BACPAC file, you can use Azure Portal, PowerShell, SQLPackage or SQL Server Management Studio.
Export BACPAC to Blob
$subscriptionId = "YOUR AZURE SUBSCRIPTION ID"
Login-AzureRmAccount
Set-AzureRmContext -SubscriptionId $subscriptionId
# Database to export
$DatabaseName = "DATABASE-NAME"
$ResourceGroupName = "RESOURCE-GROUP-NAME"
$ServerName = "SERVER-NAME"
$serverAdmin = "ADMIN-NAME"
$serverPassword = "ADMIN-PASSWORD"
$securePassword = ConvertTo-SecureString -String $serverPassword -AsPlainText -Force
$creds = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $serverAdmin, $securePassword
# Generate a unique filename for the BACPAC
$bacpacFilename = $DatabaseName + (Get-Date).ToString("yyyyMMddHHmm") + ".bacpac"
# Storage account info for the BACPAC
$BaseStorageUri = "https://storage-name.blob.core.windows.net/BLOB-CONTAINER-NAME/"
$BacpacUri = $BaseStorageUri + $bacpacFilename
$StorageKeytype = "StorageAccessKey"
$StorageKey = "YOUR STORAGE KEY"
$exportRequest = New-AzureRmSqlDatabaseExport -ResourceGroupName $ResourceGroupName -ServerName $ServerName `
-DatabaseName $DatabaseName -StorageKeytype $StorageKeytype -StorageKey $StorageKey -StorageUri $BacpacUri `
-AdministratorLogin $creds.UserName -AdministratorLoginPassword $creds.Password
$exportRequest
# Check status of the export
Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $exportRequest.OperationStatusLinkCredit: Microsoft Documentation – SQL Export Script
Import BACPAC from Blob
$ResourceGroupName = "resourceGroupName"
$ServerName = "servername"
$DatabaseName = "databasename"
$StorageName = "storageaccountname"
$StorageKeyType = "StorageAccessKey"
$StorageUri = "http://$storagename.blob.core.windows.net/containerName/filename.bacpac"
$StorageKey = "primaryaccesskey"
$credential = Get-Credential
$importRequest = New-AzureRmSqlDatabaseImport -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $DatabaseName -StorageKeytype $StorageKeyType -StorageKey $StorageKey -StorageUri $StorageUri -AdministratorLogin $credential.UserName -AdministratorLoginPassword $credential.Password -Edition Standard -ServiceObjectiveName S0 -DatabaseMaxSizeBytes 50000
Get-AzureRmSqlDatabaseImportExportStatus -OperationStatusLink $importRequest.OperationStatusLinkCredit: Microsoft Documentation – SQL Import Script
If you are looking for advanced solution and want to setup schedule export then I would recommend looking into linked GitHub Repository: Auto Export in Azure Automation .
Azure Import/Export – Physical Disk Transfer
Are we taking terabytes or something impractical to achieve over limited bandwidth? Then it would be ideal to explore through Import Export Tool. You can use Azure Import/Export service to transfer data to required region and copied to Blob Storage. This approach would take out the limitation of limited bandwidth and slow transfer rates over the internet or Azure ExpressRoute .
Moreover, follow same steps as mentioned above. Read more about Azure Import Export Service .