Create database on Azure SQL Managed Instance using Azure.Rm PowerShell library

  • Article

Azure SQL Managed Instance is a fully managed SQL Server instance hosted in Microsoft Azure cloud that enables you to work with your databases using standard TSQL or PowerShell. In this post you will see how to create your database using PowerShell.

Prerequisites

In Azure SQL Managed Instance, you can use PowerShell to create a new database without connecting directly to your instance and executing CREATE DATABASE T-Sql statement. This is useful if you need to create some automation script that will create databases when they are needed.

You just need to install Azure RM PowerShell to manage your databases. In most of the cases the following  commands might install everything that you need:

 Install-Module PowerShellGet -Force

Install-Module -Name AzureRM -AllowClobber

Then, you just need an access to the subscription where you have some Azure SQL Managed Instances and you are ready to create databases.

Create new database

Once you install PowerShell libraries, you can create a new database using New-AzureRmResurce command, as shown in the following example:

 $subscriptionId = "a8cm4923-06c1-6bde-8758-e7c13a56e9m1"
$location = "West Central US"
$resourceGroup = "my-resource-group"

$managedInstance = "my-managed-instance"
$database = "my-db"

Select-AzureRmSubscription -SubscriptionId $subscriptionId
New-AzureRmResource -Location $location `
                    -ResourceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$database" `
                    -ApiVersion "2017-03-01-preview" `
                    -AsJob -Force

In the first section you need to set the parameters such as your subscription id, data center location, and the resource group where your instance is placed. Then you need set the name of the managed instance (without domain suffix) where you want to create a new database, and a database name.

New-AzureRmResurce command will create a new resource on the specified location using the formatted resource id. Optional parameter -AsJob specifies that the command should run asynchronously so it will complete immediately even if database creation time might be longer.

As a result, you will see a new empty database in your managed instance.

Create database as copy

You can also create a database as a copy of the existing database on managed instance. In addition to copy, you can specify a point in time in the past of the original database if you don’t want to create a copy of the latest state.

The following script will create a copy of existing database:

 $subscriptionId = "a8cm4923-06c1-6bde-8758-e7c13a56e9m1"
$location = "West Central US"

$resourceGroup = "my-resource-group"
$managedInstance = "my-managed-instance"
$database = "my-db"

$pointInTime = Get-Date # or "2018-06-01T08:51:39.3882806Z"

$targetDatabase = "my-db-copy"

$properties = New-Object System.Object
$properties | Add-Member -type NoteProperty -name CreateMode -Value "PointInTimeRestore"
$properties | Add-Member -type NoteProperty -name SourceDatabaseId -Value "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$database"
$properties | Add-Member -type NoteProperty -name RestorePointInTime -Value $pointInTime

Select-AzureRmSubscription -SubscriptionId $subscriptionId

New-AzureRmResource -Location $location `
                    -Properties $properties `
                    -ResourceId "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.Sql/managedInstances/$managedInstance/databases/$targetDatabase" `
                    -ApiVersion "2017-03-01-preview" `
                    -AsJob -Force

The script is similar to the previous one with a difference in $properties object where you can specify that you want to create a database using PointInTimeRestore method from the source database.

When this job finishes, you will get a new database as a copy of source database at some point in time in the past.