Migrate SQL Server to Azure SQL database

Azure SQL has many advantages over the on-premise or local SQL Server instance. For example, high availability, security, and reliability. If you want these facilities, you may want to migrate your SQL Server database to Azure.

• Introduction to Azure SQL
• Migrate SQL Server to Azure SQL database
• Migrating from SQL Server to Azure SQL database using bacpac files
• Migration to Azure SQL data using storage account
• Migration to Azure SQL data without storage account
• Migrate your relational data stored in SQL Server to Azure SQL database
• Migrate your SQL Server database to Azure SQL database using DMA
• Azure database migration service step-by-step
• Migrate SQL Server databases to Azure database with minimal downtime

Introduction to Azure SQL

Azure SQL has three types of databases:

1. Azure SQL database: Provides a number of features as same as in the SQL Server, but lacks some features. But on the other hand, has its own advantages, for example, serverless compute.
2. Azure SQL Managed Instance: Provides all the SQL Server features. You can do almost everything as you can in the SQL Server.
3. SQL Server on Azure VMs: Create a fully independent SQL Server instance, where you have full control over the server as well as the operating system.

You can know more about these on the Microsoft official website.

There are multiple ways that you can use to migrate your SQL Server database to the Azure SQL database. Every method has its advantages and disadvantages. You can select a method according to your requirements.

Migrate SQL Server to Azure SQL database using SQL Server management Studio

In SQL Server management studio, there is an option to deploy your SQL Server database to a new Azure SQL database. This is the easiest and the most convenient method to migrate your local SQL Server database to the Azure SQL database.

Using this method, you will create the Azure SQL database during this operation and you do not need to create the database from the portal. You can follow the below steps to migrate your database to the Azure SQL database:

• Open SQL Server studio and connect to the database you want to migrate.
• In the object explorer window, right click on your database and click on Tasks and then click on Deploy Database to Microsoft Azure SQL database.

• Click on Next to skip the introduction page.
• In the Deployment Settings, click on Connect to connect to the Microsoft Azure account.
• Once connected, specify the name for the new database and then select the deployment options for the database like the service tier, storage size etc.
• Give the path where the database bacpac file will be created temporarily in the local storage. Click on Next.

• In the Summary page, you can see the summary of your deployment like the information about the source and the target database. Once you have reviewed and verified the deployment, click on Finish.

• This process will take some time, depending on the size of your database.
• When all the operations are complete, you can click on Close.

• Now you can verify that the database is created. You can go to the Azure portal and see the newly created database.
• You can also change the compute and storage settings of your Azure SQL database after it is created. For this, navigate to your database in SQL Databases and then go to Settings>>Compute + Storage.

In this way, you can use the SQL Server management studio to migrate your local SQL Server database to the Azure SQL database.

Read Pause and resume Azure SQL database

Migrating from SQL Server to Azure SQL database using bacpac files

You can also migrate your SQL Server database to the Azure SQL database using the bacpac file. A bacpac file contains the schema information as well as the data stored in the database.

There are different ways to do it. But the first thing you need to know is the process. There are two steps involved in the process:

1. Creating a bacpac file
2. Importing the bacpac file into the Azure SQL database

Secondly, there are two methods to perform this operation:

1. Migration to Azure SQL data using storage account
2. Migration to Azure SQL data without storage account

Migration to Azure SQL data using storage account

The storage account in Microsoft Azure is a service that you can use to store your data on the Azure Cloud.

If you have a storage account in Microsoft Azure, you can create and then store your SQL Server database bacpac file in a container in the Azure storage. Later, you can import this bacpac file into a new Azure SQL database.

If you don’t know how to create a storage account and a container to store the bacpac file, you can read our article Backup and restore SQL Server to Azure Blob storage.

This process is explained step by step below:

• Connect to your SQL Server database using SQL Server management studio.
• Right click on the database that you want to migrate and click on Tasks and then click on Export Data-tier Application.

• Click on Next to skip the introduction page.
• Select the option Save to Microsoft Azure.
• Click on Connect to connect to your Microsoft Azure account where you will store your database bacpac file.
• In the new dilaog box, click on Sign in and sign in with your credentials.
• Now you have to enter three things:
  • The subscription you want to use
  • The storage account to use
  • The container in the storage account where you want to store the database file

• Click on OK and also click on Next.
• In the Summary page, review the settings that you have selected.
• Click on Finish to start exporting the database bacpac file to the Azure storage container.
• Once the operation is complete verify if the file is uploaded to your storage container.
• Now you have to import this file into the Azure SQL database.
• There are two ways to import this bacpac file into your Azure SQL database:
  1. Using the Azure portal
  2. Using SQL Server management studio

Read Cannot open server requested by the login

Import bacpac from Azure storage using Azure portal

• In the Azure portal, go to your server on which you want to create the database. If you do not have one, you have to create it. You can refer to our article How to Create a Single Database in Azure SQL.
• In the server Overview, you will see the option to import the database. Click on Import database.

