As part of the Azure Database Migration Service (Azure DMS) Public Preview, we have released a PowerShell library to automate bulk database migrations from SQL Server to SQL Azure Database. Currently, the Azure DMS documentation includes a “How to” article for Migrating SQL Server on-premises to Azure SQL Database by using Azure PowerShell. However, several customers have asked for information about how to perform and end to end migration, including schema migration and creation of target infrastructure in Azure, all using a PowerShell script. This blog posting is designed to describe one of the paths you can use to accomplish this goal. First, you need to consider a few general prerequisites:

• Azure DMS requires that an Azure Virtual Network (VNET) be created by using the Azure Resource Manager deployment model, which provides site-to-site connectivity for your on-premises source servers by using either ExpressRoute or VPN. Additional information about creating a VNET is available in the Virtual Network Documentation.
• After the Azure VNET has been created, you need to ensure that the associated Security Group rules do not block the following communication ports 443, 53, 9354, 445, 12000. For more detail on Azure VNET NSG traffic filtering, see the article Filter network traffic with network security groups.
• If you are using a firewall appliance in front of your source database(s), you may need to add firewall rules to allow the Azure DMS to access the source database(s) for migration.
• Windows PowerShell version 5.0 or later is required. To install or update Windows PowerShell to version 5.0 or later, see the article Installing Windows PowerShell.
• Download and install Azure PowerShell from Azure Downloads https://azure.microsoft.com/en-us/downloads/.
• Download and install the AzureRM.DataMigration module from the PowerShell Gallery by using the Install-Module PowerShell cmdlet.
• On the computer hosting the source database, configure the Windows Firewall for access by the SQL Server database engine, as explained in the article Configure a Windows Firewall for Database Engine Access.
• The credentials used to connect to source SQL Server instance must have the CONTROL SERVER

After addressing all prerequisites listed above, on the computer your scripts will run on, download and install Python 3.6 for Windows from https://www.python.org/downloads/.

By default, the Python 3.6 installer provides the 32-bit version, but there’s also a 64-bit version available. It is recommended to use the 32-bit version. Run through installer, select Custom Installation, and then select to install all Optional Features listed on the installer screen. After the installation is complete, set the system’s PATH variable to include the directories that point to the Python components and packages we’ll add later. To do this:

1. In Control Panel, search for Environment; select Edit the System Environment Variables, and then select Environment Variables.
2. In the User Variables section, you either need to either edit an existing PATH variable or create one. If you are creating one, make PATH the variable name and add the following directories to the variable values section as shown, separated by a semicolon. If you’re editing an existing PATH, the values are presented on separate lines in the edit dialog. Select New and add one directory per line.

C:Python36-32
C:Python36-32Libsite-packages
C:Python36-32Scripts

3. Return to the Python installation process, and complete the installation
4. At the command prompt (Start Menu > Windows System > Command Prompt), type Python and then press Enter to load the Python interpreter.

Next, you need to install mssql-scripter, which is a multi-platform command line experience for scripting SQL Server databases. This library is similar to the widely used Generate Scripts Wizard experience in SSMS. You can use mssql-scripter on Linux, macOS, and Windows to generate data definition language (DDL) and data manipulation language (DML) T-SQL scripts for database objects in SQL Server. To install mssql-scripter:

1. At the Python command line, type pip install mssql-scripter, and then press Enter.
2. After the installation is comlete, you can verify that the library installed successfully by typing mssql-scripter -h and pressing Enter.

You should get usage help output as shown below:

3. Now, log in to your Azure subscription, and then start your PowerShell as administrator:

4. Run the Login-AzureRmAccount cmdlet to  launch the log in to the Azure portal, and then in the pop-up window, enter the credentials for the Azure subscription:

After logging in, you should see following detail showing information about your subscription:

5. Use Azure PowerShell to prepare the target environment in Azure:

-  Create a Resource Group. Azure resource groups provide a way to monitor, control access, provision and manage billing for collections of assets that are required to run an application or are used by a client or company department. Azure Resource Manager (ARM) is the technology that works behind the scenes so that you can administer assets by using these logical containers.

I will declare a couple of variables that will hold resource group and Azure region information and use the New-AzureRmResourceGroup cmdlet to create the resource group.

# variables declaration
$resourcegroupname = "my_rg"
$location = "Central US"

