Restore to Azure SQL PAAS database a bak file?

0
votes

Okay so I figured since Azure has a lot of tools and I am failing at best processes to just ask on SO.

What I can do:

BACPAC creation

Creation is directly from SSMS with hovering over the database in Object Explorer. Right Click>Tasks>Export Data Tier Application. Choose a disk location. The caveat is generally to not have a database that is in use querying. So have a copy or system that can be not using the database.

BACPAC restore to Azure

  1. SqlPackage.exe https://docs.microsoft.com/en-us/sql/tools/sqlpackage?view=sql-server-2017

    When I do it it is similar to: (must have Visual Studio or equivalent Sql management object DLLs installed) "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" /a:Import /sf:(bacpac Location) /tdn:(dbName)/tsn:bvtest.database.windows.net /tu:(user) /tp:(password)

  2. In Azure>SQL Servers>(Top ribbon)>Import database>(have blob storage with a bacpac created to it already). This method appears slightly faster if they are both on same region.

My question is that I have seen articles like this one: https://docs.microsoft.com/en-us/sql/azure-data-studio/tutorial-backup-restore-sql-server?view=sql-server-2017 as well as knowing SSMS pretty well. It seems all options to restore to Azure SQL PAAS are turned off for a 'bak' backup and you have to make a bacpac for going instance to Azure PAAS. The problem is that this takes hours just to make the bacpac on a relatively medium sized database of 120 gigs. Then it takes hours to restore. Other than what I have tried above is there a faster way?

1
azureazure-sql-databaserestoreazure-sql-server
Is this a one time data migration scenario where you cannot afford a huge downtime? – Kalyan Chanumolu-MSFT
@KalyanChanumolu-MSFT Yes for the most part. I am working on an existing system with an AZURE SQL PAAS on an elastic pool. So I have some flexibility to potentially take a long time to get initial baseline and then do a differential. But I cannot afford a six hour cutover to do about 3 hours for a bacpac and 3 hours for a restore. – djangojazz

1 Answers

1
votes

You can use Azure Data Migration Service (ADMS) instead of importing/exporting bacpacs. Azure Data Migration Service allows you to migrate data from SQL Server to Azure SQL Database with no downtime if your database does not have "heaps". If your database has heaps you will have to perform an offline migration. Copying/migrating data from a SQL Server ARM (IaaS) Premium Storage (200 GB / 790 tables) to an Azure SQL Database (Premium) takes 1 hour 40 minutes with an offline migration. That is the fastest data migration I have seen as a workaround to import/export bacpacs since SQL Server native backups are not supported on Azure SQL Database (DTU-model / Vcore model).

ADMS performs a validation of your data at the end, if you want.