Thought Leadership
Banner Image
February 14, 2017
Backup “on premise” SQL database and restore to Azure SQL Database
Bhushan Thorat
Tags: technology

Introduction

The overall business is now moving onto Cloud platform. Microsoft Azure is a cloud computing service and infrastructure created for building, deploying, and managing applications and services through a global network of Microsoft-managed data centers. The most important thing in migrating to Cloud is to get our databases migrated to cloud without any hiccups.

There is no way to restore a simple backup file to SQL Azure; as there is no option in Azure to do this restore, instead we can migrate a database by various ways.

There are multiple ways to migrate an “on premise” database to AZURE. Below mentioned are some of the methods and a brief description about each.

  1. SQL Azure Migration Wizard

Firstly, it is a “Codeplex” product and not a Microsoft product used for migration of “on premise” DB to Azure.

To start with, make sure the server is setup and configured correctly

Make sure the IP address is correct and consider the DB size as well. As Azure support database up to 500 GB at the moment.

It is an open source. Which means, it is not a black box. You can practically go through the code.

Another feature is that it not only analyses the database, but it can also analyze a trace file generated from an old server to check whether the statements used are dynamic in nature and whether they are compatible or not.

This tool can be used to migrate the schema only or schema & data or data only

Further, we can migrate selective database objects. It could be only 1 table or only a few tables and stored proc or views.

In the result summary, there might be some output in Red or Brown. Mostly it is for tables with no indexes. Note that in Azure, we need to have cluster index on each and every table.

  1. Generate Scripts

You can use this option through SSMS 2008 R2 or later. The ‘Generate Scripts’ option does have a feature to make SQL Azure compliant script. It can also script data. But it is recommended to use method one if you want to migrate data, too. SQL Azure Migration Wizard uses BCP tool, which dumps the data into binary files, while SSMS just generates inline INSERT Statements

  1. Using ‘Bacpac’ files

A BACPAC is a Windows file with a ‘.bacpac’ extension that captures a database’s schema and data.

To create a ‘bacpac’ file, Right-click the source database in the Object Explorer, point to ‘Tasks’, and click ‘Export Data-Tier Application’.

iii

To import BACPAC file, connect to your Azure SQL Database server, right-click the Databases folder and click on ‘Import Data-tier Application’

iiii

Provide the New database name for the database on Azure SQL DB, set the Edition of Microsoft Azure SQL Database (service tier), Service Objective (performance level) and Maximum database size.

oo

Click the ‘Next’ button and then click on ‘Finish’ to import the BACPAC file into a new database in the Azure SQL Database server.

References

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate-compatible-import-bacpac-ssms

https://channel9.msdn.com/Shows/Data-Exposed/SQL-Database-Migration-Wizard

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate-compatible-import-bacpac-ssms

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-cloud-migrate-compatible-export-bacpac-ssms

Comments
MORE ARTICLES BY THE AUTHOR
RECENT ARTICLES
RELATED ARTICLES