0

We are supporting a legacy system for our organisation. In the current scenario, we receive a SQL Server backup (.bak files) from the application vendor on an FTP location. For every weekend on Sunday it is a Full backup and for every other day its the differential one.

On our side, we have a SQL server instance running which has custom stored procedures written and scheduled to check the location every morning and then restore the backups every day. These restored backups are then used by the organisation for internal reporting purposes. There are 100s of other stored procedures written for different reports in different DBs on the same instance.

Since SQL Server 2008 is now out of support and for cost-saving purposes of running on-premise system, my team has been given a task to look into migrating this whole system to Azure SQL database.

My question is what is the most effective way in which we can move this workflow to the cloud? I have an azure trial account set up for me to try but haven't been successful in restoring the .bak files on Azure SQL instance.

Thanks.

roman
  • 11
  • 1
  • Azure SQL database doesn't support import .bak file directly. You can store the .bak file to blob storage first and then import them from blob to Azure SQL server. – Leon Yue Jul 08 '20 at 03:00
  • Thanks for the advice. But will the workflow work for a daily restore process? – roman Jul 08 '20 at 07:44
  • I'm not sure, because not all the SQL statements are supported in Azure SQL database. Such as 'USE' statements. It's very hard to answer your question. – Leon Yue Jul 08 '20 at 07:48

1 Answers1

0

You essentially have two options for Azure, either perform a fairly linear Lift and Shift to SQL Server on an Azure VM or go with a more advanced Azure PaaS offering in Azure SQL Database Managed Instance. The specific deployment Azure SQL Database (Single Instance) will not support your current solution requires with regard to the .bak file support, and I have detailed that below. For further details between the difference between Azure SQL Database Single Instance versus Managed Instance, please see: Features comparison: Azure SQL Database and Azure SQL Managed Instance

The second option, is to leverage the Azure Enterprise Ready Analytics Architecture (AERAA) (link) of Azure (PaaS) Analytics services. With Azure SQL Database (PaaS) services, as opposed to on-premise SQL Server or SQL Server on an Azure VM, there is no Integration Runtime or Analysis Services as a bundled service component. These services are separate PaaS offerings and with the help of the linked AERAA blog, you can gain a better understanding of the Azure Analytics services.

The .bak versus .bacpac support dilemma:

Since the main requirement for your solution is support of .bak files, you need to understand where .bak and where .bacpac files are supported. The term Azure SQL Database applies to both a specific deployment option for an Azure SQL database (PaaS) service and as a general term for Azure SQL cloud databases. As for the specific deployment option, Azure SQL Database (Single Instance nor Elastic Pools) will support your scenario with .bak files. This deployment option will support export/import functionality via .bacpac file format. It will not support full/partial restore functionality. The backup/restore functionality although configurable, is only in scope for the specific database hosted by an Azure SQL (logical) Server instance. Basically, you can not restore an external file. You can import, which is always a full copy. So, for that reason, for an Azure PaaS database service you will need Azure SQL Database Managed Instance for .bak file support or deploy an SQL Server VM image to an Azure VM, and migrate your objects via Azure Database Migration Service.

Regards, Mike

Mike Ubezzi
  • 1,007
  • 6
  • 8