This post has been republished via RSS; it originally appeared at: Azure SQL Database articles.
In Microsoft Azure, you can easily migrate your databases from SQL Server on-premises or Azure VMs to the fully-managed PaaS database service Azure SQL Database Managed Instance. In this post you will see how to create PowerShell scripts that automate migration to Managed Instance using Azure PowerShell and DbaTools.
The approach used in this article is OFFLINE migration. You should stop the activities on your source SQL Server instance while the databases and server-level objects are copied to the target Managed Instance. If you need ONLINE migration that will enable you to use your source databases while they are moving to Managed Instance, take a look at the Database Migration Service.
Typically, the steps involved in an offline migration process are:
- Create an Azure Blob Storage account that will be used to temporary hold the database backups that will be moved from SQL Server to Managed Instance.
- Back up the databases to Azure Blob Storage and restore them from Azure Blob Storage to Managed Instance.
- Migrate server-level objects such as logins, agent jobs from the source to destination instance.
In this article, I will use Azure PowerShell to create and manage necessary Azure resources, and DBATools PowerShell library to initiate migration.
NOTE: This procedure is based on the assumption that the machine where you are running this script on has access both to the source SQL Server and the target Managed Instance.
Before you begin, you should make sure that you have installed all necessary PowerShell libraries. The following PowerShell modules are used:
As a first step, populate the parameters that define the names of source/target instances and temporary Azure Blob Storage account used in this sample:
Change these parameters depending on your needs. In this sample, I am using a public endpoint to connect to Managed Instance. If you don’t have a public endpoint, you can set up a Point-to-site connection or run the script from the virtual machine that is placed in the same subnet as your Managed Instance.
NOTE: Make sure that you use the same region where your Managed Instance is placed because this will speed-up the database restore process.
In order to move your databases, you need to create a temporary Azure Blob Storage account. If you already have one, you can skip this step.
The following PowerShell script creates an Azure Blob Storage account that will be used in this sample:
Once you execute this script, you will have Azure Blob Storage account ready to be used in the migration.
Source instance setup
Once you create an Azure Blob Storage account, you need to generate a SAS key that will enable your SQL Server instance to access the Azure Blob Storage account and put the database backups there. This SAS key will be represented as a Credential object in SQL Server instance:
Now you are ready to back up your SQL Server databases to Azure Blob Storage.
With the prepared Azure Blob Storage account and credentials, you can take the backups of you SQL Server databases and put them to Azure Blob Storage. I will assume that you have SQL Server 2012 (11.x) SP1 CU2 or higher version that supports BACKUP TO URL. If not, you would need to modify the script slightly, by taking a backup of the database to a local folder and then uploadomg the .bak file from the local folder to Azure Blob Storage container.
The DbaTools command that backup your database to the temporary Azure Blob Storage account is shown below:
You can put a comma-separated list of databases that you want to migrate in the parameter -Database. Once you complete this step, you will have the backups of all databases that are ready to be restored on Managed Instance.
If you are taking a backup of a large database you might want to create a striped backup by using multiple URLs as target, and set of the following options: COMPRESSION, MAXTRANSFERSIZE = 4194304, and BLOCKSIZE = 65536. Please refer to Backing up a VLDB to Azure Blob Storage for more information on how to use these options.
Migrating the databases to the target instance
Now you need to setup the target Managed Instance where you need to create SAS token that will enable the Managed Instance to read .bak file from Azure Blob Storage, create a credential with this SAS token, and restore the database:
Server-level objects migration
Once you migrate your database objects, you might also need to migrate server-level objects such as Agent jobs/operators, Database Mail configurations, server-level logins, etc.
DbaTools provides a set of useful scripts that you can apply to migrate these objects using Copy-Dba commands:
In this sample selected object types from the source SQL Server are copied to target Managed Instance. You can change this part and select the objects that should be migrated.
If your Azure Blob Storage account is temporary storage that you need to use only while you are moving databases, you can remove these resources using the following PowerShell command:
Azure PowerShell and DBATools PowerShell libraries enable you to easily script and automate and customize all parts of the migration process.
This type of migration is an offline migration where you probably need to stop any changes that might happen on the source instance until you finish the migration. As an alternative, you can use Database Migration Service and perform an online migration.