Migrating SQL Server Database to Azure SQL Database

Multiple ways for Migrating on premise SQL Server database to Azure Database
  • Create BACPAC files and export them
  • Use SQL Server Management Studio; generate scripts and use the import/export data methods
  • Use the Data Migration Assistant Wizard
  • Leverage Transactional Replication to port the data over to the cloud
  • Use PowerShell and SQLPackage.exe, an SSDT tool
In this article, we will focus on how to migrate SQL Server database to Azure SQL Database using BACPAC file

We will guide in an easy way with clearly defined steps to be followed in migrating On-premise SQL Server database to Azure SQL Database. Before going into details, it is must to have a good understanding of some of the terms:

Glossary
BACPAC File
  • A BACPAC file is a ZIP file with an extension of BACPAC containing the metadata and data from the database i.e. SQL Server in our case
  • A BACPAC file can be stored in Azure Blob storage or in local storage in an on-premise location and later imported back into Azure SQL
Azure SQL Database
  • Microsoft Azure SQL Database is a managed cloud database provided as part of Microsoft Azure.
Cloud database
  • A cloud database is a database that runs on a cloud computing platform, and access to it is provided as a service. Managed database services take care of scalability, backup, and high availability of the database
Index
  • An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table.
  • An index contains keys built from one or more columns in the table or view.
  • These keys are stored in a structure (B-tree) that enables SQL server to find the row or rows associated with key values quickly and efficiently
Clustered Index
  • Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition.
  • There can be only one clustered index per table, because the data rows themselves can be stored in only one order.
  • The only time the data rows in a table are stored in sorted order is when the table contains a clustered index.
  • When a table has a clustered index, the table is called a clustered table. If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
Pre-requisites for Migrating to Azure Database
  • Consistency - Ensure that no write activity is occurring during export i.e. you are exporting a transactionally consistent copy of your database.Size of BACPAC File - If you are exporting to blob storage, the maximum size of a BACPAC file is 200 GB. To archive a larger BACPAC file, export to local storage.
  • Time to Export - f the export operation exceeds 20 hours, it may be canceled. To increase performance during export, you can:
    1. Temporarily increase your compute size.
    2. Cease all read and write activity during the export.
    3. Use a clustered index with non-null values on all large tables. Without clustered indexes, an export may fail if it takes longer than 6-12 hours. This is because the export service needs to complete a table scan to try to export entire table. A good way to determine if your tables are optimized for export is to run DBCC SHOW_STATISTICS and make sure that the RANGE_HI_KEY is not null and its value has good distribution. For details, see DBCC SHOW_STATISTICS
Exporting database using export data-tier application

Create BACPAC files using Management Studio

  • Verify that you have the latest version of SQL Server Management Studio. New versions of Management Studio are updated monthly to remain in sync with updates to the Azure portal
  • Note: It is recommended that you always use the latest version of Management Studio to remain synchronized with updates to Microsoft Azure and SQL Database.Update SQL Server Management Studio
  • Open SQL Server Management Studio (SSMS) and connect to the source database in Object Explorer. In this case, the source database is AdventureWorks2016
  • Right-click AdventureWorks2016 database in Object Explorer, point to Tasks, and click Export Data-Tier Application…
  • In the export wizard, click Next to bypass the default setting page
  • In the Export Settings tab, configure the export to save the BACPAC file to either a local disk or to Azure blob storage and click Next

Note: A BACPAC file will only be saved if you have no database compatibility issues. If there are compatibility issues, then the error message will be displayed on the console

  • In this case, let us follow the default settings
  • Click the Advanced tab and clear the Select All checkbox to skip exporting the data. Our goal at this point is only to test for compatibility
  • Select the Save to Microsoft Azure option to save the bacpac file to Azure Blob storage
  • Next, go to the Azure portal to get the storage account details. In this case, dbmigratestg is the storage account created for this exercise
  • Select the storage account and copy the access key. Paste the key in SQL Server Management Studio and click Connect
  • Now, you can access the Azure Blob storage
  • Click Advanced. In this pane, you can select the intended objects that are going to be part of the migration
  • Before you click the Finish button, let us validate the summary in detail
  • Click Next and then click Finish
  • The Export wizard performs the database compatibility checks. If any issues are found, they will appear after the wizard validates the schema
  • If no errors appear, the database is compatible and it is ready to migrate. If you encounter errors then you will need to fix them before proceeding further. To see the errors, click Error for Validating schema
  • Finally, the preparation phase to migrate on-premises SQL Server database to Azure SQL Database completed
Importing file using Import database option

In the process of migrating on-premises SQL Server database to Azure SQL Database, now we do the import of .bacpac file.

Importing the BACPAC file to Azure

Now that we have the BACPAC file ready, we can now use the same to migrate the data over to Azure. This BACPAC file could be stored locally, or on Azure Blob storage (standard)

For now, the Azure portal will allow you to only create a single database in Azure SQL Database, and this can be done only from a BACPAC file. Follow the steps below to import the BACPAC file you saved.

  • Connect to Azure portal and open the SQL database page. Navigate into your resource group and create a new instance of an Azure Database (this is out of the scope of this article). The Assumption is that Azure PaaS SQL Database is available
  • Next, select the Import database on the toolbar
  • Next, locate the Blob storage account and its respective container for the BACPAC file
  • Type in the new database name, size, and the SQL admin credentials
  • Clicking OK will begin the process of importing the BACPAC file into the new Azure Database, and you should be good to go
  • Note:I recommend going for a higher DTU; higher DTU results in a higher speed of operation. Once you are set up with Azure Database, scaling the database to a higher service tier is rather simple. After the import is complete, you could even scale down to a lower tier to suit your needs