• Then you have to select the source of your bacpac file. Click on Select Backup and select the bacpac file inside your storage container.
• Click on Configure database and specify the configuration for your new Azure SQL database like the service tier, number of vCores, maximum storage size etc.
• Also, select the name of the new database.
• Then specify the type of authentication and the credentials to authenticate to the storage account. Click OK.

• The process will take some time depending on the size of your database. Once the process is complete, you can see that a new database is created on the specified server.

Import bacpac from Azure storage using SSMS

You can also import the database bacpac file from the Azure storage to the Azure SQL database using SQL Server management studio.

You just have to connect to your Azure SQL database server on which you will create the new database through SQL Server management studio.

• In the object explorer window, right click on Databases under your Azure SQL database server and click on Import Data-tier Application.

• Click on Next to skip the introduction page.
• Select the option Import from Windows Azure and click on Connect to connect to your Microsoft Azure account.
• A new dialog box will be opened where you have to click on Sign in and sign in to your Azure account.
• Then you have to select the storage account and the container where the bacpac file is stored. After selecting the above options, click on OK.
• Now in the Database Settings, specify the configuration for your new database, for example compute and storage plan. Click on Next.

• In the Summary page, you can review the settings that you have specified. Click on Finish. Your database will be migrated to the Azure SQL database.

Hence, in this way you can migrate your local SQL Server database to Azure SQL database using the Microsoft storage service.

Read How to Connect to Azure SQL database

Migration to Azure SQL data without storage account

In this approach, you create a bacpac file in your local storage and import it into the Azure SQL database. You need to have SQL Server management studio installed. You will not use the Azure portal for this method.

• Connect to the local SQL Server instance using SQL Server management studio.
• Right click on the database you want to migrate and click on Tasks and then click on Export Data-Tier Application.

• Click on Next to skip the introduction page.
• Select the option Save to local disk.
• Click on Browse to select the path for the bacpac file in the local storage where you want to create it. Also, you have to specify the name for the bacpac file while selecting the path. Click on Next.

• In the Summary page, review the specified settings and click on Finish to start the operation to create the bacpac file.
• Once the file is created, you will see an output like below in the image:

• Now you have to import this bacpac file into the Azure SQL database.
• Connect to the Azure SQL database using SQL Server management studio.
• In the object explorer window, right click on Databases under the server name and click on Import Data-Tier Application.

• Click on Next to skip the introduction page.
• In the Import Settings, select Import from local disk.
• Click on Browse and select the path to the exported bacpac file that you have created. Click on Next.

• In the Database Settings, click on Connect to connect to your Microsoft Azure account which you want to use for the newly migrated database.
• Specify the name for the new database as a new database will be created in Azure. Also, select the compute and storage settings for the new database. Click on Next.

• In the Summary page, review the settings that you have selected and click on Finish to start the migration process.
• This process will take some time, totally depending upon the size of your database.
• Once the process is complete, you will see the output like the below screen:

• Now you can verify if the new database is created in the Azure SQL database. Also, you can change the compute and storage settings according to your requirements.

Thus, you might have learned how you can migrate your SQL Server database using the bacpac files without using Microsoft storage.

Read How to rename a database in Azure SQL

Migrate your relational data stored in SQL Server to Azure SQL database

To migrate your relational data stored in SQL Server to Azure SQL database, you can use the SQL Server management studio Import and Export Wizard. We will explain the process with an example.

Suppose we have an Azure SQL database named DemoDatabase. We want to migrate data from our local SQL Server database named BackupDatabase.

• Open the SQL Server 2019 Import and Export Wizard from the start menu. You can also run it from SQL Server management studio by right clicking on any database and select Tasks >> Export Data / Import Data.
• Firstly, you have to define the source database. Select the Data source to SQL Server Native Client 11.0. Use any of the authentication methods and enter the login credentials. Choose the database that you want to copy and click on Next.

• In the destination database options, select the Destination as .Net Framework Data Provider for SqlServer. Also, in the Connection String enter the connection string to the Azure SQL database.
• To get this connection string, go to your Azure SQL database Settings and select the option Connection strings. Copy the appropriate connection string under the ADO.NET tab and add your login credentials in the connection string.

• Paste this connection string in the Connetion String option in the Choose a Destination dialog box. Click on Next.

• Now select the tables or views that you want to migrate. Click on Next.
• Execute the SSIS package if you want this package to execute again.
• Review once the options that you have selected. Once verified, you can start the process.
• After the process is complete, you can verify if the data is migrated to the Azure SQL database or not.

Thus, you might have learned how you can migrate your relational data stored in SQL Server to Azure SQL database.

Read How to create table in azure sql database

Migrate your SQL Server database to Azure SQL database using DMA

In this section, you will learn how you can migrate your SQL Server database to an Azure SQL database using Data Migration Assistant.

Sometimes, database migration can be a very complex task. Data Migration Assistant is a tool that makes the migration process easy. You can download this tool from the given link. Download Data Migration Assistant.

Firstly, you have to create an Azure SQL database into which you want to migrate your on-premise SQL Server database. If you don’t know how to create a database in Azure SQL database, you can read our article on How to Create a Single Database in Azure SQL.

