As businesses expand and the complexity of data collected escalates, migrating to more reliable and robust database systems is integral. No offense to SQL Server, but the capabilities of the cloud-based service, Azure SQL, is remarkable.
Although there are many similarities among both, Azure’s architecture, its Intelligent Performance feature, exceptional security, and enhanced scalability makes it quite irresistible.
In this article, we’ll discuss the methods by which you can migrate from SQL Server to Azure SQL Server and also the quick steps to do so.
Methods to Migrate From SQL Server to Azure
In general, there are four techniques by which you can migrate from SQL Server to Azure SQL Server. Here’s a glimpse of each.
1. DACPAC
DACPAC is a file that contains the definitions of your database schema, including view, tables, and functions. You can use SSDT, SSMS, or SQLpackage.exe to create and restore it to the Azure SQL database.
However, the DACPAC file will migrate only the schema, and you must employ another method to bring your data to the Azure SQL database.
2. BACPAC
BACPAC is a file similar to DACPAC, but unlike the latter, it encloses both schema and data. Information is stored in JSON format and is ideal for small databases. In this article, we’ll discuss the steps to migrate to Azure SQL Server using this method.
3. Data Migration Assistant/Service
This tool can identify compatibility issues when migrating from your SQL Server along with possible fixes for the same. It has both offline and online migration options to choose from.
The offline option is ideal for small databases or applications that can manage longer downtime. For anything else, we recommend you to go for the online option.
4. Replication
In this method, you can add the Azure SQL Database as a subscriber to a transactional replication. This will allow you to migrate data and objects from your SQL server. However, you can only replicate tables having a primary key.
One of the easiest methods to migrate from SQL Server to Azure SQL Server is by exporting BACPAC files. Let’s look at how it’s done, step by step.
Steps to Migrate From SQL Server to Azure SQL Server Using BACPAC File
Step 1. Exporting BACPAC File in SQL Server Management Studio (SSMS)
In SQL Server Management Studio (SSMS), open and connect the database file.
In order to export the database file, right-click on the database name. In the context menu that opens, click on Tasks and then on Export Data-tier Application.
Now, a new window will appear. In the Introduction page, click on Next> until you reach the Export Settings page.
In the Export Settings page, choose the location where you want to save the BACPAC file. You can save the file on a local disk or your Azure Storage account. For the time being, we’ll be saving the BACPAC file on a local disk. By default, all objects in the database will be selected. If you want to migrate selected ones, visit the Advanced tab.
Click on the Next> button, and you’ll be transferred to the Summary page. You can verify and confirm the settings here. Once you do that, click on the Finish button. Your database will now be exported. If there are some errors, the results will be shown as “unsuccessful”, and you’ll need to rectify each error by clicking on them.
Once all errors are rectified and the database exported, click on the Close button.
2. Importing a BACPAC File to SQL Azure
To create an SQL Azure database from a BACPAC file, connect to the SQL Azure Server first.
Once you have connected, head to the Object Explorer pane and right-click on the database.
In the context menu, click on Import Data-tier Application.
This will open the Wizard window. In the Introduction page, click on Next> until you reach the Import Settings.
In the Import Settings page, you will be instructed to enter the location of the BACPAC file from which you wish to import. As previously mentioned, you can import the file from a local disk or an Azure Storage account. Since we have exported it to a local disk, let’s enter the file location. Once you choose the location of the BACPAC file, click on the Next> button.
Now you’ll be transferred to the Database Settings page. Here, you will be requested to enter a new database name and the maximum database size for your SQL Azure database. By default, the name of the source database will be auto-filled. Once that’s done, click on the Next> button.
You will now reach the Summary page. Once you confirm the information you have entered, click on the Finish button. The importing process will commence.
Just like in the previous section, if there are some errors, you can click on the specific error to find out its cause. Once all errors have been rectified, the database will be successfully imported. Click on the Close button to exit.
3. Validating SQL Azure Import
To confirm that the new database has been created in the SQL Azure Server, check the Object Explorer pane in SSMS and the Microsoft Azure Portal.
Bottom Line
As you can see, migrating from SQL Server to Azure doesn’t have to be daunting or time-consuming. The key is to choose the method you’re most comfortable with, and migrating with the help of a BACPAC file is an easy way to go about it.
About the Author
Terry Webb is a DevOps specialist and Founder of TheOnlineWebb.com, with expertise in database management, information technology, and DevOps. His insights have been featured in some of the most popular IT blogs, and Webb is appreciated for making SQL interesting. The majority of his work involves DevOps consulting and blogging.
