Azure SQL has many advantages over the on-premise or local SQL Server instance. For example, high availability, security, and reliability. You may want to migrate your SQL Server database to Azure cloud if you wish to use these facilities.
Migrate SQL Server to Azure SQL database
Let us discuss all the approaches individually.
Approach-1: Using SQL Server Management Studio
In SQL Server Management Studio, you can deploy your SQL Server database to a new Azure SQL database. This is the easiest and most convenient method to migrate your local SQL Server database to the Azure SQL database.
To Migrate SQL Server to Azure SQL database, follow the steps below.
- 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.
3. Click on Next to skip the introduction page.
4. click on Connect to connect to the Microsoft Azure account in the Deployment Settings.
5. Once connected, specify the name for the new database and then select the deployment options like the service tier, storage size etc.
6. Give the path where the database bacpac file will be created temporarily in the local storage. Click on Next.
7. On the Summary page, you can see the summary of your deployment, including information about the source and the target database. Once you have reviewed and verified the deployment, click on Finish.
8. 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 your Azure SQL database’s compute and storage settings after it is created. For this, navigate to your database in SQL Databases and then go to Settings>>Compute + Storage.
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.
Approach-2: 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 and 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:
- Creating a bacpac file
- Importing the bacpac file into the Azure SQL database
Secondly, there are two methods to perform this operation:
- Migration to Azure SQL data using a storage account
- 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 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 dialogue 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 that the file has been 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:
- Using the Azure portal
- 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 the server on which you want to create the database. If you do not have one, you have to make it. 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 required 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 Import from Windows Azure and click on Connect to connect to your Microsoft Azure account.
- A new dialog box will open where you have to click on Sign in and enter your Azure account.
- Then, you must select the storage account and the container where the bacpac file is stored. After choosing the above options, click on OK.
- Now, in the Database Settings, specify the configuration for your new database, for example, the compute and storage plan. Click on Next.
- On 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.
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. It would be best if you had 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.
- Review the specified settings in the Summary page and click 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 you 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 new database’s name, as it will be created in Azure. Also, select the compute and storage settings for the new database. Click on Next.
- In the Summary page, review your selected settings and click Finish to start the migration process.
- This process will take some time, depending on your database size.
- 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 computing and storage settings to match your requirements.
Thus, you might have learned how to migrate your SQL Server database using the bacpac files without using Microsoft storage.
Read How to create table in azure sql database
Approach-3: Using DMA
In this section, you will learn how to 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 link below. 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, you can read our article on how to create a single database in Azure SQL.
- Open Data Migration Assistant and click on the + 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 log in 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 database’s migration status. Here, you can see which tables and views are migrated successfully.
- Once the migration is complete, you can log in to the new database and verify the data.
- 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
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 selecting 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 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 Connection 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 whether the data has been 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 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.
To migrate your database online, you must 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:
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
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