• Open Data Migration Assistant and click on + icon to create a new project.
• Select the Project type to Migration. You can also assess the database separately by selecting this option to Assessment, but you can also assess the database during the migration process.
• Give the project a name.
• Select the Source server type as SQL Server and the Target server type as Azure SQL Database.
• Select the Migration scope to Schema and data and then click on Create.

• Provide the credentials to login to the source database and click on Connect. Select the source database. The database will be assessed before migration. You can uncheck this option if you want but it is not recommended. Click on Next.

• Provide the credentials to login to the target database and click on Connect.
• Select the target database and click on Next.

• Now you have to select the database objects to create a schema in the target database. After selecting the objects, click on Generate SQL script.
• A script for creating the schema is created. Review this script once and make some changes if you want to. Click on Deploy Schema.
• Once the script is executed, click on Migrate data.

• Select the tables that you want to migrate. In this step, the data in the tables will be migrated. You can see the row count of each table. Click on Start data migration.
• Finally, in the Migrate data tab, you can see the migration status of the database. Here you can see which tables and views are migrated successfully.

• Once the migration is complete, you can login into the new database and verify the data in the database.

• You can see that all the data is migrated from SQL Server database to the target Azure SQL database.

In this way, you can use the Data Migration Assitant to migrate a SQL Server database to an Azure SQL database.

Read Backup Azure database to local SQL Server

Azure database migration service step-by-step

In this section, you will learn how you can migrate your SQL Server database to an Azure SQL database using Azure Database Migration Service.

• Firstly, open the Azure portal and create a database. If you don’t know how to create a database in Azure SSQL database, you can read our article on How to Create a Single Database in Azure SQL.
• Then you have to create the same database schema as it is in your local on-premise SQL Server database.
• To create this database schema, connect to your SQL Server database with SQL Server management studio.
• In the object exploer window, right click on your database, click on Tasks and then click on Generate Scripts.

• This wizard will create a script for creating your database schema. You can run this script on the Azure SQL database and create the same database schema.
• You can also create this script using the Data Migration Assistant tool.

• Once you have created the database with the same schema in Azure SQL database, you are ready to use the Azure Database Migration Service.
• In the Azure portal, search and open Azure Database Migration Services.
• Click on Create to create a new migration service.

• Sepcify the instance details i.e. the subscription to use, resource group under which you will create the new service, migration service name, location of the service and service mode.
• Click on Configure tier to select the service tier and the number of vCores you want to assign to the service. Select Standard for offline migration. The Standard tier is free to use, but you have to face some downtime. Click on Next.

• In the Networking tab, select an existing virtual network or create a new one. This virtual network is needed to by the service to connect to the source and the target databases. Click on Review + create.

• Review the settings that you have specified for the migration service and then click on Create.
• Once you have created the database migration service, you have to create a project under this service.
• In your database migration service Overview, click on New Migration Project.
• Now enter the details of the new projects:
  • Project name: The project name
  • Source server type: SQL Server
  • Target server type: Azure SQL Database
  • Migration activity type: Data Migration

• After entering these details, click on Create and run activity.
• Now enter the source details and the authentication method and credentials in the Select Source tab. Click on Next.
• Then you have to select the database that you want to migrate in the Select databases tab. You can see the expected downtime for the migration process for all your databases. Select the database to migrate and click on Next.
• After that you have to select the target database. You have to specify the details of the target database. Click on Next.
• Then in the Map to target databases tab, you have to select the Azure SQL database into which you want your database to migrate. Click on Next.
• In the Configure migration settings tab, you can select which database objects you want to migrate. Select the objects to migrate and click on Next.
• In the Summary tab, you can review the settings that you have defined for the migration process. Click on Start Migration to start the process.
• After some time, the process will be completed and you can verify the data in the new database.

In this way, you can use the Microsoft Azure Database Migration Service to migrate your on-premise SQL Server database to the Azure SQL database.

Read Cannot open backup device operating system error 50 azure

Migrate SQL Server databases to Azure database with minimal downtime

All the above methods that we discussed above were offline migration methods. Offline migration means that your database will remain offline for some time. This is called downtime.

You may not want to face downtime during the migration process. Therefore, you can use the online migration method to migrate your SQL Server database to an Azure SQL database.

In the online migration method, your database faces minimal downtime. But you have to pay for it. The pricing of this migration method can be seen on this link. This method is suitable for large-scale business workloads.

For migrating your database using the online method, you have to use the Data Migration Service in Azure. We have explained this method in the above section. You just have to select the Premium price tier during the process.

Look at the below image for reference:

Related Posts:

• How to create SQL authentication user in Azure SQL database
• Reset Password Azure SQL database

Hence, in this way you can migrate your SQL Server database to the Azure SQL database with minimal downtime.

• Introduction to Azure SQL
• Migrate SQL Server to Azure SQL database
• Migrating from SQL Server to Azure SQL database using bacpac files
• Migration to Azure SQL data using storage account
• Migration to Azure SQL data without storage account
• Migrate your relational data stored in SQL Server to Azure SQL database
• Migrate your SQL Server database to Azure SQL database using DMA
• Azure database migration service step-by-step
• Migrate SQL Server databases to Azure database with minimal downtime