#creating the resource group called my_rg in the location East US
$resourceGroup = Get-AzureRmResourceGroup -Name $resourcegroupname -Location $location -ErrorAction SilentlyContinue
if ( -not $ResourceGroup ) {
     New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
     }

-  Create the target logical Azure SQL Server that will store the target Azure SQL databases. Again, I will declare couple of variables, additional to those declared in the script above, that hold server, database, login and firewall IP information needed as parameters.

# variables declaration
$servername = "myServer"
$adminlogin = "admin"
$password = "p@ssword"

# The ip address range that you want to allow to access your server - change as appropriate
$startip = "0.0.0.0"
$endip = "0.0.0.0"

# database 
$databasename = "AdventureWorks2012"
$databasename = @(" AdventureWorks2012_1", “AdventureWorks2012_2”, “AdventureWorks2012_3”)

# List of databases -
$sqlservername =Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $servername -ErrorAction SilentlyContinue
if ( -not $sqlservername) {
New-AzureRmSqlServer -ResourceGroupName $resourcegroupname ` -ServerName $servername `-Location $location `-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Write-Output "Setting up Firewall Rule"
     New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname   -ServerName $servername   -FirewallRuleName "AllowSome" -StartIpAddress $startip -EndIpAddress $endip
     }
     else{
     Write-Output "Azure SQL Server '$servername' already exists"
     }

- Create Azure SQL databases that reside on the logical Azure SQL Server. Use the New-AzureRmSqlDatabase Azure PowerShell cmdlet to run the script below, which will create three AdventureWorks2012 SQL Azure databases (named AdventureWorks2012_1, AdventureWorks2012_2, AdventureWorks2012_3) in Premium Tier (P1).  The following example shows you how to easily migrate multiple databases on the instance in a single migration workflow.

For ($i=0; $i -lt $databasename.Length; $i++) {
     $azuresqldb= Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername  -DatabaseName $sourceDBName -ErrorAction SilentlyContinue
            if ( -not $azuresqldb ) 
            {
                   # Premium Tier.
                   New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname  -ServerName $servername  -DatabaseName $databasename -RequestedServiceObjectiveName "P1"
            }
}

- Generate and deploy the source database schema by using mssql-scripter. To use that library, we need to construct a dynamic call to this library passing in source database information. In the example below, we are scripting AdventureWorks2012 database located on SQL Server instance mysourceSQL with to log in as user sourceAdmin and with password p@ssword.

#variables for source connection info

#generate schema from source via scripter
$File ="C:tempmigrationschema_run.ps1"
Clear-Content $File

#generate SQL Schema file for each db
For ($i=0; $i -lt $databasename.Length; $i++) {
$SQLFile ="C:tempmigration" + $databasename[i] + ".sql"
$dbscripter = "mssql-scripter -S mysourceSQL  -d " + $databasename + " -U sourceAdmin  -P p@ssword  --script-create --exclude-headers --check-for-existence -f C:tempmigration" + $databasename + ".sql --continue-on-error --target-server-version AzureDB --display-progress --exclude-use-database" | Out-File  $File 
}

6. After the mssql-scripter call is created, you can execute it to script out the schema for all databases:

& "C:tempmigrationschema_run.ps1"

7. After the schema is scripted, deploy it by using the following script:

#setup full name for Azure SQL Server
$servername= $servername + ".database.windows.net"

#deploy schema on target
For ($i=0; $i -lt $databasename.Length; $i++) {
sqlcmd -U $adminlogin -S $servername -P $password -d $databasename[i] -j -i "C:tempmigration” + $databasename[i] + “.sql"
}

8. Now that the schema is deployed, to migrate the data for our database, create an instance of Azure DMS named TestDMS on a virtual subnet named MySubnet.

$servicename=”TestDMS”
$sku = "Basic_2vCores"
$virtualSubNetId = “MySubnet"

#create service
$service = New-AzureRmDms -ResourceGroupName $resourcegroupname -ServiceName
$servicename -Location $location -Sku $sku -VirtualSubnetId $virtualSubNetId

9. The next step is to create an Azure DMS migration project named MyDMSProject. It’s here that we enter the source and target types, the instance, and the source database information needed for the migration:

 $ProjectName = “MyDMSProject”

 #create project
 $sourceConnInfo = New-AzureRmDmsConnInfo -ServerType SQL -DataSource " mysourceSQL" -AuthType SqlAuthentication -TrustServerCertificate:$true
 $targetConnInfo = New-AzureRmDmsConnInfo -ServerType SQL -DataSource $servername -AuthType SqlAuthentication -TrustServerCertificate:$true

 #create database info objects by iterating through source db list
 dbInfo = [System.Collections.ArrayList]@()
 For ($i=0; $i -lt $databasename.Length; $i++) {
 $dbInfo = New-AzureRmDataMigrationDatabaseInfo -SourceDatabaseName $databasename[i]
 $dbList.Add ($dbInfo)
 }
 $project = New-AzureRmDataMigrationProject -ResourceGroupName $resourcegroupname -ServiceName
 $service.Name -ProjectName $ProjectName -Location $location -SourceType SQL -TargetType SQLDB -SourceConnection $sourceConnInfo -TargetConnection $targetConnInfo -DatabaseInfo $dbList

10. The last steps required for migration are to pass credentials and mapping tables between the source and the target, and to run the DMS migration task named myDMSTask, which will migrate the data.

- Start by converting credentials to the PSCredential. Because these are server level, we will use same credential for all databases:

$taskName = “myDMSTask”

#convert creds
$secpasswd = ConvertTo-SecureString -String “p@ssword”  -AsPlainText -Force
$sourceCred = New-Object System.Management.Automation.PSCredential (“sourceAdmin”, $secpasswd)
$secpasswd = ConvertTo-SecureString -String $password -AsPlainText -Force
$targetCred = New-Object System.Management.Automation.PSCredential ($adminlogin, $secpasswd)

- Next, map the tables and databases for a task. As all databases in this example are using the same schema, we only need to create one table map (which can be reused) for this migration. For other scenarios, we would create different table map for each task/database migration.

#create table map
$tableMap = New-Object 'system.collections.generic.dictionary[string,string]'
$tableMap.Add("HumanResources.Department", "HumanResources.Department")
$tableMap.Add("HumanResources.Employee","HumanResources.Employee")
$tableMap.Add("HumanResources.EmployeeDepartmentHistory","HumanResources.EmployeeDepartmentHistory")
$tableMap.Add("HumanResources.EmployeePayHistory","HumanResources.EmployeePayHistory")
$tableMap.Add("HumanResources.JobCandidate","HumanResources.JobCandidate")
$tableMap.Add("HumanResources.Shift","HumanResources.Shift")

- Now, for each database, we will map databases between the source and the target, and then run a separate task to migrate data. We will use a GUID as a unique task name needed as a parameter.

For ($i=0; $i -lt $databasename.Length; $i++) {

#select source and target dbs
$taskName =[guid]::NewGuid().toString()
$selectedDbs = New-AzureRmDmsSqlServerSqlDbSelectedDB -Name $databasename[i] -TargetDatabaseName $ databasename[i] -TableMap $tableMap
#run task
$migTask = New-AzureRmDmsTask -TaskType MigrateSqlServerSqlDb -ResourceGroupName $resourceName -ServiceName $service.Name -ProjectName $project.Name -TaskName $taskName  -SourceConnection $sourceConnInfo -SourceCred $sourceCred -TargetConnection $targetConnInfo -TargetCred $targetCred -SelectedDatabase  $selectedDbs
}

11. You can monitor each migration task running by querying the state property of the task as shown in the following example:

 #wait to finish
 $mytask =get-azurermdmstask -Name $migTask.Name -ServiceName $service.Name -ProjectName
 $project.Name -ResourceGroupName $resourcegroupname
 if (($mytask.ProjectTask.Properties.State -eq "Running") -or ($mytask.ProjectTask.Properties.State -eq "Queued")) {
 Start-Sleep -s 15
 }

Additional resources:

• Create a single Azure SQL Database with PowerShell - https://docs.microsoft.com/en-us/azure/sql-database/sql-database-get-started-powershell.
• Migrate SQL Server on-premises to Azure SQL Database by using the Azure DMS PowerShell module - https://docs.microsoft.com/en-us/azure/dms/howto-sql-server-to-azure-sql-powershell
• Mssql-scripter GitHub - https://github.com/Microsoft/mssql-scripter
• Azure Database Migration Service Prerequisites - https://docs.microsoft.com/en-us/azure/dms/pre-reqs
• Azure Database Migration Service FAQ - https://docs.microsoft.com/en-us/azure/dms/faq