Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
ARM templates and corresponding PowerShell script files to manage moving databases into and out of a SQL Database elastic pool.
{
"$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"serverName": {
"type": "string"
},
"serverLocation": {
"type": "string"
},
"elasticPoolName": {
"type": "string"
},
"edition": {
"type": "string",
"defaultValue": "Standard"
},
"requestedServiceObjectiveId": {
"type": "string",
"defaultValue": "d1737d22-a8ea-4de7-9bd0-33395d2a7419"
},
"poolDtu": {
"type": "int"
},
"databaseDtuMin": {
"type": "int",
"defaultValue": 0
},
"databaseDtuMax": {
"type": "int"
},
"storageMB": {
"type": "int",
"defaultValue": 0
},
"databasesCount": {
"type": "int"
},
"databases": {
"type": "array"
}
},
"variables": {},
"resources": [
{
"name": "[concat(parameters('serverName'), '/', parameters('elasticPoolName'))]",
"location": "[parameters('serverLocation')]",
"type": "Microsoft.Sql/servers/elasticpools",
"apiVersion": "2014-04-01-preview",
"properties": {
"edition": "[parameters('edition')]",
"dtu": "[parameters('poolDtu')]",
"databaseDtuMin": "[parameters('databaseDtuMin')]",
"databaseDtuMax": "[parameters('databaseDtuMax')]"
}
},
{
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName') ,'/elasticpools/', parameters('elasticPoolName'))]"
],
"name": "[concat(parameters('serverName'), '/', parameters('databases')[copyIndex()])]",
"location": "[parameters('serverLocation')]",
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2014-04-01-preview",
"properties": {
"requestedServiceObjectiveId": "[parameters('requestedServiceObjectiveId')]",
"elasticPoolName": "[parameters('elasticPoolName')]"
},
"copy": {
"name": "addExistingDatabasesToElasticPool",
"count": "[parameters('databasesCount')]"
}
}
]
}
# ----------------------------------------------------------------------------------
#
# Copyright Microsoft Corporation
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ----------------------------------------------------------------------------------
# PowerShell script for creating/updating an elastic pool and populating it with all databases on a server. Does nothing
# if there are no databases on the server.
# Requires the ARM template, CreateOrUpdateElasticPoolAndPopulate.json
$resourceGroupName = 'MyResourceGroupName' # existing resource group name
$serverName = 'MyServerName' # existing server name
$serverLocation = 'MyServerLocation' # existing server location, e.g. West US
$elasticPoolName = 'MyPoolName' # name of pool to be created
$templateFile = 'TemplateFileLocation' # e.g. C:\Downloads\ArmTemplates\CreateElasticPoolAndPopulate.json
$databases = get-AzureRMSqlDatabase -ServerName $serverName -ResourceGroupName $resourceGroupName | Where DatabaseName -ne 'master'
$databaseNames = $databases | % { $_.DatabaseName }
if($databaseNames.Count -gt 0) {
New-AzureRmResourceGroupDeployment -TemplateFile $templateFile `
-ResourceGroupName $resourceGroupName -serverName $serverName -serverLocation $serverLocation `
-elasticPoolName $elasticPoolName -poolDtu 100 -databaseDtuMax 100 -databaseDtuMin 0 -edition Standard `
-databases $databaseNames -databasesCount $databases.Count
}
{
"$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"serverName": {
"type": "string"
},
"serverLocation": {
"type": "string"
},
"elasticPoolName": {
"type": "string"
},
"databasesCount": {
"type": "int"
},
"databases": {
"type": "array"
},
"requestedServiceObjectiveName": {
"type": "string"
}
},
"variables": {},
"resources": [
{
"name": "[concat(parameters('serverName'), '/', parameters('databases')[copyIndex()])]",
"location": "[parameters('serverLocation')]",
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2014-04-01-preview",
"properties": {
"requestedServiceObjectiveName": "[parameters('requestedServiceObjectiveName')]"
},
"copy": {
"name": "removeDatabasesFromElasticPool",
"count": "[parameters('databasesCount')]"
}
}
]
}
# ----------------------------------------------------------------------------------
#
# Copyright Microsoft Corporation
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ----------------------------------------------------------------------------------
# PowerShell script for evacuating an elastic pool making all databases standalone with a specified service objective.
# Requires the ARM template, EvacuatePool.json
$resourceGroupName = 'MyResourceGroupName' # existing resource group name
$serverName = 'MyServerName' # existing server name
$serverLocation = 'MyServerLocation' # existing server location, e.g. West US
$elasticPoolName = 'MyPoolName' # name of existing pool
$requestedServiceObjectiveName = 'ServiceObjectiveName' # name of service objective for all dbs {Basic|S0|S1|S2|S3|P1|P2|... etc}
$templateFile = 'TemplateFileLocation' # e.g. C:\Downloads\ArmTemplates\EvacuatePool.json
$databases = get-AzureRMSqlElasticPoolDatabase -ServerName $serverName -ResourceGroupName $resourceGroupName -ElasticPoolName $elasticPoolName
$databaseNames = $databases | % { $_.DatabaseName }
if($databaseNames.Count -gt 0) {
New-AzureRmResourceGroupDeployment -TemplateFile $templateFile `
-ResourceGroupName $resourceGroupName -serverName $serverName -serverLocation $serverLocation -elasticPoolName $elasticPoolName `
-databases $databaseNames -databasesCount $databaseNames.Count -requestedServiceObjectiveName $requestedServiceObjectiveName
}
# -------------------------------------------------------------------------
#
# Copyright Microsoft Corporation
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ----------------------------------------------------------------------------------
# PowerShell script for moving all databases from a source pool to a target pool on the same server.
# Requires the ARM template, PopulatePool.json
$resourceGroupName = 'MyResourceGroupName' # existing resource group name
$serverName = 'MyServerName' # existing server name
$serverLocation = 'MyServerLocation' # existing server location, e.g. West US
$sourceElasticPoolName = 'MySourcePoolName' # name of pool from which databases will be moved
$targetElasticPoolName = 'MyTargetPoolName' # of pool into which databases will be moved
$templateFile = 'TemplateFileLocation' # e.g. C:\Downloads\ArmTemplates\PopulatePool.json
$databases = get-AzureRMSqlElasticPoolDatabase -ServerName $serverName -ResourceGroupName $resourceGroupName -ElasticPoolName $sourceElasticPoolName
$databaseNames = $databases | % { $_.DatabaseName }
if($databaseNames.Count -gt 0) {
New-AzureRmResourceGroupDeployment -TemplateFile $templateFile `
-ResourceGroupName $resourceGroupName -serverName $serverName -serverLocation $serverLocation -elasticPoolName $targetElasticPoolName `
-databases $databaseNames -databasesCount $databaseNames.Count
}
{
"$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
"contentVersion": "1.0.0.0",
"parameters": {
"serverName": {
"type": "string"
},
"serverLocation": {
"type": "string"
},
"elasticPoolName": {
"type": "string"
},
"requestedServiceObjectiveId": {
"type": "string",
"defaultValue": "d1737d22-a8ea-4de7-9bd0-33395d2a7419"
},
"databasesCount": {
"type": "int"
},
"databases": {
"type": "array"
}
},
"variables": {},
"resources": [
{
"name": "[concat(parameters('serverName'), '/', parameters('databases')[copyIndex()])]",
"location": "[parameters('serverLocation')]",
"type": "Microsoft.Sql/servers/databases",
"apiVersion": "2014-04-01-preview",
"properties": {
"requestedServiceObjectiveId": "[parameters('requestedServiceObjectiveId')]",
"elasticPoolName": "[parameters('elasticPoolName')]"
},
"copy": {
"name": "addExistingDatabasesToElasticPool",
"count": "[parameters('databasesCount')]"
}
}
]
}
# ----------------------------------------------------------------------------------
#
# Copyright Microsoft Corporation
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
# http://www.apache.org/licenses/LICENSE-2.0
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# ----------------------------------------------------------------------------------
# PowerShell script for populating an elastic pool with all databases on a server.
# Requires the ARM template, PopulatePool.json
$resourceGroupName = 'MyResourceGroupName' # existing resource group name
$serverName = 'MyServerName' # existing server name
$serverLocation = 'MyServerLocation' # existing server location, e.g. West US
$elasticPoolName = 'MyPoolName' # name of existing pool
$templateFile = 'TemplateFileLocation' # e.g. C:\Downloads\ArmTemplates\PopulatePool.json
$databases = get-AzureRMSqlDatabase -ServerName $serverName -ResourceGroupName $resourceGroupName | Where DatabaseName -ne 'master'
$databaseNames = $databases | % { $_.DatabaseName }
if($databaseNames.Count -gt 0) {
New-AzureRmResourceGroupDeployment -TemplateFile $templateFile `
-ResourceGroupName $resourceGroupName -serverName $serverName -serverLocation $serverLocation -elasticPoolName $elasticPoolName `
-databases $databaseNames -databasesCount $databaseNames.Count
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment