Monday, November 8, 2021

Migrate SQL Server on Linux using the Azure SQL Migration extension for Azure Data Studio

This blog is authored by Kevin Barlett (Senior Customer Engineer, Customer Success Unit) and reviewed by Mohamed Kabiruddin (Senior Program Manager, Azure SQL)


In this article, we'll detail the process of migrating from SQL Server running on a Linux distribution to Azure SQL Managed Instance using the Azure SQL Migration extension in Azure Data Studio. 


When using the Azure SQL Migration extension to migrate your SQL Server database(s) running on Linux, the process is identical to SQL Server running on a Windows Operating System as detailed in the online or offline migration tutorials to Azure SQL Managed Instance.  The key difference, specifically when using a network share as the source for database backups, is the format and underlying configuration of the network share for Linux Operating System. 


When SQL Server is hosted on a Linux Operating System, the BACKUP DATABASE syntax shown below is not valid.  Linux does not natively support the SMB/CIFS protocols. 






At the same time, the Azure SQL Migration extension does not support entering a local filesystem path as a source for backup files. This is not a limitation of the extension but instead is expected behavior as a result of the use of the self-hosted integration runtime (SHIR) to perform the backup file copy process







So how do databases written to a location on the local Linux filesystem find their way to a SMB file share that is compatible with the Azure SQL Migration extension? 

The answer is Samba / Azure Files file share and the CIFS-UTILS package.  Combined, these utilities unlock multiple migration scenarios from SQL Server on Linux to Azure SQL Managed Instance.


Let’s walk through several scenarios using Samba / Azure file share to enable database migrations from SQL Server on Linux using the Azure SQL Migration extension in Azure Data Studio.  

Note: This article leverages Samba in some of the scenarios listed below to enable easy migration from SQL Server on Linux to Azure. See install and configure Samba to learn more about the setup.


Migration Scenario 1 – Mount and write backups to an Azure Files file share

In this scenario, we’ll mount an Azure file share on the Linux server file system and write database backups there. 

On the Azure file share page in the Azure portal page, choose Connect.




The Connect blade will appear to the right.  In this example, we want to mount our migrationbackups Azure Files file share on a Linux server, so we’ll choose Linux.  Doing so will present the pre-written script necessary that performs the following:

  1. Create the /mnt/migrationbackups directory (remove that line if you have that directory already exists)
  2. Create a secured file containing the Azure file share credentials
  3. Add an entry to the /etc/fstab configuration file to ensure the Azure file share persists through server restarts
  4. Mounts the Azure file share to the /mnt/migrationbackups on the local server filesystem



After successful execution of the script, let’s backup the NorthWind database from our SQL Server on Linux environment to the /mnt/migrationbackups local directory.  Similar to the configuration described in the first migration scenario, the backup is actually being written to the file system that is mounted to /mnt/migrationbackups, which in this case is an Azure Files file share.








Now we can use the Azure SQL Migration extension in Azure Data Studio to migrate the NorthWind database, specifying the Azure file share as our source backup location.




Note that the Windows user account information you provide when using an Azure file share as the source backup location is slightly different.  Here, the Windows username is specified as localhost\storageAccountName  So in the example above, the Windows user for the Azure Files file share is localhost\myairsstorage.  For the file share password, use the value in the password= portion of the original script used to mount the Azure Files file share.




Migration Scenario 2 – Create an SMB share on a Linux OS

In this configuration we’ll use Samba to make the Linux file system location appear to be a SMB share and therefore enable it to be used with the Azure SQL Migration extension. 

This time we’ll backup the NorthWind database to the /var/opt/DBbackups/NorthWind directory.  That directory is configured within Samba to act as a SMB share (like a “Windows share”).   





And again, using Samba, you can connect to the BackupShareOnLinux SMB share that is hosted on the Linux server.   




Furthermore, that SMB file share path can now be used in the Azure SQL Migration extension to perform database migrations. 




Migration Scenario 3 – Mount an SMB share on a Windows OS

In this scenario, the SQL Server instance is hosted on Ubuntu 16.04 LTS as shown below. 




Let’s take a backup of the NorthWind database and write it to /var/opt/SQLbackups.  To SQL Server, this looks and behaves as if it were a local filesystem on the Linux server. 




Looking at the backup history tables in the msdb database, we see that the database backup was successfully written to /var/opt/SQLbackups/NorthWind/NorthWind_FULL.BAK. 




And we can also see the backup file in /var/opt/SQLbackups/NorthWind in Linux. 




But using the power of Samba and CIFS-UTILS, the NorthWind database backup was actually written to the \\SAN0\DatabaseBackups\NorthWind SMB share.




Now we can supply the SMB file share path in the Azure SQL Migration extension to perform the migration to Azure SQL Managed Instance. 





In this article we described three database migration scenarios for SQL Server on Linux using Samba, Azure file share and CIFS-UTILS utilities with the Azure SQL Migration extension in Azure Data Studio.  


Happy migrations!

Posted at