First published on MSDN on Jun 04, 2018
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.