ARM templates and corresponding PowerShell script files to manage moving databases into and out of a SQL Database elastic pool.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "$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')]" | |
| } | |
| } | |
| ] | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ---------------------------------------------------------------------------------- | |
| # | |
| # 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 | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "$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')]" | |
| } | |
| } | |
| ] | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ---------------------------------------------------------------------------------- | |
| # | |
| # 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 | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ------------------------------------------------------------------------- | |
| # | |
| # 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 | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "$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')]" | |
| } | |
| } | |
| ] | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # ---------------------------------------------------------------------------------- | |
| # | |
| # 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