Skip to content

Instantly share code, notes, and snippets.

@billgib
Last active April 30, 2020 12:57
Show Gist options
  • Save billgib/d80c7687b17355d3c2ec8042323819ae to your computer and use it in GitHub Desktop.
Save billgib/d80c7687b17355d3c2ec8042323819ae to your computer and use it in GitHub Desktop.
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